Export and Import MySQL under CentOS

Recently, due to the large number of migration servers, you need to export data from the MySQL database before importing it. When phpMyAdmin is used, it is often unable to export due to PHP and execution time restrictions (because the database is large ). Even if the data is exported, the data cannot be imported again due to upload restrictions or other reasons. Therefore, I decided to use the command line to export data in Linux.

Export and useMysqldumpCommand.MysqlCommand. The following describes how to export and import commands.

(1) Export

Command:Mysqldump-u username-p Database Name> file name

If the user name requires a password, enter a password check after executing this command. If the database user name does not require a password, do not add the "-p" parameter, which is the same during import. Note that the user name you enter must have operation permissions on the corresponding database; otherwise, data cannot be exported. Because it is used for system maintenance and export of all databases, we generally use root and other super user permissions.

For example, to export the database abc as a database file named db_abc. SQL to the current directory, enter the following command:

# Mysqldump-u root-p abc> db_abc. SQL

Enter the password of the root user to export the database.

(2) Import

Command:Mysql-u username-p database name <file name

Similar to the mysqldump command, each parameter has the same meaning as mysqldump.

For example, to import data from the/root/backup/db_abc. SQL file to the abc database, run the following command:

# Mysql-u root-p abc </root/backup/db_abc. SQL

Enter the password of the root user to import the file. It is worth mentioning that if the database does not exist, it cannot be imported. That is to say, to import data to a database, at least an empty database should be created first and then imported.

With these two commands, I believe that a large database can be easily exported. The phpMyAdmin timeout error will no longer occur, and the export file is too large to be imported in phpMyAdmin.