MySQL复制

复制是指将主数据库中的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
特别注意：本站所有转载文章言论不代表本站观点，本站所提供的摄影照片，插画，设计作品，如需使用，请与原作者联系，版权归原作者所有