高级数据库技术 MySQL的主从复制和读写分离

一、简介

1、主从复制

MySQL主从复制是一种数据库架构，其中一台MySQL服务器（主服务器）将其数据复制到另一台或多台MySQL服务器（从服务器）上

主服务器上的所有数据变更都会被复制到从服务器上，从而实现数据备份、冗余和扩展等需求。主从复制可用于提高数据可用性、备份恢复、负载均衡等场景。

主从复制方式：

基于二进制文件：二进制日志文件；

基于GTID方式：全局事务标示符，自5.6版本开启的新型复制方式，GTID的组成：前面是server_uuid：后面是一个序列号

例如：

GTID=server_uuid：sequence number

GTID=ac002287-2ecb-11e9-9d50-000c2967cdfb:1

UUID：每个mysql实例的唯一ID，由于会传递到slave，所以也可以理解为源ID。

Sequence number：在每台MySQL服务器上都是从1开始自增长的序列，一个数值对应一个事务。

工作过程：

1、master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。在二进制日志中，会包含ac002287-2ecb-11e9-9d50-000c2967cdfb:

2、slave端的I/O线程将变更的binlog，写入到本地的relay log中。

3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。Sql线程拿到uuid和序列号，与自己本地的二进制日志对比，是否有此项记录

4、如果有记录，说明该GTID的事务已经执行，slave会忽略。

5、如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

6、在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描。

优势：

1、更简单的实现failover，不用以前那样在需要找二进制日志文件log_file和位置值log_pos。

2、比传统的复制更加安全可靠。

3、GTID是连续的没有空洞的，保证数据的一致性，零丢失。  

4、slave无需知道master的pos值和日志文件值，只需要知道master的ip、用户名、密码即可；

2、读写分离原理：

基于程序代码内部实现：

在代码中根据select和insert进行路由分类，性能比较好，无需任何硬件代理条件，缺点是需要开发人员的编写，运维人员无从下手；

基于中间代理层实现：

在客户端与数据库间添加代理层，代理服务器接收到客户端的请求之后，判断请求后，转发给后端的数据库；

mysql-proxy：为mysql的开源项目Atlas，但是mysql官方不建议将其引用在生产环境中；

amoeba：变形虫，由陈思儒开发，Java语言编写而成，再生产环境中amoeba代理层软件是一个不错的选择；

mycat：一个彻底开源的，面向企业应用开发的大数据库集群，支持事务、ACID、可以替代MySQL的加强版数据库；

二、搭建服务环境

操作系统

IP

CentOS7.6

47.95.117.237

CentOS7.6

47.95.117.140

案例步骤：

在master主机上安装ntp时间服务；

在两台slave主机中配置同步master的ntp时间（在此配置相同，在此只列举slave1的配置）；

安装三台Mysql数据库服务器（在此步骤相同，在此只列举master主机一台的安装）；

优化配置Master主机的mysql服务；

优化配置slave1主机的mysql服务；

优化配置slave2主机的mysql服务；

配置master主机上的master角色，配置主节点；

配置两台slave主机的s lave角色，配置从节点（在此配置相同，在此只列举slave1的配置）；

配置Master角色指定主从复制中特定的数据库及优化；

验证Slave角色的数据库复制情况；

查看并设置slave节点sql线程并发数量；

在master主机上安装ntp时间服务；

[root@master ~]# yum -y install ntp

[root@master ~]# sed -i '/^server/s/^/#/g' /etc/ntp.conf

[root@master ~]# cat <<END >>/etc/ntp.conf

server 127.127.1.0

fudge 127.127.1.0 stratum 8

END

[root@master ~]# systemctl start ntpd

[root@master ~]# systemctl enable ntpd

Created symlink from /etc/systemd/system/multi-user.target.wants/ntpd.service to /usr/lib/systemd/system/ntpd.service.

在两台slave主机中配置同步master的ntp时间；

mportant; overflow-wrap: break-word !important;">[root@slave1 ~]# yum -y install ntpdate

[root@slave1 ~]# ntpdate 47.95.117.237

4 Aug 17:27:31 ntpdate[1740]: adjust time server 47.95.117.237 offset 0.000048 sec

[root@slave1 ~]# echo "/usr/sbin/ntpdate 47.95.117.237" >>/etc/rc.local

[root@slave1 ~]# chmod +x /etc/rc.local

安装两台Mysql数据库服务器（在此步骤相同，在此只列举master主机一台的安装）

[root@master~]#wgethttps://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz#卸载原有数据库[root@master ~]# rpm -qa | grep mariadb[root@master ~]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64

#安装依赖包
[root@master ~]# yum install libaio
#创建mysql目录
[root@master ~]# mkdir /data
[root@master ~]# tar -Jxvf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz -C/data

[root@master ~]# groupadd mysql &&useradd -g mysql mysql

[root@masterdata]# mv mysql-8.0.26-linux-glibc2.12-x86_64/ mysql

[root@masterdata]# mysql/bin/mysqld --initialize --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data

2024-12-16T02:01:56.545637Z 0 [System] [MY-013169] [Server] /data/mysql/bin/mysqld (mysqld 8.0.26) initializing of server in progress as process 1673

2024-12-16T02:01:56.573715Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.

2024-12-16T02:01:57.038180Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

2024-12-16T02:01:57.469174Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main

2024-12-16T02:01:57.469414Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main

2024-12-16T02:01:57.572417Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: fdoaqiq)8NVh   ##初始化密码

[root@master mysql]# vim /etc/my.cnf

[client]

port=3306

socket=/tmp/mysql/mysql.sock 

[mysqld]

port=3306

user=mysql

socket=/tmp/mysql/mysql.sock

basedir=/data/mysql

datadir=/data/mysql/data

log-error=error.log

[root@master mysql]# ./support-files/mysql.server start

[root@master bin]# ln -s /data/mysql/bin/mysql /usr/local/bin/mysql ##做一个软链接可以直接通过mysql命令进入库[root@master data]# mysql -u root -p ###若没有注意则可以查看/data/mysql/data/error.log

配置MySQL的root密码

mysql> alter user 'root'@'localhost' identified by '123123';
mysql> exit
[root@master ~]# mysql -uroot -p123123mysql> exit

优化配置Master主机的mysql服务

[root@master ~]# vim /etc/my.cnf##末尾添加

log-bin=master-bin

log-slave-updates

server-id=1

innodb_flush_log_at_trx_commit=2

master_info_repository=table

relay_log_info_repository=TABLE

:wq

注解：

log-bin=master-bin##指定生成二进制文件，可加目录

log-slave-updates##指定开启slave角色的更新

server-id=1##指定id号码

innodb_flush_log_at_trx_commit=2##见下方

master_info_repository=table

relay_log_info_repository=TABLE

优化配置slave主机的mysql服务；

relay-log=relay1-log-bin

relay-log-index=slave-relay1-bin.index

server-id=2

innodb_flush_log_at_trx_commit=2

slave-parallel-type=LOGICAL_CLOCK

slave_parallel_workers=16

master_info_repository=table

relay_log_info_repository=TABLE

:wq

注解：

relay-log=relay-log-bin ##中继日志文件名

relay-log-index=slave-relay-bin.index ##中继日志索引文件

server-id=2 ##id号码

innodb_flush_log_at_trx_commit=2

##指定数据库在存储数据时，事务的刷新方式，如下：

0：该模式速度最快，但不太安全，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。

1：最安全的，默认值，但是最慢是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘。在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务。

2：比0安全，速度比较快，是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

slave-parallel-type=LOGICAL_CLOCK

##选择并行复制的类型

slave_parallel_workers=16

##worker线程数量

master_info_repository=table

##默认是file，master的更新或者slave的更新直接存放在文件内，若出现故障，会出现数据丢失；若改为table，则为表的方式进行存储更新，支持事务，安全性更高；

relay_log_info_repository=TABLE

##指定中继日志更新，提升性能

[root@master mysql]# ./support-files/mysql.server restart

[root@slave1 support-files]# ./mysql.server restart

[root@slave1 support-files]# ls /data/mysql/data/

Ø 配置master主机上的master角色，配置主节点；

[root@master bin]# mysql -uroot -p123123

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id     | 1     |

+---------------+-------+

mysql> CREATE USER 'data'@'%';

mysql> ALTER USER 'data'@'%' IDENTIFIED WITH mysql_native_password BY '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'data'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; 

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

+---------------+----------+--------------+------------------+-------------------+

| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000002 |     1044 |              |                  |                   |

+---------------+----------+--------------+------------------+-------------------+

mysql> create database ksy;

Query OK, 1 row affected (0.01 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| linuxfan           |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

mysql> exit

配置slave主机的slave角色，配置从节点

[root@slave ~]# mysql -uroot -p123123

mysql> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 2     |

+---------------+-------+

mysql>change master to master_host='47.95.117.237',master_user='data',master_password='123123',master_log_file='binlog.000002',master_log_pos=1044;##指定时slave的状态必须处于stop状态

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;

*************************** 1. row ***************************

Slave_IO_State: Waiting for source to send event

Master_Host:47.95.117.237

Master_User:data

 Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000002

 Read_Master_Log_Pos: 1226

Relay_Log_File: relay1-log-bin.000002

Relay_Log_Pos: 503

Relay_Master_Log_File: binlog.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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: 1226

Relay_Log_Space: 711

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: 0##备库复制延迟时间，通过将服务器当前的时间戳与二进制日志中的事件的时间戳对比得到的，所以只有在执行事件时才能报告延迟，但是存在很多问题，如大的事务会导致延迟抖动；

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: 1

Master_UUID: ba647661-bb51-11ef-b77e-000c29fc5d44

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

1 row in set, 1 warning (0.01 sec)

[root@master ~]# vi /etc/my.cnf##末尾添加

binlog-do-db=ksy

binlog-ignore-db=mysql

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

expire_logs_days=7

:wq

注解：

binlog-do-db=ksy##允许同步的数据库名称

binlog-ignore-db=mysql##不允许同步的数据库名称

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

expire_logs_days=7##指定日志存放的时间为7天

[root@master ~]# mysql -uroot -p123123

mysql> show databases

-> ;

+--------------------+

| Database |

+--------------------+

| information_schema |

| ksy |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

mysql> usekey_test;

在主上面执行sql

进行验证：

主:

从：

三、读写分离配置

读写分离是在主从复制的基础上进行的配置工作，可以采用mycat或者其他工具，把mycat 安装在主服务器，使用的默认端口8066，可使用Navicat来操作master,salve和mycat，具体配置过程有以下几个步骤。

步骤 1：部署JDK环境

步骤 2：部署mycat环境

步骤 3：验证读写分离。

1、读写分离配置

安装mycat节点的mycat代理程序；

[root@mycat ~]# tar -zxvf jdk-8u421-linux-x64.tar.gz

[root@mycat ~]# mv jdk1.8.0_421/ /data/java

[root@mycat ~]# vi /etc/profile

export JAVA_HOME=/data/java

export PATH=$PATH:$JAVA_HOME/bin

:wq

[root@mycat ~]# source /etc/profile

[root@mycat ~]# java -version

java version "1.8.0_421"

Java(TM) SE Runtime Environment (build 1.8.0_421-b09)

Java HotSpot(TM) 64-Bit Server VM (build 25.421-b09, mixed mode)

[root@mycat ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

[root@mycat ~]# mv mycat//data

[root@mycat ~]# ls/data/mycat/

bin catlet  conf  lib  logs  version.txt

Ø在master节点进行授权允许mycat程序能够连接Mysql集群，slave节点会同步权限；

[root@master ~]# mysql -uroot -p123123

mysql> CREATE USER 'admin'@'47.95.117.140' IDENTIFIED BY '123123';

Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'47.95.117.140';

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

mysql> exit

Ø配置mycat节点的代理服务并启动服务；

[root@mycat ~]# vi/data/mycat/conf/schema.xml

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100"  dataNode="dn1">

</schema>

<dataNode name="dn1" dataHost="host1" database="ksy" />

<dataHost name="host1" maxCon="1000" minCon="10" balance="0"

  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">

<heartbeat>select user()</heartbeat>

<writeHost host="hostM1" url="47.95.117.237:3306" user="root" password="123123">

<readHost host="hostS1" url="47.95.117.140:3306" user="root" password="123123" />

</writeHost>

</dataHost>

</mycat:schema>

:wq

[root@mycat ~]# vi/data/mycat/conf/server.xml

[root@mycat ~]#/data/mycat/bin/mycat start
Starting Mycat-server...
[root@mycat ~]# netstat -utpln |grep 066
tcp 0      0 0.0.0.0:8066            0.0.0.0:*               LISTEN      1147/java           
tcp 0      0 0.0.0.0:9066            0.0.0.0:*               LISTEN      1147/java

两台数据库节点创建测试数据库（在此列举mysql1节点配置）；

[root@master1 ~]# mysql -uroot -p123123

mysql> create database cloud;

Query OK, 1 row affected (0.00 sec

在Master节点创建测试数据库及表；

[root@master ~]# mysql -uroot -p123123

mysql> create database cloud;

Query OK, 1 row affected (0.00 sec)

mysql> create table cloud.it (id int,name char(16));

Query OK, 0 rows affected (0.02 sec)

mysql> exit

lamp节点客户端测试数据读写分离；

[root@lamp ~]# mysql -uadmin -padmin -h47.95.117.140 -P 8066

lamp节点客户端查看数据读写请求分布；

[root@lamp ~]# mysql -udmin -padmin -h47.95.117.140 -P 9066

mysql> show @@datasource;

打赏

支付宝微信扫一扫，打赏作者吧～

本文链接：https://www.kinber.cn/post/4405.html 转载需授权！

推荐本站淘宝优惠价购买喜欢的宝贝:

 您阅读本篇文章共花了：