mysql proxy yum_mysql 高可用架构 proxysql 之一 yum安装

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 =