MYSQL数据库备份

数据库备份

InnoDB 建议备份策略

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1  --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM 建议备份策略

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql

 

数据库冷备份和还原

#在目标服务器（10.0.0.27）安装mariadb-server
[root@centos7 ~]#yum -y install mariadb-server
​
#在源主机（10.0.0.17）执行
[root@centos7 ~]#systemctl stop mariadb.service
#拷贝配置文件
[root@centos7 ~]#scp /etc/my.cnf.d/server.cnf  10.0.0.27:/etc/my.cnf.d/
#拷贝数据库
[root@centos7 ~]#scp -r /var/lib/mysql/* 10.0.0.27:/var/lib/mysql/
#拷贝二进制日志，10.0.0.27事先存在/data/目录
[root@centos8 ~]# scp -r /data/logbin/ 10.0.0.27:/data/   
​
#目标主机执行
#设置文件属主属组，若拷贝时保留属性可用rsync -av 拷贝保留属性及增量备份，但拷贝双方都要安装rsync
[root@centos7 ~]#chown -R mysql.mysql /var/lib/mysql/
[root@centos7 ~]#chown -R mysql.mysql /data/logbin/
[root@centos7 ~]#systemctl start mariadb.service
​
​

 

 

特定数据库的备份脚本

 

#mkdir  -pv /data/backup
​
​
#!/bin/bash
#
#********************************************************************
#Author:        wei
#QQ:              
#Date:          2020-10-10
#FileName：      backup_hellodb.sh
#URL:             
#Description：       The test script
#Copyright (C):     2020 All rights reserved
#********************************************************************
TIME=`date +%F_%T`
DIR=/data/backup
DB=hellodb
​
mysqldump -F  -E  -R --triggers  --single-transaction --master-data=2  --default-character-set=utf8  -q -B $DB | gzip > ${DIR}/${DB}_${TIME}.sql.gz
​
​
#ll  /data/backup 
total 4
-rw-r--r-- 1 root root 2598 Oct 10 08:48 hellodb_2020-10-10_08:48:03.sql.gz
​

 

分库备份并压缩

[root@centos7 ~]#for db in `mysql   -uroot   -pcentos   -e 'show databases'|grep -Evi '^(database|information_schema|performance_schema)$'` ;do mysqldump -B $db |gzip > /data/${db}_`date +%F`.sql.gz;done
​
​
[root@centos7 ~]#mysql   -uroot   -pcentos  -e 'show databases'|grep  -Evi '^(database|information_schema|performance_schema)$' |while read db ;do  mysqldump -B $db |gzip > /data/${db}_`date +%F`.sql.gz;done
​
​
[root@centos7 ~]#mysql   -uroot   -pcentos  -e 'show databases'|grep -Evi '^(database|information_schema|performance_schema)$' |sed -nr 's#(.*)#mysqldump -B \1 |gzip > /data/\1.sql.gz#p' |bash
​
​
[root@centos7 ~]#mysql -e 'show databases' |sed -nr '/^(Database|information_schema|performance_schema)$/ !s#(.*)#mysqldump -B  \1 |gzip > /data/\1.sql.gz#p' |bash
​
​
​
​

 

分库备份的实战脚本

#!/bin/bash
#
#********************************************************************
#Author:        wei
#QQ:              
#Date:          2020-10-10
#FileName：      backup_db.sh
#URL:             
#Description：       The test script
#Copyright (C):     2020 All rights reserved
#********************************************************************
DIR=/data/backup
TIME=`date +%F_%T`
PASS=centos
​
[ -d "$DIR" ] || mkdir -p $DIR
​
for DB in `mysql -uroot  -p"$PASS"  -e 'show databases' |grep -Ev "^Database|.*schema$" `;do
    mysqldump  -uroot  -p"$PASS" -F --single-transaction  --master-data=2  --default-character-set=utf8  -q -B $DB  |gzip > ${DIR}/${DB}_${TIME}.sql.gz
done
​
​
​

 

完全备份和还原

#开启二进制日志
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log-bin 
​
#备份数据库
[root@centos8 ~]#mysqldump -uroot -pmagedu -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz
​
#还原数据库
[root@centos8 ~]#dnf install mariadb-server
[root@centos8 ~]#systemctl start mariadb.service
[root@centos8 ~]#gzip -d all-2020-10-10.sql.sql.gz
[root@centos8 ~]#mysql
MariaDB [(none)]> set sql_log_bin=off;
MariaDB [(none)]> source all-2020-10-10.sql.sql
MariaDB [(none)]> set sql_log_bin=on;
MariaDB [(none)]> \q;
​
[root@centos8 ~]#mysql hellodb
​

 

利用二进制日志，还原数据库最新状态

#二进制日志独立存放
vim /etc/my.cnf.d/server.cnf 
[mysqld]
log-bin=/data/mysql/mysql-bin 
​
mkdir /data/mysql
chown -R mysql.mysql /data/mysql
systemctl restart mariadb.service
​
#完全备份，并记录备份数据库的位置
mysqldump -uroot -pcentos -A -F --default-character-set=utf8 --single-transaction  --master-data=2 |gzip > /data/backup/all_`date +%F`.sql.gz
​
​
#修改数据库
update students set  classid=3;
update students set  age =18;
​
#损坏数据库
rm -rf /var/lib/mysql/*
​
#解压备份的数据库压缩包
gzip -d /data/backup/all_2020-10-10.sql.gz 
​
#CentOS 8 需要事先生成数据库相关文件，CentOS7 不需要执行此步
mysql_install_db  --user=mysql
​
#重启服务
systemctl restart mariadb
​
​
#mysql
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       288 |
| mysql-bin.000002 |       650 |
| mysql-bin.000003 |     30337 |
| mysql-bin.000004 |   1038814 |
| mysql-bin.000005 |       245 |
+------------------+-----------+
5 rows in set (0.00 sec)
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /data/backup/all_2020-10-10.sql
MariaDB [mysql]> \q
​
#查看最近的二进制日志编号
grep '^-- CHANGE MASTER TO' /data/backup/all_2020-10-10.sql;
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=245;
​
#导出二进制日志文件，并重定向到新建文件中
mysqlbinlog /data/mysql/mysql-bin.000002 --start-position=245 > /root/inc.sql
​
#导入二进制程序
mysql 
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source inc.sql
MariaDB [hellodb]> set sql_log_bin=1;
MariaDB [hellodb]> \q;
​
#查看数据库，已恢复
mysql hellodb
MariaDB [hellodb]> select * from students;
​

 

mysqldump 和二进制日志结合实现增量备份

#先对数据库做完全备份
mysqldump   -A -F --single-transaction  --master-data=2 |gzip > /data/backup/all-`date +%F`.sql.gz 
​
#观察二进制文件中的位置，将之后的二进制日志进行备份
#mariadb-bin.000003是后续生成的二进制日志
cp /var/lib/mysql/mariadb-bin.000003 10.0.0.8：/backup
​
#查看并导出二进制日志用作还原数据库数据
mysqlbinlog --start-position=389 /backup/mariadb-bin.000003 >  /backup/inc.sql
​

 

恢复误删除的表

每天2:30做完全备份，早上10:00误删除了表students，10:10才发现，现需要将数据库还原到10:10的状态，且恢复被删除的students表

#开启二进制日志，并重启服务
vim /etc/my.cnf.d/server.cnf   
[mysqld]
log-bin=/data/mysql/mysql-bin  
​
mkdir  /data/backup
mkdir /data/mysql
chown -R mysql.mysql /data/mysql
​
systemctl restart mariadb.service
​
#做完全备份
mysqldump -uroot -pcentos -A -F --single-transaction  --master-data=2  > /data/backup/allbackup_`date +%F_%T`.sql
​
# ll  /data/backup/
total 516
-rw-r--r-- 1 root root 525090 Oct 10 14:24 allbackup_2020-10-10_14:24:29.sql
​
#完全备份后，进行数据修改更新
mysql -uroot -pcentos hellodb
MariaDB [hellodb]> insert teachers (name,age,gender) values('daqiao',18,'f');
MariaDB [hellodb]> insert teachers (name,age,gender)values('xiaoqiao',16,'f');
​
#10:00误删除一个重要的表
MariaDB [hellodb]> drop tables teachers;
​
#后续其他表继续更新
MariaDB [hellodb]> use test;
MariaDB [test]> create table users  select * from hellodb.users;
MariaDB [test]> insert users (name,password) values ('xiexun','centos');
MariaDB [test]> insert users (name,password) values ('huangrong','centos');
MariaDB [test]> select * from users;
​
​
10:10发现表删除，进行还原
#停止数据库访问，并从完全备份中找到二进制日志位置
[root@centos7 ~]#systemctl stop mariadb.service
[root@centos7 ~]#grep '\-\- CHANGE MASTER TO' /data/backup/allbackup_2020-10-10_14\:24\:29.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=245;
​
#备份从完全备份后的二进制日志
mysqlbinlog  --start-position=245  /data/mysql/mysql-bin.000003  > /data/backup/inc.sql
​
#找到误删除的语句，并从备份中删除此语句
[root@centos7 ~]#vim /data/backup/inc.sql
DROP TABLE `teachers` /* generated by server */
#若文件过大可用sed 实现
sed -i.bak  '/^DROP TABLE/d' /data/backup/inc.sql
​
#利用完全备份和修改过的二进制日志进行还原
systemctl start mariadb.service;
mysql -uroot -pcentos 
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /data/backup/allbackup_2020-10-10_14:24:29.sql ;
MariaDB [hellodb]> source /data/backup/inc.sql
MariaDB [test]> set sql_log_bin=1;
MariaDB [hellodb]> select * from teachers;
​
​