MySQL 锁表重做 主从同步

1. 主库锁表操作

FLUSH TABLES WITH READ LOCK;

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+------------------+-----------+--------------+-------------------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB              |
+------------------+-----------+--------------+-------------------------------+
| mysql-bin.000580 | 111007220 |              | mysql,information_schema,test |
+------------------+-----------+--------------+-------------------------------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+-----------+--------------+-------------------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB              |
+------------------+-----------+--------------+-------------------------------+
| mysql-bin.000580 | 111007220 |              | mysql,information_schema,test |
+------------------+-----------+--------------+-------------------------------+
1 row in set (0.00 sec)

2. 备份 数据库

# mysqldump -uroot -p123456 aikaiyuan > aikaiyuan.sql

备份的 aikaiyuan.sql 发送到 从库上

3. 恢复锁表

备份完成之后, 在解除 锁表

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+-----------+--------------+-------------------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB              |
+------------------+-----------+--------------+-------------------------------+
| mysql-bin.000580 | 111050538 |              | mysql,information_schema,test |
+------------------+-----------+--------------+-------------------------------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+-----------+--------------+-------------------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB              |
+------------------+-----------+--------------+-------------------------------+
| mysql-bin.000580 | 111099746 |              | mysql,information_schema,test |
+------------------+-----------+--------------+-------------------------------+
1 row in set (0.00 sec)

4. 关闭从库 同步状态

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

5. 恢复数据库

mysql -uroot -p123456 aikaiyuan < aikaiyuan.sql

6, 开启同步

slave stop;

reset slave;

change master to master_host='主机IP',master_user='master',master_password='master',master_log_file='mysql-bin.000580',master_log_pos=111007220;

slave start;

转载请注明：爱开源 » MySQL 锁表重做 主从同步