Database backup and Recovery

mysqldump Command Backup: back up the data in the database into a text file, and the table structure and data in the table are stored in the generated text.

How to work: first identify the table structure that needs to be backed up, generate a CREATE statement in a text file, and then convert all records to an INSERT statement. Both the CREATE statement and the INSERT statement are used for later recovery.

Backup and restore are performed under the root authority of Linux, not under MySQL.

-Syntax: mysqldump-h hostname-P Port-u user name-p password (–database) database name > file name. sql

Example: Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

command to restore MySQL database
mysql -hhostname-uusername-ppassword databasename < Backupfile.sql Note that the database name must exist because backup only backs up table structure create and table data insert

Backup MySQL database is a format with a delete table that allows the backup to overwrite an existing database without having to manually delete the existing database.
Mysqldump --add-drop-table -uusername-ppassword databasename > Backupfile.sql

Directly compress the MySQL database backup
Mysqldump-hhostname-uusername-ppassword databasename | gzip > backupfile.sql.gz

--Restore compressed MySQL database
Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

Backup MySQL database (some) table
Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

Backup multiple MySQL databases simultaneously
Mysqldump-hhostname-uusername-ppassword--databases databasename1 databasename2 databasename3 > Multibackupfile.sql

--databases,-B: For backing up multiple databases, if there is no option, Mysqldump takes the first name parameter as the database name, followed by the table name. With this option,

-Just back up the database structure
Mysqldump--no-data --databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

All databases on the backup server?
mysqldump -uroot-p123lqh-a > Allbackupfile.sql mysqldump -uroot-p123lqh-a-d> al Lbackupfile.sql backing up all databases but backing up the table structure only

, transfer the database to the new server

Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName

Database backup and Recovery