MySQL双主（Master-Master）同步

来源：互联网发布：斗鱼主播唱歌软件编辑：程序博客网时间：2024/05/20 19:15

MySQL双主同步步骤（Linux服务器）：

1.分别找到两个MySQL下的my.cnf配置文件，分别加入：

[mysqld]server-id = 1log-bin=/data0/mysql/3306/binlog/binlogbinlog-do-db = Master_1binlog-ignore-db=mysqlbinlog-ignore-db=obtbinlog-ignore-db=performance_schemabinlog-ignore-db=testbinlog-ignore-db=information_schemalog-slave-updatessync_binlog=1auto_increment_offset=1auto_increment_increment=2replicate-do-db = Master_1replicate-ignore-db = mysqlreplicate-ignore-db = obtreplicate-ignore-db = performance_schemareplicate-ignore-db = testreplicate-ignore-db = information_schema

[mysqld]server-id = 2log-bin=/data/mysql/binlog/binlogbinlog-do-db = Master_2binlog-ignore-db=mysqlbinlog-ignore-db=testbinlog-ignore-db=information_schemalog-slave-updatessync_binlog=1auto_increment_offset=2auto_increment_increment=2replicate-do-db = Master_2replicate-ignore-db = mysqlreplicate-ignore-db = testreplicate-ignore-db = information_schema

server-id，auto_increment_offset为区别处

2.分别重启两台MySQL

service mysqld restart

注意：若此时发现启动失败，查看err日志文件显示：File '/data/mysql/binlog/binlog.index' not found

进入binlog目录进行授权操作再次启动mysql服务即可

[root@trade-api mysql]# chown -R mysql:mysql binlog[root@trade-api mysql]# service mysqld restart

3.分别两台MySQL中授予远程复制权限

mysql>grant replication slave,file on *.* to 'root'@'对方IP' identified by '密码';mysql>flush privileges;  mysql>grant replication slave,file on *.* to 'root'@'对方IP' identified by '密码';mysql>flush privileges;

4.锁定表

mysql>flush tables with read lock;

5.查看各自状态

mysql> show master status;+---------------+----------+--------------+-------------------------------+| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB              |+---------------+----------+--------------+-------------------------------+| binlog.000002 |     4890 | Master_1     | mysql,test,information_schema |+---------------+----------+--------------+-------------------------------+

mysql> show master status;+---------------+----------+--------------+------------------------------------------------------+| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB                                     |+---------------+----------+--------------+------------------------------------------------------+| binlog.000028 |     7078 | Master_1     | mysql,obt,performance_schema,test,information_schema |+---------------+----------+--------------+------------------------------------------------------+

6.建立平行库

[root@test7 ~]# scp  -P 端口(默认22)  root@IP地址:/tmp/Master_1.sql .

# mysql -uroot -p test < /tmp/Master_1.sql

7.同步两端数据库

mysql> change master to master_host='对方IP',master_user='root',master_password='密码',master_log_file='binlog.000028',master_log_pos=7078;

mysql> change master to master_host='对方IP',master_user='root',master_password='密码',master_log_file='binlog.000002',master_log_pos=4890;

8.解锁

mysql> UNLOCK TABLES;

9.分别启动slave

mysql>START SLAVE;

注：如果slave报错（Last_SQL_Error有信息）在slave端执行：

stop slave;
set global sql_slave_skip_counter=1;跳过一次错误
start slave;

错误：

Last_Error: Could not execute Delete_rows event on table obt.t_tour_label_map; Can't find record in 't_tour_label_map', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql_log-bin.000062, end_log_pos 98278275

根据错误分析具体原因：

1.导出主库mysql_log_bin.000062

[root@db-master log-bin]# mysqlbinlog mysql_log-bin.000062 --base64-output=decode-rows -v > mysql_log-bin.000062.log

2.找到pos所在位置

vi mysql_log-bin.000062.log

?end_log_pos 98278275

3.查看具体详情

#150918 11:09:55 server id 1  end_log_pos 98278275 CRC32 0x3e1f3e3b     Delete_rows: table id 1220 flags: STMT_END_F### DELETE FROM `obt`.`t_tour_label_map`### WHERE###   @1=239664###   @2=702###   @3='402881dd4a576a1a014a576ab6810010'# at 98278275

发现该字段未出现在从库中，原因查明

0 0