mysql主从复制读写分离
master和slave时间同步
[[emailprotected]~]#ntpdate 172.18.0.1
[[emailprotected]~]#vim /etc/chrony.conf
[[emailprotected]~]#systemctl start chronyd.service
slave步骤同上确保关闭了iptables和selinux功能
##配置主从复制
master 配置：修改配置文件
[[emailprotected]~]#vim /etc/f.d/f
授权用户MariaDB [(none)]> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO [emailprotected]%’ IDENTIFIED BY ‘123456’;
MariaDB [(none)]> FLUSH PRIVILEGES;
查看二进制日志MariaDB [(none)]> SHOW MASTER STATUS;
MariaDB [(none)]> SHOW BINLOG EVENTS IN ‘log-bin.000001’;
slave配置修改配置文件
[[emailprotected]~]#vim /etc/f.d/f
slave1和slave2配置相同，不过需要修改下server_id修改主节点MariaDB [(none)]> CHANGE MASTER TO MASTER_USER=’joah’,MASTER_HOST=’192.168.4.61’,MASTER_PASSWORD=’123456’,MASTER_LOG_FILE=’log-bin.000001’,MASTER_LOG_POS=245;
MariaDB [(none)]> START SLAVE;
MariaDB [(none)]> SHOW SLAVE STATUSG;
实现半同步复制
master节点配置安装semisync插件MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master’;
查看变量MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘rpl_semi%’;
+————————————+——-+
| Variable_name | Value |
+————————————+——-+
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+————————————+——-+
启动此插件功能MariaDB [(none)]> SET GLOBAL rpl_semi_sync_master_enabled=ON;
显示状态MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘%rpl%’;
Slave1 配置安装插件MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave’;
查看semi变量MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘%rpl_semi%’;
+———————————+——-+
| Variable_name | Value |
+———————————+——-+
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
+———————————+——-+
启动semi功能MariaDB [(none)]> SET @@global.rpl_semi_sync_slave_enabled=ON;
重启IO线程，如果不重启当执行时会超时
MariaDB [(none)]> STOP SLAVE IO_THREAD;
MariaDB [(none)]> START SLAVE IO_THREAD;
proxysql
###安装proxysql
[[emailprotected]~]#yum install proxysql-1.4.3-1-centos7.x86_64.rpm
###修改配置文件/etc/f
[[emailprotected]~]#cp /etc/f{,.bak}
[[emailprotected]~]#vim /etc/fdatadir="/var/lib/proxysql" #存放数据地址
10
11 admin_variables=
12 {
13 admin_credentials="admin:123456"
14 mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" #为了安全可见仅在本地使用
15 }
16
17 mysql_variables=
18 {
19 threads=4
20 max_connections=2048
21 default_query_delay=0
22 default_query_timeout=36000000
23 have_compress=true
24 poll_timeout=2000
25 interfaces="0.0.0.0:3306;/tmp/proxysql.sock"
26 # interfaces="0.0.0.0:6033"
27 default_schema="information_schema"
28 stacksize=1048576
29 server_version="5.5.30"
30 connect_timeout_server=3000
31 monitor_username="joah"
32 monitor_password="joah"
33 monitor_history=600000
34 monitor_connect_interval=60000
35 monitor_ping_interval=10000
36 monitor_read_only_interval=1500
37 monitor_read_only_timeout=500
38 ping_interval_server_msec=120000
39 ping_timeout_server=500
40 commands_stats=true
41 sessions_sort=true
42 connect_retries_on_failure=10
43 }
44
45
46 # defines all the MySQL servers
47 mysql_servers =
48 (
49 {
50 address = "192.168.4.61"
51 port = 3306
52 hostgroup = 0
53 status = "ONLINE"
54 weight = 1
55 compression = 0
56 },
57 {
58 address = "192.168.4.62"
59 port = 3306
60 hostgroup = 1
61 status = "ONLINE"
62 weight = 1
63 compression = 0
64 },
65 {
66 address = "192.168.4.63"
67 port = 3306
68 hostgroup = 1
69 status = "ONLINE"
70 weight = 1
71 compression = 0
72 }
73 )
74
75 # defines all the MySQL users
76 mysql_users:
77 (
78 {
79 username = "joah"
80 password = "123456"
81 default_hostgroup = 0
82 max_connections=1000
83 default_schema="hellodb"
84 active = 1
85 }
86 )
87
88 #defines MySQL Query Rules
89 mysql_query_rules:
90 (
91 )
92
93 scheduler=
94 (
95 )
96
97
98 mysql_replication_hostgroups=
99 (
100 {
101 writer_hostgroup=0
102 reader_hostgroup=1
103 comment="joah mysql"
104 }
105 )
注意：配置文件中{}如果没有后续内容没有逗号。
本地管理
[[emailprotected]~]#mysql -S /tmp/proxysql_admin.sock -uadmin -p123456
测试是否能支持读写分离
授权在上述文件中设定的mysql_userMariaDB [hellodb]> GRANT ALL ON . TO [emailprotected]%’ IDENTIFIED BY ‘123456’;
[[emailprotected]~]#mysql -ujoah -p -h192.168.4.64
