Common commands for MySQL database backup and restoration _ MySQL

Common commands for MySQL database backup and restoration: bitsCN.com

　　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 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 
 
 

Thanks, Ivan

BitsCN.com