step 1:
假设主服地址为192.168.1.1,从服为192.168.1.2
主服启用binlog,并设置server-id
[mysqld]
log-bin=mysql-bin
server-id=1
step 2:
主服创建同步账户并设置其权限:
CREATE USER 'replication'@'192.168.1.2' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.2';
step 3:
从服设置配置:
[mysqld]
server-id=2
step 4:
主服查看bin-log信息
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 111| | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从服获取主服master的position 和 binlog文件,设置master
CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=111;
step 5:
启动从服slave:
START SLAVE;
查看slave状态:
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.252.123 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 629 Relay_Log_File: master2-relay-bin.000003 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ......
检查主从复制通信状态
Slave_IO_State
#从站的当前状态 Slave_IO_Running: Yes
#读取主程序二进制日志的I/O线程是否正在运行 Slave_SQL_Running: Yes
#执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样 Seconds_Behind_Master
#是否为0,0就是已经同步了
参考: