MySQL Database Import and Export

The Basic Database Import/Export commands are mysqldump and source.

In Linux, you can directly use the command line operation. In Windows, there are two methods. One is to use the command line, and the other is to use phpMyAdmin.
The phpMyAdmin tool is easy to export and import. You do not need to create a database to restore it to the original database, but the size limit of the file to be imported cannot exceed 2 MB.
Let's talk about how fast mysqldump and source commands are to be used. But to import the exported files, you must first create a database (This database can be named casually) this step is also required for importing files exported from mysqldump using phpMyAdmin after entering the database.

There are two ways to export a MySQL database:
Method 1 use tools to export scripts
1. Export with navicat
Right-click Database-convert to SQL, and select the exported file name in the pop-up dialog box.
2. Use 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.
Method 2 use the mysqldump command line
Command Format
1. Export the entire database
Mysqldump-u username-P Database Name> exported file name
Mysqldump-u wcnc-P smgp_rj_wcnc> wcnc. SQL
2. Export a table
Mysqldump-u user name-P database name Table Name> exported file name
Mysqldump-u wcnc-P smgp_rj_wcnc users> wcnc_users. SQL
3. Export a database structure
Mysqldump-u wcnc-p-d -- add-drop-Table smgp_apps_wcnc> D: wcnc_db. SQL
-D no data -- add-drop-table adds a drop table before each create statement.
Enter the password of the Database User Name When prompted.
Memo: mysqldump can be directly used in Linux. Sometimes it needs to be used in MySQL/bin in windows, because sometimes commands like appserv can not be used directly because these commands are not in the backup files in the environment variable directory and exported using mysqldump must be created before the database can be imported.

There are two ways to import a MySQL database:
1) run the import script;
2) directly copy database directories and files.
In different operating systems or MySQL versions, the method of directly copying files may be incompatible. Therefore, we recommend that you use SQL scripts to import data.

Method 1 import the script program:
1. 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. Import and execute SQL scripts
There are two methods: navicat, phpMyAdmin (MySQL database management), or MySQL command line.
2.1 use the navicat tool to directly create a database, right-click the data "Run batch task file", select the exported SQL script, and execute the script.
2.2 The procedure for using phpMyAdmin to import data is as follows:
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.
Enter the password of the Database User Name When prompted.
2.3 use MySQL Command Line
Frequently Used source commands go to the MySQL Database Console, such as MySQL-u root-P
Mysql> Use Database and then use the source command. The following parameter is a script file (for example,. SQL used here)
Mysql> source D: wcnc_db. SQL
Enter the password of the Database User Name When prompted.
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 /*