一.mysqldump 

mysqldump备份原理，去数据库查询数据，然后进行sql语句的拼接。

优点：mysql自带，使用方便，备份结果容易操作。少量数据可以使用mysqldump

缺点:大量数据下，使用mysqldump恢复较慢，mysqldump再导入数据的时候，都是使用sql语句，需要开启事务。不建议使用.

二.语法如下:

mysqldump [arguments] >file_name

(1).备份所有数据库

mysqldump --all-databases >dump.sql

(2).备份指定数据库

mysqldump --databases wwj db2 db3 >dump.sql

(3).--single-transaction 获得备份的一致性

对于innodb有效，并且会关闭默认的--lock-tables选项，使innodb的表在导出过程中不被加锁。

如果需要加锁 需要同时加上--master-data参数

(4).--lock-tables(-l) 

在备份过程中，依次锁住备份的表。一般用于myisam引擎，用来保证一致性。对于innodb引擎不需要使用该参数。

如果同时存在innodb和myisam的表，则只能使用--lock-tables

(5).--lock-all-tables(-x)

在备份过程中，同时对所有表上锁。

(6).--add-drop-database

在create database前先运行drop database，和-all-databases 或者 --databases一起使用

--add-drop-table

在创建table前，先删除table

(7).--events(-E)

备份事件调度器

(8).--routines(-R)

备份存储过程和函数

(9).--triggers

备份触发器

(10).--hex-blob

将binary，varbinary，blog，bit列类型备份为十六进制的格式

(11).--tab=path(-T path) 

产生TAB分割的数据文件。对于每张表，创建一个包含create table语句的table_name.sql文件和包含数据的tb1_name.txt文件

(12).--where='where_condition'(-w)

导出给定条件下的数据

(13).导出某数据库中的几个表

mysqldump -uroot -p -S /tmp/mysql3306.sock --single-transaction test emp_range> t1.sql

(14).导出表结构

--no-data, -d 不导出任何数据，只导出数据库表结构

mysqldump -uroot -p -S /tmp/mysql3306.sock --single-transaction --all-databases -d > all.sql

(15).--ignore-table=name

不导出某个表，e.g = --ignore-table=database.table

(16).--insert-ignore     

Insert rows with INSERT IGNORE

(17).--log-error=name    

Append warnings and errors to given file.

(18).--master-data

mysqldump导出数据时，当这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1  当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。   

master-data参数在建立slave数据库的时候会经常用到，因为这是一个比较好用的参数，默认值为1，默认情况下，会包含change master to，这个语句包含file和position的记录始位置。master-data=2的时候，在mysqldump出来的文件包含CHANGE MASTER TO这个语句，处于被注释状态 

(19).--no-create-info, -t 只导出数据，而不添加CREATE TABLE 语句

(20).--no-create-db, -n 只导出数据，而不添加CREATE DATABASE 语句

(21).--force，-f 在导出过程中忽略出现的SQL错误，当出现错误时仍然继续后面的操作

(22).--flush-logs

导出之前，切换binlog日志。

如果导出很多数据库时，导出每个数据库都会执行一次flush logs。

如果想要日志切换和dump在同一时刻，需要添加参数--lock-all-tables or --master-data

(23).--flush-privileges

导出权限语句

(24).--dump-slave

在从库上使用，和--master-data参数一样，是为了slave建立下一级的slave

(25).--set-charset

导出脚本中显示字符集，默认开启。

(26).--default-character-set

指定是什么字符集

(27).-N --no-set-names

关闭--set-charset

(28).--compatible=(oracle/mssql/postgresql)

--mysqldump 的恢复

(1).mysql -uroot -p  

(2).source /home/mysql/test_backup.sql

或者

/usr/local/mysql/bin/mysql -uroot -p -S /tmp/mysql3306.sock < all.sql

三.mysqldump原理

mysqldump -uroot -proot --single-transaction --master-data=2 --flush-logs --routines --all-databases >fullbak16.sql

-- 关键步骤：

首先连接数据库后，做的整个流程如下：

1）FLUSH /*!40101 LOCAL */ tables

关闭打开的表，并且刷新查询缓存

closes all open tables,forces all table in use to be closed,and flushesthe query cache.

myisam：将脏数据刷到文件，同时关闭文件描述符，关闭文件。

innodb：并不会真正的关闭文件描述符，同时也不会写脏数据，所以这个功能在innodb 中用处不大。

2）FLUSH tables with read lock

简称FTWRL：执行FLUSH tables 操作，会加一个全局读锁，主要还是获取一致性备份。

主要是避免比较长的事务没有关闭，会导致FLUSH tables with read lock 操作一直得不到锁，就会阻塞其它客户端的操作。

FTWRL：一般需要持有两把全局的MDL 锁(metadata lock),而且还需要关闭所有的表对象。

FTWRL 主要包括3 个步骤：

A.上全局读锁

B.清理表缓存

C.上全局commit 锁

3）SET session transaction isolation level repeatable read

设置当前的事务隔离级别为RR，避免不可重复读和幻读

4）START transaction /*!40100 WITH CONSISTENT SNAPSHOT */

获取当前数据库的一个快照，由--single-transaction 决定。只适合支持事务的表，就是innodb 引擎。

主要是在开启事务的时候，对所有的表做一次select 操作，得到一个快照，备份时就可以一致。

案例说明：

start transaction：别人插入数据，本会话也能看见，出现备份不一致。

start transaction with consistent snapshot ：当前会话，对之前的数据可见，对后面的新数据不可见。

5）SHOW master status

这个由--master-data 参数决定,记录了开始备份时，binlog 的状态信息，包括binlog file和log position.

6）UNLOCK tables

由于第4步已经完成，所以释放锁。

7）SHOW databases

查看要备份哪些数据库。

8）开始备份所有的数据库

A.所的有备份，都不包括information_schema、sys、performance_schema。

如果要真备份这3 个数据库：

mysqldump -uroot -proot --skip-lock-tables --databases information_schema sys performance_schema > all-info-db.sql

B.备份数据

select /*!40001 SQL_NO_CACHE */ * from `dept`

C.备份结构

show create database if not exists `itpux`,

show create table `dept`

D.备份触发器

show triggers like 'dept'

E.备份函数和存储过程

show function status where Db = 'itpux'

show procedure status where Db = 'itpux'

F.保存点

savepoint sp

rollback to savepoint sp

release savepoint sp

设置savepointh 点，然后备份完了后再回滚到savepoint;

这样做的好处，不会阻塞在备份期间对已经备份完的表的DDL 操作。

主要是提高DDL 的并发性。

案例：

A：

mysql> start transaction with consistent snapshot ;

query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> savepoint sp;

query OK, 0 rows affected (0.00 sec)

mysql> select * from itpux01;

Empty set (0.00 sec)

B:

mysql> truncata table itpux01; -- hang

A：

mysql> rollback to savepoint sp;

query OK, 0 rows affected (0.00 sec)

B:

mysql> truncata table itpux01; -- 解锁

-- 重要：不能在业务高峰期做备份，建议不再用myisam 引擎，全部用innodb。

-- mysqldump 的大致实现过程是：

连接

初始化信息

刷新表（锁表）

记录偏移量

开启事务（一致性快照）

记录偏移量

解锁表

查看要备份的表

开始备份所有的数据

四.mysqldump 事务和锁

分析大家自己做作业：

1）--lock-tables

mysqldump -uroot -proot --lock-tables --database itpuxdb > itpuxdba.sql

后台看general.log

总结：会在整个导出过程中加lock read local 所有的表。针对innodb 别人只能读;针对myisam 别人可以读和插入，但阻塞update。

2）--lock-all-tables

mysqldump -uroot -proot --lock-all-tables --database itpuxdb > itpuxdba.sql

后台看general.log

总结：请求一个全局的读锁，会阻塞所有有的写入操作(insert,update,delete)，保证数据的一致性，备份完了后会话断开，自动解锁。

3) --single-transaction

mysqldump -uroot -proot --single-transaction --database itpuxdb > itpuxdba.sql

后台看general.log

总结：单独使用，不会有任何锁，但是会对备份的表持有metedata lock 的共享锁。

4) --master-data

mysqldump -uroot -proot --master-data --database itpuxdb > itpuxdba.sql

后台看general.log

5) --single-transaction 和--master-data

mysqldump -uroot -proot --single-transaction --master-data --database itpuxdb > itpuxdba.sql

后台看general.log

总结：结合使用时，也就是在开始的时候会短暂的请求一个全局的读锁，会阻止所有表的写入操作

五.mysqldump生产环境最佳的备份命令

innodb全备

mysqldump -uroot -proot --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --all-databases >db_fullbackup.sql

mysqldump -uroot -proot --skip-lock-tables --databases performance_schema information_schema sys > dbbackup_per_inf_sys.sql

innodb部分数据库备份

mysqldump -uroot -proot --single-transaction --master-data=2 --flush-logs --routines --databases db1 db2 db3 >db_fullbackup.sql

mysqldump -uroot -proot --flush-privileges --databases mysql > dbbackup_mysql.sql

myisam全备

mysqldump -uroot -proot --lock-all-tables --master-data=2 --flush-logs --flush-privileges --routines --all-databases >db_fullbackup.sql

六.逻辑备份恢复实验

1.备份恢复所有数据库

- 备份实例下所有数据库

mysqldump -u root -p -S /tmp/mysql3306.sock --all-databases --add-drop-database --single-transaction > /tmp/db1.sql

- 恢复

mysql -uroot -p -S /tmp/mysql3306.sock < /tmp/db1.sql

2.备份恢复单独的数据库

-备份数据库wwj

mysqldump -u root -p -S /tmp/mysql3306.sock --databases wwj --add-drop-database --single-transaction > /tmp/db2.sql

-恢复数据库wwj

mysql -uroot -p -S /tmp/mysql3306.sock < /tmp/db2.sql

3.备份恢复某数据库中的几个表

-备份表wwj.t3, wwj.haha22

mysqldump -u root -p -S /tmp/mysql3306.sock --single-transaction --databases wwj --tables t3 haha22 > /tmp/db3.sql

-恢复

use wwj;

source /tmp/db3.sql;