最近公司业务量有点大,服务器I/O访问频率过高,之前单节点MySQL有点扛不住压力了,于是我找老板又搞了一台服务器,准备上MySQL的主从复制和读写分离,做多库的存储,提高单个机器的性能,老板欣然同意!
最重要的是,在搭建MySQL读写分离期间,有了这篇水文,很快啊,希望读者大大们耗子尾汁,多多点赞,谢谢朋友们!
在业务复杂的系统中,有这么一个情景,有一句sql语句「需要锁表」,导致「暂时不能使用读的服务」,那么就很影响运行中的业务。
使用「主从复制」,「让主库负责写,从库负责读」,这样,即使主库出现了锁表的情景,通过读从库也可以保证业务的正常运作。
另外,可以做数据的热备份。
再一个,像我们的业务,业务量上来了,需要「做架构的扩展」,做多库的存储,「降低磁盘I/O访问的频率」,提高单个机器的I/O性能。
MySQL 主从复制是指「数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点」。
MySQL 默认采用「异步复制方式」,这样从节点不用一直访问主服务器来更新自己的数据,数据的更新可以在远程连接上进行,从节点可以复制主数据库中的所有数据库或者特定的数据库,或者特定的表。
也就是说:
一个主节点,一个从节点,简单方便,稍后我们就搭建这种一主一从的主从复制模式。
主主复制:「互为主从」
一个节点id使用奇数:
auto_increment_offset=1
auto_increment_increment=2
另一个主节点id使用偶数:
auto_increment_offset=2
auto_increment_increment=2
常用于「扩展系统读取性能」,因为「读是在从库读取」的。
从MySQL 5.7版本开始支持,也叫「多源复制」,数据流向是从多个主库同步数据到一个从库:
多应用于:
在主从复制的基础上,主从之间存在一个级联复制的从服务器,当级联复制的主机复制了主服务器的数据,「级联复制主机充当为主服务器,从服务器复制级联复制主机的数据及二进制日志数据」。
「Tip」:中间级联复制的主机无法将二进制日志传递到其他从服务器上,因此需要加上log_slave_updates
选项,「目的是为了将主服务器的二进制日志文件能写入到从服务器上」。
序号 | 主机名称 | IP地址 | 虚拟机OS | MySQL |
---|---|---|---|---|
1 | mysql-master | 192.168.2.158 | CnetOS 7.8 | 5.7 |
2 | mysql-slave | 192.168.2.159 | CnetOS 7.8 | 5.7 |
还未安装的,直接参考:
如何在CentOS7下快速安装MySQL5.7
CV操作就行了。
两台主机均执行:
mysql> create database laogong;
该数据库用来指定同步的数据库名称。
在「mysql-master」节点下配置 「/etc/my.cnf」 ,在[mysqld]
模块下添加如下内容:
log-bin=master-bin
binlog-format=ROW
server-id=1
binlog-do-db=laogong
「log-bin」:定义二进制文件名称。
「binlog-format」:二进制日志格式,有「row」、「statement」、「mixed」三种格式。
「server-id」:服务器的id,各节点的id必须不一样。
「binlog-do-db」:要同步的数据库名称。
--授权操作
set global validate_password_policy=0;
set global validate_password_length=1;
grant replication slave on *.* to 'root'@'%' identified by '123456';
--刷新权限
flush privileges;
在「mysql-slave」节点配置 /etc/my.cnf
,在 [mysqld]
模块下添加:
log-bin=slave-bin
binlog-format=ROW
server-id=2
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 154 | laogong | | |
+-------------------+----------+--------------+------------------+-------------------+
mysql> change master to master_host='192.168.2.158',master_user='root',master_password='123456',master_port=3306,master_log_file='master-bin.000001',master_log_pos=154;
mysql> start slave;
mysql> show slave status\G
查看slave状态时,可以看到有报错信息:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
这个报错是因为我偷懒,在master主机安装好MySQL以后,我直接克隆的一台slave主机,导致auto.cnf
文件中保存的UUID
是重复的。
解决方法很简单,停掉slave的mysql实例,删除其 datadir
文件夹下的的 auto.cnf
文件,再启动备库实例,此时备库就会产生一个新的 auto.cnf
文件,也就会生成和master的mysql服务不一样的UUID
。
此时再查看slave状态:
show slave status\G
就OK了。
在主库创建一个table:
mysql> use laogong;
Database changed
mysql> create table xblzer(id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)
从库读取:
mysql> use laogong;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_laogong |
+-------------------+
| xblzer |
+-------------------+
1 row in set (0.00 sec)
再在主库添加1条记录:
mysql> insert into xblzer values(1, 'laowang');
在从库查看:
mysql> select * from xblzer;
+------+---------+
| id | name |
+------+---------+
| 1 | laowang |
+------+---------+
1 row in set (0.00 sec)
这样,MySQL一主一从的主从复制架构就搭建起来了。
那么,为了减轻每台MySQL主机的访问压力,还可以对MySQL进行读写分离,实际上,主从复制和读写分离一般就是联合使用的。我们实际生产环境使用的是sharding-jdbc
来实现的读写分离。
还有其他的像「mycat」,「amoeba」等中间件也可以做读写分离,下次有机会再撸「MySQL数据库的读写分离」,本次导航就到此为止,下次再肝。。。
首发公众号 「行百里er」 ,欢迎老铁们关注阅读指正。代码仓库 「GitHub」 github.com/xblzer/JavaJourney
往期推荐
MySQL通过索引优化-这里可能有你不知道的索引优化细节(二)
MySQL通过索引优化-这里可能有你不知道的索引优化细节(一)
MySQL优化必备之执行计划explain,索引基本知识,索引数据结构推演