MySQL和MariaDB 备份 主从 读写分离
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL和MariaDB 备份 主从 读写分离,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含16386字,纯文字阅读大概需要24分钟。
内容图文
查询缓存:
如何判断是否命中:
通过查询语句的哈希值判断:哈希值考虑的因素包括
查询本身、要查询的数据库、客户端使用协议版本, ...
查询语句任何字符上的不同,都会导致缓存不能命中;
哪此查询可能不会被缓存?
查询中包含 UDF 、存储函数、用户自定义变量、临时表、 mysql 库中系统表、或者包含列级权限的表、有着不确定值的函数 (Now());
查询缓存相关的服务器变量:
query_cache_min_res_unit: 查询缓存中内存块的最小分配单位;
较小值会减少浪费,但会导致更频繁的内存分配操作;
较大值会带来浪费,会导致碎片过多;
query_cache_limit :能够缓存的最大查询结果;
对于有着较大结果的查询语句,建议在 SELECT 中使用 SQL_NO_CACHE
query_cache_size :查询缓存总共可用的内存空间;单位是字节,必须是 1024 的整数倍;
query_cache_type:ON, OFF, DEMAND
query_cache_wlock_invalidate :如果某表被其它的连接锁定,是否仍然可以从查询缓存中返回结果;默认值为 OFF ,表示可以在表被其它连接淘宝的场景中继续从缓存返回数据; ON 则表示不允许;
查询相关的状态变量
SHOWGLOBAL STATUS LIKE ‘Qcache%‘;
+-------------------------+----------+
|Variable_name | Value |
+-------------------------+----------+
|Qcache_free_blocks | 1 |
|Qcache_free_memory | 16759688 |
|Qcache_hits | 0 |
|Qcache_inserts | 0 |
|Qcache_lowmem_prunes | 0 |
|Qcache_not_cached | 0 |
|Qcache_queries_in_cache | 0 |
|Qcache_total_blocks | 1 |
+-------------------------+----------+
缓存命中率的评估: Qcache_hits/(Qcache_hits+Com_select)
查询 mysql 运行时全局变量
例:查询与缓存有关的去全局变量
showglobal variables like ‘%query%‘
select@@query_cache_type;
关闭缓存
set query_cache_type=off;
MySQL 日志:
是否启用二进制日志
sql_log_bin=on
二进制日志保存位置
log-bin = 二进制日志保存位置
show binary logs;
查看二进制日志
showbinlog events in ‘mysql-bin.000001‘\G
二进制日志文件的位置:
二进制日志保存格式
binlog_format=mixed 混合 |statement 基于语句 |row 基于行
是否启用二进制日志
sql_log_bin=on
设置单个二进制日志文件大小上限
max_binlog_size=1073741824 [ 最下 4k 最大 1G]
设置二进制日志缓存文件大小
max_binlog_cache_size=18446744073709547520[ 这个值不能大于 max_binlog_stmt_cache_size]
max_binlog_stmt_cache_size=18446744073709547520
设置多久同步一次二进制日志文件 ,0 表示不同步 , 任何正值都表示记录多少个语句后同步一次
sync_binlog=0
二进制日志的格式
[root@1GPC~]# mysqlbinlog mysql-bin.000001
MySQL 的备份和恢复
注意的要点
1. 可容忍丢失多少数据
2. 需要恢复什么
3. 持锁时长
4. 备份过程时长
5. 备份时 cpu 的负载
6. 恢复过程时长
备份类型:
完全备份,部分备份:仅备份其中的一张表或多张表
完全备份,增量备份:仅备份从上次完全备份或增量备份之后变化的数据部分
热备份、温备份、冷备份
热备份:在线备份,读写不受影响
温备份:在线备份,读可进行,写不允许
冷备份:离线备份,数据库服务器离线,备份期间不能为业务提供读写服务
MyISAM :温备份
InnoDB :热备份
物理备份和逻辑备份
物理备份:直接复制数据文件进行的备份。
优点:基于文件进行备份的,可以直接复制。恢复数据快。
缺点:和存储引擎有关, MyISAM 导出的数据不能导入到 InnoDB 中。不能跨 OS 。
逻辑备份:从数据库中 " 导出 " 数据另存而进行的备份。
优点:可以通过修改 sql 语句进行二次加工,与存储引擎无关,可以导入到任何存储引擎中;
缺点:无法保证数据导出和在导入或一致性,因为浮点数会取近似值。索引需重建
设计备份方案:
备份数据、二进制日志、 InnoDB 事务日志等日志数据、代码 ( 存储过程和存储函数、触发器、时间调度器等 ) 、服务器配置文件
完全备份 + 增量备份
建议完全备份 1 周一次, 1 天一次增量备份
使用 mysqldump 做完全备份,通过备份二进制日志实现增量备份
lvm2 快照:几乎热备,用 cp , tar 做完全备份,通过备份二进制日志实现增量备份
xtrabackup :物理备份
对 InnoDB :热备份,支持完全备份和增量备份
其他存储引擎:温备份
备份工具:
mysqldump :逻辑备份工具,支持完全备份和部分备份,不支持增量备份,若要支持需备份二进制日志,对其他存储引擎温备份, InnoDB 热备份
C/S 架构, mysqldump 是 Client , mysqld 是 SERver
-A :备份所有数据库 例: mysqldump -A -uroot -hlocalhost -x -p > /tmp/all.sql
要用 -x 锁定所要备份表 或 用 -l 锁定备份的表 [ 会导致数据不一致,最好不要使用 ]
--single-transaction :表示启动一个单一事务实现备份,可以实现锁定备份的表
-B :指定备份哪个数据库 例: mysqldump -uroot -hlocalhost -x -B test -p > /tmp/test.sql
使用 -B 和不使用 -B 直接备份数据库是有区别的, -B 会自动添加 create database 数据库这条语句,不使用则需恢复时自检创建数据库
备份表: mysqldump -uroot -hlocalhost mysql user -p > /tmp/mysql.test
-C :压缩传输,但会大量占用 Server 的 CPU
-E :备份指定数据库的事件调度器
-R :备份存储过程和存储函数
--triggers: 备份触发器
--master-data[=#]
0 表示不记录
1 记录 CHANGE MASTERTO 语句,此语句未被注释
2 记录 CHANGE MASTERTO 语句,此语句被注释
例: mysqldump --master-data=2 --lock-all-tables -A > /tmp/all.1.sql
-F :锁定表后执行 flushlogs 命令
案例:每周日完全备份,每周一到周五增量备份
二进制日志文件与数据文件不应放在同一磁盘上
例 hellodb 单个数据库:在 crontable 中设计周期任务
完全备份: mysqldump -B tpweb --flush-logs --lock-all-tables --master-data=2 -uroot -hlocalhost -p > /tmp/ tpweb -`date +%F`.sql
在这个 sql 文件中会看见
第 22 行表示备份后的所有的更改将会保存在 mysql-bin.000003 二进制文件中
增量备份:基于二进制日志文件进行备份
mysqladmin-uroot -p flush-logs
这时将会产生一个新的二进制日志文件 mysql-bin.000004,mysql-bin.000003 二进制文则会保存自上次备份后到现在的所有更改,只需把这个文件备份到安全的位置即可
mysqlbinlog /data/mysql-bin.000003 > /tmp/mysql-bin.000003
恢复备份:
mysql> SET SESSION sql_log_bin=0; 关闭还原时产生的二进制日志
mysql> SOURCE /tmp/tpweb-2015-06-16.sql
…
mysql> SET SESSION sql_log_bin=1; 恢复完成后开启二进制日志
不然就只能去编辑配置文件去关闭二进制日志,用命令行还原
1. 恢复完全备份
mysql -uroot -hlocalhost -p < /tmp/tpweb-2015-06-16.sql
2. 恢复增量备份
mysql -uroot -hlocalhost -p < /tmp/mysql-bin.000003
当数据未备份却进行了误操作,可通过二进制日志进行恢复
例:误删除了数据库
1. 备份二进制日志文件到安全的位置上
mysqlbinlog /data/mysql-bin.000003 > /tmp/mysql-bin.000003
2. 用 vimmysql-bin.00000 3
找到最近一次的误操作并删除 , 并用 mysql -uroot -hlocalhost -p < /tmp/mysql-bin.00000 3 恢复
或
1. 先用 mysqlbinlog/data/mysql-bin.000003 看下误操作的 position 即 at 后面的数字
mysqlbinlog --stop-position 7295 /data/mysql-bin.000003 > /tmp/mysql-bin.000003
2.mysql -uroot -hlocalhost-p < /tmp/mysql-bin.000003
cp , tar 等文件系统工具:物理备份工具,冷备份,一般用于 MyISAM 备份,支持完全备份和部分备份
lvm2 快照 :几乎热备份,借助 cp , tar 等文件系统工具实现物理备份 , 需备份事务日志
ibbackup :对于 InnoDB 支持热备份,需向 IBM 缴费
xtrbackup: 对 InnoDB 热备份,支持完全和增量备份
对 MyISAM 温备份,只支持完全备份
官网下载 percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm
yum install yum install perl-DBD-MySQL
yuminstall percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm
MySQL 复制
复制功用
数据分布、负载均衡、备份、高可用和故障切换、 MySQL 升级测试,
要想实现复制,需在主节点开启二进制日志
主从复制:
从服务器:
I/O 线程:从 master 请求二进制日志信息,并保存至 slave 中继日志
SQL 线程:从 [relay log] 中继日志中读取日志信息,在本地完成重放
默认工作在异步 [async] 模式
存在的问题:
1. 主从数据不一致
2. 从服务器数据落后主服务器
双主模型:
读:负载均衡
写:都要写入,无法均衡
复制时应注意的问题
1. 主节点运行很久后要加入从节点
在主节点上做一个完全备份,并记录二进制日志文件及 position 位置
在从节点恢复此完全备份,并在启动复制是从记录的二进制日志文件 position 位置开始
2. 如何限制从服务器只读
在从服务器启动 read_only :但仅对非具有 SUPER 权限的用户有效
阻止所有用户: MariaDB> flush tables with read lock
3. 如何保证主从复制时事务安全
在 master 节点上启用参数:
sync_binlog=on
若用到的为 InnoDB 存储引擎:
启用 Innodb_flush_logs_at_trx_commit 表示一旦提交了事务立即同步到磁盘上
Innodb_support_xa=on
在 slave 节点上:
skip_slave_start=on 表示跳过 slave 自动启动
主节点在不考虑磁盘 IO 的情况下:
snyc_master_info=1
从节点在不考虑磁盘 IO 的情况下 :
snyc_master_info=1
snyc_relay_log=1
sync_relay_log_info=1
3. 为了避免从服务器数据落后主服务器,可以使用 半同步复制
主节点:
安装插件,文件在 /usr/local/mysql/lib/plugin 下
MariaDB> install plugin rpl_semi_sync_master soname‘semisync_master.so‘
show global variables like ‘%semi%‘ 可以查看安装后 semisync 的状态
编辑配置文件:
rpl_semi_sync_master_enabled =on 开启半同步
rpl_semi_sync_master_timeout=2000 超时时间设为 2000 毫秒
从节点:
安装插件
MariaDB> install plugin rpl_semi_sync_slave soname‘semisync_slave.so‘
show global variables like ‘%semi%‘ 可以查看安装后 semisync 的状态
编辑配置文件:
rpl_semi_sync_slave_enabled =on 开启半同步
重启 slave 线程
stop slave io_thread;
start slave io_thread;
使用 show global status like ‘%semi%‘ 查看状态是否启动
4. 复制过滤器,不复制所有数据库,只复制指定的数据库
(1) 主服务器仅向二进制日志中记录有特定数据库相关的写操作
问题:即时点还原将无法实现
binlog_do_db = 数据库白名单
binlog_ignore_db = 数据库黑名单
(2) 从服务器的 SQL_THREAD 仅在中继日志中读取特定数据相关的语句并应用在本地
问题:会造成网络带宽和磁盘 IO 的浪费
Replicate_Do_DB: 数据库白名单
Replicate_Ignore_DB: 数据库黑名单
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: 用通配符匹配
Replicate_Wild_Ignore_Table:
例:
先 stop slave;
set global Replicate_Do_DB=tpweb;
在 start slave;
5. 基于 SSL 的复制
前提:支持 SSL
用 show global variables like ‘%ssl%‘; 查看 ssl 状态,在编译时指定开启 ssl
MariaDB[(none)]> show global variables like ‘%ssl%‘;
+---------------+----------+
|Variable_name | Value |
+---------------+----------+
| have_openssl |ENABLED |
| have_ssl | ENABLED |
| ssl_ca | ca 证书路径 |
| ssl_capath | 一堆 ca 证书路径 |
| ssl_cert | ca 给自己发的证书 |
| ssl_cipher | 加密算法 |
| ssl_key | 私钥 |
+---------------+----------+
(1) 主服务器配置证书和私钥,并创建一个要求必须使用 SSL 连接的复制账号 ( 使用 REQUIRE SSL)
(2)SLAVE 端连接 MASTER 时,使用 MASTER_SSL 相关的选项来配置证书等信息
6. 跟复制有关的文件
在从节点上的 master.info 用来保存主从连接时的相关信息,如账号,密码 …
relay-log.info :保存了当前 slave 节点上已经复制的当前二进制日志和本地中继日志的对应关系,即 position 位置
7. 复制的监控和维护
(1) 清理日志:使用 PURGE
(2) 监控复制:
show master status
show binlog events
show binary logs
show slave status
(3) 如何判断 slave 是否落后于 master
用 show slave status 中的 Seconds_Behind_Master: NULL 来观察
(4) 如何确定主从节点数据是否一致
①在创建表时启用 checksum ,之后可以通过 show table status 查看主从节点同一张表的 checksum 是否一致
②使用 percona-tools 中的 pt-table-checksum
(5) 数据不一致时的修复方法:
重新复制 master 节点的数据,重新同步
配置 MySQL 主从 :
例: 172.16.37.20 做主节点, 172.16.37.21 做从节点
master :
1. 启用二进制日志
2. 设置一个在当前集群中唯一的 server-id
3. 创建一个有复制权限 [REPLICATIONSLAVE,REPLICATION CLIENT] 的账号
在 mysql 命令行中执行
MariaDB [(none)]> grant replicationclient,replication slave on *.* to ‘testuser‘@‘172.16.%.%‘ identified by‘123456‘;
slave :
1. 启动中继日志,注释二进制日志
2. 设置一个在当前集群中唯一的 server-id
3. 不允许 client 写入操作
4. 利用有复制权限的用户账号连接至主服务器,并启动复制线程
MariaDB [(none)]> change master tomaster_host=‘172.16.37.20‘,master_user=‘testuser‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=419,master_connect_retry=5,master_heartbeat_period=2;
master_connect_retry=5 :表示每 5s 重连一次
master_heartbeat_period=2 :表示每 2s 探测一次 master 的健康状态
在 master 中查出 master_log_pos 的值;
5. 用 show slavestatus 查看 slave 状态,并用 startslave; 启动线程
MariaDB[(none)]> show slave status\G
***************************1. row ***************************
Slave_IO_State:
Master_Host: 172.16.37.21
Master_User: testuser
Master_Port: 3306
Connect_Retry: 5
Master_Log_File:master-bin.000001
Read_Master_Log_Pos: 419
Relay_Log_File:mysql-relay.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:master-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 419
Relay_Log_Space: 245
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert:No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
测试:在 master 创建数据库,在 slave 上查看是否复制成功
双主模型:
互为主从:
1. 数据可能不一致;
2.id 有 auto_increment 定义一个节点使用基数 id ,另一节点使用偶数 id ,但会产生 id 不连贯
用 show global variables like ‘%increment%‘; 查看偏移量
auto_increment_offset 表示从几开始
auto_increment_increment 表示一次增加几
定义一个节点使用基数 id
set @@auto_increment_offset=1;
set @@auto_increment_increment=2;
定义一个节点使用偶数 id
set @@auto_increment_offset=2;
set @@auto_increment_increment=2;
配置:
1. 各自使用不同的 server-id
172.16.37.20 :
172.16.37.21 :
2. 都启用二进制日志和中继日志
172.16.37.20 :
172.16.37.21 :
3. 解决自增字段问题
172.16.37.20 :
172.16.37.21 :
4. 都授权有复制权限的账号
172.16.37.20/21 都执行授权
MariaDB [(none)]> grant replicationclient,replication slave on *.* to ‘testuser‘@‘172.16.%.%‘ identified by‘123456‘;
5. 各自把对方指定为主服务器
172.16.37.20
先 showmaster status; 查看
再指定 master 为对方
MariaDB [(none)]> change master to master_host=‘172.16.37.21‘,master_user=‘testuser‘,master_password=‘123456‘,master_log_file=‘mysq2l-bin.000001‘,master_log_pos=424,master_connect_retry=5,master_heartbeat_period=2;
再 show slave status \G 查看 slave 状态,并用 startslave; 启动线程
172.16.37.21
先 showmaster status; 查看
再指定 master 为对方
MariaDB [(none)]> change master to master_host=‘172.16.37.20‘,master_user=‘testuser‘,master_password=‘123456‘,master_log_file=‘mysql-bin.000001‘,master_log_pos=424,master_connect_retry=5,master_heartbeat_period=2;
再 show slave status \G 查看 slave 状态,并用 startslave; 启动线程
测试:在两个节点上创建数据库,并在对方的 Client 上查看
读写分离:
为了实现读写分离,写操作都发往 master ,读都发往 slave
读写分离器:
mysql-proxy 测试版 --> 奇虎 360 的二次开发版 (atlas)
amoeba 淘宝研发的
dbrelay 商业产品
mysql-proxy:
先配置主从服务器
master : 172.16.37.20
1. 启用二进制日志
2. 设置一个在当前集群中唯一的 server-id
3. 创建一个有复制权限 [REPLICATIONSLAVE,REPLICATION CLIENT] 的账号
在 mysql 命令行中执行
MariaDB [(none)]> grant replicationclient,replication slave on *.* to ‘testuser‘@‘172.16.%.%‘ identified by‘123456‘;
slave : 172.16.37.21
1. 启动中继日志,注释二进制日志
2. 设置一个在当前集群中唯一的 server-id
3. 不允许 client 写入操作
4. 利用有复制权限的用户账号连接至主服务器,并启动复制线程
MariaDB [(none)]> change master tomaster_host=‘172.16.37.20‘,master_user=‘testuser‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=419,master_connect_retry=5,master_heartbeat_period=2;
master_connect_retry=5 :表示每 5s 重连一次
master_heartbeat_period=2 :表示每 2s 探测一次 master 的健康状态
在 master 中查出 master_log_pos 的值;
5. 用 show slavestatus 查看 slave 状态,并用 startslave; 启动线程
再设置登录用户 grant all on *.* to ‘lx‘@‘%‘ identified by‘123456‘;
配置 mysql-proxy 代理服务器, 172.16.37.9
epel 源: yum -yinstall mysql-proxy
使用官方二进制格式文件中的 rw-splitting.lua 脚本进行读写分离
配置文件: /etc/mysql-proxy.cnf
[mysql-proxy]
daemon= true
pid-file= /var/run/mysql-proxy.pid
log-file = /var/log/mysql-proxy.log # 可以选择关闭日志,因为记录的非常详细,占用磁盘空间
log-level = debug
max-open-files= 1024
plugins= admin,proxy
user= mysql-proxy
#
#ProxyConfiguration
proxy-address = 0.0.0.0:3306 # 代理服务监听的地址和端口
proxy-backend-addresses =172.16.100.7:3306 # 支持读写操作的后端 mysql
proxy-read-only-backend-addresses =172.16.100.8:3306 # 只读后端
#proxy-lua-script=
proxy-lua-script =/usr/lib64/mysql-proxy/lua/rw-splitting.lua # 读写分离脚本,可以注释使用默认
#proxy-skip-profiling= true
#
#Admin Configuration
admin-address = 0.0.0.0:4041 # 默认管理程序监听端口
admin-lua-script= /usr/lib64/mysql-proxy/lua/admin.lua
admin-username = admin # 管理用户
admin-password = admin # 管理密码
启动服务: /etc/init.d/mysql-proxy start
测试: mysql -h172.16.37.9 -ulx -p123456 -e ‘create database proxy-test‘; 然后去主从服务器上查看是否创建了此 database
复制的问题和解决方案:
(1) 数据损坏或丢失
从服务器崩溃回去找: master.info
最好在从节点上启动
snyc_master_info=1
snyc_binlog=1
但会增加 IO 压力
(2) 建议不要混合使用存储引擎,可以考虑都使用 InnoDB
(3) 注意要使用唯一的 serverid
(4) 复制延迟
重启从服务器的 slave 线程,要确保当前从服务器没有正在进行的请求和长连接
数据库切分: sharding
垂直切分:按表进行切分
水平切分:按表中的行进行的切分
切分条件:读集中,写离散
MySQL 数据库的向外扩展方式:复制、拆分、 sharding
拆分方式:
按功能拆分:也即按应用拆分;
数据分片:
选择切分键
把数据存储在哪里?
从哪儿读数据?
数据库切分框架:
cobar
gizzard
HibernatShards
HiveDB
本文出自 “xxlixin” 博客,请务必保留此出处http://xxlixin1993.blog.51cto.com/9498220/1664432
原文:http://xxlixin1993.blog.51cto.com/9498220/1664432
内容总结
以上是互联网集市为您收集整理的MySQL和MariaDB 备份 主从 读写分离全部内容,希望文章能够帮你解决MySQL和MariaDB 备份 主从 读写分离所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。