努力挣扎的生活
yangfk
2020-05-16

Mysql数据库备份

//

# Mysql数据库备份

为啥要备份，因为没有绝对的安全！！！

生产环境，数据实时写入，数据库备份有两种方式：逻辑备份，物理备份

逻辑备份： 导入导出速度慢，占用空间小，适合小库表备份，热导入

物理备份， 备份时间快，占用空间大，适合数据库数据大，导入需要停机

# mysqldump逻辑备份

命令行常用参数

参数 说明 示例 说明
-A 全库备份 mysqldump -uroot -p -A > full.sql 全库备份，包括mysql库
-B 指定库 mysqldump -uroot -p -B test >test.sql 导test 库,sql 文件会有删除创库语句
-d 只导表结构 mysqldump -uroot -p test test -d > test_table.sql 导test 库test 表结构
-e 非交互模式操作 mysql -uroot -p'yfk123' -e "show databases;" 非交互命令行操作
-F 重新创建一个binlog日志文件 mysqldump -uroot -p -A -F > full.sql flush logs在备份时自动刷新binlog文件（不怎么常用）
-h 指定IP mysql -uroot -p'yfk123' -h 192.168.5.5 指定IP
-t 只导表数据 mysqldump -uroot -p test test -t > test_data.sql 导test 库test表 insert 数据
-p 指定密码 myqsl -uroot -p'yfk123' 指定密码登录
-P 指定端口 mysql -uroot -p'' -P 3307 指定数据库开放端口登录
-R 导函数 mysqldump -uroot -p -R test > func.sql 未尝试
-S 指定sock 文件 mysql -uroot -p -S /etc/mysql.sock -e "show databases;" 指定mysql 启动的sock 文件
--triggers 导触发器
--single-transaction 快照备份（生产环境一定要添加) 快照备份不锁表备份
--set-gtid-purged=OFF 事务关闭，云数据库备份需要 临时关闭事务
--master-data=2 不必须，备份时加入change master 0没有1不注释2注释
--ignore-table 排除指定表不导出 mysqldump -uroot -p -R -B testdb --ignore-table=testdb.test > testDB.sql 排除 testdb 库下的 test 表

常用的mysqldump：mysqldump -uroot -p -R --single-transaction --triggers -B test > test.sql

本机的mysqldump：mysqldump --defaults-file=~/.my.cnf --single-transaction -R -B test > test.sql

mysql --defaults-file=~/.my.cnf -Nse "SELECT @@gtid_mode;"

参数写了一大堆，开始创建逻辑备份数据库脚本

  • 创建数据库用户
CREATE USER 'sdk_dev'@'%' IDENTIFIED BY '98fdKDfsfFJ%#fd2';
grant all on *.* to 'sdk_dev'@'%';
#在8的版本需要分开执行， grant all on *.* to sdk_dev@'%' identified by '98fdKDfsfFJ%#fd2';
1
2
3
  • 备份脚本一（单库备份，合并为一个文件）

生成mysql免密登录文件：

mysql_config_editor set --login-path=dbbackup --user=root --host=127.0.0.1 --port=3306 --password

加密文件名称：ls -l ~/.mylogin.cnf

查看库：mysql --login-path=dbbackup -e "show databases;"

#!/bin/bash
#全库备份数据库

source /etc/profile 
export PATH=$PATH


DB_pass='98fdKDfsfFJ%#fd2'
DB_host='172.16.255.19'
Date=$(date +%Y%m%d)
Date2=$(date -d '-5 day' +%Y%m%d)
Dir=$(cd $(dirname $0);pwd)

#获取数据库
mysql -h ${DB_host} -usdk_dev -p ${DB_pass} -e "show databases;" |egrep -wv "performance_schema|mysql|information_schema|test|Database" |tr "\n" " " > ${Dir}/table.txt

#备份库
Databases=$(cat ${Dir}/table.txt)
mysqldump -h ${DB_host}  -usdk_dev -p ${DB_pass} -B ${Databases}  --set-gtid-purged=OFF -R --triggers --single-transaction |gzip > ${Dir}/full_sdkonline_${Date}.sql.gz

rm -rf ${Dir}/full_sdkonline_${Date2}.sql.gz
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
  • 备份脚本二（单库备份，单个sql文件）
#!/bin/bash env
#全库备份数据库表结构
source /etc/profile 
export PATH=$PATH

DB_pass='98fdKDfsfFJ%#fd2'
DB_host='172.16.255.19'

if $(curl -s --connect-timeout 3 ${DB_host}:3306 > /dev/null); then

echo 'Mysql port connection is OK '
else

exit 1
fi

Date=$(date +%Y%m%d%H)
Date2=$(date -d '-5 day' +%Y%m%d%H)
Dir=$(cd $(dirname $0);pwd)

mysql -h ${DB_host} -uroot -p ${DB_pass} -e "show databases;" |egrep -vw "sys|performance_schema|mysql|information_schema|test|Database" > ${Dir}/database.txt

test -d ${Dir}/Mysqldata/${Date} ||mkdir -p ${Dir}/Mysqldata/${Date}
while read line
do
mysqldump -h ${DB_host} -uroot -p ${DB_pass} -B ${line} --ignore-table=${line}.t_idata_gnsshistory  --single-transaction -R  |gzip > ${Dir}/Mysqldata/${Date}/mysqlBackup_${line}.sql.gz

done <  ${Dir}/database.txt

if [ `ls -l Mysqldata |wc -l` -gt 30 ];then

find $Dir/Mysqldata/ -mtime +30  -exec rm -rf {} \;

fi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# sql语句恢复数据

  • 查看表大小
select 

table_schema as '数据库',

table_name as '表名',

table_rows as '记录数',

truncate(data_length/1024/1024, 2) as '数据容量(MB)',

truncate(index_length/1024/1024, 2) as '索引容量(MB)'

from information_schema.tables

WHERE table_schema='database_name'

order by data_length desc, index_length desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
  • 指定test库导入sql表

cat test_table.sql |mysql -uroot -p -B test

  • 导入data库

mysql -uroot -p < data.sql

  • 交互模式导入库或表

登录数据库，source 服务器上面的sql 文件

mysql> use data
Database changed
mysql> source /root/data_table.sql
1
2
3
4

# mysql8导入到mysql5.7 ERROR 1273

提示报错：【ERROR 1273 (HY000) at line 1375: Unknown collation: 'utf8mb4_0900_ai_ci'】

sed -i 's#utf8mb4_0900_ai_ci#utf8_general_ci#g' mysqlBackup_file.sql

sed -i 's#utf8mb4#utf8#g' mysqlBackup_file.sql

重新导入，就正常了

# xtrabackup物理冷备

备份过程中不锁库表，43G 物理数据，备份，导入只要3-5分钟，速度快

# xtrabackup安装

通过yum 安装

官方文档：https://www.percona.com/downloads

rpm -ivh https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.29/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.29-1.el7.x86_64.rpm

yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y
yum install percona-xtrabackup-24 -y
1
2
3

安装完之后，在命令行会多出一个 innobackupex和innochecksum 工具

innobackupex_v.png

常用选项含义

参数 说明
--host 主机名
--user 用户名
--port 端口号
--password 密 码
--databases 数据库名
--no-timestamp 不用日期命名备份文件存储的子目录名
--databases=" 库名 " 单个库
--databases=" 库 1 库 2" 多个库
--databases=" 库.表 " 单个表
--redo-only 日志合并
--apply-log 准备还原 ( 回滚日志 )
--copy-back 恢复数据
--incremental 目录名 增量备份
--incremental--basedir=目录名 增量备份时，指定上一次备份数据存储的目录名
--incremental-dir= 目录名 准备恢复数据时，指定增量备份数据存储的目录名
--export 导出表信息
--import 导入表空间
--slave-info 导出的信息包含 master-log、position 偏移量

# 备份数据库

备份数据库命令： 把备份文件存入到 /opt/backup/full-mysql下，密码不能为空

innobackupex --user root --password JxRkPwMH1oZw --slave-info /opt/backup/full-mysql --no-timestamp

xtrab_backup.png

备份脚本，周二全备，其余增量备份

#!/bin/bash
#auther: yangfk
#每周二进行全备，其余时间每天增备

Weekday=$(date +%A)
date=$(date +%Y%m%d)
PATH=$PATH

#创建备份目录
Backup_dir=/opt/backup/backup_mysql
test -d ${Backup_dir} || mkdir -p ${Backup_dir}

cp /etc/my.cnf ./bak-my.cnf-${date} && find ./ -name "bak-my.cnf-*" -mtime +7 -exec rm -f {} \;

#如果是周二就进行一次全备，或者增量备份
if [ $Weekday == 'Tuesday' ];then
	echo $date > $Backup_dir/day.txt
	Day=$(cat $Backup_dir/day.txt)
	test -d $Backup_dir/$Day/full_mysql || mkdir -p $Backup_dir/$Day/full_mysql
	innobackupex --user root --password Cqz41OLpwbJxRkPwMH1oZw --slave-info $Backup_dir/$Day/full_mysql/  --no-timestamp
else
	Day=$(cat $Backup_dir/day.txt)
	test -d $Backup_dir/$Day/increment_data/ || mkdir -p $Backup_dir/$Day/increment_data
	innobackupex --user=root --password=Cqz41OLpwbJxRkPwMH1oZw --incremental $Backup_dir/$Day/increment_data/ --incremental-basedir=$Backup_dir/$Day/full_mysql/

fi


#删除前比较大小，如果新备份空间大于上一次的，就删除
if [ $Weekday == 'Tuesday' ];then
Day2=$(cat $Backup_dir/delete_day.txt)
Day=$(cat $Backup_dir/day.txt)
Old=$(du -s $Backup_dir/$Day2/full_mysql |awk '{print $1}' )
New=$(du -s $Backup_dir/$Day/full_mysql |awk '{print $1}' )
	if [ $New -gt $Old ];then
	rm -rf $Backup_dir/$Day2
	echo $date > $Backup_dir/delete_day.txt
	fi

fi
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

# 恢复数据库

# 恢复操作需要在停服状态。需要以下几步

  • 1，安装xtrabackup 工具

注意：如果之前是有数据库，需要把之前的数据和日志移走，然后重新创建data 和 logs，因为用的是主库的 /etv/my.cnf 配置文件，“目录结构和主库结构一致“，full-mysql 是从备份服务器推送过来的文件

xtrab_hint.png

  • 2，拷贝配置文件

恢复库只有数据目录，和日志目录，其余都需要手动拷贝

拷贝或推送主库 /etc/my.cnf 到新服务器 /etc/ 下，如果有原来文件做个备份，在下面执行恢复数据的时候，innobackupex 会读/etc/my.cnf 配置，按照里面的 datadir 去生成对应的目录与logs

拷贝或推送 可执行文件 /opt/mysql/var 目录，

拷贝或推送 /etc/init.d/mysqld 或者system 文件到新服务器

  • 3， 通过应用 备份出来的数据库目录下的事务日志文件 xtrabackup_logfile，在 备份目录下建立新的事务日志文件 ib_logfile

innobackupex --user root --password JxRkPwMH1oZw --apply-log /opt/full-mysql/

  • 4，开始恢复数据库到他们原来的数据库安装目录

innobackupex --user root --password JxRkPwMH1oZw --copy-back /opt/full-mysql/

xtrab_backup1.png

  • 5，重新授权数据库目录属主
#创建用户
groupadd mysql
useradd -s /sbin/nologin -g mysql -M mysql
#授权
> /opt/mysql/logs/mysql-error.log
chmod 0700 /opt/mysql/data
chown -R mysql:mysql /opt/mysql/data /opt/mysql/logs
chmod +x /etc/init.d/mysqld
1
2
3
4
5
6
7
8
  • 6，启动数据库

service mysqld start

# 腾讯云物理备份恢复

#!/bin/bash

#需要修改下载下来的文件名称
filexb=yfk_backup_20191104182107.xb

Filedir=$(cd $0;pwd )

if [ -f $Filedir/$filexb ];then
	echo "$filexb file is exist !"
	else 
	echo "${filexb} file does not exist ! Please change your file name !"
	exit
fi

#安装,解压工具包
cd ${Filedir}
wget http://download.yfklife.cn/blog/dba/mysql/percona-xtrabackup-2.3.6-1.el7.x86_64.rpm
wget http://download.yfklife.cn/blog/dba/mysql/qpress-11-linux-x64.tar
yum localinstall ${Filedir}/percona-xtrabackup-2.3.6-1.el7.x86_64.rpm -y
tar -xf ${Filedir}/qpress-11-linux-x64.tar -C /usr/local/bin


[ -d ${Filedir}/data ] || mkdir -p ${Filedir}/data

#解压xb文件
xbstream -x -C ${Filedir}/data <  ${Filedir}/${filexb}

#使用 qpress 命令将目标目录下所有以.qp结尾的文件都解压出来
xtrabackup --decompress --target-dir=${Filedir}/data

#备份解压出来之后，需要执行以下命令进行 apply log 操作
xtrabackup --prepare  --target-dir=${Filedir}/data

chown -R mysql:mysql ${Filedir}
#启动mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 个人存储下载地址。。。

点击，获取下面下载资源

percona-xtrabackup-2.3.6-1.el7.x86_64.rpm
qpress-11-linux-x64.tar
1
2
3
//
如果此文章对您有帮助，点击 -->> 请博主喝咖啡 (opens new window)
上次更新: 2025/06/23, 11:02:34
mysql安装
mysql主从复制

← mysql主从复制→

最近更新
01
postgreSQL通过keepalived配置主从自动切换
 06-30
02
ubuntu2204编译安装php7.4.33
 06-30
03
nginx之resolver解析
 06-19
更多文章>
Theme by Vdoing | Copyright © 2019-2025 yangfk | 湘ICP备2021014415号-1
×
//