189 8069 5689
如何理解MySQLdump备份，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
我们提供的服务有：成都网站制作、网站建设、外贸网站建设、微信公众号开发、网站优化、网站认证、沈北新ssl等。为1000多家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的沈北新网站制作公司
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
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注创新互联行业资讯频道，感谢您对创新互联的支持。