MySQL备份还原和主从复制

备份和恢复

为什么要备份？
实现容灾恢复

硬件故障
软件故障
自然灾害
黑客攻击
误操作删除等数据丢失场景

备份注意要点

能容忍最多丢失多少数据
恢复数据需要在多长时间内完成
需要恢复哪些数据

还原要点

做还原测试，用于测试备份的可用性
还原演练

备份类型

完全备份：整个数据集
部分备份：只备份数据子集，如部分库或表
增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂
差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

注意：二进制文件不应该与数据文件放在同一磁盘

冷、温、热备份

冷备：读写操作均不可进行
温备：读操作可执行，但写操作不可执行
热备：读写操作均可执行
	MyISAM：温备，不支持热备
	InnoDB：都支持，建议使用热备

物理和逻辑备份

物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快。
逻辑备份：从数据库中“导出”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

备份时需要考虑的因素

温备的持锁多久
备份产生的负载
备份过程的时长
恢复过程的时长

备份什么

数据
二进制日志、InnoDB的事务日志
程序代码（存储过程、函数、触发器、时间调度器）
服务器的配置文件

备份工具

cp,tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
LVM的快照：先加锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
mysqldump：逻辑备份工具，适用所有存储引擎，温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份
xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份
MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现
mysqlbackup：热备份， MySQL Enterprise Edition组件
mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，
使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

mysqldump工具介绍

mysqldump：客户端命令，通过mysql协议连接至mysql服务器进行备份
用法：

mysqldump [OPTIONS] database [tables]  (不推荐使用)
mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
mysqldump [OPTIONS] –A [OPTIONS]

mysqldump常见选项：

-A， --all-databases 备份所有数据库，含create database
-B , --databases db_name… 指定备份的数据库，包括create database语句
-E, --events：备份相关的所有event scheduler,即备份计划任务
-R, --routines：备份所有存储过程和自定义函数
--triggers：备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8 指定字符集
--master-data[=#]： 此选项须启用二进制日志
	1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
	2：记录为注释的CHANGE MASTER TO语句此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--single-transaction）
-F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使用实现，此时只刷新一次日志
--compact 去掉注释，适合调试，生产不使用
-d, --no-data 只备份表结构
-t, --no-create-info 只备份数据,不备份create table
-n,--no-create-db 不备份create database，可被-A或-B覆盖
--flush-privileges 备份mysql或相关库时需要使用
-f, --force 忽略SQL错误，继续执行
--hex-blob 使用十六进制符号转储二进制列，当有包括BINARY，VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick 不缓存查询，直接输出，加快备份速度

实现数据库分库备份

方法一：

[root@mariadb ~]#mysql -e 'show databases'|grep -Eiv 'database|information_schema|performance_schema'|sed -r 's#.*#mysqldump -B -F --master-data=2 & |gzip > &_`data +%F`.sql.gzip#'|bash

方法二：

[root@mariadb ~]#for i in `mysql -e 'show databases'|grep -Eiv "database|information_schema|performance_schema"`;do mysqldump -B $i -F --master-data=2 |gzip > ${i}_`date +%F`.sql.gzip;done

MyISAM备份选项：
支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作锁定方法如下：
-x,–lock-all-tables：加全局读锁，锁定所有库的所有表，同时加–singletransaction或–lock-tables选项会关闭此选项功能
注意：数据量大时，可能会导致长时间无法并发访问数据库
-l,–lock-tables：对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,–skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

InnoDB备份选项： 支持热备，可用温备但不建议用
–single-transaction：此选项Innodb中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务，即以事务的方式开启备份
此选项通过在单个事务中转储所有表来创建一致的快照。 仅适用于存储在支持多版本控制的存储引擎中的表（目前只有InnoDB可以）; 转储不保证与其他存储引擎保持一致。 在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
此选项和–lock-tables（此选项隐含提交挂起的事务）选项是相互排斥
备份大型表时，建议将–single-transaction选项和–quick结合一起使用

生产环境备份策略

InnoDB建议备份策略：

[root@mariadb ~]#mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM建议备份策略

[root@mariadb ~]#mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql

场景：每天凌晨两点计划任务自动备份数据库，第二天早上十点数据库被误删除，设法将数据库恢复到十点的状态

1. 如果是yum安装的数据库，直接启动数据库；如果是多实例，初始化数据库后，然后启动数据库。（本实验以多实例安装为例），前提条件为二进制日志和数据库必须分离存放，即二进制日志必须保存完整，此外还要确认字符集和存储引擎。备份策略为innodb建议的备份策略。

   [root@ansible test]#sed -n '/^[^#]/p' /mysql/3306/etc/my.cnf 
   [mysqld]
   port=3306
   datadir=/mysql/3306/data/
   log-bin=/mysql/3306/logbin/mysql-bin
   socket=/mysql/3306/socket/mysql.sock
   innodb_file_per_table
   symbolic-links=0
   [mysqld_safe]
   log-error=/mysql/3306/log/mariadb.log
   pid-file=/mysql/3306/pid/mariadb.pid

2. 导入备份。
   首先关闭二进制日志：

   MariaDB [(none)]> set sql_log_bin=0;
   MariaDB [(none)]>source test/all_bak.sql

3. 将二进制日志导入数据库。
   新开终端查看当前被分的二进制节点：

   [root@ansible ~]#sed -n '/^-- CHANGE MASTER TO/p' test/all_bak.sql 
   -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=385;

将此节点之后的二进制日志导出：

[root@ansible ~]#ls /mysql/3306/logbin/
mysql-bin.000001  mysql-bin.000003  mysql-bin.000005  mysql-bin.000007  mysql-bin.000009  mysql-bin.state
mysql-bin.000002  mysql-bin.000004  mysql-bin.000006  mysql-bin.000008  mysql-bin.index
[root@ansible ~]#mysqlbinlog /mysql/3306/logbin/mysql-bin.00000{3,4,5,6,7,8,9} > /root/test/binlog_bak.sql

导入（在旧终端）：

MariaDB [(none)]>source /root/test/binlog_bak.sql

开启二进制日志：

MariaDB [(none)]> set sql_log_bin=1;

此时，已经将数据库恢复到十点的状态，恢复期间，应通过设置防火墙策略等手段禁止用户访问该实例，数据库恢复完成后，恢复用户的访问。

场景：每天凌晨两点计划任务自动备份数据库，第二天早上十点数据库中的某个表被误删除，之后对数据库的其他表做了一些写操作，直到十点十分发现问题，设法将数据库恢复到十点十分的状态，即恢复被删除的表，并将其余操作一并恢复。

1. 准备工作：依然是用hellodb数据库做演示，删除teachers表，并在students表中插入记录，而后恢复。
   hellodb库的表结构如下：

   MariaDB [hellodb]> show tables;
   +-------------------+
   | Tables_in_hellodb |
   +-------------------+
   | classes           |
   | coc               |
   | courses           |
   | scores            |
   | students          |
   | teachers          |
   | toc               |
   +-------------------+

备份数据库

[root@mariadb ~]#mysqldump -A -F --single-transaction --master-data=2 > all_bak.sql

2. 禁止用户访问此数据库实例。（利用防火墙策略等手段）

3. 关闭二进制日志

   MariaDB [hellodb]> set sql_log_bin=0;

4. 导入备份的数据库

   MariaDB [hellodb]> source /root/all_bak.sql

5. 将备份节点之后的二进制日志导出

   [root@mariadb ~]#mysqlbinlog /var/lib/mysql/mariadb-bin.000002 > binlog.sql

6. 将导出的二进制日志中的删除teachers表的语句删除

   [root@mariadb ~]#sed -i '/^DROP/s/.*/#&/' binlog.sql

7. 将二进制日志导入数据库

   MariaDB [hellodb]> source /root/binlog.sql

8. 打开二进制日志

   MariaDB [hellodb]> set sql_log_bin=1;

至此，误删除的表已经恢复，注意：所有的导入过程必须关闭二进制日志，直到恢复完成后再打开。

percona-xtrabackup实战入门

Percona

官网：www.percona.com
percona-server
InnoDB --> XtraDB

Xtrabackup
percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具
手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html
特点：

备份还原过程快速、可靠
备份过程不会打断正在执行的事务
能够基于压缩等功能节约磁盘空间和流量
自动实现备份检验
开源，免费

xtrabaclip工作原理：
https://www.percona.com/doc/percona-xtrabackup/LATEST/how_xtrabackup_works.html

下载xtrabackup

[root@mariadb tools]#wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

安装xtrabackup

[root@mariadb tools]#yum install percona*.rpm

xtrabackup用法简介

备份：innobackupex [option] BACKUP-ROOT-DIR
选项说明：https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
选项说明：

--user：该选项表示备份账号
--password：该选项表示备份的密码
--host：该选项表示备份数据库的地址
--databases：该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；如："xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
--defaults-file：该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
--incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir
--incremental-basedir：该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
--incremental-dir：该选项表示还原时增量备份的目录
--include=name：指定表名，格式：databasename.tablename

prepare：innobackupex –apply-log [option] BACKUP-DIR
选项说明

--apply-log：最后一次恢复时使用该选项，一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但
	尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。此选项作用是通过回滚未提交的事务及同步已经提交的事
	务至数据文件使数据文件处于一致性状态
--use-memory：和--apply-log选项一起使用，当prepare 备份时，做crashrecovery分配的内存大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
--export：表示开启可导出单独的表之后再导入其他Mysql中
--redo-only：不是最后一次恢复时使用，此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后一个增量备份的合并

还原：innobackupex –copy-back [选项] BACKUP-DIR
innobackupex –move-back [选项] [–defaults-group=GROUP-NAME] BACKUP-DIR
选项说明

--defaults-file=[MY.CNF]:此选项接受一个字符串参数，该参数指定要从中读取默
	认MySQL选项的文件。必须作为命令行中的第一个选项。
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，
	而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场
	景：没有足够的磁盘空间同事保留数据文件和Backup副本

还原注意事项：

1.datadir 目录必须为空。除非指定innobackupex --force-non-emptydirectorires选项指定，否则--copy-backup选项不会复制目标目录已经存在的文件。
2.在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中
3.由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户
chown -R mysql:mysql /data/mysql

以上需要在用户调用innobackupex之前完成

--force-non-empty-directories：指定该参数时候，使得innobackupex --
copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆
盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在
datadir已经存在的文件，会报错失败

使用xtrabackup备份生成的相关文件
使用innobakupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：

xtrabackup_info：innobackupex工具执行时的相关信息，包括版本，备份选项，备
	份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置
xtrabackup_checkpoints：备份类型（如完全或增量）、备份状态（如是否已经为
	prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日
	志序列号LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明
	此页面最近是如何发生改变的
xtrabackup_binlog_info：MySQL服务器当前正在使用的二进制日志文件及至备份
	这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复
backup-my.cnf：备份命令用到的配置选项信息
xtrabackup_logfile：备份生成的日志文件

实验：xtrabackup完全备份及还原

1. 在原主机做完全备份到/data/backup

   [root@mariadb tools]#xtrabackup --backup --target-dir=/data/backup

2. 将备份后生成的目录拷贝到远程主机

   [root@mariadb tools]#scp -r /data/backup/ 192.168.34.108:/data/backup/

3. 在远程主机做预准备工作

   [root@node01 ~]#xtrabackup --prepare --target-dir=/data/backup

4. 执行还原操作

   [root@node01 ~]#xtrabackup --copy-back --target-dir=/data/backup --datadir=/var/lib/mysql

5. 更改还原的数据库的属主和属组

   [root@node01 ~]#chown -R mysql.mysql /var/lib/mysql/

至此，实验结束。

实验：xtrabackup完全、增量备份及还原

创建文件夹用于实验

[root@mariadb backup]#mkdir /data/test/{base,inc1,inc2}

1. 执行完全备份

   [root@mariadb backup]#xtrabackup --backup --target-dir=/data/test/base

2. 往数据库中插入数据后执行第一次增量备份

   [root@mariadb backup]#xtrabackup --backup --target-dir=/data/test/inc1 --incremental-basedir=/data/test/base

3. 再次往数据库插入数据后执行第二次增量备份

   [root@mariadb backup]#xtrabackup --backup --target-dir=/data/test/inc2 --incremental-basedir=/data/test/inc1

4. 删除数据库，准备还原

   [root@mariadb backup]#rm -rf /var/lib/mysql/*
   ``` 
   
   5. 预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
   ```bash
   [root@mariadb backup]#xtrabackup --prepare --target-dir=/data/test/base --apply-log-only

5. 合并第1次增量备份到完全备份

   [root@mariadb backup]#xtrabackup --prepare --apply-log-only --target-dir=/data/test/base --incremental-dir=/data/test/inc1

6. 合并第2次增量备份到完全备份：最后一次还原不需要加选项–apply-log-only

   [root@mariadb backup]#xtrabackup --prepare  --target-dir=/data/test/base --incremental-dir=/data/test/inc2

7. 复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动

   [root@mariadb backup]#xtrabackup --copy-back --target-dir=/data/test/base --datadir=/var/lib/mysql/

8. 还原属性

   [root@mariadb backup]#chown -R mysql:mysql /var/lib/mysql

9. 启动服务

   [root@mariadb backup]#systemctl start mariadb

xtrabackup单表导出和导入

1. 单表备份
   innobackupex –include=’hellodb.students’ /backups
2. 备份表结构
   mysql -e ‘show create table hellodb.students’ > student.sql
3. 删除表
   mysql -e ‘drop table hellodb.students‘
   4 innobackupex –apply-log –export /backups/2018-02-23_15-03-23/
4. 创建表
   mysql>CREATE TABLE students (
   StuID int(10) unsigned NOT NULL AUTO_INCREMENT,
   Name varchar(50) NOT NULL,
   Age tinyint(3) unsigned NOT NULL,
   Gender enum(‘F’,’M’) NOT NULL,
   ClassID tinyint(3) unsigned DEFAULT NULL,
   TeacherID int(10) unsigned DEFAULT NULL,
   PRIMARY KEY (StuID)
   ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
5. 删除表空间
   alter table students discard tablespace;
6. cp /backups/2018-02-23_15-03-23/hellodb/students.{cfg,exp,ibd}
   /var/lib/mysql/hellodb/
7. chown -R mysql.mysql /var/lib/mysql/hellodb/
8. mysql>alter table students import tablespace;

mysql主从复制

mysql的扩展

读写分离
复制：每个节点都有相同的数据集
		向外扩展
		二进制日志
		单向

主从复制的功用

数据分布
负载均衡读
备份
高可用和故障切换
mysql升级测试

主从复制原理

1. master收到用户的写操作，更新数据，同时写入二进制日志。
2. master上的dump thread向slave上的I/O thread发送binary log events
3. slave thread将从master接收的binary log events保存在rely log中
4. slave上的SQL thread从rely log读取日志事件，并写入本地数据库中。至此，完成主从复制

复制类型
异步复制：master写入完成后立即反馈结果给用户，有数据丢失风险，例如master在向slave同步之前突然宕机，然而用户以为已经正确完成写入操作。
同步复制：master写入完成后先将二进制日志同步到所有的slave，之后在反馈结果给用户，缺点是用户体验差，可能造成长时间等待。
半同步复制：master写入完成后先将二进制日志同步到slave，但是只要有一个slave同步完成，即反馈结果给用户，比较合理。

主从配置过程：参看官网
https://mariadb.com/kb/en/library/setting-up-replication/
https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

主节点配置

1. 启用二进制日志
   [mysqld]
   log_bin
2. 为当前节点设置一个全局唯一的ID号
   [mysqld]
   server_id=#
   log-basename=master 可选项，设置datadir中日志名称，确保不依赖主机名
3. 创建有复制权限的账号
   GRANT REPLICATION SLAVE ON . TO ‘repluser’@’HOST’ IDENTIFIED BY
   ‘replpass’;

从节点配置

1. 启动中继日志
   [mysqld]
   server_id=# 为当前节点设置一个全局惟的ID号
   read-only
   relay_log=relay-log relay log的文件路径，默认值hostname-relay-bin
   relay_log_index=relay-log.index 默认值hostname-relay-bin.index
2. 使用有复制权限的用户账号连接至主服务器，并启动复制线程
   mysql> CHANGE MASTER TO MASTER_HOST=’host’,
   MASTER_USER=’repluser’, MASTER_PASSWORD=’replpass’,
   MASTER_LOG_FILE=’mysql-bin.xxxxx’, MASTER_LOG_POS=#;
   mysql> START SLAVE [IO_THREAD|SQL_THREAD];

实验：配置主从复制架构

1. 配置主服务器

   [mysqld]
   log-bin
   server-id=1
   binlog-format=row

2. 在主服务器创建用于主从复制的用户

   MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.34.%' identified by '137226';

3. 配置从服务器

   [mysqld]
   port=3306
   server-id=2
   read-only

4. 在从服务器指定主服务器位置，复制用户、密码，及开始复制点

   MariaDB [(none)]> CHANGE MASTER TO
     MASTER_HOST='192.168.34.102',
     MASTER_USER='repluser',
     MASTER_PASSWORD='137226',
     MASTER_PORT=3306,
     MASTER_LOG_FILE='mariadb-bin.000005',
     MASTER_LOG_POS=329;

5. 开启slave

   MariaDB [(none)]>start slave;

至此，简单的主从复制已经实现，从服务器可以配置多个，只需要server-id不同即可。

复制架构中应该注意的问题

1. 限制从服务器为只读

   在从服务器上设置read_only=ON
   	注意：此限制对拥有SUPER权限的用户均无效
   阻止所有用户, 包括主服务器复制的更新
   	mysql> FLUSH TABLES WITH READ LOCK;

2. RESET SLAVE 在从服务器清除master.info ，relay-log.info, relay log ，开始新的relaylog ,注意：需要先STOP SLAVE

RESET SLAVE ALL 清除所有从服务器上设置的主服务器同步信息如：PORT, HOST, USER和 PASSWORD 等

3. sql_slave_skip_counter = N 从服务器忽略几个主服务器的复制事件，global变量

4. 如何保证主从复制的事务安全
   参看https://mariadb.com/kb/en/library/server-system-variables/
   在master节点启用参数：

   sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差

如果用到的为InnoDB存储引擎：

innodb_flush_log_at_trx_commit=1 每次事务提交立即同步日志写磁盘
innodb_support_xa=ON 默认值，分布式事务MariaDB10.3.0废除
sync_master_info=# #次事件后master.info同步到磁盘

在slave节点启用服务器选项：

skip_slave_start=ON 不自动启动slave

在slave节点启用参数：

sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘

主从级联复制

以A(192,168,34,108)，B(192.168.34.103)，C(192,。168.34.108)分别为主，从，二级从演示。

1. 主服务器主服务器如上。

2. 在从服务器开启二进制日志，并指定更新二进制日志,其余同上：

   [mysqld]
   log-bin
   log-slave-updates

3. 将二级从服务器的主指向一级从服务器即可。

mysql主主复制

主主复制：互为主从
容易产生的问题：数据不一致；因此慎用
考虑要点：自动增长id
配置一个节点使用奇数id
	auto_increment_offset=1 开始点
	auto_increment_increment=2 增长幅度
另一个节点使用偶数id	
	auto_increment_offset=2
	auto_increment_increment=2

主主复制的配置步骤：

1. 各节点使用一个惟一server_id
2. 都启动binary log和relay log
3. 创建拥有复制权限的用户账号
4. 定义自动增长id字段的数值范围各为奇偶
5. 均把对方指定为主节点，并启动复制线程

半同步复制

默认情况下，MySQL的复制功能是异步的，异步复制可以提供最佳的性能，主库把binlog日志发送给从库即结束，并不验证从库是否接收完毕。这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失

实验：实现半同步复制实验

1. 先搭建主从复制架构，（最少一主两从三台服务器）

2. 配置主服务器

   mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
   mysql>SET GLOBAL rpl_semi_sync_master_enabled=1; #永久保存可写入配置文件
   mysql>SET GLOBAL rpl_semi_sync_master_timeout = 1000;超时长为1s #永久保存可写入配置文件
   mysql>SHOW GLOBAL VARIABLES LIKE '%semi%';
   mysql>SHOW GLOBAL STATUS LIKE '%semi%‘;

3. 配置从服务器

   mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
   mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1; #永久保存可写入配置文件

mysql复制过滤器

让从节点仅复制指定的数据库，或指定数据库的指定表
两种实现方式：

1. 服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的事件
   注意：此项和binlog_format相关
   参看：https://mariadb.com/kb/en/librarymysqld-options/#-binlogignore-db
   在主服务器配置：

   binlog_do_db = 数据库白名单列表，多个数据库需多行实现 
   binlog_ignore_db = 数据库黑名单列表

   问题：基于二进制还原将无法实现；不建议使用

2. 从服务器SQL_THREAD在replay中继日志中的事件时，仅读取与特定数
   据库(特定表)相关的事件并应用于本地
   问题：会造成网络及磁盘IO浪费
   在从服务器配置：

   replicate_do_db= 指定复制库的白名单
   replicate_ignore_db= 指定复制库黑名单
   replicate_do_table= 指定复制表的白名单
   replicate_ignore_table= 指定复制表的黑名单
   replicate_wild_do_table= foo%.bar% 支持通配符
   replicate_wild_ignore_table=

mysql主从复制加密

基于SSL复制：
在默认的主从复制过程或远程连接到MySQL/MariaDB所有的链接通信中的数据都是明文的，外网里访问数据或者复制，存在安全隐患。通过SSL/TLS加密的方式进行复制的方法，来进一步提高数据的安全性

获取证书

1. 生成私钥

   [root@mariadb ssl]#(umask 066;openssl genrsa 2048 > cakey.pem)

2. 生成自签名证书

   [root@mariadb ssl]#openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650

3. 生成主服务器私钥及证书申请

   [root@mariadb ssl]#openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key > master.csr

4. 生成从服务器私钥和证书申请

   [root@mariadb ssl]#openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key > slave.csr

5. 分别给master.csr和slave.csr办法证书

   [root@mariadb ssl]#openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 > master.crt
   [root@mariadb ssl]#openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 02 > slave.crt

6. 验证证书的有效性

   [root@mariadb ssl]#openssl verify -CAfile cacert.pem master.crt slave.crt
   master.crt: OK
   slave.crt: OK

此时的目录结构如下：

[root@mariadb ssl]#ls
cacert.pem  master.crt  master.key  slave.csr
cakey.pem   master.csr  slave.crt   slave.key
[root@mariadb ssl]#pwd
/etc/my.cnf.d/ssl

7. 将相关证书分别拷贝到主服务器和各从服务器

   [root@mariadb ssl]#scp cacert.pem slave.crt slave.key 192.168.34.108:/etc/my.cnf.d/ssl/

8. 配置主服务器

   [mysqld]
   log-bin
   server_id=1
   ssl
   ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
   ssl-cert=/etc/my.cnf.d/ssl/master.crt
   ssl-key=/etc/my.cnf.d/ssl/master.key

9. 配置从服务器

   mysql>
   CHANGE MASTER TO
   MASTER_HOST='192.168.34.102',
   MASTER_USER='rplssl',
   MASTER_PASSWORD='centos',
   MASTER_LOG_FILE='mariadb-bin.000005',
   MASTER_LOG_POS=329,
   MASTER_SSL=1,
   MASTER_SSL_CA = '/etc/my.cnf.d/ssl/cacert.pem',
   MASTER_SSL_CERT = '/etc/my.cnf.d/ssl/slave.crt',
   MASTER_SSL_KEY = '/etc/my.cnf.d/ssl/slave.key';

主从复制的监控和维护

清理日志

PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE
datetime_expr }
RESET MASTER
RESET SLAVE

复制监控

SHOW MASTER STATUS
SHOW BINLOG EVENTS
SHOW BINARY LOGS
SHOW SLAVE STATUS
SHOW PROCESSLIST

从服务器是否落后于主服务

Seconds_Behind_Master: 0

如何确定主从节点数据是否一致
percona-tools

数据不一致如何修复
删除从数据库，重新复制