Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.