MySQL主从复制

• MySQL主从复制

• MySQL主从复制原理过程

• 主数据库配置步骤

• 从数据库配置步骤

• MySQL主从复制生产标准实践过程

• MySQL主从同步配置步骤

• 定义服务器角色

• 数据库环境准备

• 主库上需要执行的操作

• 编辑主库的配置文件

• 查看开启结果

• 建立同步账号密码

• 从库上需要执行的操作

• 将全备导入到数据库

• 登陆从库执行change master语句

• 开启同步

• 查看是否同步成功语句

• 一些常用语句

• MySQL主从复制原理要点

• 生产场景快速配置MySQL主从复制方案

• MySQL线程状态

• 复制主线程状态show processlist/G

• 复制从I/O线程状态

• 复制从SQL线程状态

MySQL的主从复制有利于数据库架构的健壮性、提升访问速度和易于维护管理。

• 主从服务器互为备份

• 主从服务器读写分离分担网站压力

• 根据服务器拆分业务独立并分担压力

MySQL主从复制原理过程

以下简单描述下MySQL Replication的复制过程

1. slave服务器上执行start slave命令,开启主从复制开关；

2. slave服务器的IO线程会通过 在Master上授权的复制用户权限请求连接Master服务器，并请求从指定binlog日志文件位置(日志文件名和位置在配置主从复制服务时执行change master 命令时指定)之后发送binlog日志内容；

3. master服务器接受来自slave服务器的IO线程的请求后，master服务器上负责复制的IO线程根据slave服务器的IO线程请求的信息读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给slave的IO线程，返回的信息中除了binlog日志内容外，还有本次返回日志内容后再master服务器端的新的binlog文件名称以及在binlog中的下一个指定更新位置；

4. 当slave服务器的IO线程获取到来自master服务器上IO线程发送日志内容以及日志文件以及位置点后，将binlog日志内容一次写入到slave端自身的relaylog(中继日志)文件(mysql-relay-bin.xxxxxx)的末尾，并将新的binlog文件名和位置记录到master-info文件中，以便下次读取master端新binlog日志时能够告诉master服务器需要从新binlog日志的哪个文件哪个位置开始请求新的binlog日志内容；

5. slave服务器的SQL线程会实时的检测本地relaylog中新增加的日志内容，然后及时的把log文件中的内容解析成在master端曾经执行的SQL语句的内容，并在自身的slave服务器上按语句的顺序执行应用这些SQL语句，应用完毕后清理用过的日志；

6. 经过上面的过程，就可以确保在master端和slave端执行了同样的SQL语句。当复制状态正常的情况下，master端和slave端的数据是完全一样的，MySQL的同步机制是有一些特殊的情况，具体请参考官方的说明，大多数情况下，我们不用担心。

主数据库配置步骤

1. 开启主数据库的binlog（二进制日志功能），并设置server-id

2. 创建用于同步数据的账号rep

3. 锁表并查看当前日志名称和位置(pos)[如果备份时使用了master-info参数可不锁表]

4. 备份当前主数据库的全部数据（全备）

5. 解锁主数据库，让主数据库继续提供服务

6. 继续往主数据库写数据

从数据库配置步骤

1. 把主数据库备份的全备数据恢复到从数据库

2. 设置从数据库server-id，并确保这个ID没有被别的MySQL服务所使用。

3. 在从数据库上设置主从同步的相关信息，如主数据库服务器的IP地址、端口号、同步账号、密码、binlog文件名、binlog位置(pos)点

4. 开始主从同步start slave；

5. 查看是否同步成功，show slave status/G;

MySQL主从复制生产标准实践过程

MySQL主从同步配置步骤

1. 准备两台数据库环境，或者单台多实例环境，保证能正常启动和登录；

2. 配置my.cnf文件,主库配置log-bin和server-id参数；从库配置server-id(不能喝主库和其他从库一致)，一般不开启

   log-bin

   从库功能。（注意：修改完后重启生效）；

3. 登录主库使用grant语句增加用于从库连接主库同步的账号，例如：rep并授予

   replication slave

   同步的权限；

4. 登录主库，使用

   flush table with read lock

   （在同步的过程中窗口关闭失效，超时参数[

   interactive_timeout

   和

   wait_timeout

   ]到了也失效），然后

   show master status

   查看binlog的位置状态；

5. 新增加一个窗口，linux命令行备份或导出原有数据库数据，并拷贝到从库所在服务器（如果数据量很大，并且允许停机，可以停机打包，而不用

   mysqldump

   ）；

6. unlock tables

   解锁主库；

7. 把主库到处的原有数据备份恢复到从库；

8. 根据主库的

   show master； status

   查看到的binlog日志文件名和位置信息，在从库上执行

   change master to ...

   语句；

9. 从库

   start slave

   开启同步开关；

10. 从库

    show slave status/G

    检查同步状态，并在主库进行更新测试。

注意：
5.1版本的MySQL版本的锁表语句是flush tables with read lock;
5.5版本的MySQL的锁表语句是flush table with read lock;

定义服务器角色

主库(mysql master): [ip为192.168.247.129的3306端口]

从库(mysql slave):[ip为192.168.247.129的3309端口]

数据库环境准备

具备单机单数据库多实例的环境或者两台服务器每台机器有一个数据的环境

主库上需要执行的操作

编辑主库的配置文件

打开binlog日志(从库不做级联的话不建议开启)和配置不重复的server-id

[root@curder.centos5 /usr/local/mysql/data]# egrep 'log-bin|server-id' /usr/local/mysql/data/3306/my.cnf log-bin=mysql-binserver-id = 3306

查看开启结果

# mysql -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock -e "show variables like 'log_bin'"+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    |+---------------+-------+

建立同步账号密码

首先登陆mysql3306实例主数据库
mysql -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock

执行授权语句

mysql> grant replication slave on *.* to 'rep'@'192.168.0.%' identified by 'reppasswd';mysql> flush privileges;

授权局域网内 rep 用户通过 reppasswd 密码进行同步

另外：如果不存在全备 需要在主库上执行如下语句获得全备

mysql> flush table with read lock; #　备份期间禁止用户写操作(窗口不允许关闭)

备份语句,获得全备

# mysqldump -uroot -paaaaaa -S /usr/local/mysql/data/3306/mysql.sock -B curder --events --master-data=2 > rep.sql

最后操作完别忘了解除锁定 unlock tables;

注意：
5.1版本的MySQL版本的锁表语句是flush tables with read lock;
5.5版本的MySQL的锁表语句是flush table with read lock;

从库上需要执行的操作

将全备导入到数据库

mysql -uroot -paaaaaa -S /usr/local/mysql/data/3308/mysql.sock < ~/rep.sql

登陆从库执行change master语句

change master to master_host='192.168.0.15',master_port=3306,master_user='rep', # 账号信息为主库建立的用户信息master_password='reppasswd', # 在主库设置同步账户时设置master_log_file='mysql-bin.000003', # 该值通过在mysql master服务器全备前锁定的值上show master status;查看master_log_pos=2840; # 该值通过在master服务器上show master status;查看

上述语句信息以文件的形式存放在从库的master.info中。

开启同步

start slave

查看是否同步成功语句

show slave status/G

Slave_IO_Running: Yes # 从库IO进程(从master服务器取log的线程)Slave_SQL_Running: Yes # 从库SQL进程(读取relaylog 写数据)Seconds_Behind_Master: 0 # 落后主库的秒数

另外

一些常用语句

show master status; # 查看master的状态, 尤其是当前的二进制日志及位置show slave status; # 查看slave的状态.reset slave;      # 重置slave状态.start slave;     # 启动slave状态(一旦启动 则开始监听msater的变化)stop slave;     # 暂停slave状态;

MySQL主从复制原理要点

1. 异步方式同步；

2. 逻辑同步模式，多种模式，默认是通过SQL语句执行；

3. 主库通过记录binlog实现对从库的同步；

4. 主库1个IO线程，从库1个IO线程和一个SQL线程来完成；

5. 从库关键字文件

   master.info

   ,

   relay-log

   ,

   relay-info

   功能，

6. 如果从库还想级联从库，需要打开

   log-bin

   和

   log-slave-updates

   参数

生产场景快速配置MySQL主从复制方案

1. 安装好要配置的主从的数据库，配置好log-bin和server-id参数

2. 无需配置主库my.cnf文件，主库的log-bin和server-id参数默认就是配置好的

3. 登录主库增加用于从库连接主库同步的账号，例如：rep，并授权replication slave同步的权限

4. 使用凌晨mysqldump命令带--master-data=1备份的全备数据恢复到从库

5. 在从库执行change master to...语句，无需binlog文件以及对应位置点(在步骤4中以及通过备份参数指定)

6. 从库start slave开启同步开关

7. 从库show slave status/G，检查同步状态，并在主库进行更新测试

MySQL线程状态

复制主线程状态show processlist/G

1. sending binlog event to slave
二进制日志由各种事件组成，一个事件通常为一个更新加一些其他信息。线程已经从二进制日志读取了一个事件并且正将它发送到从服务器

2. finished reading one binlog;switching to next binlog
线程已经读完二进制日志文件并且正打开下一个要大宋到服务器的日志文件

3. has send all binlog to slave;waiting for binlog to be updated
线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。 现在正空闲，等待由主服务器上新的更新导致的出现在二进制日志中的新事件

4. waiting to finalize termination
线程停止时发生的一个很简单的状态

复制从I/O线程状态

该状态出现在Slave_IO_State列，可以通过show slave status显示。

• Connection to master
线程正试图连接主服务器

• Checking master version
建立同主服务器之间的连接后立即临时出现的状态

• Registering slave onmaster
建立同主服务器之间的连接后立即临时出现的状态

• Requesting binlog dump
建立同主服务器之间的连接后立即临时出现的状态,线程向主服务器发送一条请求，索取从请求的二进制日志文件名和位置开始的二进制日志的内容

• Waiting to reconnect after a failed binlog request
如果二进制日志转储请求失败(由于没有连接)，线程进入休眠装填，然后定期尝试重新连接。可以使用--master-connect-retry选项指定重试之间的间隔

• Reconnecting after a failed binlog dump request
线程正尝试重新连接主服务器

• Waiting for master to send event
线程已经连接上主服务器，正等待二进制日志事件到达。如果主服务器正空闲，会持续较长的时间。如果等到持续slave_read_timeout秒，则发生超时。此时线程认为连接被中断并企图重新连接。

• Queueing master event to the relay log
线程已经读取一个事件，正将它复制到中继日志供SQL线程来处理

• Waiting to reconnect after a failed master event read
读取时(由于没有连接)出现错误。线程企图重新连接前将休眠master-connect-retry秒

• Reconnectiong after a failed master event read
线程正尝试重新连接主服务器，当连接重新建立后，状态变为Waiting for master to send

• Waiting for slave SQL thread to free enough relay log space
正使用一个非零relay_log_space_limit值，中继日志已经增长到其组合大小超过该值。I/O线程正等待知道SQL线程处理中继日志内容并删除部分中继日志文件来释放足够的空间

• Waiting for slave mutex on exit
线程停止时发生的一个很简单的状态，I/O线程的State列也可以显示语句的文本，这说明已经从中继日志读取了一个事件，从中提取了语句，并且正在执行语句。

复制从SQL线程状态

• Reading event from the relay log
线程已经从中继日志读取一个事件，可以对事件进行处理

• Has read all relay log;waiting for the slave I/O thread to update it
线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志