MySQL数据备份

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

一、使用mysqldump实现逻辑备份

语法：
# mysqldump -h 服务器 -u用户名 -p密码数据库名 > 备份文件.sql
===数据库名
       –all-databases                       所有库
       school                                   数据库名
       school stu_info t1                   school数据库的表stu_info、t1
       –databases bbs test mysql   多个数据库

示例：

单库备份
# mysqldump -uroot -p123 bbs > bbs.sql
# mysqldump -uroot -p123 bbs table1 table2 > bbs-table1-table2.sql
多库备份
# mysqldump -uroot -p123 --databases bbs test mysql > bbs_test_mysql.sql
备份所有库
# mysqldump -uroot -p123 --all-databases > all.sql

二、恢复逻辑备份

方法一：
[root@yang backup]# mysql -uroot -p123 -h 192.168.2.115 < /backup/all.sql
 
方法二：
mysql> use bbs;
mysql> SET SQL_LOG_BIN=0;
mysql> source /root/bbs.sql
 
 
注1：如果备份/恢复单个库时，可以修改sql文件
DROP database if exists school;
create database school;
use school;
 
注2：对于有innodb引擎的表恢复时的注意事项：
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
 
mysql> drop database school;
Query OK, 0 rows affected (0.00 sec)

三、备份/恢复案例

数据库备份/恢复实验一：数据库损坏

备份：
1.  mysqldump -uroot -p123 --all-database > /backup/`date +%F`_all.sql
2.  mysql -uroot -p123 -e 'flush logs'                                  //截断并产生新的binlog
3.  插入数据                                                                    //模拟服务器正常运行
4. mysql> set sql_log_bin=0;                                            //模拟服务器损坏
    mysql> drop database bbs;
   
恢复：
1.  mysql> set sql_log_bin=0;           
     mysql> source /backup/2014-02-13_all.sql                    //恢复最近一次完全备份           
2.  mysqlbinlog 最后一个binlog  |mysql -uroot -p123            //恢复最后个binlog文件

数据库备份/恢复实验二：如果有误删除

备份：
1.  mysqldump -uroot -p123 --all-database > /backup/`date +%F`_all.sql
2.  mysql -uroot -p123 -e 'flush logs'                                  //截断并产生新的binlog
3.  插入数据                                                                    //模拟服务器正常运行
4.  drop table db1.t1                                                        //模拟误删除
 
恢复：
1.  mysql> set sql_log_bin=0;           
     mysql> source /backup/2014-02-13_all.sql                                            //恢复最近一次完全备份           
2.  mysqlbinlog 最后一个binlog --stop-position=260  |mysql -uroot -p123    //恢复最后个binlog文件   
     mysqlbinlog 最后一个binlog --start-position=260  |mysql -uroot -p123   

注意事项：
1. 完全恢复到一个干净的环境（例如新的数据库或删除原有的数据库）
2. 恢复期间所有SQL语句不应该记录到binlog中

四、实现自动化备份

========================================================
备份计划：
1. 什么时间 2:00
2. 对哪些数据库备份
3. 备份文件放的位置

备份脚本：
[root@yang ~]# 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@yang ~]# chmod a+x /mysql_back.sql
[root@yang ~]# chattr +i /mysql_back.sql
[root@yang ~]# /mysql_back.sql
 
配置cron：
[root@yang ~]# crontab -l
0 2 * * * /mysql_back.sql

五、表的导出和导入

SELECT... INTO OUTFILE 导出文本文件
示例：
mysql> SELECT * FROM school.student1
            INTO OUTFILE 'student1.txt'
            FIELDS TERMINATED BY ','                //定义字段分隔符
            OPTIONALLY ENCLOSED BY '”'            //定义字符串使用什么符号括起来
            LINES TERMINATED BY '\n' ;            //定义换行符
 
 
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> DELETE FROM student1;
mysql> LOAD DATA INFILE '/tmp/student1.txt'
            INTO TABLE school.student1
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '”'
            LINES TERMINATED BY '\n';

六、数据库迁移

相同版本之间迁移
# mysqldump -h 迁移源 IP -uroot -p123 –databases bbs | mysql -h 目标 IP -uroot -p456
//迁移源为本机,不用申明
========================================================

练习：
数据库备份扩展练习。
1、实现异地备份。
2、数据压缩。
3、判断mysqldump执行成功。
4、创建专门备份用户。
//建立数据库备份用户需要的权限；
grant SELECT,RELOAD,LOCK TABLES,SHOW VIEW,SHOW EVENTS on *.* to backup@'localhost' identified by '123';

#!/bin/sh
#Chenjie
backdir=/opt/backup
user=backup
pass=123
 
if [ ! -d ${backdir} ];then
    mkdir  ${backdir}
fi
 
for i in `/usr/local/mysql/bin/mysqlshow  -ubackup -p123|awk '{print $2}'|grep -vE '(Database|information_schema|backup|performance_schema)'`
do
   mysqldump -u${user} -p${pass} $i > ${backdir}/$i`date +%F`
   if [ $? -eq 0 ];then
      echo "Backup $i sucess"
      cd ${backdir}
      tar -czvf $i`date +%F`.tar.gz $i`date +%F`
   else
      echo "Backup $i error"
      exit
   fi
   mysql -u${user} -p${pass} -e 'flush logs'
done