mysql 数据备份与恢复

 

MySQL数据备份与恢复

#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。
#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
#3. 导出表： 使用客户端工具将表导入到文本文件中。 

一、使用mysqldump实现逻辑备份

#语法：

mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

 

#示例：

[root@mysql mysqlbakcup]# pwd
/root/mysqlbakcup

 

授权

mysql> grant all on *.* to 'root'@'localhost' identified by '123';
Query OK, 0 rows affected (0.15 sec)

 

#单库备份
备份数据库导出一个数据库的结构以及数据
mysqldump -uroot -p123 db1 > db1.sql

 mysqldump -uroot -p123 "db1" > /root/mysqlbakcup/db1.sql

 

[root@mysql mysqlbakcup]# ls
db1.sql

 

2. 备份数据表
导出一个数据库和数据表的结构以及数据

mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

[root@mysql mysqlbakcup]# mysqldump -uroot -p123 db1 auth_group auth_user > /root/mysqlbakcup/db1-auth_group-auth_user.sql

 

[root@mysql mysqlbakcup]# ls
db1-auth_group-auth_user.sql  db1.sql

 

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

[root@mysql mysqlbakcup]# mysqldump -uroot -p123 --databases db1 db2 db3 >/root/mysqlbakcup/db1_db2_db3.sql

 

[root@mysql mysqlbakcup]# ls
db1-auth_group-auth_user.sql  db1_db2_db3.sql  db1.sql

 

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

[root@mysql mysqlbakcup]# mysqldump -uroot -p123 --all-databases > /root/mysqlbakcup/all.sql

 

[root@mysql mysqlbakcup]# ls
all.sql

 

二、恢复逻辑备份

#方法一：

[root@mysql mysqlbakcup]# mysql -uroot -p123 < /backup/all.sql

 

mysql> drop database db3;
Query OK, 1 row affected (0.56 sec)

# 先创建数据库db3

mysql> create database db3 charset=utf8;
Query OK, 1 row affected (0.00 sec)

[root@mysql mysqlbakcup]# mysql -uroot -p123 db3 < /root/mysqlbakcup/db3.sql 


mysql> use db3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| employee      |
+---------------+
1 row in set (0.00 sec)

 

使用数据表来恢复

[root@mysql mysqlbakcup]# mysqldump -uroot -p123 db3 employee> /root/mysqlbakcup/employee.sql

[root@mysql mysqlbakcup]# ls
all.sql  db3.sql  employee.sql

 

准备

[root@mysql mysqlbakcup]# mysql -uroot -p123



mysql> use db3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> 
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| employee      |
+---------------+
1 row in set (0.01 sec)

mysql> drop table employee ;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
Empty set (0.00 sec)

 

恢复数据表

[root@mysql mysqlbakcup]# mysql -uroot -p123  db3 < /root/mysqlbakcup/employee.sql

mysql> use db3;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------+
| Tables_in_db3 |
+---------------+
| employee      |
+---------------+
1 row in set (0.03 sec)

 

-P 指定端口

-h 指定服务器ip

--no-data, -d 不导出任何数据，只导出数据库表结构。

mysqldump  -uroot -p --host=localhost --all-databases --no-data

 

1.①导出一个库结构

mysqldump -d dbname -u root -p > xxx.sql

 

②导出多个库结构

mysqldump -d -B dbname1 dbname2 -u root -p > xxx.sql

 

-B参数备份和恢复（建议使用）

（1）备份操作
a、备份
mysqldump -uroot -p'123456' -B mytest > /mnt/mytest_bak_B.sql

说明：加了-B参数后，备份文件中多的Create database和use mytest的命令
加-B参数的好处：
加上-B参数后，导出的数据文件中已存在创建库和使用库的语句，不需要手动在原库是创建库的操作，在恢复过程中不需要手动建库，可以直接还原恢复。

（2）恢复操作
a、删除mytest库
mysql -uroot -p'123456' -e "drop database mytest;"
b、恢复数据
（1）使用不带参数的导出文件导入（导入时不指定要恢复的数据库），报错
mysql -uroot - p'123456' < /mnt/mytest_bak.sql   
ERROR 1046 (3D000) at line 22: No database selected
（2）使用带-B参数的导出文件导入（导入时也不指定要恢复的数据库），成功
mysql -uroot -p'123456' < /mnt/mytest_bak_B.sql 
c、查看数据
mysql -uroot -p'123456' -e "select * from mytest.student;"

指定压缩命令来压缩备份文件

（1）备份
mysqldump -uroot -p'123456'  -B mytest | gzip > /mnt/mytest_bak_.sql.gz
说明：
mysqldump导出的文件是文本文件，压缩效率很高

备份多个数据库

（1）说明
通过-B参数指定相关数据库，每个数据库名之前用空格分格。当使用-B参数后，将所有数据库全部列全，则此时等同于-A参数。
（2）备份
mysqldump -uroot -p'123456' -B mytest wiki | gzip > /mnt/mytestAndWiki_bak.sql.gz

 

 

--quick, -q：在备份数据量比较大的表时有用。会将数据读入内存，在输出完成之前会存在内存缓冲区。，默认为打开状态，使用--skip-quick取消该选项。

mysqldump  -uroot -p --host=localhost --all-databases 

mysqldump  -uroot -p --host=localhost --all-databases --skip-quick

 

--events，-E：使用事件调度器完成备份，但是需要数据库相关权限。(导出事件)

mysqldump  -uroot -p --all-databases --events

 

--routines, -R 

导出存储过程以及自定义函数

mysqldump  -uroot -p --host=localhost --all-databases --routines

 

 

 

包括存储程序(过程和函数)的倾倒数据库输出。

这个选项需要mysql.proc表的SELECT权限。使用,例程生成的输出包含创建过程和创建函数语句创建例程。然而,这些语句不包括属性,如例程创建和修改时间戳,所以当例程.重新加载,创建时间戳等于重新加载时间。如果你需要创建例程和原来的时间戳属性,不使用,例程。相反,转储和重载mysql的内容。proc直接表,使用MySQL账户有合适的MySQL数据库特权。

 

导出包括存储程序(过程和函数)的数据。使用这个选项需要mysql.proc表的SELECT权限。 导出的数据将包含 CREATE PROCEDURE and CREATE FUNCTION 语句，该选项也不会导出相应的时间戳。如果要包含时间戳，使用mysql.proc 表的内容

 

 

 --triggers：会在导出每个表时都导出触发器，不过需要具有TIGGER的权限，此选项默认启用,用--skip-triggers禁用它。（导出触发器）

 

mysqldump -uroot -h'192.168.1.55' -P3306 -p123 -d --triggers -q -E -R backstage > backstage.sql

 

 

 

方法二：

进入mysql，进入要恢复的数据库， 对该数据库导入表 source命令

mysql> drop database db3;
Query OK, 1 row affected (0.56 sec)

# 先创建数据库db3

mysql> create database db3 charset=utf8;
Query OK, 1 row affected (0.00 sec)

#进入数据库，使用source命令导入 恢复
use db3；


mysql> source /root/mysqlbakcup/db3.sql
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

 

实现自动化备份

#!/bin/bash

mysql_backup_dir=/root/mysqlbakcup
mysql_backup_file=`date +%Y-%m-%d`_all.sql
user=root
pass=123

if [ ! -d ${mysql_backup_dir} ];then
    mkdir -p ${mysql_backup_dir}
fi

# 备份
mysqldump -u${user} -p${pass} --all-databases > ${mysql_backup_dir}/${mysql_backup_file}

# 只保留最近一周数据备份
cd $mysql_backup_dir
find . -mtime +7 -exec rm -f {} \;

授权

[root@mysql mysqlbakcup]# ls
mysql_backup.sh
[root@mysql mysqlbakcup]# chmod +x mysql_backup.sh

 

 

测试一下脚本

 

[root@mysql mysqlbakcup]# sh -x mysql_backup.sh 
+ mysql_backup_dir=/root/mysqlbakcup
++ date +%Y-%m-%d
+ mysql_backup_file=2019-03-13_all.sql
+ user=root
+ pass=123
+ '[' '!' -d /root/mysqlbakcup ']'
+ mysqldump -uroot -p123 --all-databases
Warning: Using a password on the command line interface can be insecure.
+ cd /root/mysqlbakcup
+ find . -mtime +7 -exec rm -f '{}' ';'
[root@mysql mysqlbakcup]# ls

 

 

 

 

配置crond

[root@mysql mysqlbakcup]# crontab -e

 

[root@mysql mysqlbakcup]# crontab -l
*/2 * * * * sh /root/mysqlbakcup/mysql_backup.sh