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=2MariaDB [(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