MySQL主从复制和读写分离
什么是主从复制？
主从复制就是MySQL主服务器和MySQL从服务器进行数据同步（从服务器同步主服务器的数据）什么是读写分离？
读写分离就是读取数据在一台服务上，写入数据在别的服务器上，实现读取和写入数据分开，缓解服务器压力，读写分离离不开主从复制，是要在主从复制的基础上进行一、MySQL主从复制
主从复制的原理：
1.Master在每个事务更新数据完成前，在二进制日志中记录这些变化，写入二进制文件完成后通知存储引擎提交事务
2.Slave从服务器将Master的Binary log复制到其中继日志，开启一个I/O线程，在Master主服务器上建立一个普通连接，从Master主服务器的二进制日志上读取事件，并记录到自己的中继日志上（会不断等待随着主服务器更新）
3.SQL slave thread（SQL从线程）从中继日志上读取事件，重放这些事件，实现与Master主服务器同步
主从复制配置
环境准备
三台服务器都安装好mysql，实现一主两从（如下图）
1.配置master
master配置文件添加以下：
vim /etc/my.conf
---------------------------
server-id = 1
log-bin = master-bin
log-slave-updates = true
创建slave使用的账号和密码授权
grant replication slave on *.* to 'myslave'@'192.168.31.%' identified by '123456';
2.配置slave从服务器
slave配置文件添加以下
vim /etc/my.conf
---------------------------
server-id = 2
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
3.查看master的master_log_file和master_log_pos的值
master_log_file：Master主服务器二进制日志名
master_log_pos：二进制日志位置变量
mysql> show master status;
+-----------------+----------+--------------+------------------+-----------------+
|File | Position | Binlog_Do_DB | Binlog_Ignore_DB |Executed_Gtid_Set|
+-----------------+----------+--------------+------------------+-----------------+
|master-bin.000001| 451 | | | |
+-----------------+----------+--------------+------------------+-----------------+
1 row in set (0.00 sec)
在两个slave上执行同步语句，修改ip地址和查询出来的master_log_file和master_log_pos的值，以及之前创建授权的mysql账号和密码
change master to master_host='192.168.31.101',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_r_log_pos=451;
注意：同步好了之后重启两台slave的mysql
systemctl resatrt mysqld
4.在slave登录mysql查看数据同步状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.101
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 451
Relay_Log_File: relay-log-bin.000003
Relay_Log_Pos: 321
Relay_Master_Log_File: master-bin.000001
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: 451
Relay_Log_Space: 526
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: 1e28af2a-bc84-11ec-a108-000c295d37fa
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
1 row in set (0.00 sec)
确保以下两个值都是Yes那么就主从复制配置成功了
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试结果
在Master主服务器上创建一个库创建一个表添加数据，在从服务器上查看是否同步
master主服务器：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.06 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table master(ip_address char(32),info char(32));
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| master |
+----------------+
1 row in set (0.00 sec)
mysql> insert into master(ip_address,info) values('192.168.31.101','我是master');
Query OK, 1 row affected (0.13 sec)
slave从服务器
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| master |
+----------------+
1 row in set (0.00 sec)
mysql> select * from master;
+----------------+--------------+
| ip_address | info |
+----------------+--------------+
| 192.168.31.101 | 我是master |
+----------------+--------------+
1 row in set (0.00 sec)
说明同步成功了！！！
配置遇到的坑
1.主从同步，从服务器server_uuid重复
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_Running: No
Slave_SQL_Running: Yes
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid/server_id as this slave has connected to the master; the first event 'mysql-bin.000001' at 154, the last event read from './mysql-bin.000001' at 448, the last byte read from './mysql-bin.000001' at 448.'
查看所有从服务器的id信息，是否有相同的
mysql> show variables like '%server%id%';
+----------------+--------------------------------------+
| Variable_name | Value |
+----------------+--------------------------------------+
| server_id | 2 |
| server_id_bits | 32 |
| server_uuid | f7d4244c-fc58-11ec-8e35-000c29c84ec0 |
+----------------+--------------------------------------+
3 rows in set (0.00 sec)
如果有server_id相同则在my.cnf配置文件修改再重启mysql服务
如果有server_uuid相同则删除my.cnf配置文件中记录的datadir目录下的auto.conf文件
然后重启mysql服务会自动生成新的server_uuid
二、MySQL读写分离
Amoeba实现读写分离
安装配置amoeba
1.下载安装jdk
[root@test renjie]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)
2.解压amoeba安装包
mkdir /usr/local/amoeba
tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
3.三台mysql服务器上授权账号
grant all on *.* to test@'192.168.31.%' identified by '123456';
4.配置amoeba.xml配置文件
vim /usr/local/amoeba/conf/amoeba.xml
----------------------------------------
<property name="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<property name="user">amoeba</property>
<property name="password">123456</property>
修改读写池
vim /usr/local/amoeba/conf/amoeba.xml
----------------------------------------
<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>
<property name="needParse">true</property>
</queryRouter>
5.配置dbServers.xml配置文件
vim /usr/local/amoeba/conf/dbServers.xml
------------------------------------------
<!-- mysql user -->
<property name="user">root</property>
<property name="password">123456</property>
修改配置读写池
vim /usr/local/amoeba/conf/dbServers.xml
------------------------------------------
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.31.101</property>
</factoryConfig>
</dbServer>
<dbServer name="slave1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.31.102</property>
</factoryConfig>
</dbServer>
<dbServer name="slave2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.31.103</property>
</factoryConfig>
</dbServer>
<dbServer name="slaves" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave1,slave2</property>
</poolConfig>
</dbServer>
6.启动服务并查看是否启动成功
[root@test renjie]# /usr/local/amoeba/bin/amoeba start
log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2022-07-06 02:46:58,356 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2022-07-06 02:46:58,561 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2022-07-06 02:46:58,564 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:36018.
查看默认8066端口是否启动成功
[root@test ~]# netstat -anpt |grep java
tcp6 0 0 :::8066 :::* LISTEN 20203/java
tcp6 0 0 127.0.0.1:36018 :::* LISTEN 20203/java
tcp6 0 0 192.168.31.110:33510 192.168.31.102:3306 ESTABLISHED 20203/java
tcp6 0 0 192.168.31.110:37528 192.168.31.103:3306 ESTABLISHED 20203/java
tcp6 0 0 192.168.31.110:56310 192.168.31.101:3306 ESTABLISHED 20203/java
测试结果
1.使用amoeba的8066端口登录测试
[root@test ~]# mysql -uamoeba -p123456 -h 192.168.31.110 -P 8066
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 594202065
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution
Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2.测试读写分离功能
先关闭主从复制，分别在三台mysql创建test库，whoami表，添加不一样的数据，查询该表数据，看显示结果
master服务器：
mysql> use test
Database changed
mysql> create table whoami (ip char(32),name char(32));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into whoami(ip,name) values('192.168.31.101','我是master');
Query OK, 1 row affected (0.00 sec)
mysql> select * from whoami;
+----------------+--------------+
| ip | name |
+----------------+--------------+
| 192.168.31.101 | 我是master |
+----------------+--------------+
1 row in set (0.00 sec)
slave1服务器：
mysql> use test
Database changed
mysql> create table whoami (ip char(32),name char(32));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into whoami(ip,name) values('192.168.31.102','我是slave1');
Query OK, 1 row affected (0.00 sec)
mysql> select * from whoami;
+----------------+--------------+
| ip | name |
+----------------+--------------+
| 192.168.31.102 | 我是slave1 |
+----------------+--------------+
1 row in set (0.00 sec)
slave2服务器：
mysql> use test
Database changed
mysql> create table whoami (ip char(32),name char(32));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into whoami(ip,name) values('192.168.31.103','我是slave2');
Query OK, 1 row affected (0.01 sec)
mysql> create table whoami (ip char(32),name char(32));
ERROR 1050 (42S01): Table 'whoami' already exists
mysql> select * from whoami;
+----------------+--------------+
| ip | name |
+----------------+--------------+
| 192.168.31.103 | 我是slave2 |
+----------------+--------------+
1 row in set (0.00 sec)
登录192.168.31.110的amoeba服务查询test.whoami的数据,插入一条数据
mysql> insert into whoami(ip,name) values('192.168.31.110','我是amoeba');
Query OK, 1 row affected (0.03 sec)
mysql> select * from test.whoami;
+----------------+--------------+
| ip | name |
+----------------+--------------+
| 192.168.31.102 | 我是slave1 |
+----------------+--------------+
1 row in set (0.00 sec)
mysql> select * from test.whoami;
+----------------+--------------+
| ip | name |
+----------------+--------------+
| 192.168.31.103 | 我是slave2 |
+----------------+--------------+
1 row in set (0.01 sec)
发现查询的数据是slave1和slave2的数据，没有查询到插入的数据
然后是测试查询写入数据
登录192.168.31.101查询test.whoami表
mysql> select * from test.whoami;
+----------------+--------------+
| ip | name |
+----------------+--------------+
| 192.168.31.101 | 我是master |
| 192.168.31.110 | 我是amoeba |
+----------------+--------------+
2 rows in set (0.00 sec)
发现slave1和slave2没有该数据，登录master查询则有该数据，说明读写分离配置成功！！！
MySQL-Proxy
1.下载解压安装包
tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy
2.创建存放配置文件和日志的目录
mkdir /usr/local/mysql-proxy/logs
mkdir /usr/local/mysql-proxy/conf
3.创建修改配置文件和启动脚本
vim /usr/local/mysql-proxy/conf/mysql-proxy.conf
---------------------------------------------------
[mysql-proxy]
#指定user，可以不指定
user=root
#所有IP的3306端口
proxy-address=0.0.0.0:3306
#设置master的ip，写数据
proxy-backend-addresses=192.168.0.101:3306
#设置slave的ip，读数据
proxy-read-only-backend-addresses=192.168.0.102:3306
#脚本所在位置
lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
#指定存放日志文件路径
log-file=/usr/local/mysql-proxy/logs/msyql-proxy.log
#指定存放pid文件路径
pid-file=/usr/local/mysql-proxy/logs/mysql-proxy.pid
#日志模式
log-level=debug
#打入后台
daemon=true
keepalive=true
4.修改管理进程的脚本
vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
-------------------------------------------------------
min_idle_connections = 1,
max_idle_connections = 2,
5.设置配置文件权限并启动服务
chmod 660 /usr/local/mysql-proxy/conf/mysql-proxy.conf
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
6.查看端口并测试
netstat -antlp | grep mysql-proxy
7.登录数据库读写操作测试