RDS同步本地数据库实现主从同步
-
查询主机相关信息
通过show master status;查看主机的file、position、gtid值,第3步用到 -
配置从机my.cnf
datadir=/var/lib/mysql(数据库数据存放路径)
tmpdir=/home/mysql(临时文件存放路径,包含同步的log文件)
socket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names=1innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1048576000server-id = 10001 (保证同主机的id值不一致)
gtid_mode = onenforce_gtid_consistency = true
master-info-repository = file
relay-log-info_repository = filerelay-log = mysqlslave-relay-bin
binlog_format = rowreplicate-do-db = test(需要同步的数据库)
replicate-ignore-db = mysql(不需要同步的数据库)
replicate-ignore-db = information_schemaslave-skip-errors = all(所有异常情况跳过)
-
配置从机master
配置完my.cnf文件之后,systemctl start mysqld.service重启数据库;进入mysql中:
reset master; 重置master
set @@global.gtid_purged=‘主库的GTID值’;设置GTID
change master to master_host = ‘主库Host’, master_port = 主库Port,master_user = ‘主库用于备份的账号’, master_password=‘密码’,MASTER_LOG_FILE=‘第一步查到的file’, MASTER_LOG_POS=0 -
开启slave服务
start slave; 开启服务 -
查看服务状态
show slave status\G;查看slave状态,双yes表示开启成功