How to Use the command line to export data to the MySQL database in LNMP: lnmpmysql

How to Use the command line to export data to the MySQL database in LNMP: lnmpmysql

As site or server O & M personnel, website backup and restoration operations must be skillful. MySQL database export and import operations are essential. For general users, you may use visual operation interfaces such as phpMyAdmin, however, this kind of interface operations often encounter errors when the database is large.

If you are familiar with Linux commands and use vps or ECS, you can use MySQL commands to export and import databases, which is very convenient and efficient!

Export Database

Export the database to the db_wp. SQL .gz file:

Mysqldump-u database username-p Database Password -- add-drop-table -- complete-insert database name | gzip> db_wp. SQL .gz

Please note that, based on your actual situation, modify the above "Database User Name", "Database Password", and "Database Name" as specific values. Generally, the "database username" should use the root with the highest permission. The database password should be the password of the root user.

Import Database

Because the exported database is gz compressed, We need to decompress it before import:

gzip -d db_wp.sql.gz

The extracted file is db_wp. SQL and then imported to the database:

Mysql-u database username-p Database Password Database Name <db_wp. SQL -- default-character-set = utf8

Mysql error server has gone away

During the operation, the mysql server has gone away error may occur, which is caused by a large database, insufficient cache, or operation timeout. You can modify the mysql configuration to solve the problem:

1. Open the configuration file (this shows the configuration file under the lnmp of June, if your path is not this one, find it by yourself)

vi /etc/my.cnf

2. Find the wait_timeout and interactive_timeout variables and set the value

wait_timeout=2880000interactive_timeout = 2880000

If you do not have these two parameters, simply add them.

3. Find max_allowed_packet and add the value of this variable, such as 100 M (you can also set the size you need)

max_allowed_packet = 100M

The max_allowed_packet parameter is used to control the maximum length of the Communication buffer.

Of course, other parameters can be adjusted as needed:

4. Restart the mysql service.