MYSQL复制及其配置

目录 []

if (window.showTocToggle) { var tocShowText = "显示"; var tocHideText = "隐藏"; showTocToggle(); } [] 复制介绍

MySQL支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件，并维 护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时，它通知主服务器从服务器在日志中读取的最后一次 成功更新的位置。从服务器接收从那时起发生的任何更新，然后封锁并等待主服务器通知新的更新。

复制有利于健壮性、速度和系统管理：

• 主服务器/从服务器设置增加了健壮性。主服务器出现问题时，你可以切换到从服务器作为备份。
• 通过在主服务器和从服务器之间切分处理客户查询的负荷，可以得到更好的客户响应时间。SELECT查询可以发送到从服务器以降低主服务 器的查询处理负荷。但修改数据的语句仍然应发送到主服务器，以便主服务器和从服务器保持同步。如果非更新查询为主，该负载均衡策略很有效，但一般是更新查 询。
• 使用复制的另一个好处是可以使用一个从服务器执行备份，而不会干扰主服务器。在备份过程中主服务器可以继续处理更新。

[] 复制实施细节

• MySQL使用3个线程来执行复制功能(其中1个在主服务器上，另两个在从服务器上。当发出START SLAVE时，从服务器创建一个I/O线程，以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服 务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中，即中继日志。第3个线程是SQL线程，是从服务器创建用于读取中继日志并执行日 志中包含的更新。

• 有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程；每个从服务器有自己的I/O和SQL线程。

• 这样读取和执行语句被分成两个独立的任务。如果语句执行较慢则语句读取任务没有慢下来。例如，如果从服务器有一段时间没有运行了，当从服 务器启动时，其I/O线程可以很快地从主服务器索取所有二进制日志内容，即使SQL线程远远滞后。如果从服务器在SQL线程执行完所有索取的语句前停 止，I/O 线程至少已经索取了所有内容，以便语句的安全拷贝保存到本地从服务器的中继日志中，供从服务器下次启动时执行。这样允许清空主服务器上的二进制日志，因为 不再需要等候从服务器来索取其内容。

• SHOW PROCESSLIST语句可以提供在主服务器上和从服务器上发生的关于复制的信息。

[] 复制线程状态描述 [] 复制主线程状态

下面列出了主服务器的Binlog Dump线程的State列的最常见的状态。如果你没有在主服务器上看见任何Binlog Dump线程，这说明复制没有在运行—即，目前没有连接任何从服务器。

• Sending binlog event to slave

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

• Finished reading one binlog; switching to next binlog

线程已经读完二进制日志文件并且正打开下一个要发送到从服务器的日志文件。

• Has sent all binlog to slave; waiting for binlog to be updated

线程已经从二进制日志读取所有主要的更新并已经发送到了从服务器。线程现在正空闲，等待由主服务器上新的更新导致的出现在二进制日志中的新事件。

• Waiting to finalize termination

线程停止时发生的一个很简单的状态。

[] 复制从IO线程状态

下面列出了从服务器的I/O线程的State列的最常见的状态。该状态也出现在Slave_IO_State列，由SHOW SLAVE STATUS显示。

• Connecting to master

线程正试图连接主服务器。

• Checking master version

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

• Registering slave on master

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

• Requesting binlog dump

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

• Waiting to reconnect after a failed binlog dump 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秒。

• Reconnecting after a failed master event read

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

• Waiting for the slave SQL thread to free enough relay log space

正使用一个非零relay_log_space_limit值，中继日志已经增长到其组合大小超过该值。I/O线程正等待直到SQL线程处理中继日志内容并删除部分中继日志文件来释放足够的空间。

• Waiting for slave mutex on exit

线程停止时发生的一个很简单的状态。

[] 复制从SQL线程状态

下面列出了从服务器的SQL线程的State列的最常见的状态。

• Reading event from the relay log

线程已经从中继日志读取一个事件，可以对事件进行处理了。

• Has read all relay log; waiting for the slave I/O thread to update it

线程已经处理了中继日志文件中的所有事件，现在正等待I/O线程将新事件写入中继日志。

• Waiting for slave mutex on exit

线程停止时发生的一个很简单的状态。

[] MYSQL主从配置实例

通过MYSQL自身的replication实现不同机器上多个数据库的同步。 下面示例是二个互为主从服务结点的配置分案：

[] 硬件环境

NODE 1:

eth0 : 172.16.60.12/255.255.255.0

NODE 2 :

eth0 : 172.16.60.38/255.255.255.0

[] 软件

分别在结点上安装MYSQL服务，注意MYSQL的版本要求一致.

[] 设置MYSQL Replication [] NODE 1配置

• 增加一用于数据库同步的用户repl

mysql>grant replication slave on *.* to 'repl'@'172.16.60.38' identified by '123456';

(授与从结点2登录用户repl数据复制权限，密码为123456)

mysql>flush privileges;(使权限生效)

• 停止MYSQL服务

service mysql stop

• 更改MYSQL配置文件/etc/my.cnf

[mysqld]
log-bin=mysql-bin #允许二进制更新日志
server-id=1 #服务器的唯一标识，整数介于1~2的32次方之间
binlog-do-db=test #指定做二进制更新日志的数据库，多个数据库需要用多条语句
binlog-ignore-db=mysql #指定不做二进制更新日志的数据库
#如果此服务器只作MASTER，注释掉下面各行
master-host=172.16.60.38 #MASTER服务器地址
master-user=repl #MASTER服务器上用于replicate的用户，必须有replication slave权限
master-password=123456 #MASTER服务器上用于replicate的用户的密码
master-port=3306 #MASTER服务器上TCP端口号
master-connect-retry=60 #如果连接MASTER服务器失败，重试连的间隔，以秒为单位
replicate-do-db=test #指明要复制的数据库
replicate-ignore-db=mysql #指明不做复制的数据库
user = mysql
pid-file = /usr/local/mysql/data/jacky-desktop.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /tmp
skip-external-locking

• 将要进行复制的数据库打包传到NODE 2
• 启动MYSQL

service mysql start

[] NODE 2配置

• 增加一用于数据库同步的用户repl

mysql>grant replication slave on *.* to 'repl'@'172.16.60.12' identified by '123456';

(授与从NODE 1登录用户repl数据复制权限，密码为123456)

mysql>flush privileges;

• 停止MYSQL服务

service mysql stop

• 更改MYSQL配置文件/etc/my.cnf

[mysqld]
server-id=2
#for master
log-bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql
#for slave
master-host=172.16.60.12
master-port=3306
master-user=repl
master-password=123456
master-connect-retry=60
replicate-do-db=test
replicate-ignore-db=mysql
user = mysql
pid-file = /usr/local/mysql/data/jacky-desktop.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /tmp
skip-external-locking

• 将先前从NODE 1发过来的数据库打包文件解压并替换掉原来文件，并注意文件权限

• 启动MYSQL

service mysql start

[] 测试

设置完毕后可以通过命令查看主从状态：

show master status; 查看MASTER状态

show slvae status; 查看SLAVE状态

show processlist; 查看当前进程

stop slave; 暂时停止slave进程

start slave; 开始slave进程