MySQL 复制功能 配置

MySQL 复制功能 配置

摘要

MySQL 复制功能 配置

master

配置 /etc/my.cnf

log_bin=mysql-bin
server_id=1

show master status ;
记录下文件名和position (position可以用0)

mysql-bin-changelog.002084 7253703

查看所有log
show binary logs;

slave

配置/etc/my.cnf

server-id = 2
log_bin=mysql-bin
binlog-do-db=sc
replicate_ignore_db=mysql,information_schema,performance_schema
replicate_wild_ignore_table=sc.ps_w%
slave-skip-errors=all
#slave-skip-errors=1062,1053,1146

slave执行如下命令

stop slave  ;

change master to 
master_host='master IP',
master_user='root',
master_password='xxx',
master_port=3306,
master_log_file='mysql-bin-changelog.002084',
master_log_pos=0; 


SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1    ;    -- 跳过一个错误事务
start slave ;

show slave status;
show variables like '%version%'

常用命令

常用命令

show master status; 查看当前正在使用的二进制日志
show binlog events in’mysql-bin.000001’; 查看二进制日志记录的事件[from position]
flush logs; 二进制日志滚动
show binary logs; 查看所有二进制日志
purge binary logs to ‘mysql-bin.000003’; 删除二进制日志

特别注意

slave端的版本号要比master高,至少要相同

备注AWS RDS注意事项

设置master上bin-log的备份时长

CALL mysql.rds_set_configuration('binlog retention hours',24);
CALL mysql.rds_show_configuration;

参考链接
http://docs.aws.amazon.com/zh_cn/AmazonRDS/latest/UserGuide/Appendix.MySQL.SQLRef.html