1.服务器的my.cnf配置
server-id = 1 //两台server不同
log_bin = /var/log/mysql/mariadb-bin
log_bin_index = /var/log/mysql/mariadb-bin.index
binlog_format = mixed
relay_log = /var/log/mysql/relay-bin
skip_slave_start
2.授权复制并启动复制
server1:
mysql> grant replication slave,file on *.* to 'tom'@'192.168.%.%' identified by '123456';
mysql> flush privileges;
server2:
mysql> grant replication slave,file on *.* to 'tom'@'192.168.%.%' identified by '123456';
mysql> flush privileges;
3.在各个server指向对方当主服务器:
server1:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.205',MASTER_USER='tom',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000004',MASTER_LOG_POS=1160;
注:MASTER_LOG_FILE=’master-bin.000004’,MASTER_LOG_POS=1160; 是对方主服务器192.168.10.205查看show master status得知
server2:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.10.204',MASTER_USER='tom',MASTER_PASSWORD='123456',MASTER_LOG_FILE='master-bin.000003',MASTER_LOG_POS=526;
注:MASTER_LOG_FILE=’master-bin.000004’,MASTER_LOG_POS=1160; 是对方主服务器192.168.10.204查看show master status得知
4.启动slave线程:
server1:
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
server2:
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
5.查看状态:
serever1:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.205
Master_User: tom
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 1160
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 536
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes #值为NO表示没有启动IO线程
Slave_SQL_Running: Yes #值为NO表示没有启动SQL线程。此两个线程一定要启动
执行这命令后 注意观察下面这两个参数,必须要都是yes才行
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
如果其中一个是no,那么请观察后续字段 Last_IO_Error或者Last_SQL_Error中的描述,然后根据错误去修改配置参数
其中可能出现错误的几点,在这里说明下:
1) server_id相同,导致IO错误
2) master_log_file和master_log_pos错误,导致IO错误
使用START SLAVE命令启动从服务器的IO和SQL线程,并再次查看状态