Common commands for MySQL database backup and Restoration

The following articles mainly DESCRIBE commands frequently used for backup and restoration of MySQL databases, as well as commands for backing up MySQL databases. The information I saw on the relevant website two days ago is quite good, let's share it with you. I hope you will have some gains.

 
 

  
  
mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql 
 
 

Back up the MySQL database in the format of a table with deletion

Backing 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  
 
 

The above content describes the Common commands used for MySQL database backup and restoration. I hope it will help you in this regard.