MySQL入门篇-mysqldump备份和恢复

1，备份命令：mysqldump

　　MySQL数据库自带的备份命令。是逻辑备份，导出的是SQL语句。也就是把数据从MySQL库中以逻辑的SQL语句的形式直接输出或将输出导入备份的文件的过程。

2，设置使用mysqldump的用户权限

1）mysqldump备份数据需要的权限

对于table来说mysqldump最少要有select权限

对于view来说mysqldump最少要有show view权限

对于trrigger来说mysqldump要有trriger权限

如果要产生一份一致的备份mysqldump要有lock tables权限

# 登陆数据库后执行授权命令
grant select,reload,replication client,show view,lock tables,trigger on *.* to 'DBbackup'@'192.168.1.%' identified by 'DBbackup';
# 查看授权后的信息并刷新
show grants for 'DBbackup'@'192.168.1.%';
flush privileges;

3，使用mysqldump备份和恢复

# 备份操作
mysqldump -uDBbackup -pDBbackup -B mytest > /mnt/mytest_bak_B.sql 

说明：加-B参数后，导出的数据文件中已存在创建库和使用库的语句；在恢复过程中不需要手动创建库，可以直接还原恢复。

# 恢复操作
# 删除mytest库
mysql -uDBbackup -pDBbackup -e "drop database mytest;"
# 恢复数据
mysql -uDBbackup -pDBbackup < /mnt/mytest_bak_B.sql

4，指定压缩命令来压缩备份文件

# 备份操作
mysqldump -uDBbackup -pDBbackup -B mytest | gzip > /mnt/mytest_bak_B.sql.gz

说明：mysqldump导出的文件是文本文件，压缩效率很高。

5，备份多个数据库

# 备份操作
mysqldump -uDBbackup -pDBbackup -B mytest wiki | gzip > /mnt/mytest_and_wiki_bak_B.sql.gz

说明：通过-B参数指定相关数据库，每个数据库名之前用空格分隔。当使用-B参数后，将所有数据库全部列全，则等同于-A参数。

6，分库备份

　　执行一个备份语句就备份一个库，分库备份就是执行多条相同的备份语句，只是备份的库名和备份文件名不同。可以通过shell脚本自动生成并执行相应的操作，也可以把所有单个备份语句写在同一个shell脚本中，通过cron定时任务来备份。

　　分库备份的意义当所有库都备份成一个备份文件时，恢复其中一个数据比较麻烦。所以分库备份，利于恢复。

for dbname in `mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|infor|perfor"`
do
    mysqldump -uroot -p123456 --events -B $dbname | gzip > /mnt/${dbname}_bak_B.sql.gz
done

7，其他　

# 导出整个数据库（包含数据库中的数据）
mysqldump -u username -p dbname > dbname.sql

# 导出数据库结构（不包含数据）
mysqldump -u username -p -d dbname > dbname.sql

# 导出数据库中的某张数据表（包含数据）
mysqldump -u username -p dbname tablename > tablename.sql

# 导出数据库中的某张数据表的表结构（不含数据）
mysqldump -u username -p -d dbname tablename > tablename.sql

 

数据备份策略：

完全备份：备份所有数据

增量备份：备份上次备份后，所有新产生的数据

差异备份：备份完全备份后，所有新产生的数据

完全备份（mysqldump）

# mkdir /mydata

# mysqldump  -uroot  -p123456  -A  >  /mydata/alldb.sql     (备份所有)

# mysqldump  -uroot  -p123456  db2 >  /mydata/db2.sql   （备份db2库）

# mysqldump  -uroot  -p123456  db2   t2  >  /mydata/db2_t2.sql   （备份db2库的t2表）

# mysqldump  -uroot  -p123456  -B  db3  db2 >  /mydata/twodb.sql  (备份 db3，db2库)

完全恢复数据（删除对应的备份数据，然后使用备份文件恢复数据）

# mysqldump  -uroot  -p123456  db4  < /mydata/db4_t2.sql       （恢复t2表）