Shulou(Shulou.com)05/31 Report--

How to understand mysqldump backup, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Mysqldump backup

Logical innodb backup mysql

Standard backup

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 query is not put in buffer, but directly output

The extended-insert=false export insert statement is multi-line, not insert into t values (), ()

-- lock-all-tables locks the myisam table to keep the table consistent

-- single-transaction locks the innodb table to keep the table consistent

-- record location of master-data=2 file and position, with 2 for comments

-- databases exports the statement to create the database

Restore

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 is obtained from the-- master-data parameter in the backup.

Mysql-uroot-p123456

Source a.sql

Experiment:

[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] #

The above backup is completed

Restore to another database:

[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

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.