mysql备份与还原-mysqldump备份、mysql与source还原

以下都以在linux操作系统上的mysql为例

mysqldump备份

mysqldump实际就是将数据库中的数据转化为建库、建表和插入记录的sql语句

1.备份一个数据库 [或其中几个表],不指定表表示备份整个数据库,指定表表示备份指定的表

在系统命令下:mysqldump -u用户 -p密码 库 [表1  表2  表3   …]  > /opt/backname.sql

[root@oldboy opt]# mysqldump -uroot -poldboy oldboy student >/opt/studentbak.sql

备份一个库或其中的表时,备份语句中不包含建库语句,因此还原时需要指定数据库

如果备份整个库时加上-B参数,备份语句中会包含建库和切库语句如mysqldump -uroot -poldboy -B oldboy,-B参数不适用于备份表

2.备份多个数据库

在系统命令下:mysqldump -u用户 -p密码 --databases 库1  库2  库3  … > /opt/backname.sql

mysqldump -u用户 -p密码 --B 库1  库2  库3  … > /opt/backname.sql

[root@oldboy opt]# mysqldump -uroot -poldboy --databases oldboy oldboy_utf8 >/opt/multidbback.sql

3.备份所有数据库

在系统命令下:mysqldump -u用户 -p密码 --all-databases > /opt/backname.sql

[root@oldboy opt]# mysqldump -uroot -poldboy --all-databases >/opt/alldbback.sql

参考视频 http://edu.51cto.com/course/808.html

备份时可同时对备份的结果通过gzip压缩

mysqldump -uroot -poldboy oldboy student | gzip > /opt/studentbak.sql

mysql备份时常用参数

备份时加--compact参数,会在备份结果中省去一些无用信息,简化输出 ,一般在调试时使用,生产上不使用

[root@oldboy opt]# mysqldump -uroot -poldboy --compact oldboy > /opt/dbak.sql

备份时加-d参数,只备份表结构,即--no-data

[root@oldboy opt]# mysqldump -uroot -poldboy -d oldboy student > /opt/dbak.sql

备份时加-t参数,只备份数据

[root@oldboy opt]# mysqldump -uroot -poldboy -t oldboy student > /opt/tbak.sql

备份时加-B参数,备份多个数据库,即--databases

[root@oldboy opt]# mysqldump -uroot -poldboy -B --events |gzip > /opt/Abak.sql

备份时加-A参数,备份所有数据库,即--all-databases

[root@oldboy opt]# mysqldump -uroot -poldboy -A -B --events |gzip > /opt/Abak.sql

备份时加-F参数,切割bin log,即--flush-logs

[root@oldboy opt]# mysqldump -uroot -poldboy -B -F oldboy|gzip > /opt/Abak.sql

备份时加--maste-date=1(或2,1指定logbin日志及位置时不注释,2注释)参数,会在备份结果中指出从哪个binlog开始恢复

[root@oldboy opt]# mysqldump -uroot -poldboy --compact --master-data=1 oldboy > /opt/masterbak.sql

备份时加-x参数,锁表

备份时加-l参数,只读锁表

备份时加--events参数,备份数据库事件

备份时加--single-transaction参数适合innoDB事务数据库备份,原理是将本次会话的隔离级别设定为repeatable read,以确保本次会话dump时,不会看到其他会话已经提交了的数据

mysql备份还原

1.还原一个数据库,需指定数据库(如果备份时加了-B参数,则不需要指定数据库)

在linux系统下:mysql  -u用户 -p密码 库 < /opt/backname.sql

[root@oldboy opt]# mysql -uroot -poldboy oldboy  < /opt/testback.sql

2.还原多个数据库:mysql  -u用户 -p密码 < /opt/backname.sql

mysql -uroot -poldboy < /opt/twodbback.sql

source还原

在数据库下:source 备份路径及文件名

Mysql>source /opt/oldboyB.sql

如果备份文件中不包含建库语句,则需先通过use切换到对应的库,再source还原

生产场景备份参数使用

myisam:mysqldump -uroot -poldboy -A -B -x --master-data=1 --events|gzip > /opt/all.sql.gz(混合引擎备份,以myisam为主)

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

5.5.35以前默认是myisam引擎,5.5.35以后默认是innodb,现在推荐使用innodb方式备份

mysql的mysqldump备份合何时派上用场

1.迁移或者升级数据库时

2.增加从库的时候

3.人为的DDL、DML误操作语句,主库和从库都会误操作,因此需要备份

4.跨机房灾备,需要备份到异地

(因为硬件或特殊异常情况导致主库和从库宕机,主从可以互相切换,无需备份)

mysql增量恢复的必备条件:开启binlog日志,存在一份全备和全备之后到出现问题时间点的所有增量binlog

通过将修改变量sql_log_bin为OFF,可临时不记录binlog,在用binlog进行恢复时不将执行的操作记录到binlog

全量备份:备份数据库中所有的数据

增量备份:上次全量备份之后、到下次备份时间点之间更新的数据。对于mysql来说,binlog就是增量数据。

一般由于人为或者程序逻辑的方式在数据库执行了误操作

按天备份:

每天的24:00:00全量备份,每天的数据增量备份即binlog

优点:恢复时间短,维护成本低

缺点:占用空间大,占用资源多,经常锁表影响用户体验

按周备份

每周日24:00:00全量备份,每天的数据增量备份binlog

缺点:占用空间小,占用资源少,无需锁表,用户体验好

缺点:恢复时间长,维护麻烦,成本高

企业场景:

1.中小公司,全量一般是每天一次,在业务流量低估时执行全备,备份时锁会表

2.单台数据库,用rsync(配合定时任务,或者inotify,主从辅助),把所有binog备份到远程服务器,尽量做主从复制

3.大公司周备,本周到下次周备之前都是增量备份,节省备份时间,减小备份压力,但是增量binlog文件副本太多,还原麻烦

4.一般一主多从,会有一个从库做备份,延迟同步

上一篇:mysql备份和还原命令


下一篇:java break语句的三种用法