MySQL管理与优化(22):MySQL复制

原创
2014/10/18 13:43
阅读数 302

MySQL复制

MySQL复制的优点主要包括:

  • 如果主服务器出现问题,可以快速切换到从服务器提供服务。
  • 可以在从服务器上执行查询操作,降低主服务器的访问压力。
  • 可以在从服务器上执行备份,以避免在备份期间影响主服务器的服务。

安装配置

  • 这里使用本机中MySQL作为Master,虚拟机中的MySQL作为Slave。
  • 在主服务器上创建一个用于复制的帐号,并赋予REPLICATION SLAVE权限:

       

  • 修改主服务器的配置文件my.cnf,开启BINLOG,并设置server-id的值,并重启服务:

        

  • 在主服务器上,设置读锁定有效,这个操作是为了确保没有数据库操作,以便获得一个一致性的快照:

        

  • 得到主服务器上当前的二进制日志名和偏移量值,这个操作的目的是为了在从数据库启动以后,从这个点开始进行数据的恢复:

        

  • 主数据库停止更新数据,备份主服务器数据,用cp或mysqldump方式。
  • 主数据库备份完毕,恢复写操作:

        

  • 修改从服务器my.cnf,设置server-id:

        

  • --skip-slave-start选项启动从数据库,这样不会立即启动从数据库的复制进程:

        

  • 对从数据库做相应设置,指定复制使用的用户,主服务器ip,端口,以及开始执行复制的日志文件和位置,如:

        

  • 在从服务器上启动slave线程:

       

       

  • 在主服务器上创建一些数据,查看从服务器是否同步:

       

       

主要复制启动选项:

log-slave-updates

  • log-slave-updates这个参数用来配置从服务器上的更新操作是否写入二进制日志,默认是不打开的。当其他从从服务器需要从该从服务器进行复制时,需要打开。
  • 该启动参数需要和--logs-bin参数一起使用。

master-connect-retry

  • master-connect-retry参数设置用来设置在和主服务器的连接丢失的时候,重试的时间间隔,默认60秒。

read-only

  • read-only参数用来设置从服务器只能接受超级用户的更新操作。

指定复制的数据库或表

  • 可以使用replicate-do-db, replicate-do-table, replicate-ignore-db, replicate-ignore-table, replicate-wild-do-table来指定从主数据库复制到从数据库的数据库或者表。

slave-skip-erros

  • slave-skip-errors该选项将使得从服务器在复制时,遇到某些错误时,跳过错误继续执行:

       

日常管理维护

查看从服务器状态:

  • 通过show slave status命令查看从服务器复制状态:

       

主从服务器同步维护:

  • 在负载较低的时候,可以暂时阻塞主数据库的更新,强制主从数据库更新同步:

        1. 阻塞主服务器的更新操作:

           

        2. 在从服务器上,执行下面语句,该SELECT语句会阻塞直到从服务器达到指定的日志文件和偏移量后,返回0,如果返回-1,则表示超时退出。查询             返回0时,则主从服务器同步完毕:

            

         3. 恢复主服务器的更新操作:

            

从服务器复制出错的处理:

  • 可以在从服务器执行语句SET GLOBAL SQL_SLAVE_SKIP_COUNT = n跳过主服务器的更新语句,如果更新语句不使用AUTO_INCREMENTLAST_INSERT_ID(),n值为1,否则为2。因为AUTO_INCREMENT,LAST_INSERT_ID()需要从BINLOG中取两个事件。

log event entry exceeded max_allowed_packet的处理

  • 当应用中使用大的BLOG列或者长字符串时,在从服务器恢复时,可能出现"log event entry exceeded max_allowed_packet":

        

多主复制时的自增长变量冲突问题:

  • 当从服务器从多个主服务器同步同一张表的数据时,有可能出现主键冲突,我们可以在主服务器上设置auto_increment_incrementauto_increment_offset来避免这种问题,如:

       

查看从服务器的复制进度:

  • 通过SHOW PROCESSLIST列表中的Slave_SQL_Running线程的Time值,它记录了从服务器当前执行的SQL时间戳与系统时间的差距,单位是秒:

       

切换主从服务器

  • 假设我们有主服务器M,从服务器S1(log-bin开启),S2。现在M出现故障,需要将S1切换为主服务器,并将S2的主服务器配置为S1:

        1. 确保从服务器都已经执行了relay log中的全部更新:

           

        2. 在S1上,执行STOP SLAVE停止从服务器,并RESET MASTER

        3. 在S2上STOP SLAVE, 并执行CHANGE MASTER TO MASTER_HOST='S1'重新设置主数据库,然后再启动START SLAVE

        4. 通知所有客户端应用指向S1。

        5. 删除新的主数据库服务器上的mater.info和relay-log.info文件,否则下次重启的时候还会按照从服务器启动:

           

        6. 如果M服务器能恢复,可以再设置S1为自己的Master。

不吝指正。

展开阅读全文
加载中

作者的其它热门文章

打赏
0
1 收藏
分享
打赏
0 评论
1 收藏
0
分享
返回顶部
顶部