MySQL数据备份与恢复
#1. 物理备份： 直接复制数据库文件，适用于大型数据库环境。但不能恢复到异构系统中如Windows。 #2. 逻辑备份： 备份的是建表、建库、插入等操作所执行SQL语句，适用于中小型数据库，效率相对较低。 #3. 导出表： 将表导入到文本文件中。
一、使用mysqldump实现逻辑备份
#语法： # mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql （本地使用可以省略 -h 服务器名） 当用户登陆不需要密码时（密码为空或在配置文件中已输入账号密码，登陆时无需再输入密码） 语法为：mysqldump -h 服务器 -u用户名 数据库名 > 备份文件.sql （本地使用可以省略 -h 服务器名） #示例（以下是有密码的时候的操作）： #单库备份 备份库中所有的表 mysqldump -uroot -p123 db1 > D:\\db1.sql #将数据库db1备份到D盘下，文件名称为db1.sql 备份库中部分表 mysqldump -uroot -p123 db1 table1 table2 > D:\\db1-table1-table2.sql #将数据库db1里的表table1和table2备份到D盘下名称为db1-table1-table2.sql的文件中 #多库备份 mysqldump -uroot -p123 --databases db1 db2 mysql db3 >D:\\db1_db2_mysql_db3.sql #将数据库db1,db2,mysql,db3备份到D盘中 #备份所有库 mysqldump -uroot -p123 --all-databases >D:\\all.sql #将root用户的所有数据库全部备份到D盘中，文件名为all.sql
二、恢复逻辑备份
#恢复多个库：（直接指定用户，不需要指定数据库名） # mysql -uroot -p123 < D:\\all.sql 将备份至D盘中的所有数据库，恢复至root用户下 #恢复单个库： #方法一：不需要进入mysql程序，直接在终端输入 mysql -uroot -p123 db1 < D:\\db1.sql #方法二： 从终端先进入mysql程序，然后输入 mysql> use db1; mysql> source D:\\db1.sql #注：如果备份/恢复单个库时，可以修改sql文件 DROP database if exists school; create database school; use school;
PS:不进入mysql，直接在终端执行mysql语句的方法： 如：查看root用户school数据库下的所有表 C:\Users\Administrator>mysql -uroot -e "use school;show tables;" (windows系统下必须是双引号) +------------------+ | Tables_in_school | +------------------+ | class | | course | | score | | student | | teacher | +------------------+
三、备份/恢复案例
数据库备份/恢复实验一：数据库损坏 备份： 1. # mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql 2. # mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog 3. 插入数据 //模拟服务器正常运行 4. mysql> set sql_log_bin=0; //模拟服务器损坏 mysql> drop database db; 恢复： 1. # mysqlbinlog 最后一个binlog > /backup/last_bin.log 2. mysql> set sql_log_bin=0; mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份 mysql> source /backup/last_bin.log //恢复最后个binlog文件 #数据库备份/恢复实验二：如果有误删除 备份： 1. mysqldump -uroot -p123 --all-databases > /backup/`date +%F`_all.sql 2. mysql -uroot -p123 -e 'flush logs' //截断并产生新的binlog 3. 插入数据 //模拟服务器正常运行 4. drop table db1.t1 //模拟误删除 5. 插入数据 //模拟服务器正常运行 恢复： 1. # mysqlbinlog 最后一个binlog --stop-position=260 > /tmp/1.sql # mysqlbinlog 最后一个binlog --start-position=900 > /tmp/2.sql 2. mysql> set sql_log_bin=0; mysql> source /backup/2014-02-13_all.sql //恢复最近一次完全备份 mysql> source /tmp/1.log //恢复最后个binlog文件 mysql> source /tmp/2.log //恢复最后个binlog文件 注意事项： 1. 完全恢复到一个干净的环境（例如新的数据库或删除原有的数据库） 2. 恢复期间所有SQL语句不应该记录到binlog中
四、实现自动化备份
备份计划： 1. 什么时间 2:00 2. 对哪些数据库备份 3. 备份文件放的位置 备份脚本： [root@egon ~]# vim /mysql_back.sql #!/bin/bash back_dir=/backup back_file=`date +%F`_all.sql user=root pass=123 if [ ! -d /backup ];then mkdir -p /backup fi # 备份并截断日志 mysqldump -u${user} -p${pass} --events --all-databases > ${back_dir}/${back_file} mysql -u${user} -p${pass} -e 'flush logs' # 只保留最近一周的备份 cd $back_dir find . -mtime +7 -exec rm -rf {} \; 手动测试： [root@egon ~]# chmod a+x /mysql_back.sql [root@egon ~]# chattr +i /mysql_back.sql [root@egon ~]# /mysql_back.sql 配置cron： [root@egon ~]# crontab -l 0 2 * * * /mysql_back.sql
五、表的导出和导入
SELECT... into outfile 导出文本文件 示例： mysql> select * from school.student1 into outfile 'E:\\student1.txt' //指定了导出文件的路径和文件名 fields terminated by ',' //定义文本中字段显示的分隔符 （optionally enclosed by '”'）不一定要写 //定义字符串使用什么符号括起来 lines terminated by '\n' ; //定义换行符 执行会报错，以前旧版本可以执行，新版为了保护数据安全，不能直接导出 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement mysql> show variables like '%secure%'; #查看相关设置 +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | NULL | +--------------------------+-------+ set global secure_auth=OFF; set secure_auth=OFF; 以上两种直接更改设置的方法都是行不通的，需要更改配置文件 [mysqld] secure-file-priv='E:\\' 在配置文件里更改配置后，即可执行导出操作 mysql 命令导出文本文件 示例: # mysql -u root -p123 -e 'select * from student1.school' > /tmp/student1.txt # mysql -u root -p123 --xml -e 'select * from student1.school' > /tmp/student1.xml # mysql -u root -p123 --html -e 'select * from student1.school' > /tmp/student1.html load data infile 导入文本文件（要按照表结构导入） mysql> load data infile 'E:\\student1.txt' into table school.student1 fields terminated by ',' （optionally enclosed by '”'） lines terminated by '\n';
六、数据库迁移
务必保证在相同版本之间迁移 # mysqldump (-h 源IP) -uroot -p123 --databases db1 | mysql -h 目标IP -uroot -p456 括号内的可以不写，因为源IP就是自己，不需要指定