mysql主从同步及其优化思路

主从
一主多从
主-从(主)-从 链式
互为主从

Mysql主从同步
两个核心线程
IO / SQL

1>概述:实现数据自动同步的服务结构
2>原理:
Master:启用binlog日志
Slave:
a: slave_IO:复制master主机binlog日志中SQL命令到本机relay-log文件
b:slave_SQL:执行本机relay-log文件里SQL语句,实现与Master数据一致.
3>思路:
配置主服务器:
a:启用binlog日志
b:授权用户(拷贝用户)
C:确认迁移量
配置从服务器:
a:设置server_id
b:确保与主服务器数据一致
c:指定主库信息
d:启用slave程序,查看状态信息

操作实施:
配置主服务器
1 启用binlog日志
Vim /etc/my.cnf
[mysql]
log_bin=日志名 启用binlog日志
Server_id=id值 指定服务器ID号
2 授权用户
mysql> grant replication slave on . to repluser@’%’ identified by “123qqq…A”;
3 确认偏移量
mysql> show master status\G;

配置从库:
1 设置server_id
Vim /etc/my.cnf
[mysqld]
Server_id=服务器ID号

2 确保与主服务器数据一致(库和表的结构)
主库:

mysqldump -uroot -p’密码’ --master-data 数据库名 > /allbak.sql

scp /allbak.sql root@从库IP:/root

从库:

mysql -uroot -p’密码’ 数据库名 < /root/allbak.sql

3 在从库上指定主库信息
Mysql> change master to
->master_host=”主库IP”,
->master_user=”repluser”,
->master_password=”123qqq…A”,
->master_log_file=”mysql99-bin.000001”,
->master_log_pos=154;
4 启用slave程序,查看状态
Mysql> start slave;
Mysql> show slave status\G

Master_host: …主库ip…
Slave_IO_running:Yes
Slave_sql_running:Yes
主从同步排错思路:
mysql主从同步配置文件配置完成后io节点和sql节点仍然为NO
配置过程中:
1、设置slave参数时出现报错(
a、mysql-master bin-log日志是否启用
b、mysql-master和mysql-slave server_id是否指定
c、slave 是否处于启动状态(启动状态设置会出现错误应先执行“stop slave”命令后再配置,配置完成后执行“start slave” 然后查看slave状态“show slave status\G” 如io节点和sql节点出现为两个yes则表示主从成功)

io节点(传输)
1、查看mysql-master端口是否监听,mysql-slave使用telnet测试mysql-master3306端口是否可以连接,如无法连接,查看数据库服务是否启动,iptables是否关闭。
2、在mysql-slave使用mysql-master授权给mysql-slave的用户连接mysql-master测试是否可以连接,如无法连接则查看授权
3、查看配置文件中用户名,密码,binlog日志名称及节点是否正确。(主库每次从前服务,都会重新生成日志)
4、停止再启动slave进程查看
5、克隆的服务器需要对/var/lib/mysql/auto.conf进行修改(修改注意事项,字段长度不变,修改字段内内容随便更改2个字符)

sql节点(执行)
1、主从数据库间数据是否一致(表结构, 库.表)
从库可以有除了主库的其它库表
偏移量
从库执行的 所有在主库执行过的命令

流程
数据库 : 管理数据信息 增 删 改 查 mysql .user 龙哥 很帅(不帅)
读写分离
锁:
表级锁
行级锁

MySQL数据库主从同步延迟优化。
1.MySQL数据库主从同步延迟原理。
答:谈到MySQL数据库主从同步延迟原理,得从mysql的数据库主从复制原理说起,mysql的主从复制都是单线程的操作(mysql5.6版本之前),主库对所有DDL(数据定义语句)和DML(数据语句)产生binlog,binlog是顺序写,所以效率很高;slave的Slave_IO_Running线程会到主库取日志,效率会比较高,slave的Slave_SQL_Running线程将主库的DDL和DML操作都在slave实施。DML和DDL的IO操作是随机的,不是顺序的,因此成本会很高,还可能是slave上的其他查询产生lock争用,由于Slave_SQL_Running也是单线程的,所以一个DDL卡主了,需要执行10分钟,那么所有之后的DDL会等待这个DDL执行完才会继续执行,这就导致了延时。有朋友会问:“主库上那个相同的DDL也需要执行10分,为什么slave会延时?”,答案是master可以并发,Slave_SQL_Running线程却不可以。
DDL(数据定义语句):用于操作对象和对象的属性。
DML(数据语句):用于操作数据库对象中包含的数据。(表记录表)

2.MySQL数据库主从同步延迟是怎么产生的。
答:当主库的TPS并发较高时,产生的DDL数量超过slave一个sql线程所能承受的范围,那么延时就产生了,当然还有就是可能与slave的大型query语句产生了锁等待。
3.MySQL数据库主从同步延迟解决方案
答:最简单的减少slave同步延时的方案就是在架构上做优化(读写分离),尽量让主库的DDL快速执行。还有就是主库是写,对数据安全性较高,比如sync_binlog=1,innodb_flush_log_at_trx_commit = 1 之类的设置,而slave则不需要这么高的数据安全,完全可以讲sync_binlog设置为0或者关闭binlog,innodb_flushlog也可以设置为0来提高sql的执行效率。另外就是使用比主库更好的硬件设备作为slave。
4.MySQL数据库主从同步延迟产生的因素。

  1. 网络延迟
  2. master负载
  3. slave负载
    一般的做法是,使用多台slave来分摊读请求,再从这些slave中取一台专用的服务器,只作为备份用,不进行其他任何操作,就能相对最大限度地达到’实时’的要求了
    另外,再介绍2个可以减少延迟的参数
    –slave-net-timeout=seconds
    参数含义:当slave从主数据库读取log数据失败后,等待多久重新建立连接并获取数据
    slave_net_timeout单位为秒 默认设置为 3600秒
    | slave_net_timeout | 3600
    –master-connect-retry=seconds
    参数含义:当重新建立主从连接时,如果连接建立失败,间隔多久后重试。
    master-connect-retry单位为秒 默认设置为 60秒
    通常配置以上2个参数可以减少网络问题导致的主从数据同步延迟
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值