keeplived做反向代理,转发到两个mycat中间件上,在两个mycat上做了读写分离配置,分别写入到主库,从从库读取数据库信息.
下面根据这个结构图开始进行部署,我这里已经把数据库安装好了,两个节点的数据库都是8.0版本的,从配置主从开始.
mysql主从配置
数据库创建好了,需要给从库创建一个读的用户,方便从库从主库读取信息,然后写到从库中,这里会有延迟的问题,目前先不讨论这个,看下面的操作.
[root@node-01 lnmp2.1]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.37 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. 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> CREATE USER 'mysql02'@'192.168.6.101' IDENTIFIED BY 'mysql02'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysql02'@'192.168.6.101'; Query OK, 0 rows affected (0.01 sec) mysql> SELECT user, host FROM mysql.user WHERE user = 'mysql02'; +---------+---------------+ | user | host | +---------+---------------+ | mysql02 | 192.168.6.101 | +---------+---------------+ 2 rows in set (0.00 sec)
用户创建完成后,需要配置主从,这里需要主库必须开启binlog,因为从库的操作是读取binlog来同步到本地的.
mysql> show variables like 'log_%'; +----------------------------------------+----------------------------------------+ | Variable_name | Value | +----------------------------------------+----------------------------------------+ | log_bin | ON #已经开启binlog | | log_bin_basename | /usr/local/mysql/var/mysql-bin | | log_bin_index | /usr/local/mysql/var/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | ./node-01.err | | log_error_services | log_filter_internal; log_sink_internal | | log_error_suppression_list | | | log_error_verbosity | 2 | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_raw | OFF | | log_replica_updates | ON | | log_slave_updates | ON | | log_slow_admin_statements | OFF | | log_slow_extra | OFF | | log_slow_replica_statements | OFF | | log_slow_slave_statements | OFF | | log_statements_unsafe_for_binlog | ON | | log_throttle_queries_not_using_indexes | 0 | | log_timestamps | UTC | +----------------------------------------+----------------------------------------+ 21 rows in set (0.00 sec)
如果没有开启,直接在mysql的配置文件中,增加log-bin=mysql-bin更多配置可以参考:
log-bin=mysql-bin binlog_format=mixed server-id = 1 binlog_expire_logs_seconds = 864000 early-plugin-load = "" default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_data_home_dir = /usr/local/mysql/var innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var innodb_buffer_pool_size = 16M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50
然后重启,在重新看一下,就开启了binlog,然后就需要配置读写主从,需要先看一下主库的binlog信息,后面同步的时候需要用到.
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000006 | 979 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
在从库的实例上,需要修改一下配置文件中[mysqld]下的server-id,不能和主库的id一样,要不会报错Fatal error: The replica I/O thread stops because source and replica have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on replica but this does not always make sense; please check the manual before using it).,修改后重启数据库,然后在从库中执行
[root@node-02 lnmp2.1]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.37 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. 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> change master to master_host='192.168.6.100', master_user='mysql02', master_password='mysql02', master_log_file='mysql-bin.000006', master_log_pos=979; Query OK, 0 rows affected, 8 warnings (0.02 sec) mysql> START REPLICA; #早期版本使用START SLAVE;如果关闭可以使用STOP REPLICA;或STOP SLAVE; Query OK, 0 rows affected (0.01 sec) mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.6.100 Master_User: mysql02 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 979 Relay_Log_File: node-02-relay-bin.000003 Relay_Log_Pos: 326 Relay_Master_Log_File: mysql-bin.000006 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: 979 Relay_Log_Space: 718 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: 9ff355b1-e8ec-11ef-819b-525400875799 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec)
这样,mysql的主从就搭建好了.但是同步的数据只有从mysql-bin.000006文件的979往后产生的日志才会被同步的从库中,以前创建的不会同步,所以后期的监控需要监控到这个同步进程.
mysql主从验证
mysql的主从搭建好了,我们验证一下,在主库中创建一个库,然后在创建一个表,表中插入一些数据,在从库中查看数据是否可以查看到.
mysql> use wulaoer; Database changed mysql> CREATE TABLE users ( -> id INT AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(100) NOT NULL, -> email VARCHAR(100) NOT NULL UNIQUE, -> created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -> ); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO users (name, email) -> VALUES -> ('Alice', 'alice@example.com'), -> ('Bob', 'bob@example.com'), -> ('Charlie', 'charlie@example.com'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
下面在从库中查看一下,有没有这个库以及表,还有表中的数据.
mysql> SELECT * FROM wulaoer.users; +----+---------+---------------------+---------------------+ | id | name | email | created_at | +----+---------+---------------------+---------------------+ | 1 | Alice | alice@example.com | 2025-02-12 11:45:32 | | 2 | Bob | bob@example.com | 2025-02-12 11:45:32 | | 3 | Charlie | charlie@example.com | 2025-02-12 11:45:32 | +----+---------+---------------------+---------------------+ 3 rows in set (0.00 sec)
到此,mysql的主从已经搭建好了,下面就需要搭建mycat的高可用,然后通过mycat做读写分离.
mycat集群部署
mycat的集群就是在两个mysql的节点分别起一个mycat服务,然后利用keeplived做负载均衡转发到两个mycat上.在mycat上做了同样的配置,就是读写分离.两个节点的操作是一样的.这里我只写node-01节点.
[root@node-01 ~]# wget https://github.com/MyCATApache/Mycat-Server/releases/download/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz [root@node-01 ~]# tar -zxcf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz [root@node-01 ~]# mv mycat /usr/local/
因为mycat是java写的,所以需要配置一下jdk.
[root@node-01 ~]# tar -zxf jdk-8u371-linux-x64.tar.gz [root@node-01 ~]# vim /etc/profile ........................ export JAVA_HOME=/usr/local/jdk export CLASSPATH=.:${JAVA_HOME}/jre/lib/rt.jar:${JAVA_HOME}/lib/dt.jar:${JAVA_HOME}/lib/tools.jar export PATH=$PATH:${JAVA_HOME}/bin [root@node-01 ~]# source /etc/profile [root@node-01 ~]# java -version java version "1.8.0_361" Java(TM) SE Runtime Environment (build 1.8.0_361-b09) Java HotSpot(TM) 64-Bit Server VM (build 25.361-b09, mixed mode)
配置mycat
在mycat的conf目录下有两个比较重要的文件分别是schema.xml和server.xml,其中schema.xml是配置逻辑库读写分离的文件，server.xml是负责登录用户以及路由规则的配置文件。下面先看一下schema的配置内容，我这里把注释去掉了。
[root@node-01 ~]# cat /usr/local/mycat/conf/schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="wulaoer" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1"> #指定了一个库 </schema> <dataNode name="dn1" dataHost="Host1" database="wulaoer" /> #如果多个库写多个即可 <dataHost name="Host1" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <!-- can have multi write hosts --> <writeHost host="hostM1" url="192.168.6.100:3306" user="root" password="root"> #写实例 #读实例 <readHost host="hostS1" url="192.168.6.101:3306" user="root" password="root" /> </writeHost> </dataHost> </mycat:schema>
我这里只配置了一个库,在schema.xml中配置几个库,一定要在server.xml配置文件中加几个库
[root@node-01 ~]# cat /usr/local/mycat/conf/server.xml ............................... <user name="root" defaultAccount="true"> <property name="password">root</property> <property name="schemas">wulaoer</property> #如果多个库用逗号分开 </user> ...............................
mycat配置完成,下面在另外一个实例也做同样的配置,这里就不写了.
两个节点都配置后,启动服务,启动后查看
[root@node-01 ~]# /usr/local/mycat/bin/mycat start|stop|restart [root@node-01 ~]# netstat -tlnp | grep 8066 tcp6 0 0 :::8066 :::* LISTEN 15167/java
安装keeplived
使用keeplived的目的是为了避免keeplived的下一层也就是mycat的两个节点避免单节点异常影响到业务.负载均衡是通过linux的IPVS(ip虚拟服务器)实现,高可用通过VRRP实现多机故障转移.
[root@node-01 ~]# yum install keepalived [root@node-01 ~]# cd /etc/keepalived/ [root@node-01 keepalived]# ls keepalived.conf
配置keeplived
主节点配置详解,需要注意的是主节点的标识和主节点的ip地址以及优先级和备节点不一样,其他的配置都一样.
[root@node-01 keepalived]# cat keepalived.conf ! Configuration File for keepalived global_defs { router_id 192.168.6.100 #配置主机名或者ip地址 } vrrp_instance VI_1 { state MASTER # 标识主节点服务（只有MASTER和BACKUP两种，大写） interface eth0 # VIP板顶的网卡接口 virtual_router_id 51 # 虚拟路由id，和备节点保持一致 priority 100 # 优先级，高于备节点的即可。 # nopreempt # 禁止MASTER宕机恢复后抢占服务 # smtp_alert # 激活故障时发送邮件告警 mcast_src_ip 192.168.6.100 # 本机IP地址 advert_int 1 # MASTER和BACKUP节点之间的同步检查时间间隔，单位为秒 authentication { # 验证类型和验证密码 auth_type PASS # PAAS（默认），HA auth_pass 1111 # MASTER和BACKUP使用相同明文才可以互通 } virtual_ipaddress { # 虚拟IP地址池，可以多个IP 192.168.6.243 # 虚拟IP1（VIP） } }
备节点配置和主节点配置的区别在于优先级和标识
[root@node-02 keepalived]# cat keepalived.conf ! Configuration File for keepalived global_defs { router_id 192.168.6.101 #配置主机名或者ip地址 } vrrp_instance VI_1 { state BACKUP # 标识主节点服务（只有MASTER和BACKUP两种，大写） interface eth0 # VIP板顶的网卡接口 virtual_router_id 51 # 虚拟路由id，和备节点保持一致 priority 100 # 优先级，高于备节点的即可。 # nopreempt # 禁止MASTER宕机恢复后抢占服务 # smtp_alert # 激活故障时发送邮件告警 mcast_src_ip 192.168.6.101 # 本机IP地址 advert_int 1 # MASTER和BACKUP节点之间的同步检查时间间隔，单位为秒 authentication { # 验证类型和验证密码 auth_type PASS # PAAS（默认），HA auth_pass 1111 # MASTER和BACKUP使用相同明文才可以互通 } virtual_ipaddress { # 虚拟IP地址池，可以多个IP 192.168.6.243 # 虚拟IP1（VIP） } }
启动keeplived
[root@node-01 keepalived]# systemctl start keepalived.service [root@node-01 keepalived]# systemctl restart keepalived.service [root@node-01 ~]# systemctl enable keepalived.service [root@node-01 keepalived]# chkconfig keepalived on Note: Forwarding request to 'systemctl enable keepalived.service'. Created symlink from /etc/systemd/system/multi-user.target.wants/keepalived.service to /usr/lib/systemd/system/keepalived.service. [root@node-01 keepalived]# systemctl status keepalived.service ● keepalived.service - LVS and VRRP High Availability Monitor Loaded: loaded (/usr/lib/systemd/system/keepalived.service; enabled; vendor preset: disabled) Active: active (running) since Wed 2025-02-12 14:47:19 CST; 17s ago Main PID: 16195 (keepalived) CGroup: /system.slice/keepalived.service ├─16195 /usr/sbin/keepalived -D ├─16196 /usr/sbin/keepalived -D └─16197 /usr/sbin/keepalived -D
启动keeplived后,说明keeplived搭建好了,下面测试一下keeplived找个节点把keeplived关闭,然后继续连接看看是否正常.
[root@node-01 ~]# systemctl stop keepalived.service [root@node-01 ~]# ping 192.168.6.243 PING 192.168.6.243 (192.168.6.243) 56(84) bytes of data. 64 bytes from 192.168.6.243: icmp_seq=1 ttl=64 time=0.223 ms
测试通过,单个节点的keeplived出现异常不影响整个集群的使用.目前整个结构已经部署好了,下面只需要测试,通过keeplived连接到数据库,并且写入数据,整个流程就结束了.
测试通过keeplived访问数据库
在连接数据库之前,需要给数据库配置允许远程访问策略,因为数据库默认没有远程访问授权.
[root@node-01 keepalived]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 980 Server version: 8.0.37 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. 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> use mysql Database changed mysql> update user set host='%' where user ='root'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec)
这里需要两个节点都授权,主节点的写,从节点的读,都需要远程访问,如果从节点不收取就无法读取数据库操作.
我这通过客户端连接,并插入数据,前面创建的表,继续在表中插入数据.
插入成功,说明数据库的写操作完成了.
mysql> SELECT * FROM wulaoer.users; +----+---------+---------------------+---------------------+ | id | name | email | created_at | +----+---------+---------------------+---------------------+ | 1 | Alice | alice@example.com | 2025-02-12 11:45:32 | | 2 | Bob | bob@example.com | 2025-02-12 11:45:32 | | 3 | Charlie | charlie@example.com | 2025-02-12 11:45:32 | | 4 | David | david@example.com | 2025-02-12 15:20:36 | | 5 | Eva | eva@example.com | 2025-02-12 15:20:36 | | 6 | Frank | frank@example.com | 2025-02-12 15:20:36 | | 7 | Grace | grace@example.com | 2025-02-12 15:20:36 | | 8 | Hannah | hannah@example.com | 2025-02-12 15:20:36 | +----+---------+---------------------+---------------------+ 8 rows in set (0.00 sec)
至此,整个结构的集群部署已经完成了,这里注意mycat 1.6版本的不支持mysql8.0的分库分表,如果只是作为代理连接,不使用mysql8.0的特性不影响使用,不过需要注意的是连接时需要把mysql的配置修改一下
[mysqld] default_authentication_plugin=mysql_native_password
我这个因为mysql和mycat在同一台节点,所以启动mycat的时候修改了配置,通过mysql5.7的驱动也可以连接,并且也不影响到使用.但是我在通过客户端连接时,打开表,但是通过命令可以查看表中的数据.文档中说,针对mycat对mysql8.0支持的不是特别好,无法使用mysql8.0的特性,建议使用mycat2.0版本的,后面有时间就把mycat2.0版本的写一下.
您可以选择一种方式赞助本站
支付宝扫一扫赞助
微信钱包扫描赞助
赏