前提:相同版本的两台MYSQL数据库
1、更改Master库配置项
[mysqld]
log-bin=mysql-bin
server-id = 1 #主从库的server-id不同即可
binlog-do-db = coreseek_test #需要同步的数据库名
binlog-ignore-db=mysql #忽略掉的数据库
expire_logs_days=10 #日志留存天数
2、更改Slave库配置项
[mysqld]
log-bin=mysql-bin
server-id = 130 #与主从库不同即可
修改完成后重启主从数据库。
3、登录Master数据库,授权Slave
GRANT REPLICATION SLAVE ON *.* to 'backup'@'192.168.136.130' identified by '123';
mysql> show master status;
+------------------+----------+---------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+---------------+------------------+
| mysql-bin.000012 | 107 | coreseek_test | mysql |
+------------------+----------+---------------+------------------+
1 row in set (0.00 sec)
4、登录Slave数据库,配置Master库中的日志查找
change master to master_host='192.168.136.129',master_user='backup',master_password='123',master_log_file='mysql-bin.000012',master_log_pos=107;
注意:master_log_file 和 master_log_post的值分别对应Master库 ‘show master status’ 中的file和Position的值。
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.136.129
Master_User: backup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 107
Relay_Log_File: Local-YSZ-relay-bin.000007
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000012
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
注意:Slave_IO_Running 和 Slave_SQL_Running 都为yes 才成功
剩下的任务就是见证奇迹的时刻啦。。。