主从实现

主从配置

主服务器

server-id=1   #必须。设置服务器id，为1表示主服务器。规范为服务器IP的后段
log_bin=mysql-bin  #必须。启动MySQ二进制日志系统。
log_bin_index = mysql-bin.index # index索引
binlog_format=mixed # binlog形式
binlog-do-db=osyunweidb  #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行。
binlog-ignore-db=mysql   #不同步mysql系统数据库。

从服务器

server-id=2   #必须。设置服务器id，为2表示从服务器。规范为服务器IP的后段
relay-log = relay-log # 中继日志
relay-log-index = relay-log.index  #中继日志索引
log_bin=mysql-bin  #不必须。启动MySQ二进制日志系统。
binlog-do-db=osyunweidb  #需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行。
binlog-ignore-db=mysql   #不同步mysql系统数据库。
expire_logs_days        = 100 #binlog过期时间
max_binlog_size   = 100M #binlog最大

# 停止slave同步进程
stop slave ;
# 执行同步语句
change master to master_host='192.16.168.18',master_user='admin',master_password='admin',master_log_file='mysql-bin.000001' ,master_log_pos=107;    
# 开启slave同步进程
start slave;
# 查看slave同步信息。注意：一定要看输出的信息
show slave status\G;

命令简介

查看binlog是否开启

show variables like '%log_bin%';

只查看第一个binlog文件的内容

show binlog events; 

查看指定binlog文件的内容

show binlog events in 'mysql-bin.000002';

查看当前正在写入的binlog文件

show master status\G; 

获取binlog文件列表

show binary logs; 

基于pos值查看binlog内容

mysqlbinlog --start-postion=107 --stop-position=1000 -d 库名 二进制文件  

基于开始/结束时间

mysqlbinlog --start-datetime='2013-09-10 00:00:00' --stop-datetime='2013-09-10 01:01:01' -d 库名 二进制文件 

授权用户

GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY 'xxx';

配置从库

change master to master_host='192.168.1.160',master_user='repl_user',master_password='xxx',master_log_file='mysql-bin.000001' ,master_log_pos=4;

Mysql关键配置

innodb_buffer_pool_size

Innodb最重要的参数，主要作用是缓存innodb表的索引，数据，插入数据时的缓冲，默认值为128M ，如果是一个专用DB服务器，那么它可以占到内存的70%-80% 。

 innodb_buffer_pool_size=4G 

innodb_log_file_size

innodb的logfile就是事务日志，用来在mysql crash后的恢复.所以设置合理的大小对于mysql的性能非常重要，直接影响数据库的写入速度，事务大小，异常重启后的恢复。 一般取256M可以兼顾性能和recovery的速度

 innodb_log_file_size=256M 

innodb_log_buffer_size

事务在内存中的缓冲，也就是日志缓冲区的大小， 默认设置即可，具有大量事务的可以考虑设置为16M 。

innodb_flush_log_at_trx_commit

控制事务的提交方式,也就是控制log的刷新到磁盘的方式。

0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作；
1：（默认为1）在每次事务提交的时候将logbuffer 中的数据都会写入到log file，同时也会触发文件系统到磁盘的同步；

2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作。

innodb_flush_method

这个参数控制着innodb数据文件及redo log的打开、刷写模式。

innodb_flush_method=O_DIRECT 

binlog

binlog日志格式，mysql默认采用statement，建议使用mixed

binlog_format = MIXED    

备份

xtrabackup安装

https://www.percona.com/doc/percona-xtrabackup/2.4/installation/apt_repo.html

使用说明

innobackupex：这个是其实是下面三个工具的一个perl脚本封装，可以备份MyISAM, InnoDB, XtraDB表。但在处理Myisam时需要加一个读锁。
xtrabackup：一个由C编译而来的二进制文件，只能备份InnoDB和XtraDB数据。
xbcrypt：用来加密或解密备份的数据。
xbstream：用来解压或压缩xbstream格式的压缩文件。

**innobackupex **

xtrabackup命令只备份数据文件,并不备份数据表结构(.frm)，所以使用xtrabackup恢复的时候必须有对应表结构文件(.frm)。用innobackupex命令，此命令相当于冷备份，复制数据目录的索引，数据，结构文件，但会有短暂的锁表（时间依赖于MyISAM大小）。

参数

备份

innobackupex [--compress] [--compress-threads=NUMBER-OF-THREADS] [--compress-chunk-size=CHUNK-SIZE]
             [--encrypt=ENCRYPTION-ALGORITHM] [--encrypt-threads=NUMBER-OF-THREADS] [--encrypt-chunk-size=CHUNK-SIZE]
             [--encrypt-key=LITERAL-ENCRYPTION-KEY] | [--encryption-key-file=MY.KEY]
             [--include=REGEXP] [--user=NAME]
             [--password=WORD] [--port=PORT] [--socket=SOCKET]
             [--no-timestamp] [--ibbackup=IBBACKUP-BINARY]
             [--slave-info] [--galera-info] [--stream=tar|xbstream]
             [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME]
             [--databases=LIST] [--no-lock]
             [--tmpdir=DIRECTORY] [--tables-file=FILE]
             [--history=NAME]
             [--incremental] [--incremental-basedir]
             [--incremental-dir] [--incremental-force-scan] [--incremental-lsn]
             [--incremental-history-name=NAME] [--incremental-history-uuid=UUID]
             [--close-files] [--compact]
             BACKUP-ROOT-DIR

#常用参数
--user：该选项表示备份账号。
--password：该选项表示备份的密码。
--port：该选项表示备份数据库的端口。
--host：该选项表示备份数据库的地址。
--socket：该选项表示mysql.sock所在位置，以便备份进程登录mysql。
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置。
--databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；如："db1 db2"，同时，在指定某数据库时，也可以只指定其中的某张表。如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。

#压缩参数
--compress：该选项表示压缩innodb数据文件的备份。
--compress-threads：该选项表示并行压缩worker线程的数量。
--compress-chunk-size：该选项表示每个压缩线程worker buffer的大小，单位是字节，默认是64K。

#加密参数
--encrypt：该选项表示通过ENCRYPTION_ALGORITHM的算法加密innodb数据文件的备份，目前支持的算法有ASE128,AES192,AES256。
--encrypt-key：该选项使用合适长度加密key，因为会记录到命令行，所以不推荐使用。
--encryption-key-file：该选项表示文件必须是一个简单二进制或者文本文件，加密key可通过以下命令行命令生成：openssl rand -base64 24。
--encrypt-threads：该选项表示并行加密的worker线程数量。
--encrypt-chunk-size：该选项表示每个加密线程worker buffer的大小，单位是字节，默认是64K。

#增量备份参数
--incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir。
--incremental-basedir：该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录，与--incremental同时使用。
--incremental-lsn：该选项表示指定增量备份的LSN，与--incremental选项一起使用。
--incremental-dir：该选项表示增量备份的目录。
--incremental-force-scan：该选项表示创建一份增量备份时，强制扫描所有增量备份中的数据页。
--incremental-history-name：该选项表示存储在PERCONA_SCHEMA.xtrabackup_history基于增量备份的历史记录的名字。Percona Xtrabackup搜索历史表查找最近（innodb_to_lsn）成功备份并且将to_lsn值作为增量备份启动出事lsn.与innobackupex--incremental-history-uuid互斥。如果没有检测到有效的lsn，xtrabackup会返回error。
--incremental-history-uuid：该选项表示存储在percona_schema.xtrabackup_history基于增量备份的特定历史记录的UUID。 

#主从
--slave-info：该选项表示对slave进行备份的时候使用，打印出master的名字和binlog pos，同样将这些信息以change master的命令写入xtrabackup_slave_info文件。可以通过基于这份备份启动一个从库。
--safe-slave-backup：该选项表示为保证一致性复制状态，这个选项停止SQL线程并且等到show status中的slave_open_temp_tables为0的时候开始备份，如果没有打开临时表，bakcup会立刻开始，否则SQL线程启动或者关闭知道没有打开的临时表。如果slave_open_temp_tables在--safe-slave-backup-timeount（默认300秒）秒之后不为0，从库sql线程会在备份完成的时候重启。

--include：该选项表示使用正则表达式匹配表的名字[db.tb]，要求为其指定匹配要备份的表的完整名称，即databasename.tablename。
--tables-file：该选项表示指定含有表列表的文件，格式为database.table，该选项直接传给--tables-file。
--no-timestamp：该选项可以表示不要创建一个时间戳目录来存储备份，指定到自己想要的备份文件夹。
--rsync：该选项表示通过rsync工具优化本地传输，当指定这个选项，innobackupex使用rsync拷贝非Innodb文件而替换cp，当有很多DB和表的时候会快很多，不能--stream一起使用。
--stream：该选项表示流式备份的格式，backup完成之后以指定格式到STDOUT，目前只支持tar和xbstream。
--ibbackup：该选项指定了使用哪个xtrabackup二进制程序。IBBACKUP-BINARY是运行percona xtrabackup的命令。这个选项适用于xtrbackup二进制不在你是搜索和工作目录，如果指定了该选项,innoabackupex自动决定用的二进制程序。
--kill-long-queries-timeout：该选项表示从开始执行FLUSH TABLES WITH READ LOCK到kill掉阻塞它的这些查询之间等待的秒数。默认值为0，不会kill任何查询，使用这个选项xtrabackup需要有Process和super权限。
--kill-long-query-type：该选项表示kill的类型，默认是all，可选select。
--ftwrl-wait-threshold：该选项表示检测到长查询，单位是秒，表示长查询的阈值。
--ftwrl-wait-query-type：该选项表示获得全局锁之前允许那种查询完成，默认是ALL，可选update。
--galera-info：该选项表示生成了包含创建备份时候本地节点状态的文件xtrabackup_galera_info文件，该选项只适用于备份PXC。
--defaults-extra-file：该选项指定了在标准defaults-file之前从哪个额外的文件读取MySQL配置，必须在命令行的第一个选项的位置。一般用于存备份用户的用户名和密码的配置文件。
----defaults-group：该选项表示从配置文件读取的组，innobakcupex多个实例部署时使用。
--no-lock：该选项表示关闭FTWRL的表锁，只有在所有表都是Innodb表并且不关心backup的binlog pos点，如果有任何DDL语句正在执行或者非InnoDB正在更新时（包括mysql库下的表），都不应该使用这个选项，后果是导致备份数据不一致，如果考虑备份因为获得锁失败，可以考虑--safe-slave-backup立刻停止复制线程。
--tmpdir：该选项表示指定--stream的时候，指定临时文件存在哪里，在streaming和拷贝到远程server之前，事务日志首先存在临时文件里。在 使用参数stream=tar备份的时候，你的xtrabackup_logfile可能会临时放在/tmp目录下，如果你备份的时候并发写入较大的话 xtrabackup_logfile可能会很大(5G+)，很可能会撑满你的/tmp目录，可以通过参数--tmpdir指定目录来解决这个问题。
--history：该选项表示percona server 的备份历史记录在percona_schema.xtrabackup_history表。   --close-files：该选项表示关闭不再访问的文件句柄，当xtrabackup打开表空间通常并不关闭文件句柄目的是正确的处理DDL操作。如果表空间数量巨大，这是一种可以关闭不再访问的文件句柄的方法。使用该选项有风险，会有产生不一致备份的可能。
--compact：该选项表示创建一份没有辅助索引的紧凑的备份。
--throttle：该选项表示每秒IO操作的次数，只作用于bakcup阶段有效。apply-log和--copy-back不生效不要一起用。

全量备份&还原

mysql> create database xtra_test default charset utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use xtra_test
Database changed
mysql> create table M(id int,name varchar(10))engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table I(id int,name varchar(10))engine=innodb;
Query OK, 0 rows affected (0.00 sec)

备份

innobackupex -H 127.0.0.1 -u root -p xxx -P 3306 -S /var/run/mysqld/mysqld.sock /home/xtarback/

脚本

环境变量

env.sh

#!/bin/bash
export BACK_DIR=/data/
export BACK_FOLDER=$BACK_DIR/backup
export BACK_FOLDER_FULL=$BACK_FOLDER/full
BACK_FOLDER_INCRE=$BACK_FOLDER/incre

MYSQL_CNF=/etc/mysql/mysql.cnf
MYSQL_USR=backup
MYSQL_PWD=123456

LAST_FILE=""

TRANSFER_HOST=192.168.1.161
TRANSFER_USER=root
export TRANSFER_PATH=/data/backup

全量脚本

full.sh

#!/bin/bash
. ./env.sh
## backup backupfolder
if [ -d $BACK_FOLDER ]; then
  echo "mv folder"
  echo $BACK_FOLDER
  mv $BACK_FOLDER $BACK_FOLDER"_"`date +%Y_%m_%d_%H_%M_%S`
fi
mkdir $BACK_FOLDER
innobackupex --defaults-file=$MYSQL_CNF --user=$MYSQL_USR --password=$MYSQL_PWD $BACK_FOLDER_FULL
echo "finish full backup"

if [ -d $BACK_FOLDER_FULL ]; then
  cd $BACK_FOLDER_FULL
  LAST_FILE=`ls -t | head -1 | xargs readlink -f`
fi
rsync -avPz --delete $BACK_DIR  192.168.1.161:$BACK_DIR

增量脚本

incre.sh

#!/bin/bash
. ./env.sh
if [ -d $BACK_FOLDER_FULL ]; then
  cd $BACK_FOLDER_FULL
  LAST_FILE=`ls -t | head -1 | xargs readlink -f`
fi

if [ -d $BACK_FOLDER_INCRE ]; then
  cd $BACK_FOLDER_INCRE
  LAST_FILE=`ls -t | head -1 | xargs readlink -f`
fi

if [ "$LAST_FILE" = "" ]; then 
  echo "last file is null"
  exit 1
fi

echo $LAST_FILE

innobackupex --defaults-file=$MYSQL_CNF --user=$MYSQL_USR --password=$MYSQL_PWD --incremental $BACK_FOLDER_INCRE --incremental-basedir=$LAST_FILE

rsync -avPz --delete $BACK_DIR 192.168.1.161:$BACK_DIR

恢复脚本

#!/bin/bash
. ./env.sh
FULL_FILE=""
INCRE_COUNT=0
INCRE_FILES=""
LAST_INCRE_FILE=""
echo "==============="
if [ -d $BACK_FOLDER_FULL ]; then
  cd $BACK_FOLDER_FULL
  FULL_FILE=`ls -tr | head -n 1 | xargs readlink -f`
  echo $FULL_FILE
else
  echo "no full folder"
  exit 1 
fi
echo "================"
if [ -d $BACK_FOLDER_INCRE ]; then
  cd $BACK_FOLDER_INCRE
  INCRE_COUNT=`ls -t | wc -l` 
  INCRE_FILES=`ls -tr |  awk -v count=$INCRE_COUNT 'NR<count {print $0 }'| xargs readlink -f `
  LAST_INCRE_FILE=`ls -t | head -n 1 | xargs readlink -f `
fi
echo $INCRE_FILES | xargs -n 1
echo "============"
if [ "$LAST_INCRE_FILE" = "" ]; then
  echo "full backup"
  innobackupex --apply-log $FULL_FILE
else
  echo "increment add"
  innobackupex --apply-log --redo-only $FULL_FILE
  echo $INCRE_FILES | xargs -I {} innobackupex --apply-log --redo-only --user-memory=1G $FULL_FILE --incremental-dir={}
  echo $LAST_INCRE_FILE | xargs -I {} innobackupex --apply-log --user-memory=1G $FULL_FILE --incremental-dir={}
fi
innobackupex --copy-back $FULL_FILE

单个命令

完全备份准备

innobackupex --apply-log /data/backup/2018-05-21_15-02-53/

增量数据准备

innobackupex --apply-log --redo-only /data/backup/full/2018-12-18_18-41-14
innobackupex --apply-log --redo-only  --user-memory=1G /data/backup/full/2018-12-18_18-41-14 --incremental-dir=/data/backup/incre/2018-12-18_18-41-52
innobackupex --apply-log --user-memory=1G /data/backup/full/2018-12-18_18-41-14 --incremental-dir=/data/backup/incre/2018-12-18_18-42-47
innobackupex --copy-back /data/backup/full/2018-12-18_18-41-14

恢复数据

需保证 mysql 文件目录为空

innobackupex --copy-back /data/backup/2018-05-21_15-02-53/

工具

SCP无密码传输

ssh-keygen -t rsa 
scp -r id_rsa.pub 10.10.10.17:/root/.ssh/authorized_keys

SaltStack使用

管理端
- apt install salt-master
- salt-key -L #查看当前证书签证情况
- salt-key -A -y #同意签证所有没有接受的签证情况
- salt-key -L
- salt '*' cmd.run 'ls -al'
- salt-cp '*' salt.doc /usr/local/
#被管理端
- apt install salt-minion
- sed -i 's@#master:.*@master:192.168.1.160@' /etc/salt/minion
- echo 192.168.1.161 > /etc/salt/minion_id
- service salt-minion start # debug 运行如下 salt-minion -l debug

rsync传输

 -a 包含-rtplgoD
 -r 同步目录时要加上，类似cp时的-r选项
 -v 同步时显示一些信息，让我们知道同步的过程
 -l 保留软连接
 -L 加上该选项后，同步软链接时会把源文件给同步
 -p 保持文件的权限属性
 -o 保持文件的属主
 -g 保持文件的属组
 -D 保持设备文件信息
 -t 保持文件的时间属性
 --delete 删除DEST(目标)中SRC（源）没有的文件
 --exclude 过滤指定文件，如--exclude “logs”会把文件名包含logs的文件或者目录过滤掉，不同步
 -P 显示同步过程，比如速率，比-v更加详细
 -u 加上该选项后，如果DEST中的文件比SRC新，则不同步
 -z 传输时压缩
 
-v, --verbose 详细模式输出
-q, --quiet 精简输出模式
-c, --checksum 打开校验开关，强制对文件传输进行校验
-a, --archive 归档模式，表示以递归方式传输文件，并保持所有文件属性，等于 -rlptgoD
-r, --recursive 对子目录以递归模式处理
-R, --relative 使用相对路径信息
-b, --backup 创建备份，也就是对于目的已经存在有同样的文件名时，将老的文件重新命名为~filename。可以使用 --suffix 选项来指定不同的备份文件前缀。
--backup-dir 将备份文件（如~filename) 存放在在目录下。
-suffix=SUFFIX 定义备份文件前缀
-u, --update 仅仅进行更新，也就是跳过所有已经存在于 DST，并且文件时间晚于要备份的文件。（不覆盖更新的文件）
-l, --links 保留软链结
-L, --copy-links 想对待常规文件一样处理软链结
--copy-unsafe-links 仅仅拷贝指向 SRC 路径目录树以外的链结
--safe-links 忽略指向 SRC 路径目录树以外的链结
-H, --hard-links 保留硬链结
-p, --perms 保持文件权限
-o, --owner 保持文件属主信息
-g, --group 保持文件属组信息
-D, --devices 保持设备文件信息
-t, --times 保持文件时间信息
-S, --sparse 对稀疏文件进行特殊处理以节省 DST 的空间
-n, --dry-run 现实哪些文件将被传输
-W, --whole-file 拷贝文件，不进行增量检测
-x, --one-file-system 不要跨越文件系统边界
-B, --block-size=SIZE 检验算法使用的块尺寸，默认是 700 字节
-e, --rsh=COMMAND 指定使用 rsh、ssh 方式进行数据同步
--rsync-path=PATH 指定远程服务器上的 rsync 命令所在路径信息
-C, --cvs-exclude 使用和 CVS 一样的方法自动忽略文件，用来排除那些不希望传输的文件
--existing 仅仅更新那些已经存在于 DST 的文件，而不备份那些新创建的文件
--delete 删除那些 DST 中 SRC 没有的文件
--delete-excluded 同样删除接收端那些被该选项指定排除的文件
--delete-after 传输结束以后再删除
--ignore-errors 及时出现 IO 错误也进行删除
--max-delete=NUM 最多删除 NUM 个文件
--partial 保留那些因故没有完全传输的文件，以是加快随后的再次传输
--force 强制删除目录，即使不为空
--numeric-ids 不将数字的用户和组 ID 匹配为用户名和组名
--timeout=TIME IP 超时时间，单位为秒
-I, --ignore-times 不跳过那些有同样的时间和长度的文件
--size-only 当决定是否要备份文件时，仅仅察看文件大小而不考虑文件时间
--modify-window=NUM 决定文件是否时间相同时使用的时间戳窗口，默认为 0
-T --temp-dir=DIR 在 DIR 中创建临时文件
--compare-dest=DIR 同样比较 DIR 中的文件来决定是否需要备份
-P 等同于 --partial
--progress 显示备份过程
-z, --compress 对备份的文件在传输时进行压缩处理
--exclude=PATTERN 指定排除不需要传输的文件模式
--include=PATTERN 指定不排除而需要传输的文件模式
--exclude-from=FILE 排除 FILE 中指定模式的文件
--include-from=FILE 不排除 FILE 指定模式匹配的文件
--version 打印版本信息

传输示例：

rsync -avPz   111/  root@192.168.1.109:/tmp/111/   

定时任务

crontab -e  #编辑定时任务
crontab -l  #查看定时任务

每五分钟执行  */5 * * * *
每小时执行     0 * * * *
每天执行       0 0 * * *
每周执行       0 0 * * 0
每月执行       0 0 1 * *
每年执行       0 0 1 1 *

#每周日3点全量备份
0 3 * * 0 sh /data/full.sh > /data/full.log 2>&1 &
#每天凌晨增量备份
0 0 * * * sh /data/incre.sh > /data/incre.log 2>&1 &

错误分析整理

Slave failed to initialize relay log info structure from the repository .

mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
mysql> reset slave;
Query OK, 0 rows affected (0.04 sec)

mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.07 sec)

mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.01 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.10 sec)

mysql> start slave;
Query OK, 0 rows affected (0.20 sec)

mysql> show slave status\G

'file_ignore_glob' with value None has an invalid type of NoneType,

[WARNING ] Key 'file_ignore_glob' with value None has an invalid type of NoneType, a list is required for this value
[WARNING ] Key 'file_ignore_glob' with value None has an invalid type of NoneType, a list is required for this value
[WARNING ] Key 'file_ignore_glob' with value None has an invalid type of NoneType, a list is required for this value
[WARNING ] Key 'file_ignore_glob' with value None has an invalid type of NoneType, a list is required for this value

解决方法：

/etc/salt/master的配置文件中，将file_ignore_glob的注释全部打开，重启master即可。