mysql备份和恢复

• 创建备份管理员

• 创建备份管理员，并授予管理员相应的权限

• 备份所需权限：select,reload,lock tables,replication client,show view,event,process

# 创建管理员
create user 'backup'@'localhost' identified by '123456';

# 给管理员授权
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';

• sql 文件恢复之全量恢复

• 只要将备份的 sql 文件直接导入数据库即可

mysql -uroot -p 数据库 < sql文件

• sql 文件恢复之基于时间点的恢复

# 首先进行一次基于最近一次的全量备份的文件进行一次全量恢复
mysql -uroot -p 数据库 < sql文件

# 然后查看备份的sql文件的 CHANGE MASTER 值，基于该值进行二进制日志的还原
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=154;

# 查看二进制日志,根据时间点找到误操作前一段时间的二进制日志
cd /var/lib/mysql
mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database=数据库名 binlog.000008 | grep -B3 DELETE | more

# 记录最早删除记录的节点值，执行日志导出
mysqlbinlog --start-position=开始节点 --stop-position=结束节点 --database=数据库 二进制日志名 > 导出的sql文件名
mysqlbinlog --start-position=154 --stop-position=26158 --database=laravel binlog.000007 > laravel.sql

# 对导出的sql文件进行全量的还原
mysql -uroot -p 数据库 < sql文件

• mysqldump逻辑备份

• 指定数据库多个表进行备份：mysqldump [OPTIONS] database [table]

• 指定多个数据库备份：mysqldump [OPTIONS] database [OPTIONS] DB1 DB2

• 整个数据库实例进行备份：mysqldump [OPTIONS] --all-database [OPTIONS]

• 参数 --single-transaction：开启事务保证备份数据的完整性，innodb 特有

• 参数 -l或--lock-tables：依次锁定备份数据库所有表保证备份数据的完整性

• 参数 -x或--lock-all-table：一次性锁定整个数据库实例所有数据表保证数据完整性

• 参数 --master-data=[1/2]：CHANGE MASTER TO 语句会被写成一个 sql 注释；1不会被写成注释，2写成注释，默认1

• 参数 -R或--routines：备份数据库存储过程

• 参数 --triggers：备份数据库触发器

• 参数 -E或--events：备份数据库调度事件

• 参数 --hex-blob：16进制导出bit列和blob列数据 避免数据文本不可见

• 参数 --tab=path：指定路径下为每个数据库生成两个文件：数据结构、数据

• 参数 -w或--where=过滤条件：过滤指定数据，仅支持单表导出

• 注意：--single-transaction，--lock-tables 参数是互斥的，所以，如果同一个数据库下同时存在 innodb 表和myisam表只能使用 --lock-tables 来保证备份数据的一致性，但是 --lock-tables只能保证某一备份数据库的完整性，不能保证整个实例备份的完整性

mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events 数据库 > 备份文件.sql

mysql 的分区分表

• 查看是否支持分区分表：show plugins;

• 删除分区命令：ALTER TABLE 表名 DROP PARTITION 分表名称;

• 分区分类：范围分区、哈希分区、时间分区

# 范围分区
CREATE TABLE `表名`(
    -- 数据字段
)engine=INNODB
PARTITION BY RANGE(`字段名称`) (
    PARTITION 分表名称 VALUES LESS THAN(范围),
    PARTITION 分表名称 VALUES LESS THAN(范围)
);

# 哈希分区
create table `表名`(
    -- 数据字段
)engine=INNODB
PARTITION BY HASH(UNIX TIMESTAMP(`字段名称`)) PATITIONS 4;

# 时间分区
create table `表名`(
    -- 数据字段
)engine=INNODB
PARTITION BY RANGE(YEAR(`字段名称`))(
    PARTITION p0 VALUES LESS THAN(2017),
    PARTITION p1 VALUES LESS THAN(2018),
    PARTITION p2 VALUES LESS THAN(2019)
);

# 查看分区情况
SELECT
    table_name,partition_name,partition_description,table_rows
FROM
    information_schema.`PARTITIONS`
WHERE table_name = '表名';

# 归档分区数据，mysql数据库版本需要大于等于5.7
-- 分区归档操作步骤
-- 1.新建和分区表字段一致的数据表 归档表前缀为 arch_
CREATE TABLE `归档表表名`(
 
)engine=INNODB
-- 2.进行数据交换 p0为分区名
ALTER TABLE `原数据表表名` exchange PARTITION p0 WITH TABLE `归档表表名`;
-- 3.删除分区，避免对数据的再次写入
ALTER TABLE `原数据表表名` DROP PARTITION p0;
-- 4.将归档表引擎设置为 archive  在检表语句中mysql引擎必须和原数据表引擎一致，否则无法进行数据交换
ALTER TABLE `归档表表名` ENGINE=ARCHIVE;