mysql copy_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 readlock;

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数据库中常用的功能，可以有效地保证主数据库的安全，并减轻主数据库的备份压力，以及分担一些查询压力。