Shulou(Shulou.com)06/01 Report--

The following mainly brings you the role of mysqldump tools in the MySQL database backup process. I hope these words can bring you practical use, which is also the main purpose of this article that I edit the role of mysqldump tools in the MySQL database backup process. All right, don't talk too much nonsense, let's just read the following.

Mysqldump tool is a tool that is often used in MySQL database backup. You can specify data tables, some databases, all database-level backups, type mysqldump enter on the command line, and you can see examples of these three ways.

Mysql@db01:~$ mysqldump

Usage: mysqldump [OPTIONS] database [tables]

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

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

For moreoptions, use mysqldump-- help

Through mysqldump-- help, you can see that there are many options (OPTIONS), as well as the richness of its functions. These options can be divided into several groups according to their function, and then take a look at the meaning of the key options in each group.

HelpOptions group

1.--version

In addition to the-- help option, this group also has a-- version to view the version of mysqldump.

Mysql@db01:~$ mysqldump-version

Mysqldump Ver 10.13 Distrib 5.6.24, for Linux (x86 / 64)

In addition, for the following illustration, the databases and data tables used are as follows:

(root@localhost) [(none)] > SHOW DATABASES

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | stage |

| | test |

+-+

5 rows inset (0.00 sec)

(root@localhost) [(none)] > SHOW TABLES FROM stage

+-+

| | Tables_in_stage |

+-+

| | st1 |

| | st2 |

+-+

2 rows inset (0.00 sec)

(root@localhost) [(none)] > SHOW TABLES FROM test

+-+

| | Tables_in_test |

+-+

| | tb1_bak |

| | tb2 |

| | v_tb1 |

| | v_tb2 |

+-+

4 rows inset (0.00 sec)

ConnectionOptions group

1.--bind-address=ip_address

If the database CVM has multiple network interfaces, use this option to set it to listen only on an interface and can only access the database through that interface.

2.--login-path=name

Login path, which is a collection of host, password, port, socket, user options. In the following demonstration, using this login method, the details have been described in the MySQL Database login tips.

Option-FileOptions group

1.--defaults-file=file_name

Mysqldump only reads the parameters under the client and mysqldump tags in the configuration files specified by this option. If the-- login-path option is also specified, it will also read the file. Mylogin.cnf.

2.--no-defaults

Mysqldump does not read any configuration files and is generally used for debugging when reading error parameters. If the-- login-path option is also specified, it will also read the file. Mylogin.cnf.

3.--print-defaults

Mysqldump reads the parameters under the client and mysqldump tags in these configuration files in this order / etc/my.cnf, / etc/mysql/my.cnf,/data/3316/conf-DMYSQL_DATADIR=/data/3316/my.cnf, ~ /. My.cnf, and then prints the parameter, even if it is duplicated. This option has the same meaning as the mysql command for the effect after the repeating parameter.

DDLOptions group

1.--add-drop-database

In the backed up SQL file, the DROP DATABASE statement is explicitly written before each CREATE DATABASE statement, as shown in the SQL file:

/ *! 40000DROP DATABASE IF EXISTS `stage` * /

CREATEDATABASE / *! 32312 IF NOT EXISTS*/ `stage` / *! 40100 DEFAULT CHARACTER SETutf8mb4 * /

This option is generally used with-- all-databases or-databases, and is off by default.

2.--add-drop-table

Before the CREATE TABLE statement, write the DROP TABLE statement, which is shown in the SQL file as follows:

DROPTABLE IF EXISTS `st1`

CREATETABLE `st1`.

It is on by default.

3.-- add-drop-trigger

Before the CREATE TRIGGER statement, write the DROP TRIGGER statement.

It is off by default.

4.--no-create-db,-n

When used with-- databases or-- all-databases, CREATE DATABASE statements are not written to the SQL file.

It is off by default.

5.--no-create-info,-t

No CREATE TABLE statements are written in the SQL file, that is, the data table structure is not exported.

It is off by default.

DebugOptions group

1.-- force,-f

During the execution of mysqldump, if there is an SQL error, the process will continue and the error log will be printed to the terminal.

If there is a view v_tb1, the dependent table tb1 is renamed to tb1_bak. When backing up the database test, there will be the following error, and if you encounter an error, the backup process will exit.

Mysql@db01:~/dbbackup$ mysqldump-login-path=mytest-default-character-set=utf8mb4--result-file=test.sql-single-transaction-databases test

Mysqldump:Couldn't execute 'SHOW FIELDS FROM `v_ tb1`: View' test.v_tb1' referencesinvalid table (s) or column (s) or function (s) or definer/invoker of view lackrights to use them (1356)

If you add the-- force option to the above command line, the backup process will be completed even if there is an error.

2.--log-error=file_name

Warning and error logs are output to the specified file.

Refer to the example given in the-- force option, as shown below, and output the error to the file error.txt.

Mysql@db01:~/dbbackup$ mysqldump-login-path=mytest-default-character-set=utf8mb4--force-log-error=error.txt-result-file=test.sql-single-transaction--databases test

Mysql@db01:~/dbbackup$ cat error.txt

Mysqldump:Couldn't execute 'SHOW FIELDS FROM `v_ tb1`: View' test.v_tb1' referencesinvalid table (s) or column (s) or function (s) or definer/invoker of view lackrights to use them (1356)

InternationalizationOptions group

1.--default-character-set=charset_name

Indicates that the character set used by mysqldump backups is generally the same as that displayed by SHOW GLOBAL VARIABLES LIKE'character_set_server'. If the database character set is utf8mb4, the backup does not specify the character set, and it will use the utf8 character set by default, so when the data is recovered, garbled code may occur.

2.--set-charset

Add the SET NAMES statement to the header in the backed up SQL file, and the SQL file appears as follows:

/ *! 40101SET NAMES utf8mb4 * /

ReplicationOptions group

Mysqldump is often used to build a slave database node in a master-slave replication environment. Here are two related parameters.

1.--master-data [= value]

Mysqldump obtains the coordinates from the copy of the library by obtaining the GLOBAL READ LOCK from the main database, that is, the name and location of the binlog log on the main library. The process is shown in general log as follows:

1457271Query FLUSH / *! 40101 LOCAL * / TABLES

1457271Query FLUSH TABLES WITH READ LOCK

1457271Query SHOW MASTER STATUS

1457271Query UNLOCK TABLES

The name and location of the binlog log mentioned above are written into the CHANGE MASTER TO statement, which is shown in the SQL file as follows:

-- CHANGEMASTER TO MASTER_LOG_FILE='mysql-bin.000036', MASTER_LOG_POS=345

This option has two values, 2 and 1. The former means that the CHANGE MASTER TO statement is commented, and the latter is uncommented.

2.--dump-slave [= value]

The meaning of this option is similar to-- master-data, but is executed on the slave library. It obtains the name and location of the binlog log on the corresponding master library by stopping the SQL thread of the slave library. The process is shown in general log as follows:

1770998Query SHOW SLAVE STATUS

1770998Query STOP SLAVE SQL_THREAD

1770998Query SHOW SLAVE STATUS

1770998Query UNLOCK TABLES

FormatOptions group

1.--complete-insert,-c

The data table field column names are included in the INSERT statement, which is shown in the SQL file as follows:

INSERTINTO `st1` (`a`, `b`, `c`) VALUES (1)

2.--create-options

In the CREATE TABLE statement, it contains the specific information of the MySQL data table, such as the AUTO_INCREMENT attribute of the field, storage engine, character set and so on.

3.--hex-blob

For BINARY, VARBINARY, BLOB and BIT binary data types, export in hexadecimal mode. It is recommended to export binary data in this way, which can avoid some errors caused by character set conversion.

4.--result-file=file_name.-r file_name.

Output backup data to a specified file. When using this option, you can't compress it while backing up, requiring more disk space.

FilteringOptions group

1.-- all-databases,-A

Back up all databases except information_schema and performance_schema.

2.--databases.-B.

Back up the specified database, with multiple library names separated by spaces.

3.-events,-E

Backup event.

4.--ignore-table=db_name.tbl_name

Specifies that the data of a table in a database is not exported. If there are more than one such table, it needs to be specified multiple times. It is generally used to ignore views.

5.--no-data,-d

The data in the data table is not exported.

6.--routines.-R

Export a stored procedure.

7.--triggers

Export trigger.

PerformanceOptions group

1.--disable-keys

In the backed up SQL file, add ALTER TABLE before and after the INSERT statement of each data table. DISABLE | ENABLE KEYS statement, which is shown in the SQL file as follows:

/ *! 40000ALTER TABLE `st1` DISABLE KEYS * /

INSERTINTO `st1`.

/ *! 40000ALTER TABLE `st1` ENABLE KEYS * /

After the data is imported and indexed, the import will be more efficient, but this option only affects the non-unique index of the MyISAM table.

2.--extended-insert,-e

Export data using Multiple-Row syntax, that is, the VALUES keyword is followed by a list of multiple sets of values, so that the backup file is smaller and the import is faster. The SQL file is shown as follows:

INSERTINTO `st1` VALUES (1), (2), (2), (2), (3), (3), (4), (4)

3.-- opt

This option represents an option group that contains the options shown below, and the meaning of the option is introduced before and after.

-add-drop-table,--add-locks,-- create-options,-- disable-keys,-- extended-insert,--lock-tables,-- quick,-- set-charset

It is on by default.

4.--quick,-Q

This option instructs mysqldump to write backup data directly to the SQL file instead of caching it to memory before writing to the file.

It is on by default.

TransactionalOptions group

1.--add-locks

Add the LOCK | UNLOCK TABLES statement before and after the INSERT statement of each data table in the backed up SQL file, as shown in the SQL file:

LOCKTABLES `st1` WRITE

INSERTINTO `st1`.

UNLOCKTABLES

When importing in this way, you can increase the insertion speed of INSERT statements in the SQL file.

It is on by default.

2.--flush-privileges

After backing up the mysql (system library) database, add the FLUSH PRIVILEGES statement, which is displayed in the SQL file as follows:

-- FlushGrant Tables

/ *! FLUSHPRIVILEGES * /

3.--lock-all-tables,-x

Through this parameter, the GLOBAL READ LOCK is obtained to ensure the consistency of the backup data.

If this option is used with-- single-transaction at the same time, the following error will be reported:

Mysqldump:You can't use-single-transaction and-lock-all-tables at the same time.

4.--lock-tables,-l

Before backing up each database, all its data tables are locked, as shown in the general log log:

1459882Query LOCK TABLES `st1` READ / *! 32311LOCAL * /, `st2` READ / *! 32311LOCAL * /

1459882Query UNLOCK TABLES

Because each database is locked separately, the backup data may be inconsistent.

5.--single-transaction

When backing up, set the isolation level to REPEATABLE, and then START TRANSACTION opens a thing to make use of the MVCC feature of the InnoDB storage engine for data consistent backup.

Note that the-- single-transaction and-- lock-all-tables options are mutually exclusive;-- lock-tables is turned off when used with any of the previous options.

Here are some examples of mysqldump that are actually used.

1. Export the table structure of the database stage.

Mysql@db01:~/dbbackup$ mysqldump-login-path=mytest-default-character-set=utf8mb4--force-no-data-quick-result-file=stage_struc.sql-single-transactionstage

two。 Back up the data table st2 in the database stage.

Mysql@db01:~/dbbackup$ mysqldump-login-path=mytest-default-character-set=utf8mb4--force-hex-blob-quick-result-file=stage_st2.sql-single-transactionstage st2

3. Back up the databases stage and test, and you can see the error message in the log error.txt.

Mysql@db01:~/dbbackup$ mysqldump-login-path=mytest-default-character-set=utf8mb4--force-log-error=error.txt-hex-blob-quick-result-file=stage_test.sql--single-transaction-databases stage test

Mysql@db01:~/dbbackup$ cat error.txt

Mysqldump:Couldn't execute 'SHOW FIELDS FROM `v_ tb1`: View' test.v_tb1' referencesinvalid table (s) or column (s) or function (s) or definer/invoker of view lackrights to use them (1356)

4. In the backup from the database, to build another slave database node.

[mysql@db02dbbackup] $mysqldump-- login-path=mytest-- add-drop-database-- add-drop-trigger--default-character-set=utf8mb4-- dump-slave=2-- flush-privileges--force-- log-error=error.txt-- hex-blob-- quick-- routines--single-transaction-- triggers-- all-databases | gzip > all.sql.gz

[mysql@db02dbbackup] $gzip-d all.sql.gz

[mysql@db02dbbackup] $grep'\ -\-CHANGE MASTER TO' all.sql

-- CHANGEMASTER TO MASTER_LOG_FILE='mysql-bin.000041', MASTER_LOG_POS=3068

For the above about the role of mysqldump tools in the MySQL database backup process, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.