Summary of Common commands for MySQL database backup and restoration. For more information about mysql, see
Summary of Common commands for MySQL database backup and restoration. For more information about mysql, see
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>