使用mysqldump进行数据库备份

备份类型可以分为
1.热备份：在线的备份，读写操作均不受影响；
2.温备份：在线的备份，仅可以进行读操作；
3.冷备份：离线的备份，读写操作均中止；
也可以分为：
1.物理备份：直接复制数据文件；
2.逻辑备份：将数据导出到文本文件中；
或者分为：
1.完全备份：备份全部数据；
2.增量备份：金备份完全备份或增量备份以后变化的数据；
3.差异备份：仅备份上次完全备份以来变化的数据；
对于逻辑备份而言：
1.会导致浮点数据丢失精度；
2.备份出的数据占用存储空间，但压缩后可大大节省空间；
3.不适合对大数据库做完全备份；
还有，对于MyISam存储引擎只能进行温备份，需要用到--lock-all-tablesx选项，而对于INnoDB存储引擎可以进行热备份，可以使用--single-transaction和--flush-logs等选项；

一：
mysqldump用法：
1.将数据库备份为完全备份：
[root@www ~]# mysqldump -uroot -p mydb > /mnt/mydb.sql
Enter password: 
[root@www ~]# ll /mnt/ | grep my
-rw-r--r-- 1 root root 1921 Oct  8 06:56 mydb.sql
此时删除MYSQL中的mydb数据库
mysql> drop database mydb;
Query OK, 1 row affected (0.16 sec)
还原mydb数据库：
[root@www ~]# mysql < /mnt/mydb.sql
ERROR 1046 (3D000) at line 22: No database selected
这是因为MYSQL无法自动创建数据库，所以需要在MYSQL中先手动创建数据库再还原
mysql> create database mydb;
Query OK, 1 row affected (0.01 sec)
[root@www ~]# mysql mydb < /mnt/mydb.sql
2.上述的完全备份是在没有写操作情况下备份的，如果正在备份时刚好有写操作进行，那么备份后的数据肯定是不完整的，所以可以在备份时加上一个读锁：
加上读锁然后进行一个写操作：
mysql> flush tables with read lock;
mysql> insert into course values(5,'A');
停在此刻无法进行
进行数据备份：
[root@www mnt]# mysqldump -uroot -p mydb > /mnt/mydb.sql
Enter password: 
备份完成后解锁，写操作立即生效：
mysql> unlock tables;
mysql> insert into course values(5,'A');
3.接上述操作，在进行数据备份时虽然加了读锁无法进行写操作，但是备份结束并解锁后，写操作会立刻生效，数据恢复时依旧不能恢复到现在的状态，只能恢复到完全备份时的状态。所以，可以将完全备份后的操作再进行增量备份，那么现在的状态=完全备份+每个增量备份，增量备份就是完全备份后的操作，它记录在二进制日志文件中，此时进行完全备份时，二进制日志文件的位置就是完全备份，那么此后的二进制日志记录就是完全备份后的操作即增量备份；
这里用到了mysqldump的--master-data=n选项，n的值为0,1，或2；n=0时表示不记录二进制日志文件及其位置，n=1时表示以change  master  to的方式记录文件名和位置但用于恢复后直接启动从服务器，n=2时表示以change  master  to的方式记录文件名和位置，但默认为被注释；
进行完全备份，和上次不同，里面记录了此时的二进制日志的位置：
[root@www mnt]# mysqldump -uroot -p --master-data=2 mydb > /mnt/mydb.sql
Enter password: 
[root@www mnt]# vim mydb.sql 
--CHANGE MASTER TO MASTER_LOG_FILE='www-bin.000001', MASTER_LOG_POS=336;
这里显示了此时的二进制文件是www-bin.000001位置是336所以完全备份后的操作就是336以后的内容，下次可以直接从该文件的336位置向后备份作为增量备份；
4. 根据2可知，备份时锁表可以保证数据的完整性，但是每次备份都要锁表有些麻烦，而mysqldump提供了一个自动锁表的参数
 --lock-all-tables表示备份时锁定所有表
如：
[root@www ~]# mysqldump -uroot -p --master-data=2 --lock-all-tables mydb > /mnt/mydb_lock.sql
Enter password: 
5.进行备份时，最好将二进制日志滚动一下，那么在恢复时就可以直接将该文件以及其后的所有文件直接恢复了。日志滚动可以手动也可以使用mysqldump提供的参数--flush-logs实现备份时自动日志滚动；
6.如果指定数据库中的表都是INnoDB类型以实现在线备份(热备)可以使用参数--single-transaction启动热备，而且此时是在线备份，所以也不需要锁定表了；
7. 以上只是备份一个数据库，当需要备份多个数据库时可以使用参数--all-databases而--databases db-name,db_name...可以备份指定数据库，使用这个选项,将所有databases后的参数作为数据库名称，不需要再创建数据库然后恢复到该数据库中了；

二：
mysqldump实现每周的完全备份+每天的增量备份：
步骤：mysqldump完全备份和备份二进制日志文件(最好flush logs一下)
过程：
1.以此刻为例备份所有数据库(完全备份)
[root@www ~]# mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /mnt/all.sql
Enter password: 
[root@www mnt]# vim all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='www-bin.000002', MASTER_LOG_POS=120;
此时二进制日志文件为www-bin.000002，此前的日志文件可以保留或者删除，如果删除此前的日志文件：
mysql> purge binary logs to  'www-bin.000002';
Query OK, 0 rows affected (0.03 sec)
mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| www-bin.000001 |       120 |
+----------------+-----------+
1 row in set (0.01 sec)
2.连接MYSQL进行第一天的操作，如删除部分数据
mysql> use mydb;
mysql> select * from course;
+-----+-------+
| cno | cname |
+-----+-------+
|   1 | Linux |
|   2 | C++   |
|   3 | C     |
|   4 | sql   |
|   5 | A     |
+-----+-------+
mysql> delete from course where cno>=3;
mysql> select * from course;
+-----+-------+
| cno | cname |
+-----+-------+
|   1 | Linux |
|   2 | C++   |
+-----+-------+
3.然后flush logs一下做增量备份(flush logs会使二进制日志会滚动一次)
mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)
mysql> show binary logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| www-bin.000001 |       379 |
| www-bin.000002 |       120 |
+----------------+-----------+
此时www-bin.000001就是第一天的增量备份即刚刚delete语句操作
4.保存增量备份文件
[root@www  ]# cd /mysqldata/data/
[root@www data]# cp www-bin.000001 /mnt/1_backup.sql
或者
[root@www data]# mysqlbinlog www-bin.000001 > /mnt/1_backup.sql
来保存第一天的增量备份
5.连接MYSQL进行第二天的操作，如添加部分数据
mysql> insert into course values(3,'C'),(4,'Mysql');
mysql> select * from course;
+-----+-------+
| cno | cname |
+-----+-------+
|   1 | Linux |
|   2 | C++   |
|   3 | C     |
|   4 | Mysql |
+-----+-------+
6.保存第二天的增量备份
[root@www data]# mysqlbinlog www-bin.000002 > /mnt/2_backup.sql
7.上述操作完成后，模拟数据库被删：
[root@www data]# rm -rf ./*
此时MYSQL服务也无法停止
[root@www data]# service mysqld stop
ERROR! MySQL server PID file could not be found!
但可以杀死mysqld所有进程
[root@www data]# killall mysqld
8.恢复数据库
[root@www data]# cd /usr/local/mysql/
[root@www mysql]# scripts/mysql_install_db --user=mysql --datadir=/mysqldata/data/
[root@www mysql]# service mysqld start
Starting MySQL.... SUCCESS! 
先还原完全备份：
[root@www mnt]# mysql -uroot -p < all.sql
Enter password: (因为重新执行了一次mysql_install_db所以是空密码)
mysql> select * from course;
+-----+-------+
| cno | cname |
+-----+-------+
|   1 | Linux |
|   2 | C++   |
|   3 | C     |
|   4 | sql   |
|   5 | A     |
+-----+-------+
再还原第一天的增量备份：
[root@www mnt]# mysql -uroot -p < 1_backup.sql
Enter password: 
mysql> select * from course;
+-----+-------+
| cno | cname |
+-----+-------+
|   1 | Linux |
|   2 | C++   |
+-----+-------+
同理再还原第二天的增量备份即可还原到删除数据库时的状态了；
补充说明：
使用mysqldump为没有多大的数据库做完全备份还可以，但是一旦遇到大型数据库时再做完全备份时速度会很慢；

三.
使用二进制日志虽然能进行数据库备份，但是会产生大量的数据，影响系统I/O所以在恢复数据时最好关闭二进制日志功能：
未关闭二进制日志功能当创建数据库时会将该语句写入二进制日志中：
mysql> show binlog events in 'www-bin.000001';
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name       | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| www-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.11-log, Binlog ver: 4 |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
mysql> create database aaa;
mysql> show binlog events in 'www-bin.000001';
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name       | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| www-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.11-log, Binlog ver: 4 |
| www-bin.000001 | 120 | Query       |         1 |         211 | create database aaa                   |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
关闭sql_log_bin功能：
mysql> set sql_log_bin=0;
mysql> create database bbb;
mysql> show binlog events in 'www-bin.000001';
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name       | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
| www-bin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.11-log, Binlog ver: 4 |
| www-bin.000001 | 120 | Query       |         1 |         211 | create database aaa                   |
+----------------+-----+-------------+-----------+-------------+---------------------------------------+
例如：
假如恢复数据库mydb而且临时性的关闭了二进制日志功能：
1.先临时性关闭二进制日志功能：
mysql> set sql_log_bin=0;
2.由于关闭该功能是临时性的即之针对此次会话，所以无法退出MYSQL恢复数据，此时可以使用source来导入备份数据库：
mysql> source /mnt/mydb.sql;
出现ERROR 1046 (3D000): No database selected错误，导致show  databases时没有mydb数据库
解决：
先创建mydn数据库：
mysql> create mydb;
然后将mydb设为默认库：
mysql> use mydb;
将备份数据库导入即可：
mysql> source /mnt/mydb.sql
3.将备份恢复后就可以打开二进制日志功能了，这样省去了很多的日志记录：
mysql> set sql_log_bin=1;
###如果在恢复备份数据mydb.sql时没有关闭二进制日志功能，将会产生的日志记录大小为：
mysql> show master status;
+----------------+----------+--------------+------------------+-------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------+----------+--------------+------------------+-------------------+
| www-bin.000001 |     1096 |              |                  |                   |
+----------------+----------+--------------+------------------+-------------------+