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

This article mainly introduces "the method of importing and exporting data in MySQL database". In the daily operation, I believe that many people have doubts about the method of importing and exporting data in 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 about "the method of importing and exporting data in MySQL database". Next, please follow the editor to study!

1. Overview

There are two ways to import a MySQL database:

1) Export the database SQL script before importing it

2) copy database directories and files directly.

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

Therefore, it is generally recommended to import in the form of SQL script. Two methods are described below.

two。 Method 1 SQL script form

The 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.

2.1.1 use the phpMyAdmin tool

In the export options, select Export structure and data, and do not add the DROP DATABASE and DROP TABLE options.

Select the Save as File option, or if there is more data, you can select the gzipped option.

Save the exported SQL file.

2.1.2 use the 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 through the main control interface / control panel. Suppose the database is named abc and the full user of 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 use the phpMyAdmin tool

From the control panel, select the empty database you created, and click manage to go to the management tools page.

In the "SQL" menu, browse and select the SQL file you just exported, and click execute to upload and execute.

Note: phpMyAdmin has a limit on the size of the uploaded file, and php itself has a limit on the uploaded file size, if the original sql file

Larger, you can first use gzip to compress it, for text files such as sql files, you can get a compression ratio of 1:5 or higher.

How to use gzip:

# gzip xxxxx.sql

Get

Xxxxx.sql.gz file.

2.3.2 use the mysql command line

Command format

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

Example:

Mysql-uabc_f-p abc < abc.sql

(export database abc to abc.sql file)

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

3 method 2 direct copy

If the database is relatively large, you can consider using the method of direct copy, but different versions and operating systems may not be compatible and should be used with caution.

3.1 prepare the original file

Package it into a file with tar

3.2 create an empty database

3.3 decompression

Extract it in a temporary directory, such as:

Cd / tmp

Tar zxf mydb.tar.gz

3.4 copy

Copy the extracted database files 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 file to mysql:mysql, and the permission to 660

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

Chmod 660 / var/lib/mysql/mydb/*

At this point, the study of "the method of importing and exporting data in MySQL database" 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.