MySQL主从复制与读写分离的实现

mysql 主从复制用途

• 实现灾备
• 读写分离，提供查询服务
• 备份，避免影响业务

主从部署必要条件：

• 主库开启binlog日志（设置log-bin参数）
• 主从server-id 不同
• 从库服务器能连通主库

复制原理：

• master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
• slave将master的binary log events拷贝到它的中继日志(relay log)；
• slave重做中继日志中的事件，将更改应用到自己的数据上。

问题及解决方法：

mysql 主从复制存在的问题：
- 主库宕机后，数据可能丢失
- 从库只有一个sql Thread ,主库写压力大，复制很可能延时
解决方法：
- 半同步复制—解决数据库丢失的问题
- 并行复制— 解决从库复制延迟的问题

node1主节点 192.168.243.7

[root@centos7 ~]#vim /etc/my.cnf.d/server.cnf 
  9 [server]
 10 innodb_file_per_table=ON
 11 skip_name_resolve=ON
 12 max_connections=20000    最大连接数根据情况而定

 13 log_bin=master-log
 14 server_id=1

[root@centos7 ~]#systemctl start mariadb.service 
MariaDB [(none)]> show master status;
-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 |      245 |              |                  |
+-------------------+----------+--------------+------------------

MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'repluser'@'192.168.243.%' IDENTIFIED BY 'CENTOS';

MariaDB [(none)]> FLUSH PRIVILEGES;

MariaDB [(none)]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000003 |      497 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

node2 从节点 192.168.243.9

[root@R2 ~]#vim /etc/my.cnf.d/server.cnf 
 9 [server]
 10 skip_name_resolve=on
 11 innodb_file_per_table=on
 12 max_connections=20000
 13 
 14 relay_log=relay-log
 15 server_id=2

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.243.7',MASTER_USER='repluser',MASTER_PASSWORD='CENTOS',MASTER_LOG_FILE='master-log.000003',MASTER_LOG_POS=497;
MariaDB [(none)]> SHOW SLAVE STATUS\G;    查看从节点状态
MariaDB [(none)]> START SLAVE IO_THREAD,SQL_THREAD;   启动线程

此时我们在主服务器上创建一个数据库，可以发现从服务器上显示刚创建的新数据库
主：MariaDB [(none)]> create database mydb;
从：MariaDB [(none)]> show databases;

mysql的读写分离：
     基于ProxySQL 的读写分离，在主数据库上只执行写操作，在从服务器上执行读操作。这样可以分散数据的读写压力。
实验环境： 清空防火墙规则，关闭selinux ,时间同步
三台主机
proxysql :192.168.243.7
node1(主数据库服务器)192.168.243.8
node2(从数据库服务器)192.168.243.9
两台数据库做主从同步

官网下载的proxysql包

[root@centos7 ~]#yum -y install  proxysql-1.4.2-1-centos7.x86_64.rpm 
[root@centos7 ~]#cp /etc/proxysql.cnf{,.bak}
[root@centos7 ~]#vim /etc/proxysql.cnf  

 9 datadir="/var/lib/proxysql"
 10 
 11 admin_variables=
 12 {
 13         admin_credentials="admin:admin"
 14 #       mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
 15         mysql_ifaces="0.0.0.0:6032"
 16 #       refresh_interval=2000
 17 #       debug=true
 18 }
 20 mysql_variables=
 21 {
 22         threads=4          #启动多少个线程链接mysql 
 23         max_connections=2048       #最大并发链接数 
 24         default_query_delay=0          #默认查询延迟
 25         default_query_timeout=36000000        #默认查询超时时间 
 26         have_compress=true        #是否压缩 
 27         poll_timeout=2000 
 28         interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
 29 #       interfaces="0.0.0.0:6033"
 30         default_schema="information_schema"
 31         stacksize=1048576         #栈大小
 32         server_version="5.5.30"
 33         connect_timeout_server=3000    
 36         monitor_username="monitor"
 37         monitor_password="monitor"
 38         monitor_history=600000            #保留的监控历史时长 
 39         monitor_connect_interval=60000    #链接后端主机的超时时间长 
 40         monitor_ping_interval=10000
 41         monitor_read_only_interval=1500
 42         monitor_read_only_timeout=500
 43         ping_interval_server_msec=120000
 44         ping_timeout_server=500
 45         commands_stats=true
 46         sessions_sort=true 
 47         connect_retries_on_failure=10       #连接失败后的重试间隔 
 48 }
 52 mysql_servers =
 53 (
 54         {
 55                 address = "192.168.243.8" 
 56                 port = 3306           
 57                 hostgroup = 0           # no default, required
 58                 status = "ONLINE"     # default: ONLINE
 59                 weight = 1            # default: 1
 60                 compression = 0       # default: 0
 61 #   max_replication_lag = 10 
 62         },
 63         {
 64                 address = "192.168.243.9"
 65                 port = 3306
 66                 hostgroup = 1
 67                 status = "ONLINE"
 68                 weight = 1
 69                 compression = 0
 70         }#注意没有逗号

#定义与数据库连接时使用的账号信息
 90 mysql_users:
 91 (
 92         {
 93                 username = "root" # no default , required
 94                 password = "CENTOS" # default: ''
 95                 default_hostgroup = 0 # default: 0
 96                 max_connections=1000   
 97                 default_schema="mydb"
 98                 active = 1            # default: 1
 99         }
#查询规则
114 mysql_query_rules:
115 (
130 )
#调度器
132 scheduler=
133 (
145 )
148 mysql_replication_hostgroups=
149 (
150         {
156                 writer_hostgroup=0         #写组
157                 reader_hostgroup=1        #读组
159         }
160 )

[root@centos7 ~]#systemctl  restart proxysql.service 
[root@proxy ~]# ss -ntl

看到管理接口的端口是6032， 客户端接口的端口是6033
登陆：

[root@centos7 ~]#mysql -udbadmin -pCENTOS   -h 192.168.243.8
[root@centos7 ~]#mysql -S /tmp/proxysql_admin.sock -uadmin -padmin