1、锁定正常库
flush table with read lock;
锁定后查询正常,数据不能修改插入操作。
2、导出库中的数据
mysqldump -uroot -p123456 --opt -R test > /home/bakdb.sql
注:如果出现异常信息(mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect)使用下面命令,socket在my.cnf文件中
mysqldump --socket=/var/lib/mysql/mysql.sock -uroot -p123456 --opt -R test > /home/bakdb.sql
3、查看主备数据库的信息
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 726 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 2060 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4、在从库上停止slave
stop slave;
5、导入备份的数据文件
mysql> source /home/bak.sql
6、重新设置同步节点
下面操作解锁库,并关闭slave后进行操作
stop slave;
unlock tables;
CHANGE MASTER TO master_host = '192.168.123.82',
master_port = 3306,
master_user = 'root',
master_password = '123456',
master_log_file = 'mysql-bin.000003',
master_log_pos = 2060;
CHANGE MASTER TO master_host = '192.168.123.78',
master_port = 3306,
master_user = 'root',
master_password = '123456',
master_log_file = 'mysql-bin.000006',
master_log_pos = 726;
7、正常从库异常不需要上面设置,重新启动后会同步数据。