MySQL复制

2018-06-17 23:57:48来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

复制是指将主数据库中的DDL和DML操作通过二进制日志传到复制服务器(也叫从服务器)上,然后从服务器上对这些日志重新执行(也叫重做),从而使得从服务器和主服务器的数据库的数据保持同步。

MySQL支持一台主服务器同时向多台从服务器进行复制,从服务器也可以作为其他服务器的主服务器。

复制的优点:

  • 如果主服务器出现问题,可以快速切换到从服务器提供服务;
  • 可以在从服务器上执行查询操作,降低主服务器的访问压力
  • 可以在从服务器上执行备份,以避免备份期间影响主服务器的服务
  • 由于MySQL上实现的是异步的复制,所以服务器之间存在一定的差异,实时性要求比较高的数据仍然需要从主服务器上获取。

1. 安装配置

复制的步骤:

1.确保主从服务器上安装有相同版本的数据库;

2.在主服务器上,设置一个复制使用的账户,并授予replication slave权限

3.然后修改主服务器上的配置文件,设置server-id的值

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

flush tables with read lock;

5. 然后得到主服务器上当前的二进制日志名,和偏移量。这个操作的命令是为了在从数据库启动之后,在这个点上进行数据的恢复

6.对主数据库的文件进行备份,可以参照MySQL的备份;

7.备份完毕之后,主数据库可以恢复写操作,执行“unlock tables”;

8.将主数据库的一致性备份恢复到从数据库上,如果使用.tar打包的文件,只需要解开到相应的目录下;

9.修改从数据库的配置文件,增加server_id参数,注意server_id参数必须是唯一的;

10.在从服务器上,使用--skip-slave-start选项启动从数据库,这样不会立即启动从数据库服务上的复制过程,方便我们从数据库的服务进一步配置;

11.对从数据库服务器做相应的设置,指定:复制使用的用户,主数据库服务器的IP、端口号以及开始复制的日志文件,语法如下:

mysql > change master to
-> master_host='master_host_name',
-> master_user='replication_user_name',
-> master_password='replication_password',
-> master_log_file='record_log_file_name',
-> master_log_pos='recorded_log_position';

 

12.从服务器上,启动slave线程:start slave

13.slave连接上了master,并且开始接受并执行日志;

14.更新主数据库的数据,从数据库也会同步改变。

 

其他几个常用复制启动选项

log-slave-updates  用来配置从服务器上的更新操作是否写二进制日志,默认是不打开的,但是如果这个从服务器要做为其他服务器的主服务器,搭建一个链式的复制,那么需要打开这个选项;

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

read-only  设置从服务器只能接受超级用户的更新操作,从而限制应用程序错误对从服务器的更新操作。

指定复制的数据库或者表

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

 

2. 日常管理维护

1.查看从服务器的状态: show slave status, 主要关注两个参数的信息:

slave_IO_Running : 此进程负责从服务器从主服务器上读取binlog日志,并且写入从服务器的中继日志中

slave_SQL_Running : 负责读取并且执行中继日志中的BINLOG日志

只要其中有一个的状态是no,表示复制进程停止,错误原因可以从“last_Error”看出

2.主从服务器同步维护

在某些繁忙的OLTP(在线事务处理),由于主服务器更新频繁,从服务其由于各种原因导致更新的速度比较慢,因此需要定期的进行主从服务其的数据同步。

常用的方法:在负载较低的时候暂时阻塞主服务器的更新,强制主从服务器更新同步;

在主服务器上执行:

flush tables with read lock;
show master status;
+-------------------+----------+--------------------+--------------------+
|       file        | position |  binlog_do_db      | binlog_ignore_db   |
+-------------------+----------+--------------------+--------------------+
| mysql-bin.00039   |  974     |                    |                    |
+-------------------+----------+--------------------+--------------------+

 

记录show语句输出的日志名和偏移量,

从服务器上,执行下面的语句,其中master_pos_wait()函数的参数是前面的坐标值

select master_pos_wait('mysql-bin.000039', '974');

 

这个select语句会阻塞直到从服务其达到指定的日志文件和偏移量,返回0. 表示主从同步。

 

设置从服务器跳过主服务器的更新语句

 set global sql_slave_skip_counter=n  其中n为1或者2,如果来自主服务器的更新不使用auto-increment 或者 last_insert_id(),n应该为1,否则为2

log event entry exceeded max_allowed_packed 的处理

    如果使用大的blog列或者长字符串,那么会出现上面“log event entry exceeded max_allowed_packed”错误,原因是含有大文本的记录无法通过网络进行传输导致的,结解     决办法是增加max_allowed_packet参数的大小。

    set @@global.max_allowed_packet=16777216;

  查看从服务器的赋值进度

       通过show processlist列表的slave_sql_running线程的time值得到,它记录了从服务器当前执行的sql时间戳与系统时间之间的差异

3.切换主从服务器

假设一个主服务器M,两个从服务器S1,S2同时指向主数据库服务器M, 当主数据库M故障,需要将其中一个从服务器切换成主数据库服务器,同时使得另一个从服务器指向新的主服务器,操作如下:

1.首先确保所有的从数据库都已经执行了relay log中的全部更新,在每个从服务器上执行stop slave io_thread,然后show processlist的输出,直到看到状态是 has read all relay log,表示更新结束;

2.在从数据库(S1)上,执行stop slave停止服务,然后reset master重置为主数据库

stop slave;

reset master;

3.在S2上执行stop slave停止服务,然后执行change master to master_host='s1' 重新设置主数据库。

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

 

小结

  复制是MySQL数据库中常用的功能,可以有效地保证主数据库的安全,并减轻主数据库的备份压力,以及分担一些查询压力。

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL 账户管理

下一篇:Navicat相关应用及注意事项