How to export and import MYSQL Databases

For MySQL Database Import, there are two common methods: 1) Export the database SQL script and import it again; 2) directly copy the database directory and files; the former directly imports the database using commands, the latter can be copied directly on the server.

Method 1: SQL script

The procedure is as follows:
2.1. Export an SQL script
On the original database server, you can use the phpMyAdmin tool or the mysqldump command (the mysqldump command is located in the mysql/bin/directory) command line to export the SQL script.
2.1.1 phpMyAdmin
Select "structure" and "data" to export. Do not add the "Drop DATABASE" and "Drop TABLE" options.
Select the "Save As file" option. If there is more data, select the "gzipped" option.
Save the exported SQL file.

2.1.2 use mysqldump command line

Command Format

Mysqldump-u username-p Database Name> database name. SQL

Example:

Mysqldump-uroot-p abc> abc. SQL
(Export the database abc to the abc. SQL file)

Enter the password of the Database User Name When prompted.

2.2. Create an empty Database

Create a database on the master page/control panel. Assume that the database name is abc and the database full user is abc_f.

2.3. Import and execute SQL scripts

You can use either phpMyAdmin or mysql command line.

MySQL Database Export
Run the following command to export data.

1./usr/local/mysql/bin/mysqldump-u root-p123456 zhumaohai | gzip>/home/backup/zhumaohai. SQL .gz

If you export all data, the statement is:

1./usr/local/mysql/bin/mysqldump-u root-p123456 -- all-databases | gzip>/home/backup/all. SQL .gz

Where: root is the Database User Name
123456 is the password of the corresponding root database
Zhumaohai is the name of the database to be exported.

/Home/backup/zhumaohai. SQL is the path for SQL export of database files.

MySQL Database Import
Run the following command to import data.

1. gunzip

Import all databases:

1. gunzip

2.3.1 use phpMyAdmin

From the control panel, select the created empty database and click "manage" to go to the management tools page.
In the "SQL" menu, browse and select the exported SQL file, and click "run" to load and execute the file.

Note: phpMyAdmin has a limit on the size of the uploaded file, and php itself has a limit on the size of the uploaded file. If the original SQL File
Relatively large, you can use gzip to compress it. For text files such as SQL files, you can obtain a compression rate of or higher.
Gzip usage:
# Gzip xxxxx. SQL
Get
Xxxxx. SQL .gz file.

2.3.2 use mysql Command Line
Command Format
Mysql-u username-p database name <database name. SQL
Example:
Mysql-uabc_f-p abc <abc. SQL
(Import database abc from abc. SQL file)

Enter the password of the Database User Name When prompted.
Method 2: Enter mysql, create a database, select a database, and run the following code: d: 112121. SQL is the database directory.
Mysql> source d: 2017121. SQL

3 method 2 Direct Copy
If the database is large, you can use the direct copy method. However, different versions and operating systems may be incompatible with each other, so use it with caution.
3.1 prepare the original file
Package A file with tar

3.2 Create an empty Database

3.3 decompress
Decompress the package in the temporary directory, for example:
Cd/tmp
Tar zxf mydb.tar.gz

3.4 copy
Copy the extracted database file to the relevant directory.
Cd mydb/
Cp */var/lib/mysql/mydb/

For FreeBSD:
Cp */var/db/mysql/mydb/

3.5 permission settings
Change the owner of the copied files to mysql: mysql and the permission to 660.
Chown mysql: mysql/var/lib/mysql/mydb /*
Chmod 660/var/lib/mysql/mydb /*