Mysql全量备份及主从配置

引言：生产服务器上有个主数据库，为了不直接操作线上mysql，需要做一台备库。

主要步骤：

一、全量备份mysql
二、备份后的文件发到从库的服务器上
三、数据的准备和恢复
四、启动服务并配置主从
五、注意事项

步骤一 全量备份mysql：

1、挑选工具percona-xtrabackup

1.1、安装依赖
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
1.2、安装软件
wget -c https://mirrors.tuna.tsinghua.edu.cn/percona/centos/7/os/x86_64/percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm
yum localinstall -y percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm

2、 全量备份
注意：安装完percona-xtrabackup工具包之后，有两种命令供你选择，一种是innobackupex、一种是xtrabackup,如果你的生产数据库会有ddl操作那么要选用后者，因为你需要锁表进行备份，在这里我用的后者。（详见-----https://www.percona.com/blog/2017/08/08/avoiding-the-an-optimized-without-redo-logging-ddloperation-has-been-performed-error-with-percona-xtrabackup/）。

2.1、使用命令全量备份
xtrabackup --defaults-file=配置文件绝对路径 --host=127.0.0.1 --user=用户  --password=密码  --target-dir=备份后的文件保存的位置  --lock-ddl（最重要的锁表操作）

2.2、最终的文件结构

除了数据文件和结构文件之外还会多出下列文件
..........
-rw-r----- 1 root root  317 8月  11 19:10 xtrabackup_binlog_info
-rw-r----- 1 root root  153 8月  12 16:11 xtrabackup_checkpoints
-rw-r----- 1 root root  912 8月  11 19:10 xtrabackup_info
-rw-r----- 1 root root 757M 8月  12 16:11 xtrabackup_logfile

步骤二 备份后的文件发到从库的服务器上：

1、怎么发随你便。
注意：数据量太大的话就压缩一下吧，因为压缩效率非常高，大部分压缩算法基于LZ77，也就是字典压缩，所以sql的重复率还是很高的。

步骤三 数据的准备和恢复

1、从库服务器准备mysql和percona-xtrabackup工具包
1.1、安装mysql-(见百度)
1.2、安装percona-xtrabackup工具包（和上面一样）
1.3、mysql配置文件

可以和主库的基本一致有几处地方需要改：
[mysqld]
#skip-grant-tables
datadir=自己指定目录
socket=自己指定目录/mysql.sock
log-error=自己指定目录/mysqld.log
pid-file=自己指定目录/mysqld.pid
server-id=1629281466（一定不能和主库一样）
log-bin-trust-function-creators=1

slave-skip-errors=1032,1062,1007,1050
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

#GTID
gtid_mode=on
enforce_gtid_consistency=on

binlog_format=ROW
log-bin=/data04/logs/mysql-bin
expire_logs_days=1
log-slave-updates
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

innodb_checksum_algorithm=innodb
#innodb_log_checksum_algorithm=innodb
innodb_data_file_path=ibdata1:200M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1572864000
#innodb_fast_checksum=false
innodb_page_size=16384
#innodb_log_block_size=512
innodb_undo_directory=.
innodb_undo_tablespaces=0


#rds_encrypt_data=false
#innodb_encrypt_algorithm=aes_128_ecb
#skip-grant-tables


注意！！！！！！！
上面自己指定的目录一定要改权限：
chown -R mysql.mysql 目录

1.4、数据恢复和准备

恢复准备（主要是完成一些没有完成的事务）：
innobackupex --use-memory=2G --apply-log 传过来的文件名字
恢复（就是拷贝数据到你的从库数据目录下，确保mysql是关闭的并且datadir目录下为空）：
innobackupex --defaults-file=配置文件位置 --use-memory=2G --copy-back 传过来的文件名字

步骤四 启动服务并配置主从

1、启动服务

启动：systemctl start mysqld
查看：systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 三 2020-08-12 18:38:25 CST; 1 weeks 1 days ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 16524 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─16524 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

8月 12 18:38:24 bigdata-oss systemd[1]: Starting MySQL Server...
8月 12 18:38:25 bigdata-oss systemd[1]: Started MySQL Server.

2、配置主从
2.1、主库上创建账号供从库使用

CREATE USER '用户名'@'从库ip' IDENTIFIED BY '主库密码';#创建用户
GRANT REPLICATION SLAVE ON *.* TO '用户名'@'从库ip';#分配权限
flush privileges;

2.2、配置主从
2.2.1、查看binlog停止的位置

备份的文件下找到xtrabackup_binlog_info，里面记录了日志停止的位置。
例如：
[root@bigdata-oss mysql-backup]# cat xtrabackup_binlog_info
mysql-bin.000524	979491523	66bbbdba-cece-11e9-a767-7cd30adfbec0:1-1051181081,
9e466b40-8bd3-11ea-b7f9-7cd30adb159e:1-82650160,
ab4a0bbf-1240-11e9-99e1-7cd30ae433c8:1-363166878,
dd4aaf14-aaa8-11e8-b65d-7cd30ae00a92:1-6721001,
f7f0ae3d-b8ee-11ea-adb3-00163e00bb99:1-123,
f823623e-aaa8-11e8-b65e-7cd30adb15a4:1-14204446

2.3、配置主从

change master to master_host=MASTER_HOST,master_port=3306,master_user='主库账户',master_password='主库账户密码',master_log_file='对应的binlog', master_log_pos=对应的binlog位置;

2.4、开启从节点
开启：start slave;
查看：show slave status\G

步骤五 注意事项

1、如果主库和从库落后太多的话一定要调优，不然可能永远都追不上，调优参数如下：

1.1、slave端，如果使用的存储引擎是innodb，innodb_flush_log_at_trx_commit 
1.2、sync_binlog=1
1.3、set global slave_parallel_workers=10
1.4、设置参数slave_parallel_workers>0  和slave_parallel_type＝'LOGICAL_CLOCK'。
酌情而定！！！

2、主库的binlog保存时间设置适当保存周期，别从库还没消费完，日志没了。