学习预览：
（一）mysql 运维基础篇（Linux云计算从入门到精通）
（三）mysql 触发器、存储过程和函数（数据库运维基础补充）
（五）mysql数据备份—物理备份(完备+lvm快照+xtrabackup)+逻辑备份(mysqldump+导入导出)
（六）mysql复制技术—M-S主从配置（传统+GTID）+M-M-S-S主从配置（GTID）
（七）mysql中间件mycat配置和部署（基于M-M-S-S）
（八） 数据库集群技术—Galera Cluster安装与配置
（九）数据库集群技术Galera+mycat（数据库运维学习终章）
CONTEND
5.1 MySQL数据备份技术概述
（1）所有备份数据都应放在非数据库本地,而且建议有多份副本。
测试环境中做日常恢复演练，恢复较备份更为重要。
（2）了解备份和冗余的概念：
备份:能够防止由于机械故障以及人为误操作带来的数据丢失,例如将数据库文件保存在了其它地方。
冗条:数据有多份冗余,但不等备份,只能防止机械故障还来的数据丢失,例如主备模式、数据库集群。
（3）备份过程中必须考虑因素（优先满足数据的一致性）:
- 1.数据的一致性
- 2.服务的可用性
（4）备份方式（逻辑备份和物理备份）
逻辑备份:备份的是建表、建库、插入等操作所执行SQL语句( DDL DML DCL ),适用于中小型数据库,效率相对较低。
- mysqldump
- mydumper
物理备份:直接复制数据库文件,适用于大型数据库环境,不受存储引擎的限制,但不能恢复到不同的MySQL版本。
- tar,cp
- xtrabackup
- inbackup
- Ivm snapshot
（5）备份还有完全备份、增量备份和差异备份
- 完全备份当然就是全部都备份了
- 增量备份只备份一次完整的，然后每天只增加新增的内容
- 差异备份，还是制作一个完整的备份，后面每天只针对Sunday，做差异的备份。
5.2 完全物理备份
备份很简单直接tar一个命令就OK了，然后就开始还原（只还原到备份那一刻）：
mkdir /backup
cd /var/lib/mysql
tar -cf /backup/`date +%F`.gz *
#开始还原
systemctl stop mysqld
rm -rf /var/lib/mysql/*
tar -xf /backup/2020-04-14.gz -C /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
#登录测试数据库是否还在
mysql -p'Root@321'
show databases;
5.3 LVM快照实现物理备份
数据一致，服务可用。
注: MySQL数据 lv 和将要创建的 snapshot 必须在同一VG，因此VG必须要有一定的剩于空间
优点:
- 几乎是热备(创建快照前把表上锁,创建完后立即释放)
- 支持所有存储引擎
- 备份速度快
- 无需使用昂贵的商业软件(它是操作系统级别的)
缺点:
- 可能需要跨部门协调(使用操作系统级别的命令, DBA-般没权限)
- 无法预计服务停止时间
- 数据如果分布在多个卷上比较麻烦(针对存储级别而言)
5.3.1 手动配置
一定要准备lvm的，如果刚开始装mysql的时候，就可以准备了LVM了。但是mysql运行一段时间了，数据并没有存储LVM，下面我们先将现在的数据迁移到LVM。
一般步骤：加全局锁——>创建快照——>释放锁——>从快照中恢复——>移除快照——>快照恢复mysql
而快照恢复的流程：停止数据库——>清理环境——>导入数据——>修改权限——>启动数据库
#1、准备LVM及文件系统，我是虚拟机直接添加硬盘
lsblk #查看硬盘信息
vgcreate datavg /dev/sdb
vgs #查看lvm容量
lvcreate -L 2G -n mysql datavg #创建容量2G名为mysql的lvm
mkfs.xfs /dev/datavg/mysql #格式化
#2、数据迁移到lvm
systemctl stop mysqld
mount /dev/datavg/mysql /mnt/ #临时挂载点，注意mnt下不要有其他东西
cp /var/lib/mysql/* /mnt/ -a #将mysql数据拷到这里
umount /mnt/
vim /etc/fstab #编辑这个文件添加如下一行内容
/dev/datavg/mysql /var/lib/mysql xfs defaults 0 0
chown -R mysql:mysql /var/lib/mysql #最好设置下，免得有问题
systemctl start mysqld
#3、下面开始创建快照备份
echo 'flush tables with read lock;system lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/mysql;' |mysql -p'Root@321' #加全局锁、创建快照和释放锁要在同一会话中完成，所以这边释放锁就不需要了，自动释放了
#4、从快照中备份
mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt/ #因为是xfs，要加上nouuid,只读是为了防止快照被破坏
cd /mnt/
ll
tar -cf /backup/`date +%F`-mysql-snap1.tar ./*
#5、移除快照
cd; umount /mnt/
ll /backup/
lvremove -f /dev/datavg/lv-mysql-snap
vgscan
#6、下面利用快照恢复mysql
systemctl stop mysqld
rm -rf /var/lib/mysql/*
tar -xf /backup/2020-04-15-mysql-snap1.tar -C /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
mysql -p'Root@321'
5.3.2 脚本+cron
（一般我们利用脚本实现，更方便快捷）脚本如下，可根据需要改写：
#!/bin/bash
#LVM backup mysql
echo 'flush tables with read lock;system lvcreate -L 500M -s -n lv-mysql-snap /dev/datavg/mysql;' |mysql -p'Root@321'
mount -o ro,nouuid /dev/datavg/lv-mysql-snap /mnt/
cd /mnt
tar -cf /backup/`date +%F`-mysql-snap.tar ./*
cd /root
if [ $? -eq 0 ];then
umount /mnt/
lvremove -f /dev/datavg/lv-mysql-snap
fi
~ 利用cron定期备份（每周每天凌晨2点定时备份）：
crontab -e
0 2 * * * /root/mysql_bak.sh
5.4 mysql物理备份Xtrabackup
它是开源免费的支持MySQL数据库热备份的软件,它能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份。它不暂停服务创建Innodb热备份。为mysq|做增量备份;在mysq|服务器之间做在线表迁移;使创建replication更加容易;备份mysq|而不增加服务器的负载。
percona是一家老牌的mysq|技术咨询公司。它不仅提供mysql的技术支持、培训、咨询,还发布了mysql的分支版本percona-Server。并围绕percona Server还发布了一系列的mysq|工具。
5.4.1 xtrabackup安装
因为我是mysql8，只能安装xtrabackup8，其他版本不支持哦，大家根据自己mysql版本挑选相应的xtrabackup。
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-8.0.4/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm
yum localinstall percona-xtrabackup-80-8.0.4-1.el7.x86_64.rpm
#查看版本号
xtrabackup -v
xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --server-id=6 --log_bin=/var/log/binlog/mysql-bin
xtrabackup version 8.0.4 based on MySQL server 8.0.13 Linux (x86_64) (revision id: c2c0777)
5.4.2 全量备份和恢复
#1、备份
mkdir /xtrabackup/full -p #创建备份存放的文件夹
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=Root@321 --backup --target-dir=/xtrabackup/full #开始备份
#查看下备份的文件
ls /xtrabackup/full/
backup-my.cnf ibdata1 performance_schema undo_001 xtrabackup_info
company learning shop undo_002 xtrabackup_logfile
employment mysql student xtrabackup_binlog_info xtrabackup_tablespaces
ib_buffer_pool mysql.ibd sys xtrabackup_checkpoints
#2、恢复
systemctl stop mysqld
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/xtrabackup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
mysql -p'Root@321'
show databases;
5.4.3 增量备份和恢复
#1、先创建一个数据库，插入一个数据，做周一的备份
mysql> create database testdb;
mysql> create table testdb.t1(id int);
mysql> insert into testdb.t1 values(1);
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=Root@321 --backup --target-dir=/xtrabackup/2020-04-15
#2、插入第二个数据，模仿做周二的备份（基于周一做增量备份）
mysql -p'Root@321'
mysql> insert into testdb.t1 values(2);
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=Root@321 --backup --target-dir=/xtrabackup/2020-04-16 --incremental-dir=/xtrabackup/2020-04-15
#3、周三的
mysql -p'Root@321'
mysql> insert into testdb.t1 values(3);
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=Root@321 --backup --target-dir=/xtrabackup/2020-04-17 --incremental-dir=/xtrabackup/2020-04-16/
#周三以后类似，我就不一一列举了
#开始恢复，根据需要恢复指定那天
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/xtrabackup/2020-04-16
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
mysql -p'Root@321'
5.4.4 差异备份和恢复
这个实际操作与增量备份类似。
mysql> create table testdb.t2(id int);
mysql> insert into testdb.t2 value (1);
mysql> select * from testdb.t2;
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=Root@321 --backup --target-dir=/xtrabackup/2020-04-15
#2、插入第二个数据，模仿做周二的备份（基于周一做差异备份，和增量备份一样）
mysql -p'Root@321'
mysql> insert into testdb.t2 values(2);
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=Root@321 --backup --target-dir=/xtrabackup/2020-04-16 --incremental-dir=/xtrabackup/2020-04-15/
#3、周三的（和增量备份只有一个不同，它是基于周一做差异备份的）
mysql -p'Root@321'
mysql> insert into testdb.t2 values(3);
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=Root@321 --backup --target-dir=/xtrabackup/2020-04-17 --incremental-dir=/xtrabackup/2020-04-17/
#周三以后类似，我就不一一列举了
#开始恢复，恢复指定那天就行
systemctl stop mysqld
rm -rf /var/lib/mysql/*
xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/xtrabackup/2020-04-17
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld
mysql -p'Root@321'
5.5 逻辑备份Mysqldump
5.5.1 了解mysqldump
mysqldump工具备份：导出的是SQL语句文件
优点：不论是什么存储引擎，都可以用mysqldump备成SQL语句
缺点：速度较慢，导入时可能会出现格式不兼容的突发情况，无法做增量备份和累计增量备份，不是大型数据库都推荐使用Mysqldump
语法:
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
参数说明：
| -A, --all-databases | 所有库 |
| school | 数据库名 |
| school stu_info t1 | school数据库的表stu_info. t1 |
| -B, --databases bbs test mysql | 多个数据库 |
| --single-transaction | #InnoDB一致性服务可用性 |
| x，--lock-all-tables | #MyISAM一致性服务可用性 |
| -E, --events | #备份事件调度器代码 |
| --opt | #同时启动各种高级选项 |
| -R, --routines | #备份存储过程和存储函数 |
| F, --flush-logs | #备份之前刷新日志 |
| --triggers | #备份触发器 |
| --master-data=1|2 | #该选项将会记录binlog的日志位置与文件名并追加到文件中 |
5.5.2 备份恢复所有库+binlog
（1）正常恢复+binlog
#备份所有数据库
mysqldump -p'Root@321' --all-databases --single-transaction --master-data=1 --flush-logs >/tmp/mysqldump_bak/`date +%F`-mysql-all.sql
#查看SQL文件，了解当时记录的bin位置
vim /tmp/mysqldump_bak/2020-04-17-mysql-all.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020', MASTER_LOG_POS=155;
#下面我再随便插入一些数据，利用binlog恢复
mysql> insert into t1 values (1),(2),(3);
mysql> insert into t2 values (1,'jack'),(2,'danie'),(3,'gawain');
#初始化数据库，记得另外备份binlog文件，防止丢失
cp /var/log/binlog/mysql-bin.000020 ./
systemctl stop mysqld
rm -rf /var/lib/mysql/*
systemctl start mysqld
#修改密码
grep password /var/log/mysqld.log
mysqladmin -uroot -p'rntLcST2y9/j' password 'Root@123'
#这时登上去库里是空的
msyql -p'Root@123'
#开始恢复，这时登上去还没有备份数据库之后插入的数据
mysql -uroot -pRoot@123 < /tmp/mysqldump_bak/2020-04-17-mysql-all.sql
#binlog恢复，之前SQL文件里就有截断的位置，从那恢复就可以了，如果有多个binlog可以依次恢复
mysqlbinlog mysql-bin.000020 --start-position=155 | mysql -uroot -pRoot@123
（2）当我们恢复的时候，binlog也会随之产生，有时候数据太大，我不需要这binlog，所以可以选择不产生binlog恢复。
#方法一，通过source恢复，数据库初始化我就省略咯，恢复后记得改回来set sql_log_bin=1；
mysql> set sql_log_bin=0;
mysql> source /tmp/mysqldump_bak/2020-04-17-mysql-all.sql;
#方法二，直接在SQL文件中添加set sql_log_bin=0;
vim /tmp/mysqldump_bak/2020-04-17-mysql-all.sql
set sql_log_bin=0;
（3）为了防止恢复的过程还会误删数据，比如你只想恢复binlog那一刻，后面有个drop的语句不想恢复怎么办？
#可以找到这个文件重定向一个文件中，再进行修改，把不想要的SQL语句删除
mysqlbinlog --start-position=154 mysql-bin.000020 > errorCorrect
#或者直接跳过你不想要的语句的位置，比如下面直接跳过slave2.000006中处于768到1045之间的语句
mysqlbinlog --start-position=154 slave2.000004 slave2.000005
mysqlbinlog --stop-position=768 slave2.000006
mysqlbinlog --start-position= 1045 slave2.000006
5.6 表的导入和导出
（1）导出文件select...into outfile 导出文件
#先修改下/etc/my.conf，添加如下一栏
secure-file-priv=/backup
#设置/backup权限
chown -R mysql:mysql /backup/
#开始导出文件
mysql> select * from testdb.t1 into outfile '/backup/t1';
#利用mysql命令导出文本文件
mysql -uroot -pRoot@123 -e 'select * from testdb.t1' > /backup/t1.txt
mysql -uroot -pRoot@123 --xml -e 'select * from testdb.t1' > /backup/t1.xml
mysql -uroot -pRoot@123 --html -e 'select * from testdb.t1' > /backup/t1.html
HTML文件可以通过浏览器访问：
（2）load data infile导入文本文件
mysql> delete from testdb.t1;
mysql> load data infile '/backup/t1' into table testdb.t1;
mysql> select * from testdb.t1;
+------+--------+
| id | name |
+------+--------+
| 1 | jack |
| 2 | tom |
| 3 | jannie |
+------+--------+
注意：表的导出和导入只备份表记录，不会备分表结构。因此需要通过mysqldump备份表结构，恢复时先恢复表结构。
1605
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
