mysql主备

1.备库执行：

vi /www/server/data/auto.cnf

server-uuid=c9567840-a03e-4c68-bc8d-xxxxxx //跟主库不一样

2.备库执行：

server-id=2//跟主库不一样

3.主库

mysql> CREATE USER 'master'@'172.31.0.1' IDENTIFIED WITH mysql_native_password BY '000###';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'master'@'172.31.0.1';
mysql> flush privileges;

//假设备库IP=172.31.0.1

CHANGE MASTER TO MASTER_HOST='172.31.12.56',  
MASTER_USER='master',                     
MASTER_PASSWORD='mexicoslave###',                  
MASTER_LOG_FILE='mysql-bin.000011',               
MASTER_PORT=63306,                           
MASTER_LOG_POS=855;           

获取主节点当前binary log文件名和位置（position）
show master status;

4.在在从服务器启动同步并查看状态

#开启同步
start slave;

SHOW SLAVE STATUS\G;

如果主备同步出现了错误，怎么处理？

例如手动删除了备库记录，导致主库删除记录，备库找不到记录

Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000011, end_log_pos 1656. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

解决方案：

登录主库：
 show master status\G;

登录从库：
stop slave;
CHANGE MASTER TO   MASTER_LOG_FILE='mysql-bin.000011',   MASTER_LOG_POS=2026; 
start slave;
show slave status\G;

解决方案2

stop slave;
set global sql_slave_skip_counter=1;
start slave;