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]# lsdb1-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]# lsall.sql
二、恢复逻辑备份
#方法一:
[root@mysql mysqlbakcup]# mysql -uroot -p123 < /backup/all.sql
mysql> drop databasedb3;
Query OK,1 row affected (0.56sec)
# 先创建数据库db3
mysql> create database db3 charset=utf8;
Query OK,1 row affected (0.00sec)[root@mysql mysqlbakcup]# mysql -uroot -p123 db3 < /root/mysqlbakcup/db3.sql
mysql> usedb3;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
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]# lsall.sql db3.sql employee.sql
准备
[root@mysql mysqlbakcup]# mysql -uroot -p123
mysql> usedb3;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechangedmysql>mysql>show tables;+---------------+
| Tables_in_db3 |
+---------------+
| employee |
+---------------+
1 row in set (0.01sec)
mysql> drop tableemployee ;
Query OK,0 rows affected (0.07sec)
mysql>show tables;
Emptyset (0.00 sec)
恢复
[root@mysql mysqlbakcup]# mysql -uroot -p123 db3 < /root/mysqlbakcup/employee.sql
mysql> usedb3;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
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
ERROR1046 (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 databasedb3;
Query OK,1 row affected (0.56sec)
# 先创建数据库db3
mysql> create database db3 charset=utf8;
Query OK,1 row affected (0.00sec)
#进入数据库,使用source命令导入 恢复usedb3;
mysql> source /root/mysqlbakcup/db3.sql
Query OK,0 rows affected (0.03sec)
Query OK,0 rows affected (0.00sec)
Query OK,0 rows affected (0.00sec)
Query OK,0 rows affected (0.00sec)
实现自动化备份
#!/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_dirfind . -mtime +7 -exec rm -f {} \;
授权
[root@mysql mysqlbakcup]# lsmysql_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=-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