MySQL复制M-S-S架构

master=>slave=>slave架构

在第一个slave中设置log-slave-update参数，表示从master取得并执行的二进制日志写入自己的二进制日志文件中。

注意：slave上设置了log-slave-update参数，则不能执行stop slave;reset master;将slave切成主。

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

这里插入一个疑问，如果slave不打开log-slave-update选项，但是打开log-bin选项，从master同步到中继日志文件，在sql_thread线程执行后，是否会写入slave的log-bin中。

做个测试验证一下：

mysqld4作为slave，开启log-bin，但是未设置log-slave-update

mysql> show variables like ‘log_bin‘;          

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

| Variable_name | Value |

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

| log_bin       | ON    |

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

mysql> show variables like ‘log_slave_updates‘;

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

| Variable_name     | Value |

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

| log_slave_updates | OFF   |

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

配置slave

导入master数据

mysql -S /var/lib/mysql4/mysql4.sock < dump.sql

slave指向master

change master to master_host=‘192.168.60.134‘,master_port=3306, master_user=‘repli‘,master_password=‘123456‘,master_log_file=‘ora01-bin.000025‘,master_log_pos=513;

同步状态成功。

在master、slave上都刷新一下二进制日志

mysql> flush logs;

查看mysqld4的log-bin日志

[root@ora01 mysql4]# mysqlbinlog ora01-bin.000004

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#110525 19:14:17 server id 4  end_log_pos 107   Start: binlog v 4, server v 5.5.11-log created 110525 19:14:17

# Warning: this binlog is either in use or was not closed properly.

BINLOG ‘

ieTcTQ8EAAAAZwAAAGsAAAABAAQANS41LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

‘/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

在master插入数据

mysql> insert into t select  7,now();

slave，即mysql4，查看数据是否更新过来

mysql> select * from t;

| 7 | 2011-05-25 19:16:10 |

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

再查看mysqld4的log-bin日志

[root@ora01 mysql4]# mysqlbinlog ora01-bin.000004

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#110525 19:14:17 server id 4  end_log_pos 107   Start: binlog v 4, server v 5.5.11-log created 110525 19:14:17

# Warning: this binlog is either in use or was not closed properly.

BINLOG ‘

ieTcTQ8EAAAAZwAAAGsAAAABAAQANS41LjExLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

‘/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

结论：

slave不打开log-slave-update选项，但是打开log-bin选项，从master同步到中继日志文件，在sql_thread线程执行后，不会写入slave的log-bin中。

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

目的：缓解master的IO压力。

大致结构如下

master ----slave1----slave1_1

     \          ----slave1_2

      ----slave2----slave2_1

                ----slave2_2

用单机多实例的功能，搭建一个简单的测试环境。如下

master还是用/etc/my.cnf

多实例配置文件/etc/mysqld_multi.cnf

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

[mysqld_multi]

mysqld     = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

[mysqld2]

socket     = /var/lib/mysql2/mysql2.sock

port       = 3307

datadir    = /var/lib/mysql2

user       = mysql

server-id  = 2

log-slave-update

[mysqld3]

socket     = /var/lib/mysql3/mysql3.sock

port       = 3308

datadir    = /var/lib/mysql3

user       = mysql

server-id  = 3

log-slave-update

[mysqld4]

socket     = /var/lib/mysql4/mysql4.sock

port       = 3309

datadir    = /var/lib/mysql4

user       = mysql

server-id  = 4

[mysqld5]

socket     = /var/lib/mysql5/mysql5.sock

port       = 3310

datadir    = /var/lib/mysql5

user       = mysql

server-id  = 5

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

其中mysqld4、mysqld5分别为2、3的slave

master ----slave2----slave4

     \----slave3----slave5

参考单机多实例的实验

http://blog.chinaunix.net/space.php?uid=16844903&do=blog&id=334220

再生成mysqld4和mysqld5的数据库

mysql_install_db --datadir=/var/lib/mysql4 --user=mysql

mysql_install_db --datadir=/var/lib/mysql5 --user=mysql

停止当前运行mysqld2和mysqld3

mysqld_multi --defaults-file=/etc/mysqld_multi.cnf stop

再启动所有的

mysqld_multi --defaults-file=/etc/mysqld_multi.cnf start

首先确认mysqld2与master的同步状态，在mysqld2上也应该有repli同步用户。

mysql> show master status\G

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

           File: ora01-bin.000013

       Position: 689

   Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

设置mysqld4为mysqld2的slave

#mysql -S /var/lib/mysql4/mysql4.sock

mysql> change master to master_host=‘192.168.60.134‘,master_port=3307, master_user=‘repli‘,master_password=‘123456‘,master_log_file=‘ora01-bin.000013‘,master_log_pos=689;

mysql> start slave;

mysql> show slave status\G

还可以查看mysqld4的master.info和relay-log.info

18

ora01-bin.000013

689

192.168.60.134

repli

123456

3307

60

0

0

1800.000

0

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

./ora01-relay-bin.000002

253

ora01-bin.000013

689

目前我掌握的M-S-S架构都写完了，如以后有新发现，将进行更新。

总的来说，这样的架构可以缓解master的磁盘IO压力，如果多设置一个二层的slave，可以当作替换master的备用机。