共计 3763 个字符，预计需要花费 10 分钟才能阅读完成。
如何理解 mysqldump 备份，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面丸趣 TV 小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
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
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注丸趣 TV 行业资讯频道，感谢您对丸趣 TV 的支持。
