The method of importing and exporting MySQL data (mysqldump)

One, mysqldump backup method

It seems to be the simplest way to back up a table in addition to the MySQL client. Record it.

Shell> mysqldump [OPTIONS] database [tables]

If no data table is specified, the contents of the entire database are exported.

Get help

Shell> mysqldump--help

This allows you to get a list of mysqldump options.

Note: If you run mysqldump without the –quick or –opt option, mysqldump will load the entire result set into memory before exporting the results, which could be a problem if you are exporting a large database.

1. Export the entire database

Mysqldump-u user name-p database name > exported file name
Mysqldump-u user-p Database > Back.sql

2. Export a table

Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u user-p database.table> Back.sql
Mysqldump-u user–password= password database.table > Back.sql

3. Export a database structure

Mysqldump-u user-p-d–add-drop-table Database >d:back.sql
-D No data –add-drop-table add a drop table before each CREATE statement

4. Import Database

Common source Commands
Enter the MySQL database console,
such as Mysql-u root-p
Mysql>use Database

Then use the source command, followed by the script file (such as the. SQL used here)
Mysql>source D:back.sql

5. Or import the database
Mysql-u User-p Target_db_name < Backup-file.sql

Two, copying copy data backup shortcuts

Since this method has not been validated by official documents, we are tentatively called experiments.

Objective: To backup a MySQL database in the Hosta host Testa and revert to the HOSTB machine

Test environment:

Operating system: Winnt4.0,mysql3.22.34,phpmyadmin 2.1.0

Install MySQL database in hosta and build Testa database

HOSTB machine installs MySQL database, no testa database

Method steps:

Start phpMyAdmin View the list of databases in Hosta and HostB, and there are no Testa databases in HostB

Locate the MySQL installation directory in Hosta and locate the database directory data

In my experimental environment, this directory is

C:mysqldata

Find a subdirectory of the corresponding database name

C:mysqldatatesta

Paste copy into HostB's data directory, which is the same as the Hosta file in the HostB MySQL directory

Refresh HostB phpMyAdmin Look at the list of databases, we see that Testa has already appeared, and make a query to modify the operation is normal, backup restore success

Test conclusion: MySQL database can be saved by file, backup, restore as long as the corresponding file directory restore, no need to use other tools to back up.