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

Editor to share with you the example analysis of mysqldump backup and recovery in mysql, I believe that most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

The implementation principle of the mysqldump program is that we add the system table information to the database to get the data from a table and then generate an INSERT statement and write it into the backup file. This gives rise to a problem. During the normal operation of the system, requests for data changes are likely to continue to be executed, which may result in inconsistent data backed up in mysqldump. In other words, the backup data is likely not to be data at the same point in time, and may not even be able to meet integrity constraints. Such a backup set may not be a big problem for some systems, but it is a big problem for some systems that have strict requirements for data consistency and integrity, which is a completely invalid backup set.

What should we do about such a scene? We know that if the data in the database is to be consistent, then there are only two cases in which it can be done.

First, take out all the data at the same time

Second, the data in the database is in a static state.

For the first case, people must wonder, is this possible? In any case, as long as there are more than two tables, no matter how we write the program, it is impossible to take exactly the same number of times last night. Yes, we really can't get the same point in time through conventional methods, but don't forget that the database can read data at the same point in time in the same transaction. Therefore, for the storage engine supported by transactions, such as Innodb or BDB, we can control the whole backup process to be controlled in the same transaction to achieve the consistency and integrity of the backup data, and the mysqldump program also provides us with relevant parameter options to support this function, that is, through the "- single-transaction" option, it can not affect any normal service of the database. The principle is realized through snapshots.

Add:

Single-transaction allows mysqldump to leave the table unlocked. But he has three premises:

A, innodb engine

B. Other alter table and drop table,rename table,truncate table operations cannot be performed at the same time.

C. The isolation level must be REPEATABLE READ, which will be modified by many companies, such as Aliyun's rds. The default isolation level is READ-COMMITTED.

-- single-transaction

Creates a consistent snapshot by dumping all tables in a

Single transaction. Works ONLY for tables stored in

Storage engines which support multiversioning (currently

Only InnoDB does); the dump is NOT guaranteed to be

Consistent for other storage engines. While a

-- single-transaction dump is in process, to ensure a

Valid dump file (correct table contents and binary log

Position), no other connection should use the following

Statements: ALTER TABLE, DROP TABLE, RENAME TABLE

TRUNCATE TABLE, as consistent snapshot is not isolated

From them. Option automatically turns off-- lock-tables.

For the second case, I think the first thing that comes to mind is to lock the tables that need to be backed up, allowing only reads and no writes. Yes, it's really the best we can do. We can only use a compromise way to let the database only provide data query service and lock the write service during the backup process, so as to make the data temporarily in a consistent state that will not be modified, and then unlock the write lock after the mysqldump completes the backup, and start providing complete services again. The mysqldump program itself provides options such as "--lock-tables" and "--lock-all-tables", which locks the table before execution and automatically releases the lock after execution. One thing to note here is that "- lock-tables" will require all tables of dump to be locked at once. If you need dump tables in multiple different databases, be sure to use "--lock-all-tables" to ensure the consistent integrity of the data.

Mysqldump is the client program that MySQL uses to transfer the database. A dump contains SQL statements that create and / or load tables to achieve lightweight, rapid migration or recovery of the database, which is a way for mysql databases to implement logical backups. Mysqldump is not suitable for backup and recovery of large databases. It is slow and does not support parallelism. Secondly, SQL replay will consume a lot of Ihand O.

1. View detailed mysqldump help information

[root@mysql] # mysqldump-- help

Dumping structure and contents of MySQL databases and tables.

Usage: mysqldump [OPTIONS] database [tables]

OR mysqldump [OPTIONS]-- databases [OPTIONS] DB1 [DB2 DB3...]

OR mysqldump [OPTIONS]-- all-databases [OPTIONS]

2. Introduction of main parameters in mysqldump

2.1

-- opt Same as-- add-drop-table,-- add-locks,-- create-options

-quick,-extended-insert, lock-tables, set-charset

And-- disable-keys. Enabled by default, disable with-- skip-opt.

-Q,-- quick Don't buffer query, dump directly to stdout.

(Defaults to on; use-skip-quick to disable.)

Note: the above two parameters are enabled by default. If the two parameters are not used, all the contents will be loaded into memory before the dump result, which will seriously affect the performance for larger database dumps.

2.2

-- default-character-set=name Set the default character set.

Description: sets the character set of the exported script, or UTF8 if not specified.

2.3

-d,-- no-data No row information.

Description: do not export data rows, only export structure

-t,-- no-create-info Don't write table creation info.

Description: only export table data, not table structure

2.4

Triggers Dump triggers for each dumped table. (Defaults to on; use-- skip-triggers to disable.)

Description: trigger default export

R,-- routines Dump stored routines (functions and procedures).

Description: stored procedures and functions are not exported by default

2.5

-- single-transaction

Description: create a consistent snapshot for the innodb engine only

-f,-- force Continue even if we get an SQL error.

Description: dump is still forced when there is an error

2.6

-- add-drop-table Add a DROP TABLE before each create.

(Defaults to on; use-skip-add-drop-table to disable.)

Description: when importing and restoring, delete the table before creating the table.

3. Examples

Note (pay special attention to these two points to prevent misoperation and loss of data):

-- database generates database building statements that are disabled by default, such as: CREATE DATABASE / *! 32312 IF NOT EXISTS*/ `test` / *! 40100 DEFAULT CHARACTER SET utf8 * /; it means that if the mysqldump database does not exist, it will be created when it is imported and restored, but will not be created if it exists.

-- add-drop-table is enabled by default. During import and recovery, if the table exists, it will be deleted and then created, such as: DROP TABLE IF EXISTS `test`

Disable is the parameter-- skip-add-drop-table.

3.1

Back up all databases on the server

Shell > mysqldump-uXX-pXX-all-databases-- opt-- compact-- flush-privileges-- routines > alldb_$ (date +% F). Sql

Restore all databases

Shell > mysql-uXX-pXX mysqldump-uXX-pXX-- database db_1 db_2-- routines > multidb_$ (date +% F). Sql

Restore multiple databases at the same time

Shell > mysql-uroot-poracle

< multidb_$(date +%F).sql 备份单个数据库 shell>

Mysqldump-uXX-pXX db_name-- opt-- routines > db_name_$ (date +% F). Sql

Restore a single database

Shell > mysql-uXX-pXX db_name

< db_name_$(date +%F).sql 3.2 备份数据库的结构，不备份数据 shell>

Mysqldump-uXX-pXX db_name-- no-data-- routines > db_name_onlystructure_$ (date +% F). Sql

Back up the data of the database, not the structure

Shell > mysqldump-uXX-pXX-- opt db_name-- no-create-info > db_name_onlydata_$ (date +% F). Sql

3.3

Back up specific tables on the database

Shell > mysqldump-uXX-pXX-- opt db_name t_name > db.t_name_$ (date +% F). Sql

Restore specific tables on the database

Shell > mysql-uXX-pXX dbname

< db_name_$(date +%F)_$(date +%F).sql 备份指定数据库上的多个指定表 shell>

Mysqldump-uroot-poracle-- database test test02-- routines > multi_t_$ (date +% F). Sql

Restore multiple specified tables on the specified database

Mysql-uroot-poracle

< /tmp/multi_t_$(date +%F).sql 备份表上特定的记录 shell>

Mysqldump-uXX-pXX db_name t_name-w "first_name='NICK'" > db.t_name_row_$ (date +% F). Sql

3.4

Only export stored procedures, functions, and triggers in the database

Shell > mysqldump-uXX-pXX db_name-no-create-db-- no-data-- no-tablespaces-- no-create-info-- routines > db_name_$ (date +% F). Sql

The above is all the contents of the article "sample Analysis of mysqldump backup and recovery in mysql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.