MYSQL高可用之复制(MASTER/SLAVE)

MYSQL高可用之复制(MASTER/SLAVE)

随着互联网的快速发展，对数据库的访问已经越来越频繁,仅凭单个服务器已经无法应对高并发的访问，也无法满足数据库提供7*24的服务，这个时候我们就必须考虑MYSQL的高可用方案了。当然高可用方案有很多,如：MYSQL CLUSTER,MYSQL MASTER SLAVE，云计算解决方案或者磁盘网络镜像方案。而在实际应用场景中，MYSQL MASTER SLAVE是使用最广泛的一种提高系统扩展的设计手段，通过简单的增加硬件，就可以成倍的提高原有的系统性能，下面我们一起了解MYSQL的MASTER SLAVE(以下简称MYSQL复制)。

一、什么是MYSQL复制

MYSQL复制就是把一个数据库（主库）上的所有修改复制到一个或者多个数据库（从库）上，最常见的应用就是，创建一个主库，然后配置一个或者多个从库，主库做为程序的统一写入入口，当主库的有任何改变也会复制到从库上，应用程序访问可以直接访问从库即可，因为在这整个的复制环境中数据都是一致的。

二、为什么使用MYSQL复制

1．提供数据库高可用环境：即使一个服务器宕机，可以把应用程序访问切换到其它从库上继续提供服务

2．提供读写分离作用：数据统一写入到主库，通过主库分发到不同的从库，不同的应用合理分布到不同的从库，从而提高数据库系统的承载能力

3．方便扩展：当应用需要，可以在不停服务器的情况下更加容易的配置一个一模一样的环境，做到扩展方便。

4．容错性：当在这个高可用环境中任何一个数据库数据出现问题，都可以使用其它库数据来修复

三、MYSQL复制原理（如图）

复制的过程如下：

1． 主库会把所有的操作记录到日志中，例：对数据的操作(DML)，对表结构的操作(DDL)，对用户权限的操作(DCL)等

2． SLAVE的IO进程连接上MASTER，请求获取从日志文件的指定位置之后的日志内容

3． MASTER接受到SLAVE请求后，通过复制的IO线程根据请求把日志信息和位置返回给SLAVE端的IO线程，SLAVE IO线程接受到请求后，将日志信息写入到SLAVE端的RELAY LOG文件的末端，并将读取到的信息概缆写到MASTER-INFO文件中，方便下次识别

4．SLAVE端检测到RELAY LOG日志有更新后，使用SLAVE SQL进程把RELAY LOG中的日志在从库上再重新执行一遍，使得数据更从库保持一致。

四、MYSQL复制特点

1．配置复制的权限：为了安全起见，要想让SLAVE能够成功连接到MASTER做复制，必须具备的权限如下：

CREATE USER权限: 用于创建和删除用户

REPLICATION SLAVE权限:用于复制账户，且有GRANT OPTION

RELOAD权限：执行FLUSH LOGS需要

SUPER或REPLICATION CLIENT: 执行SHOW MASTER/SLAVE STATUS需要

例：

#/opt/mysql5/bin/mysql –uroot –p****** MASTER>grant replication slave,reload,create user,super on *.* To repl@’192.168.100.%’ with grant option;

2.复制实现的级别：MYSQL的复制可以是基于一个SQL语句（如：你插入记录的一整条SQL记录到二进制日志中）,也可以基于一条记录（如：每一次改动记录为二进制日志的一行），不同的级别会影响主库日志写入方式的不同。

基于语句：对主数据库的修改操作都会记录到二进制日志中，在从库中只要把日志中的SQL语句再执行一遍，即可与主库保持一致，与基于行的复制相比，日志的数据量比较小，减少了磁盘IO的读写，但是随着MYSQL的不断更新，部分功能不能使用，例：MYSQL5.6多线程复制，SLEEP ()函数。

基于行：对主数据库每行的改变都会记录到二进制日志中，不需要记录上下文关系，相反，如果某个更新改变了多行，你可能宁愿只记录这条语句，而不想基于行的复制那样记录多行的单独的改动，经过测试我们发现，基于行的复制产生的日志会比基于语句的复制日志大到30%以上，而且不直观，不能通过MYSQLBINGLOG程序查看日志，但是基于行的复制会引用新的功能，如多线程同步，函数的使用，而且不容易出错。

3．修改参数:一旦配置了数据库复制以后，环境中的SERVER-ID都不要使用默认值，一般可以使用ip地址替代。

4．复制相关的 文件

Mysql-bin.******: 二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。

# ls -l mysql-bin* -rw-rw---- 1 mysql mysql   150 Nov 27 21:47 mysql-bin.000006 -rw-rw---- 1 mysql mysql  1317 Nov 28 10:00 mysql-bin.000007 -rw-rw---- 1 mysql mysql 33703 Nov 29 09:54 mysql-bin.000008 -rw-rw---- 1 mysql mysql   107 Nov 29 09:54 mysql-bin.000009

Mysql-bin.index:开启二进制以后,会产生一个与二进制文件同名以index结尾的文件,它用于记录系统上存在哪些二进制文件,mysql使用它来定位二进制文件。

# more mysql-bin.index /opt/mysql55/data/mysql-bin.000006 /opt/mysql55/data/mysql-bin.000007 /opt/mysql55/data/mysql-bin.000008 /opt/mysql55/data/mysql-bin.000009

relay-bin.******:用于存放从主库传输过来的中继日志

# ls -lt *relay-bin* -rw-rw---- 1 mysql mysql 200 Nov 29 09:54 relay-bin.000020 -rw-rw---- 1 mysql mysql 253 Nov 29 09:54 relay-bin.000021

Relay-bin.index: 该文件的功能与mysql-bin.index类似，但是它是针对中继日志，而不是二进制日志。

# more relay-bin.index ./relay-bin.000020 ./relay-bin.000021

Master.info:保存master和连接的相关信息

18 mysql-bin.000009 107 192．168．100．7 rep14494 111111 3306 60 0 0 1800.000 0

Relay-log.info:包含主库当前二进制的信息和中继日志的信息

[mysql@master data]$ more relay-log.info ./master-relay-bin.000021 253 mysql-bin.000009 107 0 0

四、MYSQL复制基本步骤

复制的基本步骤如下：

1．安装和配置一个MASTER 服务器(主)

2．修改MASTER服务器参数并重起，让其支持数据库MASTER复制（主）

3．创建用户和授权，从库可以使用这个用户连接到主库，并且有权限在主库上取得二进制日志（主）

4．安装和配置SLAVE服务器（从）

5．修改SLAVE服务器参数并重起，让其支持数据库SLAVE复制（从）

6．克隆主库数据到从库上

7．在从库上创建连接指向主库（从）

详细步骤如下：

1．安装MYSQL主服务器在此省略

2．修改MASTER 参数文件my.cnf，最少需要修改两个参数(log-bin,server-id)，修改如下。

#vi /etc/my.cnf [mysqld] port=3306 socket=/opt/mysql56/data/mysqld.sock datadir=/opt/mysql56/data low-priority-updates event_scheduler=ON character-set-server=utf8 key_buffer_size=4G max_allowed_packet=16M table_open_cache=1024 sort_buffer_size=4M read_buffer_size=2M read_rnd_buffer_size=8M myisam_sort_buffer_size=64M thread_cache_size=2000 query_cache_size=1024M interactive_timeout=15 wait_timeout=15 max_connections=1024 max_connect_errors=99999 max_heap_table_size=16G group_concat_max_len=102400 tmp_table_size=1G open_files_limit=65535 server-id=1921681007 log-bin=mysql-bin binlog-do-db=test1 #service mysql restart

#server-id 指定唯一的服务器名，通常我们使用服务器的IP地址来作为唯一的服务器ID，如果主库和从库server id一样，则会产生MASTER和SLAVE相同的错误。

#log-bin开启二进制日志，用于保存MASTER上所有的改变

3．创建用户和授予权限

#/opt/mysql5/bin/mysql –uroot –p****** MASTER> GRANT REPLICATION SLAVE ON *.* -> TO 'repl'@'%' IDENTIFIED BY 'slavepass'; MASTER> flush privileges;

以上是创建一个用户为repl，密码为slavepass的用户

4．安装MYSQL从服务器在此省略

5．修改SLAVE服务器参数并重起

#vi /etc/my.cnf [mysqld] port=3306 socket=/opt/mysql56/data/mysqld.sock datadir=/opt/mysql56/data low-priority-updates event_scheduler=ON character-set-server=utf8 key_buffer_size=4G max_allowed_packet=16M table_open_cache=1024 sort_buffer_size=4M read_buffer_size=2M read_rnd_buffer_size=8M myisam_sort_buffer_size=64M thread_cache_size=2000 query_cache_size=1024M interactive_timeout=15 wait_timeout=15 max_connections=1024 max_connect_errors=99999 max_heap_table_size=16G group_concat_max_len=102400 tmp_table_size=1G open_files_limit=65535 server-id=1921681008 relay-log-index=slave-relay-bin.index relay-log=slave-relay-bin replicate-do-db=test1 #service mysql restart

#replicate-do-db 对日志中的test1数据库改变进行复制

6．克隆主库数据到从库上

为了不影响正常的应用,可以在数据库打开的情况下克隆数据库,在数据库运行的环境下有很多表在缓存中,所以需要刷新所有表到磁盘并且锁定数据库,锁定以后不能退出,否则自动解锁。(主)

#/opt/mysql5/bin/mysql –uroot –p****** MASTER>flush tables with read lock;

一旦数据库锁定以后，数据将不在变化，这时可以记下日志的文件和位置，从库可以指定从当前这个位置开始同步。（主）

#/opt/mysql5/bin/mysql –uroot –p****** mysql> show master status\G; *************************** 1. row ***************************              File: mysql-bin.000010          Position: 336698349      Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)

当前数据库系统写数据的日志文件是mysql-bin.000010，位置为336698349

下一步就是克隆数据库，克隆数据库可以根据不同的存储引擎使用不同的克隆方法，如果存储引擎是MYISAM，我们可以对表文件拷贝的方法，如果是INNODB存储引擎，我们可以使用MYSQLDUMP对当前数据库备份（主）

#/opt/mysql5/bin/mysqldump –uroot –p******   --single-transaction --all-database > /opt/backup/master.sql

当备份完成以后我们就可以对表解锁，允许数据库继续提供服务。(主)

#/opt/mysql5/bin/mysql –uroot –p****** MASTER>unlock tables;

下面我们需要把主库的数据恢复到从库上（从）

#/opt/mysql5/bin/mysql  -uroot  -p****** < /opt/backup/master.sql

7．从库上创建连接指向主库（从）

#/opt/mysql5/bin/mysql  -uroot  -p****** SLAVE>CHANGE MASTER TO MASTER_HOST=’192.168.100.7’ MASTER_PORT=3306, MASTER_USER=’repl’ MASTER_PASSWORD=’slavepass’ MASTER_LOG_FILE=’mysql-bin.000010’ MASTER_LOG_POS=336698349; #启动slave SLAVE>START SLAVE;

五、验证MYSQL复制

通过前面配置的MYSQL复制的例子，我们观察一下MYSQL主库和从库有那些变化，以及如何判断MYSQL 复制是否正常工作。

1． 一旦我们创建数据库复制以后，通过查看主库当前进程,我们会发现多出一个进程Binlog Dump，这个进程是把日志信息和位置返回给SLAVE端的IO线程。（主）

#/opt/mysql5/bin/mysql  -uroot  -p****** mysql> show processlist; +----+-----------------+--------------------+------+-------------+--------+---- | Id | User            | Host               | db   | Command     | Time   | State                                                                 | Info             | Rows_sent | Rows_examined | Rows_read | +----+-----------------+--------------------+------+-------------+--------+---- |  1 | event_scheduler | localhost          | NULL | Daemon      | 104415 | Waiting on empty queue                                                | NULL             |         0 |             0 |         0 | | 96 | repl        | 192.168.100.8:53375 | NULL | Binlog Dump |  86173 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |         0 |

2．查看从服务器进程列表，我们会发现在从服务器上多出两个进程，一个是SLAVE IO进程，用于读写主库的二进制日志，还有一个是SLAVE SQL进程，用于应用主库的日志。（从）

#/opt/mysql5/bin/mysql  -uroot  -p****** mysql> show processlist; +----+-------------+-----------+------+---------+-------+---------------------- | Id | User        | Host      | db   | Command | Time  | State                                                                       | Info             | |  7 | system user |           | NULL | Connect | 86195 | Waiting for master to send event                                            | NULL             | |  8 | system user |           | NULL | Connect | 83358 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |

3．查看复制的状态。（从）

#/opt/mysql5/bin/mysql  -uroot  -p****** mysql> SHOW SLAVE STATUS\G:; *************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 192.168.100.7                   Master_User: rep1                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000009           Read_Master_Log_Pos: 107                Relay_Log_File: master-relay-bin.000021                 Relay_Log_Pos: 253         Relay_Master_Log_File: mysql-bin.000009              Slave_IO_Running: Yes             Slave_SQL_Running: Yes               Replicate_Do_DB: test_p           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                    Last_Error:                  Skip_Counter: 0           Exec_Master_Log_Pos: 107               Relay_Log_Space: 453               Until_Condition: None                Until_Log_File:                 Until_Log_Pos: 0            Master_SSL_Allowed: No            Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No                 Last_IO_Errno: 0                 Last_IO_Error:                Last_SQL_Errno: 0                Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 941 1 row in set (0.00 sec)

以上的列表记录了复制的详细信息,包括当前的复制账号,复制哪个库,忽略那些库不进行复制以及复制状态的信息,以下是我们经常关注的参数含义。

Master_Host: 192.168.100.7 指定主库的IP地址

Master_User: rep1          指定从库用于复制主库上数据的用户

Master_Port: 3306          指定主库的端口

Slave_IO_Running: Yes  表示SLAVE IO进程状态， YES表示运行正常

Slave_SQL_Running: Yes 表是SLAVE SQL进程状态，YES表示运行正常

Seconds_Behind_Master: 0 表示从库落后主库多少秒，0表是实时

Master_Log_File: mysql-bin.000009 IO线程读取主库的日志文件名称

Read_Master_Log_Pos: 107 IO线程读取主库的日志文件位置

Relay_Log_File: master-relay-bin.000021 : 表示从库应用relay log日志名称

Relay_Log_Pos: 253 表示从库应用relay log日志文件位置