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

This article mainly introduces how to use mysqldump in MySQL data backup, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

A brief introduction to mysqldump

Mysqldump is a logical backup tool that comes with MySQL. MySQLdump is a logical database backup program that can be used to back up one or more mysql databases or transfer data to other mysql servers. When executing mysqldump, the account needs to have select permission to back up the data table, show view permission is used for backup view, trigger permission is used for backup trigger, etc.

Mysqldump is not a solution for big data backup, because mysqldump needs to rebuild sql statements to achieve backup function. For database backup and restore operations with large amounts of data, the speed is relatively slow. If you open mysqldump backup, you will find that it is actually the reproduction of database sql statements.

Its backup principle is to connect to the MySQL database through protocol, query the data that needs to be backed up, and convert the queried data into corresponding insert statements. When we need to restore these data, as long as we execute these insert statements, we can restore the corresponding data.

Backup command 2.1 command format

Mysqldump [options] Database name [Table name] > script name

Or

Mysqldump [options]-- Database name [option Table name] > script name

Or

Mysqldump [options]-- all-databases [options] > script name

2.2 option description

Parameter name abbreviation-- host-h server IP address-- port-P server port number-- user-uMySQL user name-- pasword-pMySQL password-- databases

Specify the database to back up-- all-databases

Back up all databases on the mysql server-- compact

Compressed mode to produce less output-- comments

Add comment information-complete-insert

Output the completed insert statement-- lock-tables

Lock all database tables before backup-- no-create-db/--no-create-info

Prohibit the generation of database creation statements-- force

Continue the backup operation when an error occurs-default-character-set

Specify the default character set-add-locks

Lock the database table 2.3 instance when backing up the database table

Back up all databases:

Mysqldump-uroot-p-- all-databases > / backup/mysqldump/all.db

Back up the specified database:

Mysqldump-uroot-p test > / backup/mysqldump/test.db

Back up the specified database specified table (multiple tables spaced by spaces)

Mysqldump-uroot-p mysql db event > / backup/mysqldump/2table.db

Backup specified database excludes certain tables

Mysqldump-uroot-p test-- ignore-table=test.t1-- ignore-table=test.t2 > / backup/mysqldump/test2.db III. Restore command 3.1 system line command mysqladmin-uroot-p create db_name mysql-uroot-p db_name

Use db_namemysql > source / backup/mysqldump/db_name.db attached: common command mysqldump-uroot-p-- all-databases > D:/mysql.sql # backup all databases mysqldump-uroot-p123456-- databases db1 db2 db3 > D:/mysql.sql # backup multiple databases mysqldump-hhostname-Pport-uroot-p "123456"-- databases dbname > D:/mysql.sql # remote backup (remote, need to add-h: host name -P: Port number) # conditional export part of the data of the specified table. Note that the mysqldump export time zone is set to + 00: 00 by default. The timestamp time after w will also be considered to be the data in the zone + 00: 00:00. This leads to problems with the data mysqldump-h322.222.221.197-uroot-proot DBname TABLEname-t-- complete-insert-- skip-tz-utc-w "sys_create > '2020-12-25 16purl 0000'" > export.sql # one-time export and import to the database (!! Use with caution, it is finished if the two servers are written upside down, because there is a drop table statement in the exported sql) mysqldump-- host=h2-uroot-proot-- databases db1 | mysql-- host=h3-uroot-proot db2 # is exported as a compressed package, which will be much smaller. Online servers try to use this, recommended! Mysqldump-- opt-uroot-p123456-h227.0.0.1-- databases dbname-- ignore-table=dbname.table1 | gzip > / db_back/dbname_ `date +% F`.zip Thank you for reading this article carefully. I hope the article "how to use mysqldump in MySQL data backup" shared by the editor will be helpful to you. At the same time, I hope you will support us, pay attention to the industry information channel, and more related knowledge is waiting for you to learn!

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.