一、本次环境架构图
由于公司内网服务器有限，所以后端采用Mariadb自带的mysql_multi模型实现多实例。
mysql的多实例有两种方式可以实现，两种方式各有利弊。
1、使用多个配置文件启动不同的进程来实现多实例，这种方式的优势逻辑简单，配置简单，缺点是管理起来不太方便。
2、通过官方自带的mysqld_multi使用单独的配置文件来实现多实例，这种方式定制每个实例的配置不太方便，优点是管理起来很方便，集中管理。
二、mariadb多实例实现：
mariadb配置文件如下(红色部分为修改内容)：
注意使用了mysqld_multi，就不能在启用mysqld模块，红色部分为修改或者添加内容
[client] port = 3306 socket = /tmp/mysql.sock #default-character-set=utf8 [mysqld_multi] # 启用 mysqld_multi 多实例模式 mysqld = /usr/local/mysql/bin/mysqld_safe # 指定两个管理命令 mysqladmin = /usr/local/mysql/bin/mysqladmin [mysqld1] # 定义mysql1实例 port = 3306 socket = /tmp/mysql3306.sock basedir = /usr/local/mysql datadir = /mysqldata/data3306
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 log-bin=mysql-bin-1 server-id = 1 # 标记server-id 不能重复 log-bin=mysql-bin3306 # 用于同步数据的日志文件 binlog_ignore_db = mysql # 忽视mysql库的同步 log_slave_updates # 从库binlog记录主库同步的操作日志 slave_skip_errors = all # 跳过从库出错的SQL auto_increment_increment = 2 # 主主同步时，每次递增量 auto_increment_offset = 1 # 主主同步，从自增开始，从那个数开始自增 [mysqld2] port = 3307 socket = /tmp/mysql3307.sock basedir = /usr/local/mysql datadir = /mysqldata/data3307
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M thread_concurrency = 8 log-bin=mysql-bin-2 server-id = 2 log_bin=mysql-bin3307 log_slave_updates auto_increment_increment = 2 auto_increment_offset = 2 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash
default-character-set = utf8 [myisamchk] key_buffer_size = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
初始化数据库：
[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mysqldata/data3306/
[root@localhost mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/mysqldata/data3307/
启动库：
# 这里有很多种启动方式 mysqld_multi start 1, mysqld_multi start 2
# 注意这里是启动实例，则不需要启动mysqld
[root@localhost mysql]# mysqld_multi start 1-2 [root@localhost mysql]# ss -ntpl | grep mysqld LISTEN 0 50 *:3306 *:* users:(("mysqld",pid=4775,fd=15)) LISTEN 0 50 *:3307 *:* users:(("mysqld",pid=4774,fd=15))
连接实例库：
连接3306
[root@localhost ~]# mysql -uroot -S /tmp/mysql3306.sock
连接3307
[root@localhost ~]# mysql -uroot -S /tmp/mysql3307.sock
三、实现mariadb 主主同步
1. 在两个实例中分别创建用于同步的用户。注：用于同步的用户必须具备: replication slave, reload, super 权限
3306库：
MariaDB [(none)]> GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* TO 'backup'@'192.168.118.187' identified by '123456';
3307库：
MariaDB [(none)]> GRANT REPLICATION SLAVE, RELOAD, SUPER ON *.* TO 'backup'@'192.168.118.187' identified by '123456';
2. 分别查看两个实例的master log 和 Position
3306库：
MariaDB [(none)]> show master statusG *************************** 1. row *************************** File: mysql-bin3306.000001 Position: 503 Binlog_Do_DB: Binlog_Ignore_DB: mysql
3307库：
MariaDB [(none)]> show master statusG *************************** 1. row *************************** File: mysql-bin3307.000001 Position: 503 Binlog_Do_DB: Binlog_Ignore_DB:
3. 分别为两个实例配置master 互为主备
3306实例配置：
# 这里的master为在实例3307上配置的用户信息，master_log_file 为3307的master file master_log_pos 为 3307的Position MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.118.187', MASTER_PORT=3307, MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin3307.000001', MASTER_LOG_POS=503;
3307实例配置：
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.118.187', MASTER_PORT=3306, MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin3306.000001', MASTER_LOG_POS=503;
MariaDB [(none)]> show slave statusG # 查看是否配置成功 *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.118.187 Master_User: backup Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin3307.000001 Read_Master_Log_Pos: 503 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 533 Relay_Master_Log_File: mysql-bin3307.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ...
这里最主要的两个参数：Slave_IO_Running: Yes、 Slave_SQL_Running: Yes 如果这里都是Yes表示配置成功。配置master时，注意思路要清晰，端口不能错。
4. 测试：
MariaDB [(none)]> CREATE DATABASE hello_db; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | hello_db | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) 3307库查看是否同步： MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | hello_db | | mysql | | performance_schema | | test | +--------------------+
3307 删除hello_db：
MariaDB [(none)]> DROP DATABASE hello_db; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> FLUSH PRIVILEGES; 3306 查看是否同步删除： MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+
通过以上测试，Mariadb主主配置成功。
四、配置HAProxy实现后端mariadb负载均衡
[root@localhost ~]# yum install haproxy -y
HAProxy 配置文件：
global log 127.0.0.1 local2 chroot /var/lib/haproxy pidfile /var/run/haproxy.pid maxconn 4000 user haproxy group haproxy daemon stats socket /var/lib/haproxy/stats defaults mode http log global option tcplog option dontlognull #option http-server-close #option forwardfor except 127.0.0.0/8 option redispatch retries 3 timeout http-request 10s timeout queue 1m timeout connect 10s timeout client 1m timeout server 1m timeout http-keep-alive 10s timeout check 10s maxconn 3000 listen proxy-sql 192.168.118.187:23306 # 创建proxy-sql mode tcp balance roundrobin # 算法为轮询 option mysql-check user haproxy # 在mysql中创建无任何权限用户haproxy，且无密码用于监控 server mysql3306 192.168.118.187:3306 check maxconn 2000 weight 1 # 后端主机 server mysql3307 192.168.118.187:3307 check maxconn 2000 weight 1 [root@localhost haproxy]# ss -tnlp | grep haproxy LISTEN 0 128 192.168.118.187:23306 *:* users:(("haproxy",pid=5448,fd=5))
2. 创建用于监控的mariadb 用户 haproxy
# 两个实例都需要创建，创建一个后，可以直接查看第二个实例是否也同步了。 MariaDB [(none)]> CREATE USER 'haproxy'@'%' ;
3. 通过haproxy端口连接数据库
HAProxy工作原理：当HAProxy接收到用户请求后，不是直接透明转发客户端请求到后端服务器，而是HAProxy到后端服务器取数据。
由此可见，当客户端发情mariadb连接请求后，由HAProxy来建立连接mariadb。因此，HAProxy客户端地址为HAProxy服务器地址而不是客户端地址。
PS: 因为这里HAProxy是对外服务，所以监听的地址是：192.168.118.187 而不是本地回环地址，因此需要对IP地址为192.168.118.187赋予权限。
这里就牵扯到mariadb权限的问题，给与haproxy地址访问权限：
MariaDB [(none)]> GRANT ALL ON *.* TO 'root'@'192.168.118.187' IDENTIFIED BY '123456';
以上操作必须要保证两个实例都要具备:
MariaDB [(none)]> SELECT HOST, USER, PASSWORD FROM mysql.user WHERE HOST='192.168.118.187'; +-----------------+--------+-------------------------------------------+ | HOST | USER | PASSWORD | +-----------------+--------+-------------------------------------------+ | 192.168.118.187 | backup | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 192.168.118.187 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------------+--------+-------------------------------------------+
启动服务
[root@localhost ~]# systemctl start haproxy # 启动HAProxy服务
4. 本地测试：
[root@localhost ~]# mysql -uroot -p123456 -P 23306 -h 192.168.118.187 Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 558 Server version: 5.5.52-MariaDB MariaDB Server Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]>
本次测试成功。
再次使用Navicat连接：
这样通过HAProxy代理到后端mariadb负载成功。
五、总结：
1. 这样的架构最好只是在测试环境使用，因为影响到的因素会很多。比如磁盘IO问题：用户请求响应的IO，实例同步的IO。
生产环境建议如下架构：
