Database import and export under Linux

Most recently, because of the number of migration servers, you need to export the data from the MySQL database and import it frequently. With phpMyAdmin, due to the limitations of PHP and execution time, there are often problems that cannot be exported (because the database is larger). And even if it is exported, it is likely because of the upload restrictions and other reasons can not be imported again. So I decided to try to export it under Linux in the form of a command line.

The export uses the mysqldump command, and the import uses the mysql command. Let's explain the detailed usage of the export and import commands separately.

(i) Export

Command:mysqldump-u user name-p database name > file name

If the user name requires a password, you need to enter the password check once this command executes, and if the database user name does not require a password, do not add the "-P" parameter to the same time as the import. Note The user name you enter needs to have permission to operate the corresponding database, otherwise you cannot export the data. As the system maintenance and the export of all databases, we generally use root and other super-user rights.

For example, to export the ABC database as a database file named Db_abc.sql to the current directory, enter the following command:

#mysqldump-u root-p ABC > Db_abc.sql

Next, enter the root user's password to export the database.

(ii) Import

Command:mysql-u user name-p database name < file Name

As with the mysqldump command, the meanings of each parameter are the same as the mysqldump.

For example, to import data from the/root/backup/db_abc.sql file into the ABC database, use the following command:

#mysql-u root-p ABC </root/backup/db_abc.sql

Then enter the root user's password to import. It is worth mentioning that if the database itself does not exist, it cannot be imported. In other words, to import to a database, at least you should establish an empty database before importing.

With these two instructions, I believe that the large database, can be handy to export. There is no longer a phpMyAdmin timeout error, and there is an issue where the exported file is too large to be imported in phpMyAdmin.

(c) Supplement the general usage of the import

Command: Mysql-uroot-p

Enter the password in the database

show databases;

You can find the database currently in the database, if not, you need to create

CREATE DATABASE * * *;

Use * * *;

Source/home/***.sql (indicates the specific directory of the database to be imported)

That's it.

Database import and export under Linux