MySQL learning -- backup and restore with mysqldump command

Keywords: MySQL mysqldump SQL Database

Absrtact: This paper mainly studied how to use mysqldump command to backup and restore database.

Derived data

Export all data

Parameters:

1 --all-databases, -A

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A > db_back.sql

Export the specified database

Parameters:

1 --databases, -B

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -B test mysql > db_back.sql

Export all tablespaces

Parameters:

1 --all-tablespaces, -Y

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A -Y > db_back.sql

Add delete database statement before each database is created

Parameters:

1 --add-drop-database

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --add-drop-database > db_back.sql

Add delete table operation before creating each table

The default is open. Use the -- skip add drop table parameter to cancel.

Parameters:

1 --add-drop-table// Add delete table operation.
2 –-skip-add-drop-table// Cancel the delete table operation.

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A > db_back.sql// Add or delete tables by default.
2 mysqldump -h127.0.0.1 -uroot -p -A --skip-add-drop-table > db_back.sql//Cancel the delete table operation.

Lock the table before exporting each table and unlock it after exporting

It is open by default and canceled with the -- skip add locks parameter.

Parameters:

1 --add-locks// Add lock table and unlock operation.
2 --skip-add-locks// Cancel the lock table and unlock operation.

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A > db_back.sql// Add lock table and unlock operation by default.
2 mysqldump -h127.0.0.1 -uroot -p -A --skip-add-locks > db_back.sql// Cancel the lock table and unlock operation.

Ignore specified table

Do not export the specified table. When you specify to ignore multiple tables, you need to repeat multiple times. Each table must specify both the database and the table name.

Parameters:

1 --ignore-table

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --ignore-table=database.table1 --ignore-table=database.table2 > db_back.sql

Export data only and create database

Parameters:

1 --no-create-db, -n

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --no-create-db > db_back.sql

Export data only and create data table

Parameters:

1 --no-create-info, -t

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --no-create-info > db_back.sql

Do not export data export structure only

Parameters:

1 --no-data, -d

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --no-data > db_back.sql

Export stored procedures and custom functions

Parameters:

1 --routines, -R

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --routines > db_back.sql

Set directory for character set files

The default is utf8.

Parameters:

1 --default-character-set

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --default-character-set=latin1 > db_back.sql

Set directory for character set files

Parameters:

1 --character-sets-dir

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --character-sets-dir=/data/mysql/share/charsets > db_back.sql

Enable compressed delivery of all information between client and server

Parameters:

1 --compress, -C

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A -C > db_back.sql

Port number to connect to the database

Parameters:

1 --port, -P

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --port=3306 > db_back.sql

Connection protocol used

Parameters:

1 --protocol

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --protocol=tcp > db_back.sql

Processing and changing lines

Output directly to the specified file. This option should be used on systems that use a carriage return line feed (\ \ r\n) line feed (for example, DOS, Windows). This option ensures that only one row is used.

Parameters:

1 --result-file, -r

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A --result-file=/tmp/mysqldump_result_file.txt > db_back.sql

Add export time

Add the export time to the output file. It is open by default and canceled with the -- skip dump date parameter.

Parameters:

1 --dump-date// Add the export time to the output file.
2 --skip-dump-date// Do not add the export time to the output file.

Give an example:

1 mysqldump -h127.0.0.1 -uroot -p -A// The export time is added to the output file by default.
2 mysqldump -h127.0.0.1 -uroot -p -A --skip-dump-date// Do not add the export time to the output file.

Posted by gaza165 on Tue, 24 Dec 2019 02:58:30 -0800