Command for backing up MySQL database
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
Back up the MySQL database in the format of a table with deletionBacking up a MySQL database is in the format of a table with deletion, so that the backup can overwrite existing databases without the need to manually delete the original database.
mysqldump ---add-drop-table -uusername -ppassword databasename > backupfile.sql
Directly compress and back up the MySQL database
mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
Back up a MySQL database table
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
Back up multiple MySQL databases at the same time
mysqldump -hhostname -uusername -ppassword --databases databasename1 databasename2 databasename3 > multibackupfile.sql
Back up database structures only
mysqldump --no-data --databases databasename1 databasename2 databasename3 > structurebackupfile.sql
Back up all databases on the server
mysqldump --all-databases allbackupfile.sql
Command for restoring MySQL database
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
Restore a compressed MySQL database
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
Transfer database to new server
mysqldump \-uusername \-ppassword databasename \| mysql \--host=*.*.*.\* \-C databasename
Compressed backup
Back up and Compress With gzip:
mysqldump < mysqldump options> | gzip > outputfile.sql.gz
Restore from gzip backup:
gunzip < outputfile.sql.gz | mysql < mysql options>
Back up and Compress With bzip:
mysqldump < mysqldump options> | bzip2 > outputfile.sql.bz2
Recover from bzip2 backup:
bunzip2 < outputfile.sql.bz2 | mysql < mysql options>