How to use mysqldump to backup MySQL data

1. Introduction to mysqldump

mysqldump is a logical backup tool that comes with MySQL. MySQLdump is a database logical 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 the select permission to back up the data table, the show view permission is used to back up the view, and the trigger permission is used to back up the trigger, etc.

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

Its backup principle is to connect to the MySQL database through the 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, we only need to execute these insert statements to restore the corresponding data.

2. Backup Command

2.1 Command Format

mysqldump [options] database name [table name] > script name

or

mysqldump [options] --database name [options table name] > script name

or

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

2.2 Option Description

2.3 Examples

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 and table (multiple tables are separated by spaces)

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

Back up the specified database excluding certain tables

mysqldump -uroot -p test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db

3. Restore Command

3.1 System command line

mysqladmin -uroot -p create db_name 

mysql -uroot -p db_name < /backup/mysqldump/db_name.db

Note: Before importing the backup database, if db_name does not exist, it needs to be created; and it can only be imported if the database name is the same as the database name in db_name.db.

3.2 Source Method

mysql > use db_name

mysql > source /backup/mysqldump/db_name.db

Appendix: Commonly used commands

mysqldump -u root -p --all-databases > D:/mysql.sql #Backup all databasesmysqldump -uroot -p123456 --databases db1 db2 db3 > D:/mysql.sql #Backup multiple databasesmysqldump -hhostname -Pport -uroot -p"123456" --databases dbname > D:/mysql.sql #Remote backup (when remote, you need to add -h: host name, -P: port number)



#Export part of the data of the specified table with conditions. Note that the default time zone for mysqldump export is +00:00. The timestamp time after w will also be considered as data in the +00:00 time zone, which will cause data problems.mysqldump -h222.222.221.197 -uroot -proot DBname TABLEname -t --complete-insert --skip-tz-utc -w"sys_create > '2020-12-25 16:00:00'">export.sql   



#Export and import the database at one time (!!! Use with caution, if the two servers are written in reverse, it will be a disaster, because there is a drop table statement in the exported SQL)

mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2 



#Export as a compressed package, which will be much smaller. Try to use this for online servers. Recommended! ! !

mysqldump --opt -uroot -p123456 -h127.0.0.1 --databases dbname --ignore-table=dbname.table1 | gzip>/db_back/dbname_`date +%F`.zip

Summarize

This is the end of this article about the use of mysqldump for MySQL data backup. For more information about the use of MySQL mysqldump, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!