MHA实现mysql主从数据库手动切换的方法,mhamysql主从数据库
本文实例讲述了MHA实现mysql主从数据库手动切换的方法,分享给大家供大家参考。具体方法如下:
一、准备工作
1、分别在Master和Slave执行如下,方便mha检查复制:
grant all privileges on *.* to 'root'@'10.1.1.234' identified by 'rootpass';
grant replication slave on *.* to 'jpsync'@'10.1.1.231' identified by 'jppasswd';
grant replication slave on *.* to 'jpsync'@'10.1.1.234' identified by 'jppasswd';
flush privileges;
2、将master设置为只读
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
交互模式:
或非交互模式:
二、切换完以后,如何让10.1.1.231为主,10.1.1.234为从,操作步骤:
1、主上执行:
+-------------------------+----------+--------------+--------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+--------------------------------------+-------------------+
| mysql-master-bin.000013 | 120 | denovo_ng | mysql,denovo,test,information_schema | |
+-------------------------+----------+--------------+--------------------------------------+-------------------+
1 row in set (0.00 sec)
2、在10.1.1.234上执行如下sql命令;
master_password='jppasswd', master_log_file='mysql-master-bin.000013',master_log_pos=120;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.231
Master_User: jpsync
Master_Port: 63306
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000013
Read_Master_Log_Pos: 120
Relay_Log_File: compute-0-52-relay-bin.000002
Relay_Log_Pos: 290
Relay_Master_Log_File: mysql-master-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3、查看master状态,并测试
+-----------+------+-------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+-------+-----------+--------------------------------------+
| 1052 | | 63306 | 1025 | e25a3e4a-39c0-11e4-80cb-00259086c4b6 |
+-----------+------+-------+-----------+--------------------------------------+
1 row in set (0.00 sec)
主库10.1.1.231上插入记录
Query OK, 1 row affected (0.00 sec)
从库查询记录已经存在
+-----------+-----+-----------+--------------+----------+----------------+--------------+
| id | tag | ticket_id | candidate_id | duration | source_file_id | source_start |
+-----------+-----+-----------+--------------+----------+----------------+--------------+
| 555551111 | 1 | 55555 | 99999 | 44.11 | 2222 | 91919 |
+-----------+-----+-----------+--------------+----------+----------------+--------------+
1 row in set (0.00 sec)
4、更新配置文件:
更新主库my.cnf配置添加
注意:防止重启数据库,启动slave进程,导致数据不一致。
更新从库my.cnf配置添加,设置slave库为只读:
relay_log_purge=0
然后重启主库和从库,观察库的信息:
主库信息:
+----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1 | jpsync | 10.1.1.234:49085 | NULL | Binlog Dump | 17 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 2 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
mysql> show master status;
+-------------------------+----------+--------------+--------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------------+----------+--------------+--------------------------------------+-------------------+
| mysql-master-bin.000014 | 120 | denovo_ng | mysql,denovo,test,information_schema | |
+-------------------------+----------+--------------+--------------------------------------+-------------------+
1 row in set (0.00 sec)
从库信息:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.231
Master_User: jpsync
Master_Port: 63306
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000014
Read_Master_Log_Pos: 120
Relay_Log_File: compute-0-52-relay-bin.000005
Relay_Log_Pos: 290
Relay_Master_Log_File: mysql-master-bin.000014
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1 | system user | | NULL | Connect | 58 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 58 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 3 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
希望本文所述对大家的MySQL数据库程序设计有所帮助。
1.主服务器:
#Master start
log-bin="d:/log/mysql/mysql_log_bin"
server-id=1
#Master end
2.从服务器:
#Slave start
log-bin="D:/log/mysql2/log-bin.log"
relay_log="D:/log/mysql2/relay-log-bin"
#从机id,区别于主机id
server-id=2
#主机ip,供从机连接主机用
#master-host=localhost
#主机端口
#master-port=3300
#刚才为从机复制主机数据新建的账号
#master-user=slave
#刚才为从机复制主机数据新建的密码
#master-password=654321
#重试间隔时间10秒
#master-connect-retry=10
#需要同步的数据库
#replicate-do-db=test
#启用从库日志,这样可以进行链式复制
log-slave-updates
#从库是否只读,0表示可读写,1表示只读
read-only=1
#只复制某个表
#replicate-do-table=tablename
#只复制某些表(可用匹配符)
#replicate-wild-do-table=tablename%
#只复制某个库
#replicate-do-db=dbname
#不复制某个表
#replicate-ignore-table=tablename
#不复制某些表
#replicate-wild-ignore-table=tablename%
#不复制某个库
#replicate-ignore-db=dbname
#Slave end
3.对从服务器制定主服务器使用CHANGE MASTER 语句
注意:1.一定要在主服务器上创建一个可以执行replication的用户
2.该用户名在从服务器上可远程登录到主服务器。
3.开启MySQL的log-bin日志功能
参考资料:blog.163.com/...31959/
不是很清楚,我不是用这个数据库。