mysql slave同步_mysql slave重新同步数据

查看master状态

mysql> show master status\G;

*************************** 1. row ***************************

File: mysql-bin.000002 #binlog正在写入的二进制文件,等下回用到

Position: 13465 #这个信息下面也会用到

Binlog_Do_DB: learn

Binlog_Ignore_DB: mysql,information_schema,performance_schema

Executed_Gtid_Set: cea1268e-c2c9-11e8-9e7d-00502ab853ae:1-56

1 row in set (0.00 sec)

master节点进行锁表,防止写入新数据

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)

备份主库

[root@node1 ~]# mysqldump -uroot -pbaiyongjie learn --set-gtid-purged=OFF > learn.0930.sql

在slave节点还原数据库

[root@node1 ~]# scp learn.0930.sql node2:/root

root@node2's password:

learn.0930.sql 100% 2910 1.9MB/s 00:00

[root@node2 ~]# mysqldump -uroot -pbaiyongjie

mysql> CREATE DATABASE learn DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.00 sec)

mysql> use learn;

mysql> source /root/learn.0930.sql

重新设置同步

mysql> stop slave;

mysql> change master to master_host='10.241.0.10',master_user='myslave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000002',master_log_pos=13465,master_connect_retry=30;

mysql> start slave;

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.241.0.10

Master_User: myslave

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 13465

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 13465

Relay_Log_Space: 561

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: cea1268e-c2c9-11e8-9e7d-00502ab853ae

Master_Info_File: /data/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set: 43b6cf75-c2cc-11e8-a3eb-001200189925:1-29,

cea1268e-c2c9-11e8-9e7d-00502ab853ae:20-56

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

在主库解锁表

mysql> UNLOCK TABLES;

测试同步是否生效

#在主节点上插入数据

mysql> insert into student set id=10, name='baiyongjie9',grade=18;

Query OK, 1 row affected (0.00 sec)

#到从库查看是否同步

mysql> select * from student where id=10 and name='baiyongjie9';

+----+-------------+-------+

| id | name | grade |

+----+-------------+-------+

| 10 | baiyongjie9 | 18 |

+----+-------------+-------+

#可以看到已经可以正常同步了.