Backup and restore operation of MySQL database
This article mainly introduces "the operation of backup and restore of MySQL database". In the daily operation, I believe that many people have doubts about the operation of backup and restore of MySQL database. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "backup and restore operation of MySQL database". Next, please follow the editor to study!
1. Common operations:
Back up the entire database
Format:
Mysqldump-h hostname-P port-u user name-p password (- database) database name > file name. Sql
Mysqldump-h {hostname}-P {port}-u {username}-p {password} {databasename} > {backupfile.sql}
For example:
The code is as follows:
one
Mysqldump-hlocalhost-P3306-uzhuchao-p123456 db_test > backfile1.sql
Back up the MySQL database in a format with deleted tables
The backup MySQL database is in the format with deleted tables, which allows the backup to overwrite the existing database without manually deleting the existing database.
Format: mysqldump-- add-drop-table-u {username}-p {password} {databasename} > {backfile.sql}
For example:
The code is as follows:
one
Mysqldump-- add-drop-table-uzhuchao-p123456 db_test > backfile2.sql
Compress backup of MySQL database directly
Format: mysqldump-h {hostname}-u {username}-p {password} {databasename} | gzip > {backfile.sql.gz}
For example:
The code is as follows:
one
Mysqldump-hlocalhost-uzhuchao-p123456 db_test1 | gzip > backfile3.sql.gz
Back up some table (s) in the MySQL database
Format: mysqldump-h hostname-P port-u username-p password (- tables |-quick) database name table name 1 (table name 2 …) > file name. Sql (default in parentheses).
Mysqldump-h {hostname} (- P {port})-u {user}-p {password} (- tables |-quick) {databasename} {table1} {table2} > {backfile.sql}
For example:
The code is as follows:
one
two
three
four
Mysqldump-hlocalhost-uzhuchao-p123456 db_test tbl_test > backfile4-1.sql
Mysqldump-hlocalhost-P3306-uzhuchao-p123456 db_test tbl_test > backfile4-2.sql
Mysqldump-hlocalhost-P3306-uzhuchao-p123456-- quick db_test tbl_test > backfile4-3.sql
Mysqldump-hlocalhost-P3306-uzhuchao-p123456-- tables db_test tbl_test1 tbl_test2 > backfile4-4.sql
Backup multiple MySQL databases at the same time
Format: mysqldump-h {hostname} (- P {port})-u {username}-p {password}-databases {databasename1} {databasename2} {databasename3} > multibackfile.sql
For example:
The code is as follows:
one
Mysqldump-hlocalhost-uzhuchao-p123456-databases db_test1 db_test2 db_test3 > multibackfile.sql
Just back up the database structure
Format: mysqldump-no-data-databases {databasename1} {databasename2} > {structurebackfile.sql}
For example:
The code is as follows:
one
Mysqldump-no-data-databases db_test1 db_test2 > structurebackfile.sql
Back up all databases on the server
Format: mysqldump-all-databases > allbackupfile.sql
=
Commands for restoring MySQL databases
Format: mysql-h {hostname}-u {username}-p {password} {databasename} < {backfile.sql}
For example:
The code is as follows:
one
Mysql-hlocalhost-uroot-p123456 db_test4 < back_file1.sql
Restore a compressed MySQL database
Format: gunzip < {backfile.sql.gz} | mysql-u {username}-p {password} {databasename}
For example:
The code is as follows:
one
Gunzip < backfile.sql.gz | mysql-uzhuchao-p123456 db_test5
Transfer the database to a new server
Mysqldump-u {username}-p {password} {databasename} | mysql-host=*.*-C {databasename}
2. Others:
1. If the port is 3306 by default, the entry-P {port number} can be omitted.
2. The contents in {} in the command line format are all variables.
Hostname: {hostname}
Port: {port} (default is 3306, but default is available)
User name: {user} {username} (such as root)
Secret code: {password}
Database name: {databasename}
Table name: {table} {table1} {table2}
File name: {backfile.sql}
At this point, the study of "MySQL database backup and restore operation" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!
