mysql数据库主从同步之双主配置----互为主从

Mysql数据库复制原理：

整体上来说，复制有3个步骤：  

(1)master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；

(2)slave将master的binary log events拷贝到它的中继日志(relay log)；

(3)slave重做中继日志中的事件，将改变反映它自己的数据。

下图描述了复制的过程：

      该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日 志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。

       下一步就是slave将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中 继日志。

       SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数 据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

        此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制 过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

具体配置

环境

MySQL-master1：10.6.1.210

MySQL-master2：10.6.1.211

OS版本：CentOS 6.6
MySQL版本：5.1.73

一、MySQL master-master配置

1.1、修改MySQL配置文件

两台MySQL均如要开启binlog日志功能，开启方法：在MySQL-master1  配置文件/etc/my.cnf

 [MySQLd]段中加上

user = mysql
log-bin=mysql-bin

binlog_format=mixed

relay-log=relay-bin

server-id = 1
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates=1     (log-slave-updates也可以)
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1

在MySQL-master2 配置文件/etc/my.cnf

 [MySQLd]段中加上

user = mysql
log-bin=mysql-bin

binlog_format=mixed
relay-log=relay-bin

server-id=10
binlog-do-db=test
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates=1   (log-slave-updates也可以)

slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2

将两个配置文件保存,分别重启mysql服务器

讲解几个重要的常用选项：

server_id

值必须为2到2³²–1之间的一个正整数值。ID值唯一的标识了复制群集中的主从服务器，因此它们必须各不相同。

binlog-do-db=database

是要记录日志的数据库；

同步多个数据库重复设置选项 binlog-do-db=test 和replicate-do-db=test

例如                              

 binlog-do-db=test1

 replicate-do-db=test1

 binlog-do-db=test2 

 replicate-do-db=test2

binlog-ignore-db

不要记录日志的数据库名，多个数据库中间用逗号(,)隔开；

log-slave-updates=1

这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，但是，如果这个从服务器B是服务器A的 从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作

sync_binlog=1

  这个参数直接影响mysql的性能和完整性. 在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统Crash，在文件系统缓存中的所有Binlog信息都会丢失

auto_increment_increment=2
auto_increment_offset=1

auto_increment字段产生的数值是：1, 3, 5, 7, …等奇数ID了

auto_increment_increment = 2

auto_increment_offset = 2

auto_increment字段产生的数值是：2, 4, 6, 8, …等偶数ID了

这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。

另外：

master-connect-retry

master-connect-retry这个参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒

read-only

read-only是用来限制普通用户对从数据库的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对从数据库进行更新操作

slave-skip-errors

在复制过程中，由于各种的原因，从服务器可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，服务器会停止复制进程，不再进行同步，等到用户自行来处理。

Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。

skip-slave-start

表示从mysql服务器启动时不启动同步线程，这就要在启动从服务器之后，手工启动同步线程

1.2  做同步之前要保证两个数据库数据一致.

如果主数据库有数据的话。 数据库锁表操作，不让数据再进行写入动作。

mysql> FLUSH TABLES WITH READ LOCK;

用命令mysqldump备份数据库。

在master服务器执行

shell> mysqldump -uroot -p123456 test >test.sql

看主数据库的状态 mysql> show master status;

记录File 和 Position 项的值

注：没有锁定主服务器，这里记录的主服务器二进制日志position值可能会大于做mysqldump时的值，这将导致从服务器丢失在此期间的更新。如果可以保证在此期间主服务器不会出现创建新表的更新，那么丢失的影响不大；否则，将导致从服务器复制线程失败，这时必须在做mysqldump时锁定主服务器。

-----------------------------------------------

从服务器中my.cnf文件中加入选项

skip-slave-start

表示从mysql服务器启动时不启动同步线程，这就要在启动从服务器之后，手工启动同步线程，在mysql> 提示符下面运行“start slave”就可以

保存my.cnf后

执行

shell> mysqladmin -uroot -p123456 create test

shell> mysql -uroot -p123456 test <test.sql

启动从服务器线程

mysql>start slave;

取消主数据库锁定 mysql>UNLOCK TABLES;

1.3、将10.6.1.210设为10.6.1.211的主服务器

在10.6.1.210上新建授权用户 

MySQL> grant  replicationclient,replication  slave  on  *.*   to  'repluser'@'10.6.1.211'  identified by  '123456';

Query OK, 0 rows affected (0.00 sec) 

MySQL>flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看Master数据库上的bin文件以及时间点. 登录Master服务器的mysql  后执行：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 |     2474 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

此处，bin文件为mysql-bin.000015，节点为2474。

在10.6.1.211将10.6.1.210设为自己的主服务器

MySQL> change master to master_host='10.6.1.210',master_user=' repluser ',master_password='123456',master_log_file='MySQL-bin.000015',master_log_pos=2474;
Query OK, 0 rows affected (0.05 sec)

红色标注部分为10.6.1.210主机中show master status;命令结果中的file、postion两个值

MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)

MySQL> show slave status/G

…

…

Slave_IO_Running:Yes            #IO thread  是否运行

Slave_SQL_Running:Yes         #SQL thread是否运行

…

1.4、将10.6.1.211设为10.6.1.210的主服务器

在10.6.1.211上新建授权用户 

MySQL> grant  replicationclient,replication  slave  on  *.*   to 'repluser'@'10.6.1.210'  identified by  '123456';

Query OK, 0 rows affected (0.00 sec)

 MySQL>flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看Master数据库上的bin文件以及时间点. 登录Master服务器的mysql  后执行：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |     1476 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

此处，bin文件为mysql-bin.000004，节点为1476。

在10.6.1.210将10.6.1.211设为自己的主服务器

MySQL> change master to master_host='10.6.1.211',master_user=' repluser ',master_password='123456',master_log_file='MySQL-bin.000004',master_log_pos=1476;
Query OK, 0 rows affected (0.05 sec)  

MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)   

MySQL>

MySQL> show slave status/G

…

…

Slave_IO_Running:Yes            #IO thread  是否运行

Slave_SQL_Running:Yes         #SQL thread是否运行

…

1.5 、其他命令

1.5.1、查看复制进度
需要在主库上运行
mysql>show processlist \G;

1.5.2、主服务器上的相关命令：
show processlist;
show master status
show slave hosts
show {master|binary} logs
show binlog events
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'
reset master(老版本flush master)
set sql_log_bin={0|1}

1.5.3、从服务器上的相关命令:
slave start
slave stop
slave stop IO_THREAD //此线程把master段的日志写到本地
slave start IO_THREAD
slave stop SQL_THREAD //此线程把写到本地的日志应用于数据库
slave start SQL_THREAD
reset slave
set global sql_slave_skip_counter
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //动态改变master信息
PURGE MASTER [before 'date'] 删除master端已同步过的日志