Backup and restore of MySQL database and table

Backup

Export Database all table structure

mysqldump -uroot -ppassword -d dbname > db.sql

Export a table structure for a database

mysqldump -uroot -ppassword -d dbname tablename > db.sql

Export database all table structure and data

mysqldump -uroot -ppassword dbname > db.sql

Export a table structure and data for a database

mysqldump -uroot -ppassword dbname tablename > db.sql

Export part of the data

--where="条件"mysqldump -uroot -ppassword dbname tablename -w "条件" > db.sql

-W or –where, the condition must be surrounded by double quotes, single quotation mark not.

Note
Exporting data does not require-d

The following error may occur

Mysqldump:got error 1044:access denied for use ' zndw ' @ ' localhost ' to database ' scilibrary ' when doing LOCK TABLES

This error occurs because the user has no lock tables permissions.

Solutions
(1) Granting permission to the user lock tables
(2) Add --skip-lock-tables , as

mysqldump -uroot -ppassword dbname --skip-lock-tables > db.sql

Import

source xxx.sql

(1) A semicolon cannot be followed by a statement;
(2) User must have insert and other permissions

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Backup and restore of MySQL database and table