MySQL 单个数据库备份还原
原创
©著作权归作者所有:来自51CTO博客作者JeesonHuang的原创作品,请联系作者获取转载授权,否则将追究法律责任
数据库备份还原
MYISAM: --lock-all-tables
INNODB: --single-transaction
#单个数据库备份及压缩
mysqldump -uroot -pmysql --opt --databases --routines --events --flush-logs --single-transaction --master-data=2 --default-character-set=utf8 test | gzip > /opt/testdb_back.sql.gz
mysqldump -uroot -pmysql --opt -B -R -E -F --single-transaction --master-data=2 --default-character-set=utf8 test | gzip > /opt/testdb_back.sql.gz
#所有数据库备份及压缩
mysqldump -uroot -pmysql --opt --databases --routines --events --flush-logs --all-databases --flush-privileges --single-transaction --master-data=2 --default-character-set=utf8 | gzip > /opt/alldb_back.sql.gz
mysqldump -uroot -pmysql --opt -B -R -E -F -A --flush-privileges --single-transaction --master-data=2 --default-character-set=utf8 | gzip > /opt/alldb_back.sql.gz
单个用户数据库备份还原
#备份单个数据库
shell> mysqldump -uroot -pmysql --opt -B -R -E -F --single-transaction --master-data=2 --default-character-set=utf8 test > /root/test/backtest.sql
#此时删除数据库 "test"
mysql> drop database test ;
#还原数据库(方法一)
mysql> source /root/test/backtest.sql
#还原数据库(方法二)
shell> mysql -uroot -pmysql < /root/test/backtest.sql
---------------------------------------------------
#查看完整备份位置
shell> cat /root/test/backtest.sql | grep "MASTER_LOG_POS"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000018', MASTER_LOG_POS=154;
#查看(数据库 test)事务日志内容,日志太大不建议这么查看
shell> mysqlbinlog --no-defaults -d test mysql-bin.000018 --base64-output=decode-rows -v
---------------------------------------------------
#确定日志起始时间
shell> mysqlbinlog --no-defaults -d test mysql-bin.000018 --base64-output=decode-rows -v | grep "end_log_pos 154"
#获取某个数据库时间范围内的事务日志
shell> mysqlbinlog --no-defaults -d test mysql-bin.000018 --start-datetime="2017-07-08 21:22:35" --stop-datetime="2017-07-08 22:12:23" > /root/test/backtestlog.sql
#执行日志脚本(SQL 操作,将再次记录到事务日志中)
shell> mysql -uroot -pmysql < /root/test/backtestlog.sql
---------------------------------------------------
#或者:
#因同一时间可能有多个操作,用时间还原不精确,也可以查看时间附近确定 position
shell> mysqlbinlog --no-defaults -d test mysql-bin.000018 --base64-output=decode-rows -v | grep -10 "170708 22:12:23"
#获取某个数据库位置点间的事务日志
shell> mysqlbinlog --no-defaults -d test mysql-bin.000018 --start-position=154 --stop-position=10556 > /root/test/backtestlog.sql
#执行日志脚本(SQL 操作,将再次记录到事务日志中)
shell> mysql -uroot -pmysql < /root/test/backtestlog.sql
还原为一个新的数据库
因为备份时添加了选项"-B",则"create database ……" 及 "use <dbname>" 会记录在备份中。如果打算还原为一个新的数据库,则必须把备份文件的相关信息更改。如果还原在同一个实例,视图、存储过程、函数、触发器、事件等有调用原数据库表的,注意更改!尤其触发器,如果创建时限定了数据库(如 CREATE TRIGGER dbname.tr_test ……),则还原会提示已存在,如使用触发器,最好不要把数据库名写上。
本测试中,备份的是数据库 test,现在还原为一个新的数据库 testdb 。
#备份时不加参数"-B",还原时指定数据库即可。若备份有参数"-B",把脚本中的数据库替换。
shell> cp /root/test/backtest.sql /root/test/backtest_b.sql
shell> sed -i 's/CREATE DATABASE \/\*!32312 IF NOT EXISTS\*\/ `test`/CREATE DATABASE \/\*!32312 IF NOT EXISTS\*\/ `testdb`/g' /root/test/backtest_b.sql
shell> sed -i 's/USE `test`;/USE `testdb`;/g' /root/test/backtest_b.sql
#还原数据库(方法一)
mysql> source /root/test/backtest_b.sql
#还原数据库(方法二)
shell> mysql -uroot -pmysql < /root/test/backtest_b.sql
在 Windows 中使用命令行 cmd 备份数据库并压缩文件:(需安装 winrar)
set bkdb=mydb
set bkuser=root
set bkpw=mysql
set bkhost=localhost
set bkpath=D:\mysql_backup\
set dt=%date:~,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~6,2%
set bkfile="%bkpath%%bkdb%_full_%dt%.sql"
set rarfile="%bkpath%%bkdb%_full_.rar"
mysqldump -u%bkuser% -p%bkpw% --opt -B -R -E -F --single-transaction --default-character-set=utf8 %bkdb% > %bkfile%
"%ProgramFiles(x86)%\WinRAR\Rar.exe" a -ep1 -ag -df -m5 -ibck -y %rarfile% %bkfile%
a: 添加压缩
-ag :默认生成的压缩文件添加时间,格式为:YYYYMMDDHHMMSS (可自定义)
-ep1:压缩文件不含输入的路径
-df :压缩后删彻底除源文件
-dr :压缩后删除源文件到回收站
-m5 :使用 RAR 5.0 压缩格式
-ibck :后台运行
-y:对所有询问均回应为"是"
RAR 更多参数说明,参考安装目录中的文档:C:\Program Files (x86)\WinRAR\WinRAR.chm
window bat 导出 MySQL 授权脚本(此处只导出 grant 脚本)
@echo off
set "user=root"
set "pwd=mysql"
set "sqlfile=D:/MSSQLDATA/Backup/mysql_grant_sql.sql"
echo.> %sqlfile%
mysql -u%user% -p%pwd% -B -N -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS grantsql FROM mysql.user;" | mysql -u%user% -p%pwd% -B -N | for /F "delims=" %%a in (' findstr /i "^grant"') do echo %%a; >> %sqlfile%
测试时注意:
#命令行执行
for /F "delims=" %a in (' findstr /i "^grant"') do echo %a;
#脚本中执行
for /F "delims=" %%a in (' findstr /i "^grant"') do echo %%a;
为了数据库的高可用扩展,视图、存储过程、函数、触发器 等都不需要创建,数据库回归本质,只存储数据,所以上面的一些备份参数可以忽略。