MySQL对数据库数据进行复制的基本过程详解

复制

复制是从一个MySQL服务器(master)将数据拷贝到另外一台或多台MySQL服务器(slaves)的过程.复制是异步进行的--slaves服务器不需要持续地保持连接来接收master的数据.依据配置的不同,可以复制所有数据库,或指定的数据库,甚至是某一数据库指定的表.

使用复制功能的目的在于:

向外扩展的解决方案 -- 通过在多台服务器之间分散负载来提高性能.在这种环境下,所有写和更新操作都在master服务器上进行,而读操作则发生在一台或多台slaves服务器上.
数据安全 -- 因为数据是被复制到slave上的,并且slave可以暂停复制过程,因此可以在不破坏master数据的前提下在slave服务器上进行备份
分析 -- 实时数据在master上创建,然而数据分析却可以slave服务器上进行,且不会影响master的性能
长距离数据分布 -- 如果分公司需要主公司的数据复本进行工作,就可以通过复制创建一个本地复本,从而不需要长久地访问master服务器
      MySQL的复制是单向异步的,这与MySQL Cluster的同步复制特性正好相反.MySQL5.5支持半同步(semisynchronous),即在master上的提交之后,并不是立即返回,而是等待至少有一个slave确认说已经收到和记录了当前事务之后,再返回.

最好的复制方法与数据的展现方式及所选择的存储引擎有关,核心的复制格式有两种:SBR(Statement Based Replication) -- 复制所有的SQL语句,和RBR(Row Based Replication) -- 仅复制被改变的rows. 当然也有最三种方案可供选择:MBR(Mixed Based Replication),这也是MySQL5.5之后版本的默认模式.

复制配置

MySQL服务器之间的复制使用的是二进制日志机制.对master的更新与变动都会作为事件(event)记录在日志中,日志中的信息会随变化的不同被记录成不同的格式.slaves被配置成从master读取日志,并且执行二进制日志中的事件到slave本地数据库.一旦master启动二进制日志功能,那么所有语句操作都会被记录下来,每一个slave会收到一份整个日志内容的拷贝.slave的责任就是决定日志中的哪条语句需要被执行,而我们不能通过配置master来仅仅记录某些特定的事件.如果您没有另行指定，在主服务器二进制日志中的所有事件都在slave上执行.如果需要,还可以配置slave仅应用来自于特定数据库或表的事件.

每个slave都会保持一份二进制日志文件的记录,且记录其已经读取和处理过记录的位置.这表明,多个slaves可以连接到master,并且执行日志的不同部分,因为slave自己来控制这个过程,单个slave的断开与连接,不会影响master的操作.同时也正因为每个slave会记录二进制日志中的位置,所以slaves可以断开,重连,然后从记录的位置开始起上.

Master和每一个slave都必须赋予一个唯一的ID(可能使用server_id),另外,还必须告知slave其master的主机,日志文件名和位置(position).可以在会话中通过CHANGE MASTER TO来改变,详细信息会记录在master.info文件中.

1. 如何启动复制

      1.1 创建一个用于复制的用户

每个slave都必须使用标准MySQL用户名和密码连接到master,任何帐号都可以,只要被授予了REPLICATION SLAVE权限.虽然创建一个单独用于复制的用户并不是必须的,但是你需要清楚的是用于复制的帐号的用户名与密码都是用明文的方式存储在master.info中的,因此出于安全的考虑还是创建一个的好.如:

      mysql>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.158.1.100' IDENTIFIED BY 'testpass';

即创建了一个用户名为"repl",密码为"testpass"的帐号,所有的slaves都可以使用同一个帐号,当然我们也可以为每一个slave都创建一个登录帐号.

      1.2 配置Master

首先,必须得开启master的二进制日志功能,其次为master设置一个唯一的server-id -- 1~p, li { white-space: pre-wrap; }232-1 之间的正整数.如在my.cnf或my.ini中作如下设置:

      [mysqld]

      log-bin=master-bin

      server-id=1

需要注意的是:为了在使用InnoDB事务时创建复制达到最大可能的稳定及一致,你需要使用:innodb_flush_log_at_trx_commit=1和sync_binlog=1两个选项.并同时确保:skip-networking=0否则slave与master就无法通信了.

     1.3配置Slave

在slave上我们唯一需要配置的就是为slave指定一个唯一的server-id. Slave上的二进制日志功能的开启不必须的,但开启可以用来作slave上的数据备份或灾难数据恢复,同时也可以使用slave作为更复杂复制拓扑架构的一部分(如:某个slave作为其它slaver的master时).

     1.4 获取Master信息

为了配置slave复制,你需要知道master在其二进制日志中的当前位置,这样当slave开始复制过程时,就知道从当前这个点开始处理事件了.如果在master上已经存在数据,且这些数据需要在开始复制之前同步到其它slaves上,那么你就得让master停止处理语句,获得当前位置,然后导出数据.为了得到master的状态信息,需要通过下面的步骤:

执行:

mysql>FLUSH TABLES WITH READ LOCK 

来阻止所有的写操作,包括InnoDB的commit操作. 需要注意的此时只有退出了连接客户端这个"锁"才能被释放掉.
通过:

mysql>SHOW MASTER STATUS

来确定当前的二进制日志文件及位移量(offset)
p, li { white-space: pr

1.5 在Slave上配置Master信息

mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='master_bin_log_file_name',
-> MASTER_LOG_POS='recorded_log_position';

2. 复制格式的选择

每种二进制日志格式都有自己的优缺点,对大多数用户来说,MBR提供了最好的效果.但当需要为某些特定任务选取SBR或RBR时,可以通过下面的比较来决定哪一个更适合:

     SBR的优势:

从MySQL3.23开始,就被证明了的技术
更少的数据写入日志. 当更新或删除影响到很多行时,SBR会使用更少的存储空间,这也意味着在导入或恢复时需要更少的时间
日志文件包含所有的语句操作所作的变动,因此可以用来审计数据库
     SBR的劣势:

语句表述(Statements)对SBR来说是不安全的,不是所有修改数据的语句都可以使用SBR复制.任何为确定的行为都很难被复制,如具有LIMIT或ORDER BY的DELETE或UPDATE
INSERT ... SELECT 比RBR需要更多数量的行锁定
需要扫描整个表的UPDATE(因为没有在WHERE中使用索引)比RBR要锁定更多的行
对InnoDB,使用了AUTO_INCREMENT的INSERT会阻塞其它非冲突的INSERT
对于复杂的语句,slave在更新或插入之前必须先进行评估和执行,而RBR只需要运行语句应用不同就可以了
存储过程执行同样的NOW()
确定的UDFs必须被应用到所有的slaves上
master与slave上的表必须(几乎)相同
      RBR的优势:

所有的改变都能被复制,这是最安全的复制模式. 但mysql数据库不会被复制,mysql会被认为是一个特殊节点数据库
这种技术与很多其它数据库管理系统一样,因此可以许多在其它系统上的认知,都可以转移到MySQL上来
Master需要更少的锁定来执行:INSERT ... SELECT,INSERT中有AUTO_INCREMENT,以及WHERE中没有使用键值的 UPDATE/DELETE
Slaves在执行INSERT,UPDATE/DELETE时,需要更少的锁定
      RBR的劣势:

RBR势必会产生更多的日志数据
你不能通过log知道什么语句被执行了,然后你却可以通过mysqlbinlog查看什么数据被改变了
相关命令

• mysql>show slave hosts -- 查看所有连接到Master的Slave信息
• mysql>show master status -- 查看Master状态信息
• mysql>show slave status -- 查看Slave状态信息
• mysql>show binary logs -- 查看所有二进制日志
• mysql>show binlog events [IN log_file] -- 查看二进制日志中的事件