Mysql mss生产中使用_MySQL之-实现MSS主从复制(读写分离)的示例代码

前面，在博文《MySQL之——MS主从复制(读写分离)实现》一文中，我们讲解了如何搭建MS模式的MySQL主从复制架构，即：一台Master、一台Slave的MySQL主从复制架构。那么，如何将MS(一Master对应一Slave)扩展为MSSS...(一Master对应多Slave)架构呢？ 现在，我就为大家演示一下如何搭建MSS(一台Master，两台Slave)架构的MySQL主从复制，其他一Master对应多Slave的搭建方式类似。

注意：本文是基于《MySQL之——MS主从复制(读写分离)实现》一文写的，是博文《MySQL之——MS主从复制(读写分离)实现》的扩展，即：本文的实现方式是在《MySQL之——MS主从复制(读写分离)实现》一文的基础上增加了一台Slave服务器(192.168.0.154)，并且对《MySQL之——MS主从复制(读写分离)实现》一文中的Slave服务器(192.168.0.153)的MySQL配置做了相应的修改。并使新增加的Slave服务器的Master指向上文《MySQL之——MS主从复制(读写分离)实现》中的Slave服务器(192.168.0.153)，从而使《MySQL之——MS主从复制(读写分离)实现》一文的Slave服务器(192.168.0.153)变为中继Slave服务器。

阅读本文前，请首先阅读《MySQL之——MS主从复制(读写分离)实现》一文。

一、服务器规划

之前的服务器规划为两台，一台Master服务器(192.168.0.152)，一台Slave服务器(192.168.0.153)。

现在服务器的规划为三台，一台Master服务器(192168.0.152)，一台中继Slave服务器(192.168.0.153)，一台Slave服务器(192.168.0.154)

具体如下：主机名IP地址节点

liuyazhuang152192.168.0.152Master

liuyazhuang153192.168.0.153中继Slave

liuyazhuang154192.168.0.154Slave

二、主从配置

1、修改中继Slave服务器my.cnf文件vim /etc/my.cnf

在原有配置中新增如下一行配置log_slave_updates

说明：如果你在使用链式从服务器结构的复制模式 (A->B->C),你需要在服务器B上打开此项，此选项打开在从线程上重做过的更新的日志, 并将其写入从服务器的二进制日志.

具体如下：[mysqld]

server-id=2

log-bin=/data/mysql3306log/mysql-bin

binlog_format=mixed

relay-log=/data/mysql3306log/slave-relay-bin

relay-log-index=/data/mysql3306log/slave-relay-bin.index

sync_binlog=1

log_slave_updates

#注意：下面这个参数需要修改为服务器内存的70%左右

innodb_buffer_pool_size = 512M

innodb_flush_log_at_trx_commit=1

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO

lower_case_table_names=1

log_bin_trust_function_creators=1

修改之后要重启mysql：service mysqld restart

2、修改Slave服务器my.cnf文件[mysqld]

server-id=3

log-bin=/data/mysql3306log/mysql-bin

binlog_format=mixed

relay-log=/data/mysql3306log/slave-relay-bin

relay-log-index=/data/mysql3306log/slave-relay-bin.index

sync_binlog=1

#注意：下面这个参数需要修改为服务器内存的70%左右

innodb_buffer_pool_size = 512M

innodb_flush_log_at_trx_commit=1

sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO

lower_case_table_names=1

log_bin_trust_function_creators=1

3、SSH登录到中继服务器MySQL数据库

(1)在中继Slave数据库上创建用于主从复制的账户(192.168.0.154换成你的从数据库IP)：

首先登陆MySQL服务器mysql -uroot -proot

然后在MySQL服务器中执行如下命令：mysql> GRANT REPLICATION SLAVE ON *.* TO 'lyz'@'192.168.0.154' IDENTIFIED BY 'lyz';

(2)中继Slave数据库锁表(禁止再插入数据以获取主数据库的的二进制日志坐标)mysql> FLUSH TABLES WITH READ LOCK;

(3)然后克隆一个SSH会话窗口，在这个窗口打开MySQL命令行：

首先登陆MySQL服务器mysql -uroot -proot

执行如下命令mysql> SHOW MASTER STATUS;

结果如下：mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000003 | 543 | | | |

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

1 row in set (0.01 sec)

退出MySQLmysql> exit;

在这个例子中，二进制日志文件是mysql-bin.000003，位置是543，记录下这两个值，稍后要用到。

(4)在中继Slave服务器上使用mysqldump命令创建一个数据快照：#mysqldump -uroot -proot -h127.0.0.1 -P3306 --all-databases --triggers --routines --events >all.sql

(5)解锁第(2)步中继Slave数据的锁表操作：mysql> UNLOCK TABLES;

(6)将第(4)步生成的all.sql复制到Slave服务器scp ./all.sql liuyazhuang154:/data/mysql/backup

4、SSH登录到新增的Slave数据库

(1)导入中继Slave数据库的快照# cd /data/mysql/backup

# mysql -uroot -proot -h127.0.0.1 -P3306 < all.sql

(2)给Slave数据库设置复制的中继Slave数据库信息(注意修改MASTER_LOG_FILE和MASTER_LOG_POS的值)：# mysql -uroot -proot

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.153',MASTER_USER='lyz',MASTER_PASSWORD='lyz',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=543;

# 然后启动从数据库的复制线程：

mysql> START slave;

# 接着查询数据库的slave状态：

mysql> SHOW slave STATUS \G

# 如果下面两个参数都是Yes，则说明主从配置成功！

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

(3)至此，MySQL MSS主从复制配置结束。接下来你可以在Master数据库上创建数据库、表、插入数据，然后看中继Slave和Slave数据库是否同步了这些操作

三、附录

1、中继Slave服务器my.cnf文件完整配置# 以下选项会被MySQL客户端应用读取。

# 注意只有MySQL附带的客户端应用程序保证可以读取这段内容。

# 如果你想你自己的MySQL应用程序获取这些值。

# 需要在MySQL客户端库初始化的时候指定这些选项。

#

[client]

#password = [your_password]

port = 3306

socket = /usr/local/mysql3306/mysql.sock

# *** 应用定制选项 ***

#

# MySQL 服务端

#

[mysqld]

# 一般配置选项

port = 3306

socket = /usr/local/mysql3306/mysql.sock

basedir =/usr/local/mysql3306

datadir = /data/mysql3306db

# back_log 是操作系统在监听队列中所能保持的连接数,

# 队列保存了在 MySQL 连接管理器线程处理之前的连接.

# 如果你有非常高的连接率并且出现 “connection refused” 报错,

# 你就应该增加此处的值.

# 检查你的操作系统文档来获取这个变量的最大值.

# 如果将back_log设定到比你操作系统限制更高的值,将会没有效果

#back_log = 300

# 不在 TCP/IP 端口上进行监听.

# 如果所有的进程都是在同一台服务器连接到本地的 mysqld,

# 这样设置将是增强安全的方法

# 所有 mysqld 的连接都是通过 Unix Sockets 或者命名管道进行的.

# 注意在 Windows下如果没有打开命名管道选项而只是用此项

# (通过 “enable-named-pipe” 选项) 将会导致 MySQL 服务没有任何作用!

#skip-networking

# MySQL 服务所允许的同时会话数的上限

# 其中一个连接将被 SUPER 权限保留作为管理员登录.

# 即便已经达到了连接数的上限.

max_connections = 3000

# 每个客户端连接最大的错误允许数量,如果达到了此限制.

# 这个客户端将会被 MySQL 服务阻止直到执行了 “FLUSH HOSTS” 或者服务重启

# 非法的密码以及其他在链接时的错误会增加此值.

# 查看 “Aborted_connects” 状态来获取全局计数器.

max_connect_errors = 100

# 所有线程所打开表的数量.

# 增加此值就增加了 mysqld 所需要的文件描述符的数量

# 这样你需要确认在 [mysqld_safe] 中 “open-files-limit” 变量设置打开文件数量允许至少等于 table_cache 的值

table_open