version: '2.1'
services:
  master:
    image: bergerx/mysql-replication:5.6
    network_mode: "host"
    restart: always
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "1"
      MYSQL_DATABASE: "test_db"
    volumes:
      - "/etc/timezone:/etc/timezone:ro"
      - "/etc/localtime:/etc/localtime:ro"
    command: ["--character-set-server=utf8mb4", "--collation-server=utf8mb4_unicode_ci"]

使用docker-compose up -d启动（对docker-compose不熟悉的，这边稍微提示一下，docker-compose命令需要在docker-compose.yml同级目录下执行,因为它默认使用当前目录下的docker-compose.yml）

(2) 在 192.168.100.242上安装mysql-slave, docker-compose.yml如下

version: '2'
services:
  slave:
    image: bergerx/mysql-replication:5.6
    restart: always
    environment:
      MYSQL_ALLOW_EMPTY_PASSWORD: "1"
      MASTER_HOST: "192.168.100.241"
      MASTER_PORT: 3306
    volumes:
      - "/etc/timezone:/etc/timezone:ro"
      - "/etc/localtime:/etc/localtime:ro"
    network_mode: host
    command: ["--character-set-server=utf8mb4", "--collation-server=utf8mb4_unicode_ci"]

使用docker-compose up -d启动

（3）验证主从同步效果

从上面的配置我们可以看到，master默认创建了test_db数据库,这时只需要检查slave上是否也有test_db数据库即可,具体方式如下：在192.168.100.242的docker-compose.yml同级目录下执行如下命令

$: docker-compose exec slave mysql -e "show databases"

此时如果test_db存在,则证明mysql-replication安装成功

（4）创建用户供proxysql使用，这里主要创建两个用户monitor和msandbox，前者用于proxysql监控mysql，后者用于外部程序通过proxysql访问mysql,由于已经实现了主从同步,因此只在192.168.100.241上操作即可

$: docker-compose exec master mysql

进入mysql控制台，创建用户

mysql> insert into mysql.user(Host,User,Password) values("%","monitor",password("monitor"));

mysql> grant all privileges on *.* to monitor@"%" identified by "monitor";

mysql> flush privileges;

mysql> insert into mysql.user(Host,User,Password) values("%","msandbox",password("msandbox"));

mysql> grant all privileges on test_db.* to msandbox@"%" identified by 'msandbox';

mysql> flush privileges;

二、安装proxysql

在192.168.100.231和192.168.100.232上的操作是一样的

(1) 首先创建一个目录 /home/ubuntu/proxysql,然后创建docker-compose.yml

version: '2.1'
services:
  proxysql:
    image: severalnines/proxysql
    restart: always
    network_mode: host
    environment:
      MONITOR_CONFIG_CHANGE: "true"
    volumes:
      - "/etc/timezone:/etc/timezone:ro"
      - "/etc/localtime:/etc/localtime:ro"
      - "./proxysql.cnf:/etc/proxysql.cnf:ro"

(2) 上面的步骤中挂载了本地的一个proxysql.cnf文件,这个主要用于自定义proxysql配置，如下

#file proxysql.cfg

datadir="/var/lib/proxysql"

admin_variables=
{
        // cluster1:secret1pass主要用于cluster访问
        admin_credentials="admin:admin;cluster1:secret1pass"
        mysql_ifaces="0.0.0.0:6032"
        // 以下配置只有需要实现proxysql-cluster时才需要
        cluster_username="cluster1"
        cluster_password="secret1pass"
        cluster_check_interval_ms=200
        cluster_check_status_frequency=100
        cluster_mysql_query_rules_save_to_disk=true
        cluster_mysql_servers_save_to_disk=true
        cluster_mysql_users_save_to_disk=true
        cluster_proxysql_servers_save_to_disk=true
        cluster_mysql_query_rules_diffs_before_sync=3
        cluster_mysql_servers_diffs_before_sync=3
        cluster_mysql_users_diffs_before_sync=3
        cluster_proxysql_servers_diffs_before_sync=3
}

// 若要实现proxysql-cluster,则proxysql_servers必须在配置文件中进行配置
proxysql_servers =
(
    {
        hostname="192.168.100.231"
        port=6032
        comment="proxysql_231"
    },
    {
        hostname="192.168.100.232"
        port=6032
        comment="proxysql_232"
    }
)

mysql_variables=
{
  threads=4
  max_connections=2048
  default_query_delay=0
  default_query_timeout=36000000
  have_compress=true
  poll_timeout=2000
  interfaces="0.0.0.0:6033"
  default_schema="information_schema"
  stacksize=1048576
  server_version="5.5.30"
  connect_timeout_server=3000
  monitor_username="monitor"
  monitor_password="monitor"
  monitor_history=600000
  monitor_connect_interval=60000
  monitor_ping_interval=10000
  monitor_read_only_interval=1500
  monitor_read_only_timeout=500
  ping_interval_server_msec=120000
  ping_timeout_server=500
  commands_stats=true
  sessions_sort=true
  connect_retries_on_failure=10
}


# defines all the MySQL servers
mysql_servers =
(
  { address="192.168.100.241" , port=3306 , hostgroup=1 },
  { address="192.168.100.242" , port=3306 , hostgroup=2 }
)


# defines all the MySQL users
mysql_users:
(
  {
    username = "msandbox" 
    password = "msandbox"
    default_hostgroup = 1
    active = 1 
  }
)


#defines MySQL Query Rules
mysql_query_rules:
(
 {
   rule_id=100
   active=1
   schemaname=test_db
   username=msandbox
   match_digest="(?i)^SELECT.*$"
   destination_hostgroup=2
   apply=1
 },
 {
   rule_id=200
   active=1
   schemaname=test_db
   username=msandbox
   match_digest="(?i)^(?!SELECT).*$"
   destination_hostgroup=1
   apply=1
 }
)

scheduler=
(
#  {
#    id=1
#    active=0
#    interval_ms=10000
#    filename="/var/lib/proxysql/proxysql_galera_checker.sh"
#    arg1="0"
#    arg2="0"
#    arg3="0"
#    arg4="1"
#    arg5="/var/lib/proxysql/proxysql_galera_checker.log"
#  }
)


mysql_replication_hostgroups=
(
#        {
#                writer_hostgroup=30
#                reader_hostgroup=40
#                comment="test repl 1"
#       },
#       {
#                writer_hostgroup=50
#                reader_hostgroup=60
#                comment="test repl 2"
#        }
)

以上配置文件内容比较多,其实默认情况下proxysql有一个默认的proxysql.cnf，即使我们不挂载自己的proxysql.cnf，proxysql也可以正常启动，像 mysql_variables、mysql_users、mysql_servers、mysql_query_rules 是可以动态配置的，除了少数的配置必须在启动时配置,比如proxysql_servers这些关于proxysql-cluster的配置,必须在初次启动时配置好,不支持动态修改。

(3)启动proxysql,在 /home/ubuntu/proxysql目录下执行如下命令即可

$: docker-compose up -d

三、动态配置proxysql

在192.168.100.231或192.168.100.232 的/home/ubuntu/proxysql目录下执行命令

$: docker-compose exec proxysql mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

命令执行后界面如下

(1) 添加mysql后端实例

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'192.168.100.241',3306);
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'192.168.100.242',3306);

-- 让配置在运行环境中生效
Admin> LOAD MYSQL SERVERS TO RUNTIME;

-- 让配置持久化到硬盘
Admin> SAVE MYSQL SERVERS TO DISK;

（2）添加监控用户（mysql中创建的monitor用户）

Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';

-- 让配置在运行环境中生效
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
-- 让配置持久化到硬盘
Admin> SAVE MYSQL VARIABLES TO DISK;

-- 检查与后端服务的连接情况

Admin> select * from mysql_server_ping_log order by time_start_us desc limit 5\G

若出现下面的信息,则表示与mysql连接正常

（3）添加mysql用户(mysql中创建的msandbox用户）

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('msandbox','msandbox',1);
-- 使配置在运行环境中生效
Admin> LOAD MYSQL USERS TO RUNTIME;
-- 将配置持久化到硬盘
Admin> SAVE MYSQL USERS TO DISK;

(4)添加路由规则实现读写分离

-- hostgroup为2的负责读,根据步骤(1)的配置,即是让192.168.100.242执行读操作

Admin> INSERT INTO mysql_query_rules (active,schemaname,username,match_digest,destination_hostgroup,apply) VALUES (1,'test_db','msandbox','(?i)^SELECT',2,1);

-- hostgroup为1的负责写，根据步骤(1)的配置,即是让192.168.100.241执行写操作
Admin> INSERT INTO mysql_query_rules (active,schemaname,username,match_digest,destination_hostgroup,apply) VALUES (1,'test_db','msandbox','(?i)^(?!select).*$',1,1);

-- 使配置在运行环境中生效
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
-- 将配置持久化到硬盘
Admin> SAVE MYSQL QUERY RULES TO DISK;

有几个需要注意的地方：

a、 LOAD XX XX XX TO RUNTIME 表示让修改的配置在运行环境中生效

b、SAVE XX XX XX TO DISK 表示将配置持久化到硬盘,必须proxysql重启后丢失数据

c、本文已实现了proxysql-cluster，因此只需要在其中一个proxysql实例中进行动态配置即可让另一个proxysql实例的配置同步修改，触发cluster进行配置同步的命令是 LOAD XX XX XX TO RUNTIME

四、使用proxysql

(1)使用mysql-client访问proxysql后端的mysql

$: mysql -umsandbox -pmsandbox -h192.168.100.231 -P6033

由于实现了proxysql-cluster，也可以访问192.168.100.232

$: mysql -umsandbox -pmsandbox -h192.168.100.232 -P6033

(2) 在spring-boot中访问proxysql

spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
  url: "jdbc:mysql://192.168.100.231:6033/test_db?useUnicode=true&characterEncoding=utf8"
  username: msandbox
  password: msandbox

收录于