How to import and export a MySQL database

1. Overview

There are two ways to import a MySQL database:

1 lead out the database SQL script, and then import;

2 directly copy the database directory and files.

In the case of different operating systems or versions of MySQL, the method of directly copying files may be incompatible.

Therefore, it is generally recommended to import in SQL script form. Here are two ways to describe each.

2. Method one SQL script form

Action steps are as follows:

2.1. Export SQL Script

On the original database server, you can export the SQL script using the phpMyAdmin tool, or the mysqldump command line (mysqldump command in the mysql/bin/directory).

2.1.1 with phpMyAdmin Tools

Export options, select export structure and data, and do not add drop DATABASE and drop TABLE options.

Select the Save As File option to select the gzipped option if the data is more numerous.

Save the exported SQL file.

2.1.2 with mysqldump command line

Command format

Mysqldump-u user name-p database name > database name. sql

Example:

MYSQLDUMP-UROOT-P ABC > Abc.sql

(Export database ABC to abc.sql file)

When prompted for a password, enter the password for the database user name.

2.2. Create an empty database

Create a database from the Master interface/Control Panel. Suppose the database is named ABC and the database is Abc_f.

2.3. Import SQL script into execution

There are also two methods, one using the phpMyAdmin (MySQL database management) tool, or the MySQL command line.

2.3.1 with phpMyAdmin Tools

From the Control Panel, choose to create an empty database, click "Manage", and go to the admin tools page.

On the SQL menu, browse to select the SQL file you just exported and click "Execute" to download and execute.

Note: phpMyAdmin has limits on the size of uploaded files, and PHP itself has limits on the size of uploaded files, if the original SQL file

Relatively large, you can first use gzip to compress it, for SQL files such as text files, you can get 1:5 or higher compression rate.

Gzip Use method:

# gzip Xxxxx.sql

Get

xxxxx.sql.gz files.

2.3.2 with mysql command line

Command format

Mysql-u user name-p database name < database name. sql

Example:

MYSQL-UABC_F-P ABC < Abc.sql

(Import database ABC from Abc.sql file)

When prompted for a password, enter the password for the database user name.

Method 2 into the MySQL, set up a database, select the database, into the following code, D:112121.SQL for the database directory.

Mysql>source D:112121.sql

3 Method Two direct copies

If the database is large, you can consider using a direct copy method, but the different versions and operating systems may be incompatible between the use of caution.

3.1 Preparation of original documents

Packaged in tar as a file

3.2 Creating an empty database

3.3 Decompression

Unzip in the temp directory, such as:

Cd/tmp

Tar zxf mydb.tar.gz

3.4 copies

Copy the extracted database files to the related directory

CD mydb/

CP */var/lib/mysql/mydb/

For FreeBSD:

CP */var/db/mysql/mydb/

3.5 Permission settings

Change the owner of the document copying the past to Mysql:mysql, with the permission changed to 660

Chown mysql:mysql/var/lib/mysql/mydb/*

chmod 660/var/lib/mysql/mydb/*