MySQL 单个数据库备份还原

数据库备份还原

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;
           

为了数据库的高可用扩展，视图、存储过程、函数、触发器 等都不需要创建，数据库回归本质，只存储数据，所以上面的一些备份参数可以忽略。