mysql的主从复制
mysql视频教程栏目介绍主从复制
相关免费学习引荐：mysql视频教程
数据库复制关于系统高可用、高机能的晋升饰演者很重要的角色，本文就mysql主从复制触及相关知识停止总结，假如您刚好在从事这方面工作，但愿能够对您有所帮忙。
1 主库配置
1.1 my.cnf配置：
在主库配置文件my.cnf中停止如下根本配置：
log-bin = mysql-bin //二进制日志文件名称主体 log-bin-index = mysql-bin.index //二进制日志文件索引文件 server-id = 1 //独一的效劳器ID，为了保持独一性，可以去ip的尾部 binlog-format = mixed //操纵复制基于的方式，有基于语句(statement)，基于行(row)，混合(mixed)，**主从库需要保持一致** #sync_binlog=1 //引荐配置，开启该选项，mysql每次在事务提交前会将二进制日志同步到磁盘上，包管在效劳器崩溃时不会丧失事件。
默许复制全部数据库，假如需要指定数据库，请参照第7节（复制过滤）。
比方说要指定db1和db2两个数据库停止主从复制： binlog-do-db = db1 binlog-do-db = db2
1.2 增加复制账户：
复制账户增加乃至权限设定：
mysql> grant replication slave, replicatin client on \*.\* to repl@'172.16.226.192' identified by 'repl123456'; //其中repl是会员名，repl123456为账户密码，172.16.226.168为备库的地址。 mysql> flush privileges; //在不重新启动mysql效劳的状况下完成权限的更新
2 备库配置
在备库配置文件my.cnf中停止如下根本配置：
relay-log = slave-relay-bin //中继日志文件名称主体 relay-log-index = slave-relay-bin.index //中继日志文件索引文件 server-id = 2 //独一的效劳器ID，必需要异于主库 #read_only = 1 //限制备库为只读，可选 log_slave_updates = 1 //操纵可否在中继日志施行之后，将同步过来的数据增加到本人的binlog中去，1代表是 skip_slave_start // 该选项能够阻挠备库在崩溃后主动启动复制，倡议开启 即便开启了倡议的选项，备库依然大概在崩溃后被中止，由于master.info和中级日志文件都不是崩溃平安的，所以倡议开启一下选项： sync_master_info = 1 sync_relay_log = 1 sync_relay_log_info = 1
一样可以过滤待同步的数据库，或者表，参照 复制过滤一节。
3 数据库长途备份
数据库长途备份可以选中mysqldump（逻辑备份）停止热备，但关于数据量较大时会比力慢，Xtrabackup（物理备份）也可以对mysql数据库停止热备（这里使用innobackupex-1.5.1），Xtrabackup可以实现innoDB等数据库的在线备份，速度较快且不影响正常读写。这里对全库停止备份。
3.1 创立备份账户
在主效劳器创立会员backup（使用最小权限），用于数据库备份。
mysql> grant reload, lock tables, replication client on \*.\* to backup@'%' identified by 'backup123'; mysql> flush privileges; //在不重新启动mysql效劳的状况下完成权限的更新
3.2 数据库完全备份
完全备份和复原预备两个步骤都是在主库效劳器完成。
innobackupex-1.5.1 --defaults-file=/etc/mysql/my.cnf --user=backup --password=backup123 /mysqlbackup --defaults-file：选中默许的配置文件 --user和--password：离别为停止备份的会员名和密码 /mysqlbackup：目标名目
3.3 复原预备
一样状况下，在备份完成后，数据尚且不克不及用于复原操纵，由于备份的数据中大概会包括尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处置不一致状态。“预备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
innobakupex命令的--apply-log选项可用于实现上述功效。如下面的命令：
innobackupex-1.5.1 --apply-log --user=backup --password=backup123 /mysqlbackup/2017-01-11_21-20-57 假如施行准确，其最后输出的几行信息平常如下： xtrabackup: starting shutdown with innodb_fast_shutdown = 1 120407 9:01:36 InnoDB: Starting shutdown... 120407 9:01:40 InnoDB: Shutdown completed; log sequence number 92036620 120407 09:01:40 innobackupex: completed OK!
在实现“预备”的历程中，innobackupex平常还可以使用--use-memory选项来指定其可以使用的内存的大小，默许平常为100M。假如有足够的内存可用，可以多划分一些内存给prepare的历程，以提高其完成速度。
3.4 数据拷贝
将主效劳器上预备好的数据库拷贝到从效劳器。(当然也可以打包后再拷贝)
scp -r /mysqlbackup/ copyer@192.168.1.192:/data/
3.5 数据复原
在数据复原此前第一关闭从效劳器mysql效劳，并从备份文件夹中的xtrabackup_binlog_info文件中猎取当前正在使用的二进制日志文件，乃至备份这一刻为止二进制日志事件的位置。假如datadir名目不为空，还需要清空datadir名目。
innobackupex命令的--copy-back选项用于施行复原操纵，其通过复制所有数据相关的文件至mysql效劳器datadir名目中来施行复原历程。innobackupex通过backup-my.cnf来猎取datadir名目的相关信息（也可以通过--defaults-file指定my.cnf名目，还要确保datadir途径为空）
innobackupex-1.5.1 --copy-back /mysqlbackup 假如施行准确，其输出信息的最后几行平常如下： innobackupex: Starting to copy InnoDB log files innobackupex: in '/backup/2012-04-07_08-17-03' innobackupex: back to original InnoDB log directory '/mydata/data' innobackupex: Finished copying back files. 120407 09:36:10 innobackupex: completed OK!
请确保如上信息的最后一行显现“innobackupex: completed OK!”。
当数据复原至datadir名目今后，还需要确保所有数据文件的属主和属组均为准确的会员，如mysql，不然，在启动mysqld此前还需要事先修改数据文件的属主和属组。如：
chown -R mysql:mysql /var/lib/mysql/
4 主从连接
4.1 开启从数据库
service mysql start
假如开启mysql失败，可以通过查看错误日志寻觅失败缘由。
4.2 创立主从连接
从库通过复制账户连接到主库：（slave必需处于stop状态才能使以下连接生效）
mysql> change master to master_host='192.168.1.208'，master_user='repl'， master_password='repl123456'，master_log_file='mysql-bin.000001'（备份时得到的活动日志）,master_log_pos=0（备份时得到的活动日志中事件的位置）;
注：假如这里在停止主从连接的时候不断连不上master，有一个大概的缘由是my.cnf配置文件中绑定了本机，即bind-address = 127.0.0.1，我们要做的就是将其注释掉，不然外部机器是拜访不了的。
开启slave：
mysql> start slave;
查看slave状态，可以发明IO线程和SQL线程已处于开启状态，有非常多表征从库连接状态的变量（这些变量一样可以用于设定主从监控），在这里不一一做介绍。
mysql> show slave status; Slave_IO_Running: Yes //表示IO线程运转正常 Slave_SQL_Running: Yes //表示SQL线程运转正常 Seconds_Behind_Master: 0 //表示在网络前提较好的状况下，从库能够及时同步上主库
4.3 常用监控命令
mysql> show processlist\G; //查看数据库效劳线程状况 mysql> show master/slave status\G; //查看主备库状态 mysql> flush logs; //强迫轮换(rotate)二进制日志，从而得到一个完全的二进制日志文件 mysql> show binlog events in '指定二进制日志文件名称' from （从指定位置开端显示） limit （需要显示的事件数目）\G; //查看binlog中事件 mysql> show binary logs; //显示所有的binlogs mysql> reset master; //删除所有二进制日志文件并清空索引文件 mysql> reset slave; //删除slave上复制用的所有文件从新开端 mysql> show slave hosts; //查看主库所具有的从库信息
5 从库延迟较大
假如发明从库延迟较大，就需要寻到延迟大的缘由。参数 innodb_flush_log_at_trx_commit对mysql的写入效力影响较大，有三个取值：
0：每隔一秒，把事务日志缓存区的数据写到日志文件中，乃至把日志文件的数据刷新到磁盘上； 1：每个事务提交时候，把事务日志从缓存区写到日志文件中，并且刷新日志文件的数据到磁盘上； 2：每事务提交的时候，把事务日志数据从缓存区写到日志文件中；每隔一秒，刷新一次日志文件，但不必然刷新到磁盘上，而是取决于操纵系统的调度；
取1时的IO消耗最大，虽然一致性和完全性方面结果最好，但是写入效力最低，而这也是致使从库延迟较大的缘由（假如效劳器配置较高或许会好些）。取0时mysql写入机能很好，但假如 mysqld 进程崩溃，平常会致使最后 1s 的日志丧失 。取2时的写入机能也很好，每次事务提交会写入日志文件，但并不会马上刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时假如 mysqld 进程崩溃，由于日志已经写入到系统缓存，所以并不会丧失数据；在操纵系统崩溃的状况下，平常会致使最后 1s 的日志丧失。
6 混合模式复制
正常状况下使用使用基于语句的复制，而对不平安的语句则切换到基于行的复制。主要有以下几种状况：
- 该语句调取了：
- UUID函数
- 会员自定义函数
- CURRENT_USER或USER函数
- LOAD_FILE函数
- 一个语句同时更新了两个或者两个以上含有AUTO_INCREMENT列的表
- 语句使用了效劳器变量
- 储备引擎不同意使用基于语句的复制，例如，mysql cluster引擎
7 复制过滤
有时候我们不需要对数据库中所有的库停止复制，或者不想对指定库中的某些表停止复制操纵，那么我们就需要对复制停止必然的过滤配置，以到达更合理的复制结果。
1. 基于master
**binlog-do-db=mysql**：主库只是将指定库(mysql)发生的转变记载到二进制日志中。 **binlog-ignore-db=mysql**：主库取消将指定库(mysql)发生的转变记载到二进制日志中。
2. 基于slave
针对数据库停止的过滤：
**replicate-do-db=mysql**：从库只是将指定库(mysql)发生的转变停止重现。 **replicate-ignore-db=mysql**：从库取消将指定库(mysql)发生的转变停止重现。 针对表停止的过滤： **replicate-wild_do-table=mysql.learn**：从库只是将指定库(mysql)中指定表(learn)发生的转变停止重现。 **replicate-wild_ignore-table=mysql.learn**：从库取消将指定库(mysql)中指定表(learn)发生的转变停止重现。
以上复制过滤方式乍一看没有问题，其实还是有需要留意的地方。由于这些过滤方式的结果与复制方式有关系。假如是基于语句的复制，binlog-do-db、binlog-ignore-db、replicate-do-db、replicate-ignore-db与跨库(如use库内和use外)有关系，这一点需要留意。
8 日志清算
暴力清算：（没有主从复制的状况下）
1、重新启动mysql效劳器即可关闭bin日志的记载 2、通过reset master命令停止清算
前提清算
假如存在主从复制关系，则应当使用purge的方式来清算bin日志，语法如下：
purge {master|binary} logs to 'log_name'
purge {master|binary} logs before 'date'会员删除列于在指定的日志或日期此前的日志索引中的所有二进制日志，同时这些日志也会从日志索引文件的清单中删除。
eg. purge master logs to 'mysql-bin.000005'; purge master logs before '2014-08-30 00:00:00';//清除指定日期此前的日志 purge master logs before date_sub(now(),Interval 3 day);清除三天前的日志
按时清算
参数：expire_logs_days
说明：二进制日志主动删除/过期的天数。默许值为'0'，即没有过期的
示例：expire_logs_days = 5，代表日志的有效时间为5天
什么时候会删除过期日志？
每次停止log flush的时候会主动删除过期的日志
什么时候会触发log flush？
1、重新启动 2、binlog文件的大小到达了最大限制 3、手动施行flush logs命令
写在最后
本文只是结合本人的学习乃至实践历程停止了相关总结，如有不当之处望您批判指正。引荐大家学习《高可用MYSQL》、《高机能MYSQL》两本书，最重要的还是实践实践再实践，欢迎交流，共同进步。
