study-24: Mysql数据库的备份与恢复

最新推荐文章于 2021-01-19 20:11:52 发布

流浪猫之家

最新推荐文章于 2021-01-19 20:11:52 发布

阅读量347

点赞数

一，备份单个数据库练习多种参数使用

1，mysql数据库自带一个很好用的备份命令，就是mysqldump，他的基本使用如下：

1）语法：mysqldump -u 用户名 -p 数据库名 > 备份的文件名

备份：mysqldump -uroot -p123456 -S /data/3306/mysql.sock www> /opt/mysql_bak.sql

恢复：mysql -uroot -p123456 -S /data/3306/mysql.sock www< /opt/mysql_bak.sql

2）加-B参数进行备份，可以添加创建数据库的SQL语句（加-B还可以后面跟多个数据库名，进行多数据库的备份）：

> CREATE DATABASE /*!32312 IF NOT EXISTS*/ `www` /*!40100 DEFAULT CHARACTER SET utf8 */;
>
> USE `www`;

例如：

备份：mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B www> /opt/mysql_bak_B.sql

恢复：mysql -uroot -p123456 -S /data/3306/mysql.sock < /opt/mysql_bak_B.sql

3）指定gzip压缩命令（通过gunzip解压）

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B www | gzip > /opt/mysql_bak_B.sql.gz

2，mysqldump原理

1）通过生成SQL语句，进行数据的逻辑备份

2）指定-B参数，备份多个库

mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B www bbs blog> /opt/mysql_bak_B.sql

3，如何做分库备份（如果多个库备份在一个文件中，且其中一个库坏了，会比较麻烦，所以最好一个库备份一个文件）

实际是一个语句备份一个库；多写几个语句；

# 一条命令行对多个库进行备份

#通过shell脚本编程实现

http://edu.51cto.com/course/course_id-808.html

4，备份多个表（数据库表1 表2 表3 。。。。）

备份：mysqldump -uroot -p123456 -S /data/3306/mysql.sock www student test #www为库 student为表 test为表

5，如果做分表备份（如果多个表备份在一个文件中，且其中一个表坏了，会比较麻烦，所以最好一个表备份一个文件）

解决：所有库一次循环、每个库中二次循环备份单个表

6，只备份表结构

加参数：-d

7，只备份表数据

加参数：-t

8，不指定数据名，备份所有：

mysqldump -uroot -p123456 -A -B --events | gzip > /opt/sss.sql.gz

9，刷新blnlog（mysql开启log-bin开关）：

mysqldump -uroot -p123456 -A -B -F --events | gzip > /opt/sss.sql.gz

10，增加binlog日志文件名及对应的位置点（--master-data=1 ）：

mysqldump -uroot -p123456 --master-data=1 -S /data/3306/mysql.sock oldboy

#将来做增量恢复时，可以不做日志切割，不刷新binlog；因为文件中会显示：CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;

#已经告诉了日志文件在mysql-bin.000001；增量恢复从 107这个位置点开始，往下恢复就行了

11，锁表：-x

12，只读锁表：-l

13，--single-transaction 适合innodb事务数据库备份

innoDB表在备份时，通常启用选项 --single-transaction来保证备份的一致性，实际上他的工作原理是设定本次会话的隔离级别为：

REPEATABLE READ，以确保本次会话(dump)时，不会看到其他会话已经提交了的数据（http://imysql.cn/2008_10_24_deep_into_mysqldump_options）

14，工作中使用总结：

1）只有表库的情况

MyISAM表备份命令：

mysqldump -uroot -p123456 -A -B --master-data=1 --events -x | gzip > /opt/all.sql.gz #--master-data=2的区别

InnoDB表备份命令：工作中推荐使用的表引擎

mysqldump -uroot -p123456 -A -B --master-data=1 --events --single-transaction | gzip > /opt/all.sql.gz

2）有库有表，还有触发器、存储过程、视图等

MyISAM表：

mysqldump --user=root --all-databases --flush-privileges --lock-all-tables --master-data=1 --flush-logs --triggers --routines --events --hex-blob > xxx.sql

InnoDB表：

mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 --flush-logs --triggers --routines --events --hex-blob > xxx.sql

15，使用source命令恢复数据库

通常需要进入到myql命令行进行操作；也可以通过 -e 在linux命令行进行操作

16，mysqlbinlog 恢复

# 按照位置截取

mysqlbinlog mysqlbin.000020 -d www --start-position=365 --stop-position=456 -r pos.sql # -d www：指定只恢复 www 数据库的数据

# 按照时间截取

mysqlbinlog mysql-bin.000020 --start-datetime='2014-10-16 17:14:15' --stop-datetime='2014-10-16 17:15:15' -r time.sql