Shulou(Shulou.com)06/01 Report--

This article mainly introduces proxysql+mysql to achieve master-slave replication read-write separation, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand proxysql+mysql to achieve master-slave replication read-write separation bar.

I. explanation

ProxySQL is an open source MySQL proxy cloud server, which means it acts as an intermediary between the MySQL cloud server and the application that accesses its database. ProxySQL can improve performance by distributing traffic among multiple database cloud server pools, and can also improve availability by automatically failing over to standby databases if one or more database cloud servers fail.

System environment: master1:ubuntu16.04 mysql5.6 192.168.1.10 3307

Master2:ubuntu16.04 mysql5.6 192.168.1.20 3307

Slave1: ubuntu16.04 mysql5.6 192.168.1.10 3308

Slave2: ubuntu16.04 mysql5.6 192.168.1.10 3309

Slave3: ubuntu16.04 mysql5.6 192.168.1.20 3308

Slave4: ubuntu16.04 mysql5.6 192.168.1.20 3309

[proxysql]: ubuntu16.04 mysql 192.168.1.30 3306

Master1 and master2 are double master and double slave mode.

Master-slave copy of the previous articles and online articles are particularly detailed. There is no introduction here. Please refer to https://blog.51cto.com/13120271/2140400.

Step 1-install ProxySQL

ProxySQL developers provide the official Ubuntu package for all ProxySQL versions on their GitHub version page, so we will download and install the latest package version from there.

You can find the latest packages in the release list. The naming convention is proxysql_version-distribution.deb, where version is similar to the 1.4.4 string of version 1.4.4, and distribution is a ubuntu16_amd64 string similar to 64-bit Ubuntu 16.04.

Download the latest official package (1.4.4 at the time of this writing) to the / tmp directory.

Cd / tmp

Curl-OL https://github.com/sysown/proxysql/releases/download/v1.4.4/proxysql_1.4.4-ubuntu16_amd64.deb

Install the package with dpkg to manage the .deb package. The-I flag indicates that we are going to install from the specified file.

Sudo dpkg-I proxysql_*

At this point, you no longer need the .deb file, so you can delete it.

Rm proxysql_*

Next, we need a MySQL client application to connect to the ProxySQL instance. This is because ProxySQL uses an MySQL-compatible interface internally to perform administrative tasks. We will use the mysql command line tool, which is part of the available packages in the mysql-client Ubuntu repository.

Update the package repository to ensure that the latest pre-bundled version is available, and then install the mysql-client package.

Sudo apt-get update

Sudo apt-get install mysql-client

You now meet all the requirements for running ProxySQL, but the service does not start automatically after installation, so start it manually immediately.

Sudo systemctl start proxysql

ProxySQL should now run with its default configuration. You can view it using systemctl.

Systemctl status proxysql

The output looks like this:

● proxysql.service-LSB: High Performance Advanced Proxy for MySQL

Loaded: loaded (/ etc/init.d/proxysql; bad; vendor preset: enabled)

Active: active (running) since Thu 2017-12-21 19:19:20 UTC; 5s ago

Docs: man:systemd-sysv-generator (8)

Process: 12350 ExecStart=/etc/init.d/proxysql start (code=exited, status=0/SUCCESS)

Tasks: 23

Memory: 30.9M

CPU: 86ms

CGroup: / system.slice/proxysql.service

├─ 12355 proxysql-c / etc/proxysql.cnf-D / var/lib/proxysql

└─ 12356 proxysql-c / etc/proxysql.cnf-D / var/lib/proxysql

The line Active (running) indicates that ProxySQL is installed and running.

Next, we will improve security by setting a password to access the ProxySQL administrative interface.

Step 2-set the ProxySQL administrator password

When you start a new ProxySQL installation for the first time, it uses the configuration file provided by the package to initialize the default values of all its configuration variables. After initialization, ProxySQL stores its configuration in the database, which you can manage and modify from the command line.

To set the administrator password in ProxySQL, we will connect to the configuration database and update the appropriate variables.

First, access the management interface. You will be prompted for a password, which is admin at the default installation.

Mysql-u admin-p-h 127.0.0.1-P 6032-- prompt='ProxySQLAdmin >'

-u specifies the user we want to connect to, here is admin, the default user for administrative tasks, such as changing configuration settings. -h 127.0.0.1 tells mysql to connect to the local ProxySQL instance. We need to define it clearly because ProxySQL does not listen on the socket file that mysql assumes by default. -P specifies the port to connect to. The management interface of ProxySQL listens to 6032. -- prompt is an optional flag to change the default prompt, usually mysql >. Here, we change it to ProxySQLAdmin > to show that we are connected to the ProxySQL administration interface. This will help avoid confusion later when we will also connect to the MySQL interface on the replicated database cloud server. *

After connecting, you will see the ProxySQLAdmin > prompt:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.5.30 (ProxySQL Admin Module)

Copyright (c) 2000, 2017, 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.

ProxySQLAdmin >

Change the administrative account password by updating (UPDATE) the admin-admin_credentials configuration variable in the global_variables database. Please change the password of the following command to the strong password of your choice.

UPDATE global_variables SET variable_value='admin:password' WHERE variable_name='admin-admin_credentials'

Query OK, 1 row affected (0.00 sec)

Due to the way ProxySQL's configuration system works, this change will not take effect immediately. It consists of three separate layers:

Memory, which is changed when modified from the command line interface.

At run time, ProxySQL uses it as a valid configuration.

Disk, which is used to keep the configuration unchanged during reboot.

Now, the changes you make are in memory. For the changes to take effect, you must copy the memory settings to the runtime realm and then save them to disk to keep them unchanged.

ProxySQLAdmin > LOAD ADMIN VARIABLES TO RUNTIME

ProxySQLAdmin > SAVE ADMIN VARIABLES TO DISK

Step 3-modify the configuration file

Egrep-v'(^ $| ^ #)'/ etc/proxysql.cnf

Admin_credentials= "admin:admin"

Mysql_ifaces= "127.0.0.1lug 6032Tracer TMP hand proxysqlaccounadmin.sock"

}

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.0VOU 6033 Tracer TMP Universe proxysql.sock"

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

}

Mysql_servers =

(

{

Address = "192.168.1.20" # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Port = 3307 # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Hostgroup = 1 # no default, required

Status = "ONLINE" # default: ONLINE

Weight = 1 # default: 1

Compression = 0 # default: 0

Max_connections = 200

}

{

Address = "192.168.1.10" # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Port = 3307 # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Hostgroup = 1 # no default, required

Status = "ONLINE" # default: ONLINE

Weight = 1 # default: 1

Compression = 0 # default: 0

Max_connections = 200

}

{

Address = "192.168.1.20" # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Port = 3308 # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Hostgroup = 2 # no default, required

Status = "ONLINE" # default: ONLINE

Weight = 1 # default: 1

Compression = 0 # default: 0

Max_connections = 1000

}

{

Address = "192.168.1.20" # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Port = 3309 # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Hostgroup = 2 # no default, required

Status = "ONLINE" # default: ONLINE

Weight = 1 # default: 1

Compression = 0 # default: 0

Max_connections = 1000

}

{

Address = "192.168.1.10" # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Port = 3308 # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Hostgroup = 2 # no default, required

Status = "ONLINE" # default: ONLINE

Weight = 1 # default: 1

Compression = 0 # default: 0

Max_connections = 1000

}

{

Address = "192.168.1.10" # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Port = 3309 # no default, required. If port is 0, address is interpred as a Unix Socket Domain

Hostgroup = 2 # no default, required

Status = "ONLINE" # default: ONLINE

Weight = 1 # default: 1

Compression = 0 # default: 0

Max_connections = 1000

}

)

Mysql_users:

(

{

Username = "admin" # no default, required

Password = "password" # default:''

Default_hostgroup = 1 # default: 0

Max_connections=1000

Default_schema= "test"

Active = 1 # default: 1

}

)

Mysql_query_rules:

(

)

Scheduler=

(

)

Mysql_replication_hostgroups=

(

{

Writer_hostgroup=1

Reader_hostgroup=2

Comment= "test repl 1"

}

)

Restart proxysql.

Sudo systemctl restart proxysql

Then let's log in and verify it.

Mysql-u admin-p-h 127.0.0.1-P 6032-- prompt='ProxySQLAdmin >'

Mysql > show databases

Mysql > show databases

+-+ | Database |

+-+ | information_schema | | mysql | | performance_schema | | study | | sys |

The above stats,monitor,main is the database obtained from the configuration file; it can be modified by similar mysql runtime without reboot; for more command line configuration, please refer to github

So far, we have completed the read-write separation architecture based on proxysql master-slave replication.

The dual-master or multi-master model does not need to achieve read-write separation, but only needs load balancing: haproxy, nginx, lvs, etc.

Proxysql does not solve the problem when the master data machine; MHA is needed to solve it at this point.

After reading the above about proxysql+mysql to achieve the separation of master from replication reading and writing, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.