LNMP the MySQL database using command export

As a site or server operation personnel, the site backup and restore operations must be skilled. MySQL database Export and import operations are essential, for the general user, may use more than phpmyadmin such a visual interface, but this interface operation in the database is relatively large case, often error.

If you are familiar with the commands under Linux, and you are using a VPS or cloud server, you can use MySQL-related commands to export and import the database, very convenient and efficient!

Export Database

Export database as db_wp.sql.gz file:

Mysqldump-u database user name-p database password--add-drop-table--complete-insert database name | gzip > db_wp.sql.gz
Please note that according to their actual situation, modify the above "database user name", "Database Password", "database name" for the specific value, in general, "database user name" should use the highest authority root, the database password should be the root user password

Import Database

Because we have just exported the database is GZ compressed, so before importing we need to unpack:

Gzip-d db_wp.sql.gz

The extracted files are db_wp.sql and then imported to the database:

Mysql-u database user name-p database Password database name < Db_wp.sql--default-character-set=utf8
MySQL error server has gone away

In the course of operation, may appear the MySQL server has gone away error, this is the database is too big, the cache is insufficient or the operation timeout causes, may modify under the MySQL configuration to solve:

1, open the configuration file (here is a demonstration of the Army Brother Lnmp under the configuration file, if your path is not this, find yourself)

Vi/etc/my.cnf

2, find Wait_timeout, interactive_timeout two variables, set the value of

wait_timeout=2880000
Interactive_timeout = 2880000

If you do not have these two parameters, you can add them directly

3, and then find Max_allowed_packet, increase the value of this variable, such as 100M (you can also set the size you want)

Max_allowed_packet = 100M

The function of the Max_allowed_packet parameter is to control the maximum length of its communication buffer.

Of course, other parameters can be adjusted according to their own needs: