mysql 数据备份与恢复
MySQL数据备份与恢复
#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。
#2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。
#3. 导出表： 使用客户端工具将表导入到文本文件中。
一、使用mysqldump实现逻辑备份
#语法：
mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。
#示例：
[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)
方法二：
进入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
更多精彩
