mysql主从复制生产环境搭建

传统方式：

主库操作：

修改/etc/my.cnf:
1、log_bin=fpc ---binlog文件的前缀 , 启用binlog
2、server-id=1
3、binlog_do_db=jxcms  ---指定的数据库产生binlog

4、修改数据库root密码：
mysqladmin -uroot password 123456
5、
grant replication slave on *.* to 'repl'@'%' identified by 'repl@123';

备库操作：

修改/etc/my.cnf:
从数据库不用启用binlog
1、server-id=2   
2、修改数据库root密码：
mysqladmin -uroot password 123456
3、CHANGE MASTER TO 
MASTER_HOST='192.168.0.104',
MASTER_USER='repl',
MASTER_PASSWORD='repl@123',
MASTER_LOG_FILE='fpc.000001',
MASTER_LOG_POS=245,
master_port=3306;
4、start slave(备库启动io线程和sql线程)

导出sql:mysqldump -uroot -p123456 jxcms > jxcms.sql

导入sql：mysql -uroot -p123456 jxcms < jxcms.sql

读写分离基本原则是读写往从库，写发往主库。首先明确sql读写的定义。中间件通过解析sql,确定sql读写属性。读sql包括select,set,show,explain,usedb语句，其他的sql识别为写sql。

单台mysql性能瓶颈，如何击碎性能瓶颈:

单台服务器上加cpu，加固态硬盘，做raid磁盘阵列，raid5 8块硬盘变成1块，写8gb数据，每块各写1g
主从同步，从数据库要开启sql线程将中继日志翻译成sql语句，mysql5.7支持多个sql线程同时翻译,但是服务器如果核心不够，假如1块核心，也无法多线程翻译，所以加cpu

运维在mysql集群解决就两个问题：主从切换，主从不一致

传统方式有个致命确点就是主从切换，因为是主从肯定是高可用，不然主挂了咋搞，肯定要对主做高可用，这时问题就来了：

a,b两台主机,a是主，b是从
a挂了，切到b做为主，后面a恢复，怎么弄？第一种将b做为主,如果后面b又挂了咋弄，第二种将a做为主，不管怎么样，都会遇到一台比另一台数据多，主从切换的时候这时只有将多的一方导出来，将少的一方数据删掉导入，然后重新建立主从关系，很混乱
这就是主从切换带来的问题。
所以引入GTID模式

GTID模式的主从同步：原理是基于主从之间基于差异化同步，这时候无论怎么切换都可以自动同步了，而传统的方式是需要指定binlog的位置来同步，gitd模式是基于事务事件，不根据binlog的位置来同步

每个非查询的sql都是一个事件，一个事务，这些事件在写入binlog的时候会在事件里加上事务id，事务id由这台数据库的uuid值加上事务的编号，每个sql都是一个事务，如图中的36:1

192.168.3.8 主库

192.168.3.7从库

需要再配置中开启gtid模式

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
# query_cache_type=1

slow_query_log=ON
slow_query_log_file='/var/lib/mysql/slow-sql.log'
long_query_time=2

validate_password_length=6
validate_password_policy=LOW


log-bin=jx
server-id=36
binlog_do_db=jxcms
binlog_do_db=jxsy
binlog_do_db=jxtk
binlog_do_db=tpcc_test

character-set-server=utf8
collation-server=utf8_general_ci

gtid-mode=on #开启gtid模式
binlog-format=row #默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates=1 #从库binlog记录主库同步的操作日志
skip-slave-start=1 #跳过slave复制线程
enforce-gtid-consistency=on #强制gtid一致性，开启后对特定的create table不支持

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=utf8

可以看到多了一列gtid

create database jxsy;

创建主从复制的用户，两台都要执行，主从切换都可以是主
grant all on *.* to "repl"@"%" identified by "12345678";

重置gtid：
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------+----------+---------------------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB              | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+---------------------------+------------------+-------------------+
| fpc.000001 |      154 | jxcms,jxsy,jxtk,tpcc_test |                  |                   |
+------------+----------+---------------------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

在从库执行：
change master to  master_host='192.168.3.8' ,master_user='repl',master_password='12345678',master_auto_position=1  #这个不产生事务id

其中

master_auto_position=1的意思是，自动根据事务编号同步，具体含义就是我先向主库发我现在有什么，然后主库会回我缺什么，这个1是固定的

主库执行：drop database jxsy

从库执行：
mysql> start slave;
执行show slave status;

可以看到很快就同步了主库的事务1，从库中show maste status可以看到是gitd_set中的uuid值就是主库uuid

 Retrieved_Gtid_Set: 0f97ad80-3c39-11ef-8e68-000c292cc3e1:1 #这是io线程写到中继日志的事务id
 Executed_Gtid_Set: 0f97ad80-3c39-11ef-8e68-000c292cc3e1:1   #这是sql线程执行sql的事务id
中继日志就是缓冲，因为sql执行很慢，先将sql都写到中继日志中，后面慢慢执行

Seconds_Behind_Master: 0 这是比主库慢了多少秒，正在执行主多少秒的事件

这时候假设主库挂了，192.168.3.7成为主库

这时候在192.168.3.7上执行sql：

可以看到gtid那列，原本的38的事务id1,和自己的事务id1

mysql> show master status;
+------------+----------+---------------------------+------------------+--------------------------------------------------------------------------------+
| File       | Position | Binlog_Do_DB              | Binlog_Ignore_DB | Executed_Gtid_Set                                                              |
+------------+----------+---------------------------+------------------+--------------------------------------------------------------------------------+
| fpc.000001 |      454 | jxcms,jxsy,jxtk,tpcc_test |                  | 0f97ad80-3c39-11ef-8e68-000c292cc3e1:1,
6ba9bae6-3c35-11ef-bf39-000c292ac79a:1 |
+------------+----------+----------------

后来192.168.3.8恢复了，就充当从库

192.168.3.7 主 192.168.3.8从库

192.168.3.8 上执行
change master to  master_host='192.168.3.7' ,master_user='repl',master_password='12345678',master_auto_position=1   #这个不产生事务id

start slave;

mysql> show master status;
+------------+----------+---------------------------+------------------+--------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+---------------------------+------------------+--------------------------------------------------------------------------------+
| fpc.000001 | 454 | jxcms,jxsy,jxtk,tpcc_test | | 0f97ad80-3c39-11ef-8e68-000c292cc3e1:1,
6ba9bae6-3c35-11ef-bf39-000c292ac79a:1 |
+------------+----------+---------------------------+------------------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

可以看到192.168.3.8同步了192.168.3.7上的事务id1

总之，gtid模式，解决主从切换的数据同步问题。

压测：

tpcc-mysql是percona基于TPC-C(下面简写成TPCC)衍生出来的产品，专用于MySQL性能测试。

导入tpcc_test

安装tpcc_load和tpcc_start

主库执行：

tpcc_start -h192.168.3.8 -d tpcc_test -ujxtest -p123456 -w 10 -c 32 -r 60 -l 300

1、初始化表结构及测试数据

#创建tpcc100库，初始化DDL

mysqladmin create tpcc100
mysql -f tpcc100 < create_table.sql
mysql -f tpcc100 < add_fkey_idx.sql

#初始化测试数据，加载100个仓库的测试数据，仓库数越大，测试数据也越大，整个过程也越慢，需要根据服务器配置适当调整

tpcc_load localhost tpcc tpcc_user tpcc_passwd 100

#下面的例子中，模拟对100个仓库(-w 100)，并发128个线程(-c 128)，预热5分钟(-r 300)，持续压测1小时(-l 3600)

tpcc_start -hlocalhost -utpcc_user -ptpcc_password -d tpcc100 -w 100 -c 128 -r 300 -l 3600

可以看到retived_gtid_set与主库的executed_gtid_set值能保持实时同步，说明从主库的binlog写到从库的relaylog 这个是实时的

真正耗费时间的是从库的executed_gtid_set 落后retived_gtid_set 好多事件了，真正耗费时间是在执行sql，这是从库的sql线程做的事

开启两个sql线程：

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-bin=jx
server-id=37

validate_password_length=6
validate_password_policy=LOW

character-set-server=utf8
collation-server=utf8_general_ci

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

gtid-mode=on
enforce-gtid-consistency=on
binlog-format=row
log-slave-updates=1
skip-slave-start=1

sync_binlog = 1
slave_parallel_workers=2
innodb_flush_log_at_trx_commit = 1
slave_preserve_commit_order = 1
slave_parallel_type = LOGICAL_CLOCK

[client]
default-character-set=utf8

 在从库执行：show full processlist;
非system user都是客户端线程
system user都是数据库的线程
system user有四个线程： wating for master to send envent 是io线程，同步binlog；
slave has read all relay log;waiting for more updates;这是调度线程，分配任务给sql线程执行
system lock和waiting for an event from coordinator是两个sql线程，并发执行

可看出开启两个sql线程执行的时候基本无延迟，这是解决主从不一致的一种方法，但是真正耗费时间的是在执行sql上面，如果一条sql执行很慢，主库已经执行完毕了，但从库先拿到sql，假设拿到sql没耗费时间，再在从库执行sql, 相差sql的执行时间

所以不管怎么样都无法真正解决主从不一致的问题：

可以从优化慢sql来，开启慢sql

slow_query_log=ON
slow_query_log_file='/var/lib/mysql/slow-sql.log'
long_query_time=2

找到慢sql，优化sql语句才是解决主从不一致的途径，

gtid模式对主从切换不影响，是根据两个数据差异来同步数据的，所以不影响，无论什么时候切换，最终都会和主库一样。

重置gtid处理值

mysql> STOP slave;##停止主从
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> RESET SLAVE ALL;##从新设置主从
Query OK, 0 rows affected (0.00 sec)
mysql> RESET MASTER;##重新设置以复制状态集
Query OK, 0 rows affected (0.02 sec)
mysql> change master to master_host='10.0.0.161',master_user='用户',master_password='密码不',master_port=3306,master_auto_position=1;##从新建立gtid
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> START SLAVE;##启动复制
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G;

posted @ 2024-05-12 17:03 *Tesla* 阅读(30) 评论(0) 收藏举报

刷新页面返回顶部

xiaofang.bk

mysql主从复制生产环境搭建

传统方式：

运维在mysql集群解决就两个问题：主从切换，主从不一致

GTID模式的主从同步：原理是基于主从之间基于差异化同步，这时候无论怎么切换都可以自动同步了，而传统的方式是需要指定binlog的位置来同步，gitd模式是基于事务事件，不根据binlog的位置来同步

压测：

公告