MySQL主从复制的原理及配置方法(比较详细)
一、复制的原理
MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新,以便从服务器可以对其数据拷贝执行相同的更新。
将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且,该语句将获得全局读锁定。
MySQL 使用3个线程来执行复制功能,其中1个在主服务器上,另两个在从服务器上。当发出START SLAVE时,从服务器创建一个I/O线程,以连接主服务器并让它发送记录在其二进制日志中的语句。
主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。
从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中,即中继日志。
第3个线程是SQL线程,是从服务器创建用于读取中继日志并执行日志中包含的更新。
有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程;每个从服务器有自己的I/O和SQL线程。
二、复制线程的状态
1.复制主线程的状态
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
线程停止时发生的一个很简单的状态。
2.复制从I/O线程状态
线程正试图连接主服务器。
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
线程停止时发生的一个很简单的状态。
3.复制从SQL线程状态
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
线程停止时发生的一个很简单的状态。
三、复制传递和状态文件
从服务器靠中继日志来接收从主服务器上传回来的日志。并依靠状态文件来记录已经从主服务器接收了哪些日志,已经恢复了哪些日志。
中继日志与二进制日志的格式相同,并且可以用mysqlbinlog读取。SQL线程执行完中继日志中的所有事件并且不再需要之后,立即自动删除它。可以采用 relay-log和 relay-log-index服务器选项覆盖默认中继日志和索引文件名。其中索引文件名的作用是记录目前正在使用中继日志。
在下面的条件下将创建新的中继日志:
1.每次I/O线程启动时创建一个新的中继日志。
2.当日志被刷新时;例如,用FLUSH LOGS或mysqladmin flush-logs。
3.当当前的中继日志文件变得太大时。“太大”含义的确定方法:
max_relay_log_size,如果max_relay_log_size > 0
max_binlog_size,如果max_relay_log_size = 0
状态文件名默认为master.info和relay-log.info。其中IO线程更新master.info文件,SQL线程更新relay-log.info文件。
文件中的行和SHOW SLAVE STATUS显示的列的对应关系为:
master.info文件:
1 文件中的行号
2 Master_Log_File
3 Read_Master_Log_Pos
4 Master_Host
5 Master_User
6 密码(不由SHOW SLAVE STATUS显示)
7 Master_Port
8 Connect_Retry
9 Master_SSL_Allowed
10 Master_SSL_CA_File
11 Master_SSL_CA_Path
12 Master_SSL_Cert
13 Master_SSL_Cipher
14 Master_SSL_Key
relay-log.info文件:
1 Relay_Log_File
2 Relay_Log_Pos
3 Relay_Master_Log_File
4 Exec_Master_Log_Pos
当备份从服务器的数据时,你还应备份这两个小文件以及中继日志文件。它们用来在恢复从服务器的数据后继续进行复制。如果丢失了中继日志但仍然有 relay-log.info文件,你可以通过检查该文件来确定SQL线程已经执行的主服务器中二进制日志的程度。然后可以用 Master_Log_File和Master_LOG_POS选项执行CHANGE MASTER TO来告诉从服务器重新从该点读取二进制日志。当然,要求二进制日志仍然在主服务器上。所以最好建议将自动删除中继日志的特性关闭,手工写shell角本来防止空间满的问题。
四、复制的配置步骤
1.创建专门用于复制的用户(建议这样做),从服务器采用该帐户登陆主服务器:
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%' IDENTIFIED BY 'logzgh' ;
如果你计划从从属服务器主机使用LOAD TABLE FROM MASTER或LOAD DATA FROM MASTER语句,你需要授予该账户其它权限:
授予账户SUPER和RELOAD全局权限。
为所有想要装载的表授予SELECT权限。任何该 账户不能SELECT的主服务器上的表被LOAD DATA FROM MASTER忽略掉。
2.将数据库文件移到从服务器上
情况一:若只用到MyISAM表
(刷新所有表并且阻止其它写入,不要退出该客户端,以保持读锁有效。若退出,读锁就会释放。)
比较简单的办法就是把数据目录打包压缩。
$ tar -xvf /home/mysql/snapshot.tar (在slave上)
可能不需要同步 mysql 数据库,因为在slave上的权限表和master不一样。这时,解开压缩包的时候要排除它。
同时在压缩包中也不要包含任何日志文件,和状态文件master.info、relay-log.info。
+――――――+―――-+―――― +――――――+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+――――――+―――-+―――― +――――――+
| mysql-bin.000058 | 45036137 | | |
+――――――+―――-+―――― +――――――+
mysql> UNLOCK TABLES;
情况二:若用到InnoDB表
方法一:使用InnoDB Hot Backup工具。它无需在master上请求任何锁就能做到快照的一致性,并且在后面中在slave上要用到的快照中已经记录了日志文件名以及偏移位置。
方法二:记录当前日志文件及偏移位置,在master关闭前执行:
mysql> SHOW MASTER STATUS;
尽快记下显示结果中的日志文件及偏移位置。然后,在不解锁的情况下关闭master,确保master上的快照和记录的结果一致。
关闭master服务器,$ mysqladmin -u root shutdown
拷贝 InnoDB 数据文件,日志文件,以及表结构定义文件(.frm文件)。
情况三:可以同时用于MyISAM和InnoDB表
在master上做SQL转储而无需如上所述备份二进制日志。运行mysqldump master-data命令,然后把结果文件转储到slave上。
不过,这比拷贝二进制日志慢点。
3.修改my.cnf文件
在master上my.cnf文件:(重启生效)
log_bin
server_id=1 (值是 1 到 2^32-1 之间的正整数)
在slave上my.cnf文件:
server_id=2 (ID必须和master的ID不同。若有多个slave,则每个slave都必须有唯一的id。)
配置slave的扩展选项
master_port=3306
master_user=rep
master_password=freitag
master_connect_retry=60 (若master宕机或者slave连接断开,slave会定期尝试连接到master上,重试的间隔由该选项来控制,默认值是60秒。)
report_host=db-slave.mycompany.com
slave_net_timeout=3600 (slave默认会在3600秒后,若还没收到来自master的数据,则会当作网络断开的情况来处理。)
服务器认为master.info的优先级比配置文件my.cnf高,
第一次启动slave时,master.info不存在,它从my.cnf中读取选项值,然后把它们保存在master.info中。
下次重启slave时,它只读取master.info的内容,而不会读取my.cnf中的选项值。
想要使用不同的选项值,可以删除master.info后重启slave,或者使用CHANGE MASTER TO语句(推荐)重置选项值。
4.启动从服务器线程
设置master_log_file等参数
mysql> CHANGE MASTER TO MASTER_HOST='qa-sandbox-1′,
MASTER_USER='rep',
MASTER_PASSWORD='logzgh',
MASTER_LOG_FILE='mysql-bin.000007′,
MASTER_LOG_POS=471632;
mysql> START SLAVE;
执行这些程序后,从服务器应连接主服务器,并补充自从快照以来发生的任何更新。
如果你忘记设置主服务器的server-id值,从服务器不能连接主服务器。
注释:为了保证事务InnoDB复制设置的最大可能的耐受性和一致性,
应在主服务器的my.cnf文件中使用innodb_flush_log_at_trx_commit=1和sync-binlog=1。
mysql> show variables; (检查是否read-only,该选项令slave除了slave线程或者拥有SUPER权限用户之外的都不能更新数据,确保slave不会接受来自其他客户端的更新。)
mysql> show processlist; (检查是否slave-start)
在启动mysql的同时启动slave:
mysql> SHOW SLAVE STATUSG;
5.切换slave为master,在slave上:
mysql> RESET MASTER;
五.复制启动选项