1: 在my.cnf中打开binlog和相关配置:
log-bin=mysql-bin
server-id=2 ###这个地方需要不一样
skip_name_resolve=ON
relay-log=/var/log/relay-log
relay-log-index=/var/log/relay-log.index
2: 主库上给从库授权:
grant REPLIACTION SLAVE on *.* to slave_user@'172.16.251.%' identified by '123456;
flush privileges;
主库上查看主库的状态:
show master status; 假设结果为:
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1147 | | | |
+------------------+----------+--------------+------------------+-------------------+
从库上执行:
CHANGE MASTER TO MASTER_HOST='1.1.1.1,MASTER_PORT=3306,MASTER_USER='slave_user',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1147;
start slave;
show slave status \G
有下面的字段说明成功
Slave_IO_Running: Yes
Slave_SQL_Running: Ye
3:基于GTID的主从复制
在主从库的my.cnf上加上:
gtid_mode=on
enforce-gtid-consistency=on
binlog_gtid_simple_recovery=1
在从库上执行:
MASTER_HOST='1.1.1.1',MASTER_PORT=3306,MASTER_USER='slave_user',MASTER_PASSWORD='123456',master_auto_position=1;
start slave;
show slave status \G