备份和恢复MYSQL数据库

This method works regardless of the size of your database. You must have SSH access to your server. On (gs) plans you can invoke SSH access from within your Control Panel. On (dv) plans you must enable Shell Access through Plesk.

这个方法推荐给具有大数据库的家伙，而且你必须有SSH权限，

备份BACKUP:

1) Log into your server via SSH and cd into a directory where your user has write access. On (ss) plans
you would do something like this:

cd /var/www/html/

2) Enter the following command:

mysqldump –add-drop-table -u Username -p dbname > dbname.sql

- omit the ‘–add-drop-table’ argument if you’ll want to merge this backup with an existing database
upon restoral
- Where ‘Username’ is replaced by the mySQL username. On (ss) plans this user is the same as the
administrative FTP user. On (as) plans this user can be found by logging into Plesk, clicking
on the domain and going to databases and clicking on the database to be backed up.
- Replace ‘dbname’ with the name of the database to be backed up.
- Replace dbname.sql with what you’d like to name the backup.

3) Enter your mySQL password at the prompt. If you don’t know it you can reset it in your webcontrol or Plesk
admin panel 要输入正确的数据库密码，要是忘了就在控制面板里重设。
- If you get an error that looks like this:

ERROR 1045: Access denied for user: ‘Username@localhost’ (Using password: YES)

you have entered an incorrect password, please retype it carefully or reset it to something else via
the webcontrol panel or Plesk administrator

- On (ss) plans if you have reset the password in the webcontrol panel and are sure you’ve entered it correctly
on the command line this might be a permissions problem. Try renaming your database to something else in the
webcontrol panel and then back to the original, this often fixes permissions problems.

4) Use FTP to download the file to your backup location, probably your local computer.
- Don’t forget to delete the backup from your public html directory after you’ve found a safe place for it.
You don’t want to leave your backup lying around where anyone with a web browser can download a copy.

恢复RESTORE:

1) Use FTP to upload the file to your server, your public html directory will work for now
- Don’t forget to delete the backup from your public html directory after you’ve done the database restoral.
You don’t want to leave your backup lying around where anyone with a web browser can download a copy.
用FTP工具把数据库文件上传到你的public_html目录，别忘了删除这个备份，别人是可以下载到的。

2) Log into your server via SSH and cd to the directory where you’ve uploaded the file. On the (ss) plan if you
uploaded the backup into your public html directory you would use the command:

cd /var/www/html/

3) Enter the following command:

mysql -u Username -p dbname <>

键入这行命令

- Where ‘Username’ is replaced by the mySQL username. Username处是你数据库管理员的名称，同你的FTP登录名。
On (ss) plans this user is the same as the
administrative FTP user. On (as) plans this user can be found by logging into Plesk, clicking
on the domain and going to databases and clicking on the database to be restored.
- Replace ‘dbname’ with the name of the database to be restored. 将被恢复的数据库名称
- Replace dbname.sql with the name of the backup.dbname.sql是备份数据库名称

If you have a zipped backup of your database you can use this line instead:如果是ZIP形式的，就用下面的命令。

gunzip <>

- Where ‘Username’ is replaced by the mySQL username. On (ss) plans this user is the same as the
administrative FTP user. On (as) plans this user can be found by logging into Plesk, clicking
on the domain and going to databases and clicking on the database to be restored.
- Replace ‘dbname’ with the name of the database to be restored.
- Replace dbname.gz with the name of the backup.

4) Enter your mySQL password at the prompt. If you don’t know it you can reset it in your webcontrol or Plesk
admin panel 然后输入密码。如果对了就大功告成，如果错了就出这个信息了。
- If you get an error that looks like this:

ERROR 1045: Access denied for user: ‘Username@localhost’ (Using password: YES)

you have entered an incorrect password, please retype it carefully or reset it to something else via
the webcontrol panel or Plesk administrator

这里只说text模式，binary模式先不提。

1.mysqldump备份database或table：

$mysqldump database [table1 table2] > dump.sql

比如备份数据库cm2：

$mysqldump cm2 >cm2.sql

或者只是cm2中的mail,user表：

$mysqldump cm2 mail usrer >cm2_mail_user.sql

mysqldump参数介绍

–databases： mysqldump后跟的所有都是database，而不是table。这个在需要备份多个数据库的时候有效。

–all-databases：备份所有的数据库的所有表到一个文件

另外这使用两个参数会自动在备份文件里添加判断database是否存在，并create database的命令。

而在默认的情况下没有此功能，如果原来的database没了，需要先创建database，才能导入。

2.使用mysql恢复数据库或表：

$mysql database <>

比如恢复之前备份的数据库cm2

$mysql cm2 <>

如果没有使用--databases参数备份，那么必须指定数据库，并且该数据库存在。

如果使用了--databases参数，则可以不指定数据库，并且数据库可不存在。

所以一般情况备份database的情况下加上这个参数会比较好。

3.使用mysqldump和mysql拷贝database或者table

$mysqldump database [table] | mysql [-h other.host.com] database1

这里copy的前提是database1必须存在。