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