当前位置: > 数据库 > MySQL >

MySQL增量备份及恢复

时间:2018-05-30 13:39来源:linux.it.net.cn 作者:IT
MySQL自带备份命令->mysqldump



一 语法格式及命令参数
mysqldump -u 用户名-p 数据库名> 备份的文件名字



例子:mysqldump-uroot -p123456 hadoop > /opt/data/mysql_hadoop_

bak.sql

备份过程:它会将将老数据删掉,然后插入或者删除才释放锁他会将将老数据删掉,然后插入或者删除才释放锁





-A或者--all-databases:备份所有数据库

mysqldump-uroot -p123456 -A -B -E|gzip > /opt/bak/all.sql.gz



-B:导出脚本会创建库并加上use 数据库的过程;并且-B 还可以指定多个库

默认是不会use 你要导出的数据库,否则我们在恢复的时候还需要手动创建库,而且还不了解以前参数

mysqldump-uroot -p123456 -B hadoop > /opt/data/mysql_hadoop_bak.sql

备份多个数据库:

mysqldump-uroot -p123456 -B hadoop nicky|gzip > /opt/data/hadoop_nicky_gzip_bak.sql



--compact:可以优化输出内容,让容量更少,只是适合调试

mysqldump-uroot -p123456 --compact -B hadoop >

/opt/data/mysql_hadoop_bak.sql



-Y:备份所有表

-y:不备份表空间

-E--events:dumpevents

-F或者--flush-logs :dump之前刷新bin log

mysqldump-uroot -p123456 -A -B -E -F|gzip > /opt/bak/all.sql.gz

--flush-privileges:dump之后刷新权限表

-h: 主机名

-P:port

-p:密码

-R:dump存储过程

-S指定socket

-t:指示导出数据

--ignore-table=name不dump指定的表

-x,--lock-all-tables

-d:只是dump结构,不dump数据

--master-data=1可以保证不刷新binlog,就是全备的那个点,自动找到,自动找到bin log位置

mysqldump-uroot -p123456 --master-data=1 -B hadoop >/opt/data/hadoop_master_date_bak.sql





--default-charactor-set:指定字符集的mysqldump

mysqldump-uroot -p123456 --default-charactor-set=utf-8 hadoop >

/opt/data/mysql_hadoop_bak.sql



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

InnoDB表在备份的时候,经常启用选项:--single-transaction保证备份的一致性,实际上他的工作原理是设定本次会话隔离级别为

REPEATABLEREAD(可重复读),以确保本次会话不会看到其他会话已经提交了的数据





Linuxegrep命令用于在文件内查找指定的字符串。



数据库名|压缩格式:支持压缩

mysqldump-uroot -p123456 -B hadoop|gzip > /opt/data/mysql_hadoop_bak_gzip.sql.gz

此时sql就是一个二进制文件



恢复:

先删掉表student

mysql-uroot -p123456 -e "use hadoop;drop table student;"

恢复必须指定库



>>结论:

1导出数据最好使用-B参数

2可以对备份数据进行gzip压缩



二 mysqldump 的工作原理
我们利用mysql命令备份数据库的过程,实际上就是把数据从mysql库里以逻辑的sql语句形式直接输出或者生成备份文件的过程

恢复就是把备份是sql语句在mysql重新执行一次的过程



三 如何做分库备份
分库备份:执行一个备份语句备份一个库,入锅数据库有多个库,就执行多条相同的备份的单个库备份语句

查看所有数据库:

mysql-uroot -p123456 -e "show databases;"|grep -Evi"database|info|test|perf"

grep

-E:支持使用扩展的正则

-e:支持使用标准的正则

-i:忽略大小写

-v:选中非匹配的行

-c:统计行数

字符串结果交给bash运行

echo"ls -al /opt/data"|bash



mysql-uroot -p123456 -e "show databases;"|grep -Evi"database|info|perf|mysql"

hadoop

nicky

test

然后开始循环

#!/bin/bash

DB_PATH=/opt/bak

MYSQL_USER=root

MYSQL_PASSWD=123456

SOCKET=/data/3306/mysql.sock

MYSQL_CMD="mysql-u$MYSQL_USER -p$MYSQL_PASSWD -S $SOCKET"

MYSQL_DUMP="mysqldump-u$MYSQL_USER -p$MYSQL_PASSWD -S $SOCKET -x -B -F -R"

DB_LIST=`$MYSQL_CMD-e "show databases;"|grep -Evi "database|info|perf|mysql"`



[! -d $DB_PATH ] && mkdir -p $DB_PATH

fordbname in $DB_LIST

do

    $MYSQL_DUMP $dbname|gzip >$DB_PATH/${dbname}_$(date +%F).sql.gz

done



>>备份单个表

mysqldump-uroot -p123456 数据库 表名1 >/opt/bak/hadoop_student.sql

>>备份多个表

mysqldump-uroot -p123456 数据库 表名1 表名2...> /opt/bak/tables.sql









一般备份方案:

myisam:

mysqldump-uroot -p123456 -A -B --master-data=1 -x -R |gzip > /opt/all.sql.gz



mysqldump-uroot -p123456 -A --flush-privileges --lock-all-tables \

--master-data=1-F --triggers -R -E --hex-blob > /opt/bak/all.sql

innodb:

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



mysqldump-uroot -p123456 -A --flush-privileges --single-transaction \

--master-data=1-F --triggers -R -E --hex-blob > /opt/bak/all.sql

如果两者都有,以myisam为准



四 利用source恢复MySQL数据
在mysql 执行 sourcedump文件路径

如果时gzip格式的,我们需要先解压文件

'gzip-d /opt/data/mysql_hadoop_bak.sql.gz'

mysql>source /opt/data/mysql_hadoop_bak.sql





五 查看当前数据库有哪些进程正在连接mysql
mysql-uroot -p123456 -e "show processlist"

mysql-uroot -p123456 -e "show full processlist"

如果连续两次show,还有那个进程在哪带着,可能是慢查询





六 查看当前数据库参数信息showvariables
mysql-uroot -p123456 -e "show variables;"

$mysql -uroot -p123456 -e "show variables;"|grep log_bin

log_bin     OFF

log_bin_trust_function_creators     OFF

sql_log_bin    ON





七 查看整个数据库状态showglobal status
>>查看当前会话数据库状态showglobal status,很重要,并要做好监控



八 修改数据库参数不重启就生效
key_buffer:myisam引擎存放索引的缓冲区,内存空间大了,就减少了去磁盘查找index

setkey_buffer_size=1024*1024*32;

然后配置文件修改

这样无论重启与否,都生效



九 mysqlbinlog 解析mysqlbin log日志的
作用:

用来记录mysql内部增删改等对mysql所有数据库有更新的内容的记录,只查不改的不会记录



命令:

==>查看binlog日志

如果我们想以可视化的形式查看日志,需要使用mysqlbinlog来查看,否则你看到的式二进制文件

mysqlbinlog/usr/local/mysql/data/mysql-bin.000001



现在有一个问题,如果全备份已经分库了,但是使用mysqlbinlog恢复数据的时候,binlog记录的时所有库的所有表数据

别的库没有问题,然后你在插入数据,别的库就会报错。

所以我们需要针对binlog拆库



==>mysqlbinlog拆库: -d 指定数据库名字

我只想恢复 hadoop库 的bin log

mysqlbinlog-d hadoop mysql-bin.000001 > /opt/bak/hadoop.sql





==>指定时间和位置点恢复 -r 重定向

mysqlbinlog/usr/local/mysql/data/mysql-bin.000001 --start-position=177 --stop-position=286-r /opt/bak/hadoop_bin.sql

mysqlbinlog/usr/local/mysql/data/mysql-bin.000001 --start-datetime='2016-11-29 11:11:11' \

--stop-datetime='2016-11-2911:11:13' -r /opt/bak/hadoop_bin.sql



比如假设某一个数据库在早上9点全备份,mysqldump,这时候bin log会记录一个位置

然后12点数据库不小心删掉了,那么先回复全备份,然后9-12点的恢复就靠这期间的

binlog文件




mysql-uroot -p123456 hadoop < /opt/data/mysql_hadoop_bak.sql

#查找当前目录下包含字符串“Linux”的文件

egrepLinux *

(责任编辑:IT)
------分隔线----------------------------
栏目列表
推荐内容