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.