mysql 数据备份与还原

[BruceOuyang]

备份

1、备份所有数据库

mysqldump -uroot -p --all-databases > /your_path/your_backup_sql_name.sql

eg.:
mysqldump -uroot -p --all-databases > /home/backup/data/20220218.all.sql

2、备份指定数据库

多个数据库用空格分开

mysqldump -uroot -p --databases db1 db2 db3... > /your_path/your_backup_sql_name.sql

eg.: mysqldump -uroot -p --databases db_user db_order db_store > /home/backup/data/20220218.user_order_store.sql

## 还原
### 方法1、使用 mysql 的 source 命令

登录 mysql

mysql -uroot -p

执行 source 指令

source /your_path/your_backup_sql_name.sql

eg. source /home/backup/data/20220218.all.sql

[BruceOuyang]

Mysql 通过 binlog 还原数据

方式一：遇到错误,停止执行

mysql -uroot -p your_database < your_backup_sql_name.sql

eg.
mysql -uroot -p db_user <  /home/backup/data/20220218.user_order_store.sql

方式二：遇到错误，继续执行

mysql -uroot -p
source /home/backup/data/20220218.user_order_store.sql

方式二，先登录 mysql，再执行 source 指令

[BruceOuyang]

centos 上的 mysql 定期备份

1. 登录 mysql，创建用来备份的数据库用户

   
   mysql> grant select, show view, lock tables, trigger, reload, super, replication client, process on *.* to dumper@'localhost';
   Query OK, 0 rows affected, 1 warning (0.20 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.05 sec)

> 用户名：dumper 密码：（无）

2. 编辑 `crontab` 

crontab -e 20 18 * /www/server/mysql/bin/mysqldump -udumpuser -B -F -R -x --master-data=2 --databases db_biz1 db_biz2 dbbiz3 | gzip > /home/databak/gzip/biz$(date +%F).sql.gz

### 脚本方式
1.创建脚本 biz_dumper.sh，输入如下内容

!/bin/bash

设置mysql的登录用户名和密码(根据实际情况填写)

mysql_user="dumper" mysql_host="localhost" mysql_port="3306" mysql_charset="utf8mb4" mysql_bin=/www/server/mysql/bin

需要备份的数据(根据实际情况填写，多个用空格隔开)

databases="db_biz01 db_biz02 db_biz03"

备份文件存放地址(根据实际情况填写)

backup_location=/home/databak/bizdata

是否删除过期数据

expire_backup_delete="ON" expire_days=7 backuptime=`date +%Y%m%d%H%M%S` backup_dir=$backup_location

echo $(date +"%Y-%m-%d %H:%M:%S")" -----------------------------------------------------------" echo $(date +"%Y-%m-%d %H:%M:%S")" Dumper Start Daily Work~~~" echo $(date +"%Y-%m-%d %H:%M:%S")

判断mysql实例是否正常运行

echo $(date +"%Y-%m-%d %H:%M:%S")" step 1 check mysqld service is ok or not" mysql_ps=ps -ef |grep mysql |wc -l mysql_listen=netstat -an |grep LISTEN |grep $mysql_port|wc -l if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then echo $(date +"%Y-%m-%d %H:%M:%S")" ERROR:MySQL is not running! backup stop!" exit else echo $(date +"%Y-%m-%d %H:%M:%S")" mysqld is running" fi

echo $(date +"%Y-%m-%d %H:%M:%S")

备份指定数据库中数据(此处假设数据库是mysql_backup_test)

echo $(date +"%Y-%m-%d %H:%M:%S")" step 2 run mysqldump command" $mysql_bin/mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -B -F -R -x --master-data=2 --databases $databases | gzip > $backupdir/biz$backup_time.sql.gz flag=echo $? if [ $flag == "0" ];then echo $(date +"%Y-%m-%d %H:%M:%S")" all-biz data success backup to $backupdir/biz$backup_time.sql.gz" else echo $(date +"%Y-%m-%d %H:%M:%S")" all-biz data backup fail!" exit fi

echo $(date +"%Y-%m-%d %H:%M:%S")

删除过期数据

echo $(date +"%Y-%m-%d %H:%M:%S")" step 3 delete old-bak data(expire_days=$expire_days)"

if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf echo $(date +"%Y-%m-%d %H:%M:%S")" Expired backup data delete complete!" fi echo $(date +"%Y-%m-%d %H:%M:%S") echo $(date +"%Y-%m-%d %H:%M:%S")" Dumper Finished Daily Work........." echo $(date +"%Y-%m-%d %H:%M:%S")" -----------------------------------------------------------" echo "" echo ""

2.设置权限

chmod +x biz_dumper.sh

3.设置crontab

crontab -e 0 4 * cd /home/databak;sh biz_dumper.sh >> dumper.log 2>> dumper.log