mysql数据的备份与恢复

2020-01-13

MYSQLDUMP

  1. 可以生成SQL，CSV，其他分隔符的文本或XML格式的文件
  2. 不会转储INFORMATION_SCHEMA， performance_schema或sys表。如果要转储这些表，需要在命令行上明确指定
  3. 不会转储NDB群集ndbinfo信息数据库
  4. 不转储InnoDB CREATE TABLESPACE语句

权限

  1. 转储的表要有SELECT权限
  2. 对转储的视图要有SHOW VIEW权限
  3. 对转储的触发器要有TRIGGER权限

备份

转储test数据库的test1、test2表，不指定数据表时直接转储整个数据库

mysqldump test test1 test2 > test1_2.sql

mysqldump test > dump.sql

转储多个数据库

mysqldump --databases test test1 > dump.sql
mysqldump -B test test1 > dump.sql

转储全部数据库，不会转储INFORMATION_SCHEMA和performance_schema数据库

mysqldump --all-databases > dump.sql
mysqldump -A > dump.sql

转储时忽略指定表，必须同时使用数据库名和表名，要忽略多个表，多次使用此选项

mysqldump --ignore-table=db_name.tbl_name > dump.sql

转储为XML文件

mysqldump --xml test test1 > dump.sql
mysqldump -X test test1 > dump.sql

转储文件，文件将生成表结构(tbl_name.sq)和表数据(tbl_name.txt)(一个表一行，数据以制表符分隔)两个文件

mysqldump --tab=path --fields-terminated-by=str --fields-enclosed-by=char  --fields-optionally-enclosed-by=char  --fields-escaped-by=char --lines-terminated-by=str test > dump.sql

mysqldump -T dir_name --fields-terminated-by=str --fields-enclosed-by=char  --fields-optionally-enclosed-by=char  --fields-escaped-by=char --lines-terminated-by=str test > dump.sql

# 用于分隔列值的字符串（默认值：制表符）
--fields-terminated-by=str
# 包含列值的字符（默认值：无字符）
--fields-enclosed-by=char
# 包含非数字列值的字符（默认值：无字符）
--fields-optionally-enclosed-by=char
# 用于转义特殊字符的字符（默认值：不转义）
--fields-escaped-by=char
# 行终止字符串（默认值：换行符）
--lines-terminated-by=str

指定转储时的账号密码等

mysqldump -h host_name -P port_num  -u user_name  --protocol=TCP -p[password] --defaults-file=file_name > dump.sql

mysqldump -S path --defaults-file=file_name > dump.sql

# ip
-h host_name
# 端口
-P port_num
# 用户名
-u user_name
# 密码，-p后不跟随密码时将会在命令行中提示输入（更安全）
-p [password]
# 连接传输协议
--protocol={TCP|SOCKET|PIPE|MEMORY}
# 使用指定的配置文件
--defaults-file=file_name
# 使用套接字连接本地数据库
-S path

转储数据库的全部内容，包括（触发器，事件计划，存储过程）

mysqldump --events --routines --triggers test > dump.sql

# 事件计划程序事件，默认禁用
--events/-E
# 存储过程和函数，默认禁用
--routines/-R
# 触发器，默认启用
--triggers

转储时跳过触发器，事件计划，存储过程

mysqldump --skip-events --skip-routines --skip-triggers  test > dump.sql

# 事件计划
--skip-events
# 存储过程和函数
--skip-routines
# 触发器
--skip-triggers

只转储数据表结构

mysqldump --no-data test > dump.sql

只转储数据表数据

mysqldump --no-create-info test > dump.sql

转储时在创建语句前增加删除语句，重建数据库表

mysqldump --add-drop-database --add-drop-table --add-drop-trigger test > dump.sql

# 数据库
--add-drop-database
# 数据表
--add-drop-table
# 触发器
--add-drop-trigger

转储时不生成建库语句，使备份文件可以在任意数据库中恢复

# 方法1
mysqldump --no-create-db --databases test > dump.sql

# 方法2
mysqldump test > dump.sql

#  在--databases或--all-databases中使用
--no-create-db

根据指定条件转储

# 如果条件中包含空格或其他命令解释程序特有的字符，则必须在条件周围加引号
mysqldump --where='where_condition' test > dump.sql

高效转储

mysqldump --add-drop-table --disable-keys --add-locks --extended-insert --insert-ignore --single-transaction --compact --quick test > dump.sql

# --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset组合的简写
# 提供了快速的转储操作并生成转储文件，可以快速地重新加载到MySQL服务器。默认启用
--opt
# 使得加载转储文件更快，因为索引是在所有行插入之后创建的。仅对MyISAM表的非唯一索引有效
--disable-keys
# 使用包含多个VALUES列表的多行INSERT语法编写语句。生成较小的转储文件，并在重新加载文件时加快插入速度
--extended-insert
# 将INSERT语句改为INSERT IGNORE
--insert-ignore
# InnoDB表以一致状态转储而不会阻塞任何应用程序，转储大表时与--quick结合使用
--single-transaction
#  转储大型表很有用,强制mysqldump每次从服务器检索一个表的行，而不是检索整个行集并在写出来之前将其缓冲到内存中
--quick
# 产生更紧凑的输出
--compact
# 使用LOCK TABLES和UNLOCK TABLES语句包围每个表。重新装入转储文件时，这可以加快插入速度
--add-locks
# 对于每个转储的数据库，在转储它们之前，锁定要转储的所有表。对于MyISAM表，使用READ LOCAL锁定表，以允许并发插入。对于InnoDB这样的事务表，--single-transaction是比--lock-tables更好的选择，因为--single-transaction不需要锁定表。
--lock-tables

转储时提高客户端/服务器通信缓冲区的最大大小。默认值为24MB，最大为1GB。

mysqldump --max-allowed-packet=value test > dump.sql

转储MyISAN表高效加载到InnoDB

mysqldump --order-by-primary test > dump.sql

# 按其主键或第一个唯一索引(如果存在这样的索引)排序每个表的行。但会使转储操作花费更长的时间。
--order-by-primary

转储带有GTID的数据库

mysqldump --set-gtid-purged=value test > dump.sql

/*
value
ON    在输出中添加一条SET语句。如果未在服务器上启用GTID，则会发生错误。
OFF   在输出中不添加任何SET语句。
AUTO  如果在服务器上启用了GTID，则在输出中添加一条SET语句。
用来部署从数据库指定为ON，署非从数据库指定为OFF
*/

转储时日志处理

mysqldump --flush-logs --delete-master-logs test > dump.sql

# 开始转储之前，刷新MySQL服务器日志文件，与--lock-all-table、--master-data或--single-transaction一起使用转储和日志刷新完全同时发生
--flush-logs
# 删除不再需要的二进制日志
--delete-master-logs

转储数据加载到从库

mysqldump --master-data=value --all-databases > dump.sql

/*
将二进制日志信息（文件名、坐标）写入其输出
value
1   在文件中协议CHANGE MASTER TO语句，并在重新加载转储文件时生效，默认值
2   CHANGE MASTER TO语句将写为SQL注释，仅供参考；重新加载转储文件时，它无效
*/

转储时写入数据库信息，程序版本，服务器版本和主机，默认启用

mysqldump -i test > dump.sql

转储时忽略错误并将警告和错误存到指定文件

mysqldump -f --log-error=file_name test > dump.sql

转储可以应用于老版本服务器的文件

mysqldump --skip-opt  test > dump.sql

备份时刷新MySQL二进制日志，并且转储文件中包含新的当前二进制日志的名称

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > backup_sunday_1_PM.sql

自动备份脚本

linux版

脚本中的文件用绝对路径，否则在设置定时任务时，可能执行失败；执行脚本时如果提交命令不存在，那么将命令的环境变量路径补全（`which 命令名称`  查看命令的环境变量）

thouch /home/mysql_bak.sh

echo  -e
"
#!/bin/bash
DTIME=$(date "+%Y%m%d%H")

/www/server/mysql/bin/mysqldump -uroot -p123456 --databases database_name > /www/sql_$DTIME.sql

# 压缩备份文件
#/www/server/mysql/bin/mysqldump -uroot -p123456 --databases database_name | /usr/bin/gzip > /www/sql_$DTIME.sql.gz

# 删除15天前的备份
/usr/bin/find /www/  -name "sql*" -type f -mtime +15 -exec /usr/bin/rm -f {} \;
" > /home/mysql_bak.sh

# 赋予脚本可执行权限
chmod  u+x /home/mysql_bak.sh

# 测试脚本运行
./home/mysql_bak.sh

# 使用crontab设置定时执行备份脚本
crontab -e

# 输入此行然后保存   意义：每天2点执行脚本
0 2 * * * /home/mysql_bak.sh

# 重启crontab使定时任务生效
systemctl restart crond.service

# 在/var/log/cron中查看crontab日志，看定时任务是否执行

二进制日志备份

实时备份

grant replication slave on *.* to 'repl'@'localhost' identified by 'xxxx';创建一个用户repl
mysqlbiglog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -u root -p xxxx 二进制文件名；备份二进制日志，会在后台一直运行

flush logs;刷新二进制日志

MYSQLPUMP

与MYSQLDUMP类似的备份程序，命令与mysqldump类似，在mysqldump的命令参数基础上增加了一些命令参数：并行导出、跳过数据库表等

优点

  1. 并行处理数据库以及数据库中的对象，以加快转储过程
  2. 更好地控制要转储的数据库和数据库对象（表，存储的程序，用户帐户）
  3. 转储用户帐户作为帐户管理报表(CREATE USER， GRANT)，而不是插入到mysql系统数据库
  4. 创建压缩输出的能力
  5. 进度指示器（是估计值）
  6. 对于转储文件重新加载，InnoDB通过在插入行后添加索引，可以更快地为表创建二级索引

限制

  1. 默认不转储 INFORMATION_SCHEMA， performance_schema， ndbinfo，或sys默认模式。要转储任何这些，请在命令行上明确命名它们。
  2. 默认不转储 InnoDB CREATE TABLESPACE语句
  3. 默认不能使用多个线程并行转储单个表

默认不转储用户用户定义的授权表(user、db、tables_priv、columns_priv、procs_priv或proxies_priv)，如果需要转储，需要指定数据库名

权限

  1. 对要转储的表需要有SELECT权限、LOCK权限
  2. 对要转储的视图需要有SHOW VIEW权限
  3. 对要转储的触发器需要有TRIGGER权限

备份

转储时在创建用户前增加删除语句

mysqlpump --add-drop-user test > dump.sql

转储时跳过指定数据库、表、事件、存储例程、触发器、数据库用户

mysqlpump --exclude-databases=db_list --exclude-tables=table_list --exclude-events=event_list --exclude-routines=routine_list --exclude-triggers=trigger_list --exclude-users=user_list test > dump.sql

# list可以指定用逗号分隔的多个数据；参数可以多次指定；参数可以通配符%和_
# %匹配任何零个或多个字符的序列
# _匹配任何单个字符

# 数据库
databases
# 数据表，多个数据库时需要指定数据库名：db.table
tables
# 数据库用户
users
# 事件，多个数据库时需要指定数据库名：db.event
events
# 存储例程，多个数据库时需要指定数据库名：db.routine
routines
# 触发器，多个数据库时需要指定数据库名：db.trigger
triggers

转储时只转储指定数据库、表、事件、存储例程、触发器、数据库用户

mysqlpump --include-databases=db_list --include-tables=table_list --include-events=event_list --include-routines=routine_list --include-triggers=trigger_list --include-users=user_list test > dump.sql

# list可以指定用逗号分隔的多个数据；参数可以多次指定；参数可以通配符%和_
# %匹配任何零个或多个字符的序列
# _匹配任何单个字符

# 数据库
databases
# 数据表，多个数据库时需要指定数据库名：db.table
tables
# 数据库用户
users
# 事件，多个数据库时需要指定数据库名：db.event
events
# 存储例程，多个数据库时需要指定数据库名：db.routine
routines
# 触发器，多个数据库时需要指定数据库名：db.trigger
triggers

使用指定算法压缩转储数据,可以使用LZ4和ZLIB算法；需要使用LZ4和ZLIB做解压

mysqlpump --compress-output=algorithm test > dump.sql

lz4_decompress dump.lz4 dump.txt
zlib_decompress dump.zlib dump.txt

以将索引创建推迟在加载后表数据后的方式转储数据，适用于所有存储引擎，对于InnoDB表仅二级索引可用，默认启用    

mysqlpump --defer-table-indexes test > dump.sql

使用队列并行转储多个数据库

mysqlpump --default-parallelism=4 --parallel-schemas=5:db1,db2 --parallel-schemas=3:db3 --parallel-schemas=db4 --all-databases > dbs.sql

# 代表一个队列，转储db_list
# db_list可以是一个或用逗号分隔的多个数据库
# N表示转储的线程数，不指定N，则使用--default-parallelism设置的数量
# 没有在--parallel-schemas中指定的数据库将处在默认队列
--parallel-schemas=[N:]db_list

# 队列线程数，默认2
--default-parallelism=N

# --default-parallelism=0和不带--parallel-schemas选项的mysqlpump作为单线程进程运行

转储时显示进度，提供有关已完成的表、行和其他对象的总数和总数的信息。

mysqlpump --watch-progress test > dbs.sql

# 不显示进度
--skip-watch-progress

MYSQLIMPORT

提供了LOAD DATA SQL语句的命令行接口。mysqlimport的大多数选项都直接对应于加载数据语法的子句。对于在命令行上命名的每个文本文件，不包含扩展名的文件名就是要导入内容的数据表名

mysqlimport [options] db_name textfile1 [textfile2 ...]

导入文件

导入时指定账号密码等

mysqlimport -h host_name -P port_num  -u user_name  --protocol=TCP -p[password] --defaults-file=file_name db_name textfile1

mysqlimport -S path db_name textfile1

# ip
-h host_name
# 端口
-P port_num
# 用户名
-u user_name
# 密码，-p后不跟随密码时将会在命令行中提示输入（更安全）
-p [password]
# 连接传输协议
--protocol={TCP|SOCKET|PIPE|MEMORY}
# 使用套接字连接本地数据库
-S path
# 使用给定的配置文件
--defaults-file=file_name

多进程并行导入数据

mysqlimport --use-threads=N  db_name file1

导入数据时指定列名

mysqlimport --columns=column_list db_name textfile1 [textfile2 ...]

# 以逗号分隔的列名，列名的顺序与数据文件列匹配

导入数据时忽略错误

mysqlimport --force db_name textfile1 [textfile2 ...]

导入数据时压缩服务器与客户端发送的信息

mysqlimport --compress db_name textfile1 [textfile2 ...]

导入数据时锁表

mysqlimport --lock-tables db_name textfile1 [textfile2 ...]

导入数据时清空表

mysqlimport --delete db_name textfile1 [textfile2 ...]

导入数据时忽略文件前N行数据

mysqlimport --ignore-lines=N db_name textfile1 [textfile2 ...]

导入数据时使用指定字符集

mysqlimport --default-character-set=charset_name db_name textfile1 [textfile2 ...]

导入本地文件数据

mysqlimport --local db_name textfile1 [textfile2 ...]

导入数据时指定换行符

mysqlimport --lines-terminated-by=... db_name textfile1 [textfile2 ...]

恢复

导入转储的文件

恢复时不指定数据库，导入到转储文件中包含的CREATE DATABASE和USE指定的数据库

shell> mysql < dump.sql
或
mysql> source dump.sql

恢复时指定要导入的数据库，转储文件中不能包含CREATE DATABASE和USE指定的数据库

shell> mysql db1 < dump.sql
或
mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

恢复使用--tab参数转储的数据

shell> mysql db1 < t1.sql
shell> mysqlimport db1 t1.txt
或
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1;

恢复使用--fields-*参数备份的数据 ，MYSQLIMPORT或LOAD DATA需要指定与MYSQLDUMP时相同的--fields-*参数

shell> mysqlimport --fields-terminated-by=,
         --fields-enclosed-by='"' --lines-terminated-by=0x0d0a db1 t1.txt
或
mysql> USE db1;
mysql> LOAD DATA INFILE 't1.txt' INTO TABLE t1
       FIELDS TERMINATED BY ',' FIELDS ENCLOSED BY '"'
       LINES TERMINATED BY '\r\n';

使用二进制日志进行恢复

二进制日志中数据表是指定在某个数据库下的，所以恢复时不需要指定数据库，如果想使用恢复到其他数据库，可使用--rewrite-db="from_name->to_name"参数

如果MySQL服务器上有多个二进制日志，应该将多个二进制在一条语句中处理，如果分开处理，可能是不安全的

shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql

从包含GTID的二进制日志读回写入转储文件并恢复到数据库

shell> mysqlbinlog --skip-gtids binlog.000001 >  /tmp/dump.sql
shell> mysql -u root -p -e "source /tmp/dump.sql"

将二进制日志输出到文件中，然后恢复到数据库

# 输出到文件
shell> mysqlbinlog binlog_files > tmpfile
# 追加到文件
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql

shell> mysql -u root -p -e "source /tmp/dump.sql"

将二进制日志的数据更改应用到数据库中

shell> mysqlbinlog binlog_files... | mysql -u root -p

使用二进制日志事件位置进行时间点恢复

  • 找到最近一次的完整备份，记录其中的二进制日志位置。然后将备份还原
  • 查找与要还原数据库的时间点相对应的精确二进制日志事件位置 
    shell> mysqlbinlog  --start-datetime="2020-05-27 12:59:00" --stop-datetime="2020-05-27 13:06:00" \
  --verbose /var/lib/mysql/bin.123456|grep ''
  • 将事件以二进制日志文件的形式应用到服务器， 
    shell> mysqlbinlog --start-position=1006 --stop-position=1868 /var/lib/mysql/bin.123456 \
    | mysql -u root -p
CATALOG
标签
mysql redis sqlserver
{/if}