写点什么

一文搞懂│mysql 中的备份恢复、分区分表、主从复制、读写分离

作者:极客飞兔
  • 2022 年 8 月 16 日
    江苏
  • 本文字数:3128 字

    阅读完需:约 10 分钟

一文搞懂│mysql 中的备份恢复、分区分表、主从复制、读写分离

🎈 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/mysqlmysqlbinlog --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-blob16进制导出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
复制代码


  • XtraBackup 备份

  • 自定义下载地址:Percona 存储库

  • 命令下载:yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

  • 测试储存库安装是否成功命令:yum list | grep percona

# 下载 libev软件包yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm # 安装 libev软件包yum install percona-xtrabackup-24
复制代码


# 全量备份innobackupex --user=管理员账号 --password=密码 --parallel=2 备份路径innobackupex --user=backup --password=Gzjunyu19970925. --parallel=2 /home/db_backup/
# 全量恢复,建议恢复前停止mysql服务,且清空mysql数据文件innobackupex --datadir=mysql数据路径 --copy-back 备份路径innobackupex --datadir=/var/lib/mysql --copy-back /home/db_backup/2018-04-21_10-44-22/
# 修改mysql数据路径的权限为777chmod -R 777 /var/lib/mysql
复制代码

🎈 mysql 的分区分表

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

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

  • 分区分类:范围分区哈希分区时间分区

# 范围分区CREATE TABLE `表名`(    -- 数据字段)engine=INNODBPARTITION BY RANGE(`字段名称`) (    PARTITION 分表名称 VALUES LESS THAN(范围),    PARTITION 分表名称 VALUES LESS THAN(范围));
# 哈希分区create table `表名`( -- 数据字段)engine=INNODBPARTITION BY HASH(UNIX TIMESTAMP(`字段名称`)) PATITIONS 4;
# 时间分区create table `表名`( -- 数据字段)engine=INNODBPARTITION 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_rowsFROM 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;
复制代码

🎈 mysql 的主从复制读写分离

  • 授权远程访问 mysql 数据库

  • 新建相关数据库管理员,授权并开启远程访问权限

-- 建议新建一个备份和主从复制的数据库管理员CREATE USER 'backup'@'localhost' IDENTIFIED BY '密码';-- 分配相关权限grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';-- 开启远程访问权限GRANT ALL PRIVILEGES ON *.* TO 授权用户名@被授权服务器的IP IDENTIFIED BY '授权密码';FLUSH PRIVILEGES;
复制代码


  • 配置 mysql 主服务器

  • 需要在 master 服务器和 slave 服务器都建立一个同名的数据(备份数据库)

  • 在主服务器中开启 binlog 日志和设置要发生主从同步数据库,使用 vim 打开 /etc/my.cnf 文件,修改配置如下

  • 配置完毕后,需要重启服务:service mysqld restart;

#mysql的bin-log日志配置选项,假设做读写(主从),这个选项在从服务器必须关闭log_bin = binlog#端口信息,其实可以不写port = 3306#主服务器的id,这id不一定设为1,只要主从不一样就行server-id = 1#要做同步的数据库名字,可以是多个数据库,之间用分号分割。binlog_do_db = test
复制代码


  • 检查配置

  • 登录 mysql 查看 binlog 日志相关参数是否正确

show master status;show variables like 'log_bin';
复制代码


  • 配置 mysql 从服务器

  • 主服务器已经配置成功,这时要切换到从服务器开始配置

  • 在从服务器中开启 binlog 日志和设置要发生主从同步数据库,使用 vim 打开 /etc/my.cnf 文件,修改配置如下

#从服务器的id,必须与主服务器的id是不同server-id = 2#主服务器的ip地址master-host = 192.168.56.2#grant授权的可复制用户账号master-user = backup#grant授权的可复制密码master-password = 123456#主服务器的mysql端口master-port = 3306#这个参数是用来设置在和主服务器连接丢失的时候,重试的时间间隔,默认是60秒master-connect-retry = 20#需要同步的主服务器数据库replicate-do-db = test
复制代码


  • 检测主从复制配置是否成功


show slave status\G -- 如果结果包含如下参数,则证明主从已经配置成功Slave_IO_Running: YesSlave_SQL_Running: Yes
复制代码


发布于: 刚刚阅读数: 7
用户头像

极客飞兔

关注

腾讯 2021.03.22 加入

毕业于全国重点大学,CSDN博客专家、阿里云专家博主、华为云云享专家、北极代码库贡献者、github开发者计划成员、任职国内知名游戏公司

评论

发布
暂无评论
一文搞懂│mysql 中的备份恢复、分区分表、主从复制、读写分离_MySQL_极客飞兔_InfoQ写作社区