mysql ab复制_mysql-AB复制-CSDN博客

本文链接：https://blog.csdn.net/weixin_42360778/article/details/113423110

Mysql复制的基础是主服务器对数据库修改记录二进制日志binlog，从服务器通过主服务器的binlog自动执行更新。

本例使用的主服务器是线上的主库192.168.9.1，从服务器是新上线的192.168.9.2

***************************************************************************************************************************************

一.主服务器操作

1.打开binlog记录，配置#vi my.cnf

##打开binlog

log-bin=mysql-bin

server-id = 1

##指定不写binlog的数据库，不同步系统库

binlog-ignore-db = mysql

binlog-ignore-db = information_schema

##指定写二进制的数据库，多个要多行

replicate-do-db = DB_Test

##指定跨库更新也同步的数据库。和replicate-do-db相同多行

replicate-wild-do-table=DB\_Test.%

备注：★:关于过滤复制对象的问题，但是如果不是特殊需要，不要使用复制过滤，因为同样有几率在一些事件触发的时候导致一些错误。介绍几种过滤参数。

binlog_do_db 只复制指定的数据库(主服务器配置文件里设置)

binlog_ignore_db 不复制指定的数据库(主服务器配置文件里设置)

replicate_ignore_table 不复制指定的表(从服务器配置文件里设置)

replicate_wild_ignore_table 使用wild匹配来不复制的指定表(从服务器配置文件里设置),比如参数设为abc.%,表示不复制abc的所有表。

2.主服务器上添加数据库复制用户

grant replication slave on *.* to 'slave'@'192.168.9.2' identified by 'password';

flush privileges;

3.数据导出

主服务器上要保证数据一致性，获取数据快照。在获取快照后，主服务器不能有写操作。

先刷新所有语句并阻止写入，这样的目的是保证主机数据快照的一致性：

FLUSH TABLES WITH READ LOCK;

执行完这句后，所有对主服务器数据库的更新都被挂起，直到执行UNLOCK TABLES;或者关闭当前的终端。

获取主服务器的快照：

show master status\G;

记录File 及Position 项的值，以便之后对从服务器进行配置。File是当前处理的binlog，Position是处理的binlog位置，这是从服务器的同步起点。

接下来是导出主服务器的数据。导出数据有两种方式：mysqldump导出需要同步的数据库和拷贝数据库数据目录。

a.mysqldump -uroot -p --databases db1 –opt --master-data=1–single-transaction –flush-logs > db1.sql

b.也可以直接把数据库存放的目录打包，打包传到从库的文件，注意目录的属性和属主

*这里可以利用主库192.168.9.1每天零点做的数据库备份文件(通过mysqldump备份的)，这样就可以随时操作，不用再对主库进行锁表操作。在备份文件的前几行可以看到binlog名称和Position偏移量。

[root@www-slave-db2 data]# head -n 30 192.168.9.1_DB.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000937', MASTER_LOG_POS=106;

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～

二、从库的操作

1.a.进入mysql> source db1.sql; 把文件导入

b.把打包的文件解压到从库的数据库目录下，注意文件的属主，目录应该是mysql:mysql用户所有

2.配置/etc/my.cnf文件

server-id =102 此ID不能重复。

replicate_wild_ignore_table=mysql.% 匹配来不复制的指定表

3.配置主库

mysql>CHANGE MASTER TO MASTER_HOST='192.168.9.1',MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='password',MASTER_LOG_FILE='mysql-bin.00093',MASTER_LOG_POS=106;

注意：在这里配置好master的信息后就不要在/etc/my.cnf里配置master信息了。不然无法启动mysql。

日志中的报错：111207 12:27:56 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'master-host=192.168.9.1' 注释掉my.cnf里的配置就好了。

4.启动slave

mysql> start slave;

配置好后，可以启动show slave status\G ，查看当时服务器的复制状态，出现 Slave_IO_Running: Yes Slave_SQL_Running: Yes 就是没有问题了。

也可以登录主库show processlist，查看复制的进程。

以上信息参考http://www.cnblogs.com/luoine/archive/2011/05/25/2056493.html 根据实际情况做了修改。

问题：

mysql> show slave status\G

Relay_Log_File: localhost-relay-bin.000535

Relay_Log_Pos: 21795072

Relay_Master_Log_File: localhost-bin.000094

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

......

解决办法一、

Slave_SQL_Running: No

1.程序可能在slave上进行了写操作

2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：

解决办法：

mysql> slave stop;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> slave start;

解决办法二、

首先停掉Slave服务：slave stop

到主服务器上查看主机状态：

记录File和Position对应的值

进入master

mysql> show master status;

+----------------------+----------+--------------+------------------+

+----------------------+----------+--------------+------------------+

| localhost-bin.000094 | 33622483 | | |

+----------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

然后到slave服务器上执行手动同步：

mysql> change master to master_host='192.168.9.1',master_user='user',master_password='pwd',master_port=3306,master_log_file=localhost-bin.000094',master_log_pos=33622483 ;

1 row in set (0.00 sec)

mysql> slave start;

mysql> show slave status\G

*************************1. row*************************

........

Master_Log_File: localhost-bin.000094

Read_Master_Log_Pos: 33768775

Relay_Log_File: localhost-relay-bin.000537

Relay_Log_Pos: 1094034

Relay_Master_Log_File: localhost-bin.000094

Slave_IO_Running: Yes

Slave_SQL_Running: Yes