Summary of common commands for MySQL database backup and restore

　

Summary of COMMON commands for MySQL database backup and restore , learn MySQL friends can refer to the following:

commands to back up the MySQL database

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

Backing up the MySQL database for a format with a delete table the mysql database is formatted with a delete table, allowing the backup to overwrite the existing database without having to manually delete the existing database.

　　Mysqldump---add-drop-table-uusername-ppassword databasename > Backupfile.sql

Compress the MySQL database directly backup

　　Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

Back up a MySQL database ( some ) 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

Backing up the database structure only

　　Mysqldump--no-data--databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

Back up all databases on the server

　　Mysqldump--all-databases Allbackupfile.sql

commands to restore MySQL Database

　　Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

Restore a compacted MySQL Database

　　Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

To transfer a database to a new server

　　Mysqldump \-uusername \-ppassword databasename \| MySQL \--host=*.*.*.\* \-c databasename

Compress backup

Backup and compress with gzip :

　　mysqldump < mysqldump options> | gzip > outputfile.sql.gz

Restore from gzip Backup:

　　Gunzip < outputfile.sql.gz | MySQL < MySQL options>

Backup and compress with bzip :

　　mysqldump < mysqldump options> | bzip2 > outputfile.sql.bz2

Restore from bzip2 backup :

　　Bunzip2 < OUTPUTFILE.SQL.BZ2 | MySQL < MySQL options>

This article original www.dbfen.com reprint please indicate the source

This article is from the "Big Meatball" blog, please make sure to keep this source http://12478147.blog.51cto.com/9663367/1589560

Summary of common commands for MySQL database backup and restore