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

本篇文章给大家分享《一文搞懂│mysql 中的备份恢复、分区分表、主从复制、读写分离》，覆盖了数据库的常见基础知识，其实一个语言的全部知识点一篇文章是不可能说完的，但希望通过这些问题，让读者对自己的掌握程度有一定的认识(B 数)，从而弥补自己的不足，更好的掌握它。

? mysql 的备份和恢复

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

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

  • sql 文件恢复之全量恢复
  • 只要将备份的

    mysql -uroot -p 数据库 

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

    # 首先进行一次基于最近一次的全量备份的文件进行一次全量恢复
    mysql -uroot -p 数据库  导出的sql文件名
    mysqlbinlog --start-position=154 --stop-position=26158 --database=laravel binlog.000007 > laravel.sql
    
    # 对导出的sql文件进行全量的还原
    mysql -uroot -p 数据库 

    • mysqldump逻辑备份
    • 指定数据库多个表进行备份：

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

      • XtraBackup备份
      • 自定义下载地址：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数据路径的权限为777
        chmod -R 777 /var/lib/mysql

        ? mysql 的分区分表

        • 查看是否支持分区分表：

          # 范围分区
          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;

        ? 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主服务器
        • 需要在

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

          • 检查配置
          • 登录

            show master status;
            show variables like 'log_bin';

            • 配置mysql从服务器
            • 主服务器已经配置成功，这时要切换到从服务器开始配置
            • 在从服务器中开启

              #从服务器的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: Yes
              Slave_SQL_Running: Yes

              理论要掌握，实操不能落！以上关于《一文搞懂│mysql 中的备份恢复、分区分表、主从复制、读写分离》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！