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;
