如何理解mysqldump备份

如何理解mysqldump备份，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

mysqldump备份

逻辑innodb备份mysql

标准备份

mysqldump -uroot -p123 --default-character-set=utf8 --single-transaction --extended-insert=false --hex-blob --master-data=2 --log-error=/tmp/test.err --routines --triggers --events --quick

--flush-logs --databases test1 t1 > test1.sql 

--quick  查询出来不放在buffer，直接输出

--extended-insert=false  导出insert语句是多行，并非 insert into t values (),();

--lock-all-tables 将myisam表锁住，保持表的一致性

--single-transaction  将innodb表锁住，保持表的一致性

--master-data=2  file和position的记录位置 ,2 代表注释

--databases 导出创建数据库的语句

恢复

mysql -uroot -p1234356 < czb.sql

恢复其他库

1、将备份里的创建数据语句删除

2、use ‘新库’

3、在mysql数据库创建新库

4、mysql -uroot -p123456 新库名 < czb.sql

binlog追加数据

根据--master-data 参数备份记录备份的位置，通过mysqlbinlog 查看

mysqlbinlog --start-position=3444  -d czb mysql-bin.000003 >a.sql

--start-position=3444 //3444这个值通过备份里的--master-data参数得到的

mysql -uroot -p123456

source a.sql

实验：

[root@mysql_master backup]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 25

Server version: 5.6.29-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type help; or \h for help. Type \c to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| czb                |

| mysql              |

| performance_schema |

| t                  |

| test               |

+--------------------+

6 rows in set (0.00 sec)

mysql> use czb

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+--------------------+

| Tables_in_czb      |

+--------------------+

| F_ORDERINFO_DETAIL |

| t                  |

+--------------------+

2 rows in set (0.00 sec)

[root@mysql_master backup]# mysqldump -uroot -p123456 --default-character-set=utf8 --single-transaction --extended-insert=false --hex-blob --master-data=2 --log-error=/tmp/test.err --routines --triggers --events --quick --flush-logs --databases czb t > test1.sql

Warning: Using a password on the command line interface can be insecure.

[root@mysql_master backup]# ls

test1.sql

[root@mysql_master backup]#

以上备份完成

恢复到其他数据库：

[root@mysql_master backup]# vi test1.sql

#CREATE DATABASE /*!32312 IF NOT EXISTS*/ `czb` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `back`;

[root@mysql_master backup]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 27

Server version: 5.6.29-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type help; or \h for help. Type \c to clear the current input statement.

mysql> create database back;

Query OK, 1 row affected (0.04 sec)

mysql> 

[root@mysql_master backup]# mysql -uroot -p123456 back < test1.sql 

Warning: Using a password on the command line interface can be insecure.

以上就是将czb库中的t表恢复到back库中

追备份后的增加的数据（通过binlog）

假如现在备份的表有新的数据插入了。

[root@mysql_master backup]# more test1.sql

-- CHANGE MASTER TO MASTER_LOG_FILE=mysql-bin.000004, MASTER_LOG_POS=120;

[root@mysql_master data]# mysqlbinlog --start-position=120  -d czb /usr/local/mysql/data/mysql-bin.000004 >a.sql

[root@mysql_master backup]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 31

Server version: 5.6.29-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type help; or \h for help. Type \c to clear the current input statement.

mysql> use back

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注行业资讯频道，感谢您对的支持。