3

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

 1 year ago
source link: https://blog.51cto.com/autofelix/5581152
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

一文搞懂│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/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
  • 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数据路径的权限为777
chmod -R 777 /var/lib/mysql

🎈 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;

🎈 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: Yes
Slave_SQL_Running: Yes
  • 1赞
  • 1收藏
  • 评论
  • 分享
  • 举报

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK