os:centos 7.4
mysql: 5.7
proxysql: 1.4.10
ip 規劃如下：
192.168.56.101 node1 (proxysql)
192.168.56.102 node2 (mysql master)
192.168.56.103 node3 (mysql slave)
192.168.56.104 node4 (mysql slave)
安裝mysql 5.7
node2、node3、node4 安裝 mysql 5.7 software
詳細過稱略，參考另外一篇博客。
初始化mysql 5.7，配置好master slave
node2、node3、node4 各個節點先初始化 mysql 5.7，再配置 master/slave
master 修改密碼
mysql> set password for 'root'@'localhost'= password('2wsx3edc');
mysql> flush privileges;
master 創建復制用戶
mysql> create user 'replicator'@'192.168.56.%' identified by '2wsx3edc';
mysql> grant replication slave on *.* to 'replicator'@'192.168.56.%';
mysql> flush privileges;
master 使用 mysqldump 出集合,再在slave端導入
# mysqldump -uroot -p --master-data=2 --single-transaction -R --triggers -A > mysql_all.sql
其中
–master-data=2代表備份時刻記錄master的Binlog位置和Position,
–single-transaction意思是獲取一致性快照，
-R意思是備份存儲過程和函數，
–triggres的意思是備份觸發器，
-A代表備份所有的庫。
mysql> change master to
master_host='192.168.56.102',
master_user='replicator',
master_password='2wsx3edc',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=154;
mysql> start slave;
salve設置為 read only，從庫對外提供讀服務，只所以沒有寫進配置文件，是因為隨時slave會提升為master。
mysql> set global read_only=1;
mysql> set global relay_log_purge=0;
至此，已經配置好了1master、2slave
配置本地免密登錄
登錄主機后，登錄mysql需要輸入密碼，配置個密碼文件。免得每次都需要輸入密碼。
node2、node3、node4節點都需要操作
# vi ~/.my.cnf
[client]
host=localhost
user='root'
password='2wsx3edc'
# chmod 700 ~/.my.cnf
下載、安裝 proxysql
node1 節點安裝mysql 5.7 client，mysql 5.7 lib
# yum install mysql-community-client mysql-community-common mysql-community-devel mysql-community-libs mysql-community-libs-compat
安裝依賴包
# yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
# cd /etc/yum.repos.d/
# cat <
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
# yum install proxysql
=======================================================================================================================================
Package Arch Version Repository Size
=======================================================================================================================================
Installing:
proxysql x86_64 1.4.10-1 proxysql_repo 5.7 M
Transaction Summary
=======================================================================================================================================
查看 proxysql-1.4.10-1.x86_64 涉及到哪些文件
# rpm -ql proxysql-1.4.10-1.x86_64
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
# systemctl status proxysql.service
● proxysql.service - LSB: High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/rc.d/init.d/proxysql; bad; vendor preset: disabled)
Active: inactive (dead)
Docs: man:systemd-sysv-generator(8)
# cat /etc/init.d/proxysql
/etc/init.d/proxysql 腳本涉及到如下目錄、文件
OLDDATADIR="/var/run/proxysql"
DATADIR="/var/lib/proxysql"
OPTS="-c /etc/proxysql.cnf -D $DATADIR"
PIDFILE="$DATADIR/proxysql.pid"
/run/systemd/generator.late/proxysql.service
# more /run/systemd/generator.late/proxysql.service
# Automatically generated by systemd-sysv-generator
[Unit]
Documentation=man:systemd-sysv-generator(8)
SourcePath=/etc/rc.d/init.d/proxysql
Description=LSB: High Performance Advanced Proxy for MySQL
Before=runlevel2.target
Before=runlevel3.target
Before=runlevel4.target
Before=runlevel5.target
Before=shutdown.target
After=network-online.target
Conflicts=shutdown.target
[Service]
Type=forking
Restart=no
TimeoutSec=5min
IgnoreSIGPIPE=no
KillMode=process
GuessMainPID=no
RemainAfterExit=yes
ExecStart=/etc/rc.d/init.d/proxysql start
ExecStop=/etc/rc.d/init.d/proxysql stop
ExecReload=/etc/rc.d/init.d/proxysql reload
配置文件 /etc/proxysql.cnf
檢查版本
# which proxysql
/usr/bin/proxysql
# proxysql --version
ProxySQL version v1.4.10-1-g5eb0f3e, codename Truls
# proxysql --help
High Performance Advanced Proxy for MySQL
USAGE: proxysql [OPTIONS]
OPTIONS:
-c, --config ARG Configuraton file-D, --datadir ARG Datadir-e, --exit-on-error Do not restart ProxySQL if crashes-f, --foreground Run in foreground-h, -help, --help, --usage Display usage instructions.-M, --no-monitor Do not start Monitor Module-n, --no-start Starts only the admin service-r, --reuseport Use SO_REUSEPORT-S, --admin-socket ARG Administration Unix Socket-V, --version Print version--idle-threads Create auxiliary threads to handle idle connections--initial Rename/empty database file--reload Merge config file into database file--sqlite3-server Enable SQLite3 Server
ProxySQL rev. v1.4.10-1-g5eb0f3e -- Tue Aug 7 12:31:55 2018
Copyright (C) 2013-2018 ProxySQL LLC
This program is free and without warranty
有 -c –config 的option，可以把參數全部導入到參數文件，易于管理。
配置proxysql.cnf
# cp /etc/proxysql.cnf /etc/proxysql.cnf.bak
# vi /etc/proxysql.cnf
配置文件只在第一次啟動的時候讀取進行初始化，后面只讀取db文件。
所以還是先啟動，然后再修改參數。
啟動 proxysql
# service proxysql start
Starting ProxySQL: 2018-08-13 11:08:25 [INFO] Using config file /etc/proxysql.cnf
DONE!
# ps -ef|grep -i proxysql
root 7859 1 0 11:08 ? 00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
root 7860 7859 0 11:08 ? 00:00:00 proxysql -c /etc/proxysql.cnf -D /var/lib/proxysql
# netstat -antp|grep -i 7859
# netstat -antp|grep -i 7860
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 7860/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 7860/proxysql
根據 /etc/proxysql.cnf 文件內容，6032 是管理端口，6033 是 mysql 連接端口。
連接 proxysql 6032 管理端口
# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
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.
Admin>
proxysql 的安裝先說的這里，下一篇blog說下配置。
cat /etc/proxysql.cnf
#file proxysql.cfg
########################################################################################
# This config file is parsed using libconfig , and its grammar is described in:
# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
# Grammar is also copied at the end of this file
########################################################################################
########################################################################################
# IMPORTANT INFORMATION REGARDING THIS CONFIGURATION FILE:
########################################################################################
# On startup, ProxySQL reads its config file (if present) to determine its datadir.
# What happens next depends on if the database file (disk) is present in the defined
# datadir (i.e. "/var/lib/proxysql/proxysql.db").
#
# If the database file is found, ProxySQL initializes its in-memory configuration from
# the persisted on-disk database. So, disk configuration gets loaded into memory and
# then propagated towards the runtime configuration.
#
# If the database file is not found and a config file exists, the config file is parsed
# and its content is loaded into the in-memory database, to then be both saved on-disk
# database and loaded at runtime.
#
# IMPORTANT: If a database file is found, the config file is NOT parsed. In this case
# ProxySQL initializes its in-memory configuration from the persisted on-disk
# database ONLY. In other words, the configuration found in the proxysql.cnf
# file is only used to initial the on-disk database read on the first startup.
#
# In order to FORCE a re-initialise of the on-disk database from the configuration file
# the ProxySQL service should be started with "service proxysql initial".
#
########################################################################################
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin"
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
# refresh_interval=2000
# debug=true
}
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;/tmp/proxysql.sock"
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
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 = "127.0.0.1" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
# port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
# hostgroup = 0 # no default, required
# status = "ONLINE" # default: ONLINE
# weight = 1 # default: 1
# compression = 0 # default: 0
# max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
# },
# {
# address = "/var/lib/mysql/mysql.sock"
# port = 0
# hostgroup = 0
# },
# {
# address="127.0.0.1"
# port=21891
# hostgroup=0
# max_connections=200
# },
# { address="127.0.0.2" , port=3306 , hostgroup=0, max_connections=5 },
# { address="127.0.0.1" , port=21892 , hostgroup=1 },
# { address="127.0.0.1" , port=21893 , hostgroup=1 }
# { address="127.0.0.2" , port=3306 , hostgroup=1 },
# { address="127.0.0.3" , port=3306 , hostgroup=1 },
# { address="127.0.0.4" , port=3306 , hostgroup=1 },
# { address="/var/lib/mysql/mysql.sock" , port=0 , hostgroup=1 }
)
# defines all the MySQL users
mysql_users:
(
# {
# username = "username" # no default , required
# password = "password" # default: ''
# default_hostgroup = 0 # default: 0
# active = 1 # default: 1
# },
# {
# username = "root"
# password = ""
# default_hostgroup = 0
# max_connections=1000
# default_schema="test"
# active = 1
# },
# { username = "user1" , password = "password" , default_hostgroup = 0 , active = 0 }
)
#defines MySQL Query Rules
mysql_query_rules:
(
# {
# rule_id=1
# active=1
# match_pattern="^SELECT .* FOR UPDATE$"
# destination_hostgroup=0
# apply=1
# },
# {
# rule_id=2
# active=1
# match_pattern="^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"
# }
)
# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
#
# Below is the BNF grammar for configuration files. Comments and include directives are not part of the grammar, so they are not included here.
#
# configuration = setting-list | empty
#
# setting-list = setting | setting-list setting
#
# setting = name (":" | "=") value (";" | "," | empty)
#
# value = scalar-value | array | list | group
#
# value-list = value | value-list "," value
#
# scalar-value = boolean | integer | integer64 | hex | hex64 | float
# | string
#
# scalar-value-list = scalar-value | scalar-value-list "," scalar-value
#
# array = "[" (scalar-value-list | empty) "]"
#
# list = "(" (value-list | empty) ")"
#
# group = "{" (setting-list | empty) "}"
#
# empty =
)
