mysqldump 备份与恢复数据库（完全备份+增量备份）

mysqldump备份数据库，速度相对较慢，适合中小型数据库。

使用mysqldump命令备份

mysqldump -h主机名 -P端口 -u用户名 -p密码 (–database) 数据库名 > 文件名.sql

1. 备份MySQL数据库的命令

   mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

2. 备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

   mysqldump --add-drop-table -uusername -ppassword databasename > backupfile.sql

3. 直接将MySQL数据库压缩备份

   mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz

4. 备份MySQL数据库某个(些)表

   mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql

5. 同时备份多个MySQL数据库

   mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql

6. 仅仅备份数据库结构

   mysqldump -hhostname -uusername -ppassword --no-data --databases databasename1 databasename2 databasename3 > structurebackupfile.sql

7. 备份服务器上所有数据库

   mysqldump -hhostname -uusername -ppassword --all-databases > allbackupfile.sql

8. 还原MySQL数据库的命令

   mysql -hhostname -uusername -ppassword databasename < backupfile.sql

9. 还原压缩的MySQL数据库

   gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

10. 将数据库转移到新服务器

    mysqldump -uusername -ppassword databasename | mysql –host=... -C databasename

一、mysqldump全部备份与恢复

1. 数据库备份与恢复

   • 全部备份数据库

     格式： mysqldump -u用户名 -p密码 (–default-character-set=utf8) 数据库名 (| gzip ) > 备份文件名

     [root@bogon ~]# mysqldump -uroot -p'ABCabc123!'  it > /date/mysql/it_$(date +%F).sql         
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
     

     这样备份有个缺点，那就是恢复数据库时数据库名要手动创建，否则会出现如下错误：

     ERROR 1046 (3D000) at line 22: No database selected
     

     但手动创建数据库，我们不一定知道是哪个数据库出错，所以我们备份时一般会加上参数 -B，这样子就会自动创建数据库。

     [root@bogon ~]# mysqldump -uroot -p'ABCabc123!' -B it > /date/mysql/it_$(date +%F).sql
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
     

   • 恢复数据库

     [root@bogon ~]# mysql -uroot -pABCabc123! < /date/mysql/it_2019-04-11.sql
     

2. 表的全部备份与恢复

   • 备份多个表

     格式：mysqldump -u用户名 -p密码 数据库名 表名1 表名2 … > 备份文件名

     备份表不需要用到参数 -B

     [root@bogon ~]# mysqldump -uroot -pABCabc123! test s t worker > /date/mysql/test_3t_$(date +%F).sql
     mysqldump: [Warning] Using a password on the command line interface can be insecure.
     [root@bogon ~]# ls /date/mysql/
     test_3t_2019-04-11.sql
     

   • 恢复表

     格式：mysql -u用户名 -p密码 数据库名 < 备份文件名

     [root@bogon ~]# mysql -uroot -pABCabc123! test < /date/mysql/test_3t_2019-04-11.sql
     mysql: [Warning] Using a password on the command line interface can be insecure.
     

二、MySQLdump增量备份

MySQL的增量备份需开启MySQL的二进制日志。

开启二进制日志请参考：
https://blog.csdn.net/hdyebd/article/details/89190558

开启二进制日志后就可以进行增量备份了。

示例：

1. 准备数据库和表

   mysql> create database it DEFAULT CHARACTER SET utf8;
   mysql> use it      # 切换到此数据库
   mysql> CREATE TABLE `Student` (
     `Sno` int(10) NOT NULL COMMENT '学号',  
     `Sname` varchar(16) NOT NULL COMMENT '姓名',
     `Ssex` char(2) NOT NULL COMMENT '性别', 
     `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄',
     `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别',  PRIMARY KEY (`Sno`)
   ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
   

2. 向表中插入数据

   mysql> INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务');
   

3. 半夜零点手工全备份

   模拟半夜零点全备 date -s "2019-04-11"
   [root@bogon ~]# date -s "2019-04-11"
   [root@bogon ~]# mysqldump -uroot -pABCabc123! -B it | gzip  > /date/mysql/it_allbackup_$(date +%F).sql.gz
   [root@bogon ~]# ls /date/mysql/
   it_allbackup_2019-04-11.sql.gz
   

4. 备份后继续插入数据

   mysql> INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'),(0006,'wangzhao','男',21,'导弹专业');
   

5. 模拟用户破坏数据

   mysql> drop database it;
   

6. 检查全备份

   [root@bogon ~]# ls -l /date/mysql/
   

7. 检查全备后的所有 bin_log

   [root@bogon ~]# ls -l /var/lib/mysql/mysql.*
   

8. 立即刷新并备份bin_log

   [root@bogon ~]# mysqladmin -uroot -pABCabc123! flush-logs
   [root@bogon ~]# cp /var/lib/mysql/mysql.000005 /tmp/
   
    #提示：根据时间点及前一个binlog可以知道发现问题时刻前binlog日志为mysql-bin.000005
    如果不确定，可以查看bin_log日志内容
   [root@bogon ~]# mysqlbinlog --base64-output=decode-rows -vv /var/lib/mysql/mysql.000005 
   

9. 恢复binlog生成sql语句

   有两种方法生成 ：一种是按时间，一种是按位置

   • 按时间生成
     

      mysqlbinlog /tmp/mysql.000001 --start-datetime='2019-04-11 0:06:36' --stop-datetime='2019-04-11 0:07:09' -r /date/time.sql
     

   • 按位置生成
     

     [root@bogon ~]# mysqlbinlog /tmp/mysql.000001 --start-position=1340 --stop-position=1527 -r /date/posi.sql
     [root@bogon ~]# mysql -uroot -pABCabc123! < /date/posi.sql                                              
     mysql: [Warning] Using a password on the command line interface can be insecure.
     

10. 恢复全备份

    [root@bogon ~]# gunzip /date/mysql/it_allbackup_2019-04-11.sql.gz
    [root@bogon ~]# ls /date/mysql/it_allbackup_2019-04-11.sql
    /date/mysql/it_allbackup_2019-04-11.sql
    [root@bogon ~]# mysql -uroot -pABCabc123! < /date/mysql/it_allbackup_2019-04-11.sql
    

    检查：
    

11. 恢复增量备份

    [root@bogon ~]# mysql -uroot -pABCabc123! < /date/posi.sql
    

    检查：
    

完成！