Mysql复制
含义是将主数据库的DDL和DML操作通过二进制日志传到复制服务器(也叫从服务器)上,然后在从服务器上对这些日志重新执行(也叫重做),从而使得从服务器和主服务器的数据保持同步.
Mysql支持一台主服务器同时向多台从服务器进行复制,从服务器同时也可以作为其他服务器的主服务器,实现链状复制.
优点:
1.如果主服务器出现问题,可以快速切换到从服务器提供服务.
2.可以在从服务器上执行查询操作,降低主服务器的访问压力.
3.可以在从服务器上执行备份,以避免备份期间影响主服务器的服务.
注意：由于Mysql实现的是异步复制,所以主从服务器之间存在一定的差距,在从服务器上进行查询操作需要考虑到这些数据的差异,一般只有更新不频繁的数据或对实时性要求不高的数据可以通过从服务器查询,实时性要求高的数据仍然需要从主服务器获得.
安装配置(详细步骤在书中451页)
1.确保主从服务器安装了相同版本的数据库.
2.在主服务器上,设置一个复制使用的账户,可以从ip192.168.1.101的主机进行连接,并授予replication slave权限.
Mysql> grant replication slave on *.* to 'rep1'@'192.168.1.101'identified by '1234' ;
3.修改主数据库服务器的配置文件my.cnf,开启binlog并设置server-id的值.
[mysqld]
Log-bin = /home/mysql/log/mysql-bin.log
Server-id = 1
4.在主服务器上,设置读锁定有效,这个操作是为了确保没有数据库操作,以便获得一个一致性的快照.
Mysql> flush tables with read lock;
5.然后得到主服务器上当前的二进制日志名和偏移量值.这个操作的目的是为了在从数据库启动后,从这个点开始进行数据的恢复,
Mysql> show master status;
6.现在主数据库服务器已经停止了更新操作,需要生成主数据库的备份,备份方式可以是直接cp全部的数据文件到从数据库服务器上,也可以通过mysqldump导出数据.如果主数据库的服务可以停止,那么直接使用cp数据文件是最快的方法.
Shell> tar -cvf data.tar data#打包数据库下data目录的文件(只要数据文件和mysql-bin文件)
7.主数据库的备份完毕后,主数据库可以恢复写操作,剩下的操作只需要在从服务器上执行.
Mysql> unlock tables;
8.将主数据库的一致性备份恢复到从数据库上,如果是使用.tar打包的文件包,只需要解开到相应的目录即可.
9.修改从数据库的配置文件my.cnf,增加server-id参数.注意server-id的值必须是唯一的,不能和主数据库的配置相同,如果有多个从数据库服务器,每个从数据库服务器必须有自己唯一的server-id值.
[mysqld]
Server-id = 2
10.在从服务器上,使用--skip-slave-start选项启动从数据库,这样不会立即启动从数据库服务上的复制进程,方便我们对从数据库的服务进行进一步的配置.
Shell> ./bin/mysqld_safe --skip-slave-start &
Shell> starting mysqld daemon with databases from /home/mysql/sysdb/data
11.对从数据库服务器做相应的设置,指定复制使用的用户,主数据库服务器的IP,端口以及开始执行复制的日志文件和位置等,具体语法如下:
Mysql> change master to
-> master_host = 'master_host_name',
-> master_port = master_port,
-> master_user = 'replication_user_name',
-> master_password = 'replication_password',
-> master_log_file = 'recorded_log_file_name',
-> master_log_pos = recorded_log_position;
对于本例应该是:
Mysql> change master to
-> master_host = '192.168.1.100',
-> master_port = 3306,
-> master_user = 'rep1',
-> master_password = '1234',
-> master_log_file = 'mysql-bin.000039',
-> master_log_pos = 102;
12.在从服务器上,启动slave线程.
Mysql> start slave;
13.这时在slave上执行show processlist命令将显示类似如下进程(参考书中453页).
Mysql> show processlist \G ;
14.在slave上执行show slave status \G ;如果Slave_IO_Running和Slave_SQL_Running都为YES，则证明配置成功,如果为NO,那你就可以查看mysql_error日志文件查看.
主要复制的启动选项
上一节的配置选项中,我们已经提到了一些选项如master_host,master_user等.下面介绍几个常用的启动选项.
Log-slave-updates:这个参数用来配置从服务器上的更新操作是否写二进制日志,默认是不打开的.但是如果这个从服务器同时也要作为其他服务器的主服务器,搭建一个链式的复制,那么就需要打开这个选项.这个参数需要和--logs-bin参数一起使用.
Master-connect-retry:这个参数用来设置在和主服务器连接丢失的时候,重试的时间间隔,默认是60秒.
Read-only:这个参数用来设置从服务器只能接受超级用户的更新操作,从而限制应用程序错误的对从服务器的更新操作.下面创建了一个普通用户,在默认情况下,该用户是可以更新从数据库中的数据,但是使用read-only选项启动从数据库后,该用户对从数据的更新会提示错误.
指定复制的数据库或者表
Replicate-do-db,replicate-do-table,
replicate-ignore-db,replicate-ignore-table可以用来指定主数据库复制到从数据库的数据库或表.例如只复制主数据库test中的xiaozhe表,则在启动时:
shell>./bin/mysqld_safe --replicate-do-table = test.xiaozhe &
Shell> starting mysqld daemon with databases from /home/mysql/sysdb/data
Slave-skip-errors:在复制过程中,由于各种原因,从服务器可能会遇到执行Binlog中的sql出错的情况(比如主键冲突),默认情况下会停止复制进程,不再同步,等待用户介入处理.此参数可以跳过错误号,继续执行后面的sql语句.可以定义多个错误号或者定义成all跳过全部的错误.
--slave-skip-errors = [err_code1,err_code2,... | all]
主,从服务器的维护
查看从服务器的状态:mysql> show slave status \G ;
在显示的信息中,我们主要关系的是slave_io_running和slave_sql_running这个两个进程状态是否为yes.只要有一个为no,则表示进程停止,错误原因从last_errno字段可以查看.
Slave_io_running:此进程负责从服务器从主服务器上读取binlog日志,并写入从服务器上的中继日志中.
Slave_sql_running:此进程负责读取并且执行中继日志中的binlog日志.
主从服务器同步维护：在某些繁忙的OLTP(在线事务处理)系统上,由于主服务器更新频繁而从服务器更新速度缓慢,使得主从服务器之间的数据差距越来越大,这种情况下我们需要进行主从服务器的数据同步.常用的方法是在负载较低的时候暂时阻塞主数据库的更新,强制主从数据库更新同步.具体步骤如下(书中460详细).
1.在主服务器上,执行以下语句(注意,会阻塞主数据的所有更新操作)
Mysql> flush tables with read lock ;
记录二进制日志名和偏移量,这些是从服务器负载的目的坐标,假如名字是mysql-bin.000039,位置是974
Mysql> show master status;
2.在从服务器上,执行下面语句,其中master_pos_wait()函数的参数是前面步骤中得到的复制坐标值
Mysql> select master_pos_wait('mysql-bin.000039','974')
这个select语句会阻塞直到从服务器达到指定的日志文件和偏移量后,返回0,如果返回-1,则表示超时退出.返回0则从服务器与主服务器同步.
3.在主服务器上,执行下面语句允许主服务器重新开始处理更新
Mysql> unlock tables;
Log event entry exceeded max_allowed_packet的处理
如果应用中使用大的blog列或者长字符串,那么在从服务器上恢复的时候,可能会出现log event entry exceeded max_allowed_packet的错误,这是因为含有大文本的记录无法通过网络进行传输导致.方法是在my.cnf将max_allowed_packet参数设置为16M,默认是1M.
多主复制时自增变量冲突的问题(463页)
在大多数情况下,一般是单主复制(一台主服务器多台从服务器),但是在某些情况下,可能需要使用多主复制(多台主服务器多台从服务器).这个时候如果主服务器的表采用自动增加变量,那么复制到从服务器的同一张表后很可能会引起主键冲突.因为系统参数auto_increment_increment和auto_increment_offset默认值为1.所以在多主复制的时候,就要定制以上两个系统参数.比如两个master的情况可以这样设置:
Master1 : auto_increment_increment = 2,auto_increment_offset = 1; (1,3,5,7,.....)
Master2 : auto_increment_increment = 2,auto_increment_offset = 0; (0,2,4,6,.....)
查看从服务器的复制进度(466页)
这个值通过show processlist列表中的slave_sql_running线程的time值得到,它记录了从服务器当前执行的sql时间戳与系统时间之间的差距,单位是秒.
切换主从服务器(468页)
假设一个主数据库服务器M和两个从数据库服务器S1,S2.当主数据库M出现问题的时候,我们需要将从数据库S1切换成新的主数据库,同时修改从数据库S2指向新的主数据库S1.此外还需要通知应用修改主数据库的IP地址,如果有可能将出现故障的主数据库M修复或重新重置成新的从数据库.
1.首先要确保所有的从数据库都已经执行了relay log中的全部更新,在每个从服务器上,执行stop slave io_thread,然后检查show processlist的输出,直到看到状态是has read all relay log,表示更新都执行完毕.
Mysql> stop slave io_thread ;
Mysql> show processlist \G ;
2.在从数据库S1上,执行stop slave停止从服务,然后reset master重置成主数据库.
Mysql> stop slave;
Mysql> reset master;
3.在S2上,执行stop slave停止从服务,然后执行change master to master_host = 'S1' 重新设置主数据库,然后再执行start slave启动复制.
Mysql> stop slave;
Mysql> change master to master_host = 's1_host';
Mysql> start slave ;
4.通知所有的客户端将应用指向S1,这样客户端发送的所有更新语法写入到S1的二进制日志.
5.删除新的主数据库服务器(S1)上的master.info和relay-log.info文件,否则下次启动的时候还会按照从服务器启动.
6.最后如果M服务器可以修复,则按照S2的方法配置成S1的从服务器.
注意：上面测试的步骤是默认S1是打开log-bin选项的,这样重置成主数据库后可以讲二进制日志传输到其他从服务器,其次S1上没有打开log-slave-updates参数,否则重置成主数据库后,可以会将已经执行过的二进制日志重复传输给S2.
