mysql主从复制读写分离的配置方法详解

一、说明

前面我们说了mysql的安装配置,mysql语句使用以及备份恢复mysql数据;本次要介绍的是mysql的主从复制,读写分离;及高可用MHA;

环境如下:

master:CentOS7_x64 mysql5.721 172.16.3.175 db1
slave1:CentOS7_x64 mysql5.7.21 172.16.3.235 db2
slave2:CentOS7_x64 mysql5.7.21 172.16.3.235 db3
proxysql/MHA:CentOS7_x64 mysql5.7.21 172.16.3.235 proxysql

架构图:

说明:

配置测试时为了方便关闭了防火墙头,selinux安全策略;
现实中请开放防火墙策略;myslqdb的安装已经有脚本一键安装并配置好;这里就不在重复配置;只对对应的角色贴出对应的配置或安装与之相关的软件;

二、主从复制配置

一台主数据库,N从节点;从节点开启两个线程,通过Slave_IO_Running线程和主节点上有权限的账号从主数据库节点复制binlog日志到本地,能过Slave_SQL_Running线程在本地执行binlog日志,达到主从节点内容同步;

master配置:

egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf[mysqld]datadir=/data1/mysqldbsocket=/tmp/mysql.sockkey_buffer_size   = 16Mmax_allowed_packet  = 16Mthread_stack   = 192Kthread_cache_size  = 8query_cache_limit  = 1Mquery_cache_size  = 64Mquery_cache_type  = 1symbolic-links=0innodb_file_per_table=ONskip_name_resolve=ONserver-id  = 1log_bin   = /data1/mysqldb/mysql-bin.log[mysqld_safe]log-error=/usr/local/mysql/logs/error.logpid-file=/data1/mysqldb/mysql.pid!includedir /usr/local/mysql/etc/my.cnf.d

mysql > grant replication client,replication slave on *.* to 'repluser'@'172.16.3.%' identified by 'replpass';mysql > flush privileges;mysql >show master logs;+------------------+-----------+| Log_name   | File_size |+------------------+-----------+| mysql-bin.000001 |  622 |

egrep -v '(^$|^#)' /usr/local/mysql/etc/my.cnf[mysqld]datadir=/data1/mysqldbsocket=/data1/mysqldb/mysql.sockkey_buffer_size   = 16Mmax_allowed_packet  = 16Mthread_stack   = 192Kthread_cache_size  = 8query_cache_limit  = 1Mquery_cache_size  = 64Mquery_cache_type  = 1symbolic-links=0innodb_file_per_table=ONskip_name_resolve=ONserver-id    = 11   #从节点标识ID 各从节点均不一样 relay_log = relay-logread_only=ON[mysqld_safe]log-error=/usr/local/mysql/log/error.logpid-file=/var/run/mysql/mysql.pid!includedir /usr/local/mysql/etc/my.cnf.d

注意:两台从节点的server-id 值不一样;其他的都一样;因此从节点只展示一个配置文件;
登录数据库并同步数据启动slave

两台slave均要同步并启动

mysql > CHANGE MASTER TO MASTER_HOST="172.16.3.175",MASTER_USER="repluser",MASTER_PASSWORD="replpass",MASTER_PORT=3306,MASTER_LOG_FILE="mysql-bin.000001",MASTER_LOG_POS=622;mysql > start slave;  #启动从节点()#查看从节点状态mysql > SHOW SLAVE STATUS;*************************** 1. row ***************************    Slave_IO_State: Waiting for master to send event     Master_Host: 172.16.3.175     #主节点     Master_User: repluser       #同步账号     Master_Port: 3306    Connect_Retry: 60    Master_Log_File: mysql-bin.000001   Read_Master_Log_Pos: 622    Relay_Log_File: relay-log.000001    Relay_Log_Pos: 582  Relay_Master_Log_File: mysql-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    #最后同步的错误 0表示正常同步     Last_Error:      Skip_Counter: 0   Exec_Master_Log_Pos: 622    Relay_Log_Space: 615    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: 0Master_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: 57017c43-36e3-11e8-ac76-080027393fc7    Master_Info_File: /data1/mysqldb/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)ERROR: No query specified

在master导入测试数据;修改数据并查看slave 中的数据是否一致;

[root@db1 ~]# mysql < Testdb.sql登录数据库[root@db1 ~]# mysql -uroot -pmysql> show databases;+--------------------+| Database   |+--------------------+ study    |+--------------------+5 rows in set (0.00 sec)###study测试数据库导入成功mysql> use study;Database changedmysql> show tables;+-----------------+| Tables_in_study |+-----------------+| class   || course   || part   || score   || student   || tb31   || tb32   || teacher   || test1   || test2   || user_info  |+-----------------+11 rows in set (0.00 sec)#删除test1 test2表

mysql> show tables;+-----------------+| Tables_in_study |+-----------------+| class   || course   || part   || score   || student   || tb31   || tb32   || teacher   || user_info  |+-----------------+9 rows in set (0.00 sec)

三、proxysql之读写分离

更多相关文章

随机推荐