2.mysqldump备份

介绍

mysqldump是一种广泛使用的逻辑备份工具。 它提供了多种选项来包含或排除数据库，选择要备份的特定数据，仅备份没有数据的模式，或者只是备份存储的例程而不需要任何其他内容等等。

设置

mysqldump实用程序随附mysql二进制文件，因此不需要单独安装它。

指令如下：

shell> mysqldump [options]

在选项中，您可以指定用于连接数据库的用户名，密码和主机名，如下所示：

--user <user_name> --password <password>
or
-u <user_name> -p<password>

1 数据库全备

shell> mysqldump -u <user_name> -p<password> --all-databases > dump.sql

–all-databases选项备份所有数据库和所有表。 >运算符将输出重定向到dump.sql文件。 在MySQL 8之前，存储过程和事件存储在mysql.proc和mysql.event表中。 从MySQL 8开始，相应对象的定义存储在数据字典表中，但不会转储这些表。 要在使用–all-databases创建的转储中包含存储的例程和事件，请使用–routines和–events选项。

shell> mysqldump -u <user_name> -p<password> --all-databases --routines --events > dump.sql

可以打开dump.sql文件以查看其结构。 前几行是转储时的会话变量。 接下来是CREATE DATABASE语句，后跟USE DATABASE命令。 接下来是DROP TABLE IF EXISTS语句，后跟CREATE TABLE;然后我们有插入数据的实际INSERT语句。 由于数据存储为SQL语句，因此称为逻辑备份。在还原转储时，DROP TABLE语句将在创建表之前擦除所有表。

2 按照时间点恢复

要获得时间点恢复，您应指定–single-transaction和–master-data。

–single-transaction选项通过在执行备份之前将事务隔离模式更改为REPEATABLE READ并执行START TRANSACTION来提供一致的备份。 它仅适用于事务表，例如InnoDB，因为它会在发出START TRANSACTION时转储数据库的一致状态，而不会阻止任何应用程序。

–master-data选项将服务器的二进制日志坐标打印到转储文件。 如果–master-data = 2，则打印为注释。 这也使用FLUSH TABLES WITH READ LOCK语句来获取二进制日志的快照。

shell> mysqldump -u <user_name> -p<password> --all-databases --routines --events --single-transaction --master-data > dump.sql

3 转储Binlog

备份始终在从站上进行。 要在获取备份时获取主服务器的二进制日志坐标，可以使用–dump-slave选项。 如果要从主服务器获取二进制日志备份，请使用此选项。 否则，请使用–master-data选项：

shell> mysqldump -u <user_name> -p<password> --all-databases --routines --events --single-transaction --dump-slave > dump.sql

1. =1和=2的区别很明显，只是自动执行和手工执行的区别
2. –master-data用于在master端dump数据，用于建立slave。–dump-slave用户在slave端dump数据，建立新的slave,至少是第2个slave,也就是已经有A–>B,现在从B上导出数据建立A–>C的复制

4 备份指定数据库或指定数据库下的指定表

备份指定数据库

shell> mysqldump -u <user_name> -p<password> --databases employees > employees_backup.sql

备份指定表

shell> mysqldump -u <user_name> -p<password> --databases employees --tables employees > employees_backup.sql

5 不导出指定表

要忽略某些表，可以使用–ignore-table = database.table选项。 要指定多个要忽略的表，请多次使用该指令：

shell> mysqldump -u <user_name> -p<password> --databases employees --ignore-table=employees.salary > employees_backup.sql

6 导出某些行

mysqldump可以滤备份的数据。 假设要对2000年后加入的员工进行备份：

shell> mysqldump -u <user_name> -p<password> --databases employees --tables employees --databases employees --tables employees  --where="hire_date>'2000-01-01'" > employees_after_2000.sql

也可以加入Limit语句

shell> mysqldump -u <user_name> -p<password> --databases employees --tables employees --databases employees --tables employees  --where="hire_date >= '2000-01-01' LIMIT 10" > employees_after_2000_limit_10.sql

7 从远程服务器备份

可以使用–hostname选项提及主机名。 确保用户具有连接和执行备份的适当权限：

shell> mysqldump -u <user_name> -p<password> --all-databases --routines --events --triggers --hostname <remote_hostname> > dump.sql

8 通过备份集重建不同的schema

8.1 仅备份schema不包含数据

shell> mysqldump -u <user_name> -p<password> --all-databases --routines --events --triggers --no-data > schema.sql

8.2 仅备份数据不包含schema

–complete-insert将在INSERT语句中打印列名，这将在修改后的表中有额外的列时提供帮助：

shell> mysqldump -u <user_name> -p<password> --all-databases --no-create-db --no-create-info --complete-insert > data.sql

9 通过备份集合并数据

可以采用备份方式替换旧数据或保持旧数据不存在冲突。

9.1 替换旧数据

假设您要将数据从生产数据库还原到已具有某些数据的开发计算机。 如果要将生产中的数据与开发合并，可以使用–replace选项，该选项将使用REPLACE INTO语句而不是INSERT语句。 您还应该包括–skip-add-drop-table选项，该选项不会将DROP TABLE语句写入转储文件。 如果您具有相同数量的表和结构，还可以包含–no-create-info选项，该选项将跳过转储文件中的CREATE TABLE语句：

shell> mysqldump -u <user_name> -p<password> --databases employees --skip-add-drop-table --no-create-info --replace > to_development.sql

如果生产中有一些额外的表，则在恢复时前面的转储将失败，因为该表在开发服务器上不存在。 在这种情况下，您不应添加–no-create-info选项并在还原时使用force选项。 否则，还原将在CREATE TABLE失败，表示该表已存在。 不幸的是，mysqldump没有提供CREATE TABLE IF NOT EXISTS的选项。

现在做一个验证

CREATE TABLE `a` (
`id`  int(10) NOT NULL ,
`a`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)

随便插入点数据，我操作后如图

将A库全库导出然后到B库当中，保证两库一样然后进行测试

shell> mysqldump -u root -proot --all-databases > dump.sql
shell> scp dump.sql 192.168.200.102:/usr/MyWorkSpace/
shell> mysql -u root -p -f < dump.sql

现在A库与B库完全一样

然后修改一下A库表的值，如图

shell> mysqldump -u root -proot --databases gtiddatabase --skip-add-drop-table --no-create-info --replace > to_development.sql
shell> scp to_development.sql 192.168.200.102:/usr/MyWorkSpace/
shell> mysql -u root -p -f < to_development.sql

导入之后数据被A库替换

9.2 跳过数据

在写入转储文件时，可以使用INSERT IGNORE语句代替REPLACE。 这将保留服务器上的现有数据并插入新数据。

shell> mysqldump -u root -proot --databases gtiddatabase --skip-add-drop-table --no-create-info --insert-ignore > to_development.sql
shell> scp to_development.sql 192.168.200.102:/usr/MyWorkSpace/
shell> mysql -u root -p -f < to_development.sql

你会发现相同的主键数据无法被插入