MySQL master-slave and proxy server

MySQL master-slave principle and process

principle

MySQL Replication is an asynchronous replication process (mysql5.1.7 or later is divided into asynchronous replication and semi-synchronous modes), copied from a Mysql instace (we call it Master) to another Mysql instance (we call it Slave) . Implementation of the Master and Slave The entire replication process is mainly done by three threads, two threads (Sql thread and IO thread) on the Slave side and another thread (IO thread) on the Master side.

To implement MySQL Replication, you must first open the Binary Log (mysql-bin.xxxxxx) function on the Master side, otherwise it will not be implemented. Because the entire copy process is actually the various operations recorded in the log that Slave takes the log from the Master and then executes it in its own complete sequence. Open MySQL’s Binary Log by adding the “-log-bin” parameter option during the startup of MySQL Server, or by adding the “log-bin” parameter to the mysqld parameter group in the configuration file (parameter part of the [mysqld] ID) item.

Basic process

1.Slave The IO thread above connects to the Master and requests the contents of the log after the specified location of the specified log file (or the log from the beginning);

2. After receiving the request from the IO thread of the slave, the master reads the log information after the specified location of the specified log according to the request information through the IO thread responsible for the copy, and returns it to the IO thread of the slave. In addition to the information contained in the log, the return information includes the name of the Binary Log file on the Master side and the location in the Binary Log.

3. After receiving the information, the IO thread of the Slave writes the received log content to the end of the Relay Log file (mysql-relay-bin.xxxxxx) on the Slave end, and reads the bin-log of the Master. The file name and location are recorded in the master-info file so that the next time you read it, you can clearly tell the Master “Which location I need to start from a bin-log, please send it to me”

4.Slave’s SQL thread detects the newly added content in the Relay Log, and immediately parses the contents of the log file into executable Query statements when the Master side is actually executed, and executes the Query on its own. In this way, the same Query is actually executed on the Master side and the Slave side, so the data at both ends is exactly the same.

Several modes of MySQL replication

Starting with MySQL 5.1.12, you can do this in three modes:

– based on statement-based replication (SBR),

– row-based replication (RBR),

– mixed-based replication (MBR)

Accordingly, there are three formats for binlog: STATEMENT, ROW, MIXED. In the MBR mode, the SBR mode is the default.

Set master-slave replication mode:
log-bin=mysql-bin

#binlog_format=”STATEMENT”

#binlog_format=”ROW”

binlog_format=”MIXED”

It is also possible to dynamically modify the format of the binlog at runtime. For example
mysql> SET SESSION binlog_format = ‘STATEMENT’;

mysql> SET SESSION binlog_format = ‘ROW’;

mysql> SET SESSION binlog_format = ‘MIXED’;

mysql> SET GLOBAL binlog_format = ‘STATEMENT’;

Mysql master-slave replication configuration

Version: mysql5.7 CentOS 7.2

Scenario description:
Primary database server: 192.168.1.100, MySQL is installed, and there is no application data.
From the database server: 192.168.1.200, MySQL is already installed and there is no application data.

1 Operations on the primary server

Start mysql service
service mysqld start

Authorize copy permissions to the database server 192.168.1.200
mysql> GRANT REPLICATION SLAVE ON *.* to ‘rep1’@’192.168.1.200’ identified by ‘password’;

Query the status of the primary database

Need to pay attention here, if the query returns
mysql> show slave status;
Empty set (0.01 sec)

This is because the bin-log is not enabled, you need to modify the /etc/my.cnf file
server-id =1
log-bin=mysql-master-bin

Also need to pay attention to when modifying the file. After mysql5.7, you need to specify the server-id when you open the binlog. Otherwise, you will get an error.

2 Configuring the slave server

Modify the configuration file from the server /opt/mysql/etc/my.cnf

Change server-id = 1 to server-id = 2 and make sure the ID is not used by other MySQL services.

Start mysql service
service mysqld start

change master to
master_host=’192.168.1.100′,
master_user=’root’,
master_password=’mohan..’,
master_log_file=’mysql-master-bin.000001′,
master_log_pos=154;

Start the slave synchronization process
mysql> start slave after the correct execution ;

Note that there is another pit here.
Even if the start slave is successful, the master-slave copy is still failing.
1. Error message
mysql> show slave staus;

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have Equal MySQL server UUIDs;
These UUIDs must be different for replication to work.

2. View the master-slave server_id variable
master_mysql> show variables like ‘server_id’;
+—————+- ——+
| Variable_name | Value |
+—————+——-+
| server_id | 33|
+——- ——–+——-+

3 from mysql , solve the fault
### view auto.cnf file
[root@dbsrv1 ~] cat /data/mysqldata/auto.cnf ### uuid
[Auto]
Server-uuid = 62ee10aa-b1f7-11e4-90ae-080 027 615 026

[dbsrv2 the root @ ~] More /data/mysqldata/auto.cnf # ### from the uuid, there really is repeated, because the cloned Virtual machine, only change server_id not
[auto]
server-uuid=62ee10aa-b1f7-11e4-90ae-080027615026

[root@dbsrv2 ~]# mv /data/mysqldata/auto.cnf /data/mysqldata/auto.cnf.bk # ##Rename the file
[root@dbsrv2 ~]# service mysql restart ### Restart mysql
Shutting down MySQL.[ OK ]
Starting MySQL.[ OK ]
[root@dbsrv2 ~]# more /data/mysqldata/auto.cnf ###Automatically generate a new auto.cnf file after reboot, ie new UUID
[auto]
server-uuid=6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9

slave

[root@dbsrv1 ~]# mysql -uroot -pxxx -e “show slave status\G”|grep Running
Warning: Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

master_mysql> show slave hosts;
+———–+——+——+———–+————————————–+
| Server_id | Host | Port | Master_id | Slave_UUID |
+———–+——+——+———–+————————————–+
|33 | | 3306 |11 | 62ee10aa-b1f7-11e4-90ae-080027615030 |
|22 | | 3306 |11 | 6ac0fdae-b5d7-11e4-a9f3-0800278ce5c9 |
+———–+——+——+———–+————————————–+

The values ??of Slave_IO_Running and Slave_SQL_Running must be YES to indicate that the status is normal.

If the application data already exists on the primary server, the following processing is required when performing the master-slave replication:
(1) The primary database performs the lock table operation, and the data is not allowed to be written again.
mysql> FLUSH TABLES WITH READ LOCK;

(2) View the status of the main database
mysql> show master status;

(3) Record the values ??of FILE and Position.
Copy the data file of the primary server (the entire /opt/mysql/data directory) to the secondary server. It is recommended to compress it through the tar archive and then transfer it to the secondary server.

(4) cancel the main database lock
mysql> UNLOCK TABLES;

3 Verify master-slave replication

Create the database first_db on the primary server
mysql> create database first_db;
Query Ok, 1 row affected (0.01 sec)

Create a table first_tb on the primary server
mysql> create table first_tb(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)

Insert the record
mysql> insert into first_tb values ??(001, “myself”) in the table first_tb on the primary server ;
Query Ok, 1 row affected (0.00 sec)

Viewing from the server

mysql> show databases;

MySQL read and write separation configuration under CentOS 7.2
MySQL read and write separation configuration

Environment: CentOS 7.2 MySQL 5.7

Scene Description:
Database Master Primary Server: 192.168.1.100
Database Slave Slave Server: 192.168.1.200
MySQL-Proxy Dispatch Server: 192.168.1.210

The following operations are performed on the 192.168.1.210 MySQL-Proxy scheduling server.

1. Check the software package required by the system

You need to configure the EPEL YUM source
wget before installation https://mirrors.ustc.edu.cn/epel//7/x86_64/Packages/e/epel-release-7-11.noarch.rpm
rpm -ivh epel-release-7 -11.noarch.rpm
yum clean all
yum update

yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib*

2. Compile and install lua

The read-write separation of MySQL-Proxy is mainly implemented by the rw-splitting.lua script, so you need to install lua.

Lua can
download the source package from http://www.lua.org/download.html in the following way.

Search for the relevant rpm package from rpm.pbone.net
download. Fedora . RedHat .com/pub/fedora/epel/5/i386/lua-5.1.4-4.el5.i386.rpm
download.fedora.redhat.com/ Pub/fedora/epel/5/x86_64/lua-5.1.4-4.el5.x86_64.rpm

Here we recommend to use the source package to install
cd /opt/install
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar zvfx lua-5.1.4.tar.gz
cd lua-5.1 .4
make linux
make install
mkdir /usr/lib/pkgconfig/
cp /opt/install/lua-5.1.4/etc/lua.pc /usr/lib/pkgconfig/
export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig

Attention problem

When compiling, the problem is that there is a lack of dependencies** readline**, then readline depends on ncurses, so you must first install two software
yum install -y readline-devel ncurses-devel

3. Install and configure MySQL-Proxy

Download mysql-proxy

???http://dev.mysql.com/downloads/mysql-proxy/
wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
mv zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

** Configure mysql-proxy, create the main configuration file **
cd /usr/local/mysql-proxy
mkdir lua #Create script storage directory
mkdir logs #Create log directory
cp share / doc / mysql-proxy / rw-splitting.lua . /lua #copy read and write separation configuration file
vi /etc/mysql-proxy.cnf #Create configuration file
[mysql-proxy]
user=root
#Run mysql-proxy user admin-username=proxyuser #??mysql user
admin- Password=123456 #user’s password
proxy-address=192.168.1.210:4040 #mysql-proxyRun ip and port, no port, default 4040
proxy-read-only-backend-addresses=192.168.1.200 #Specify backend from slave Read the data
proxy-backend-addresses=192.168.1.100 #Specify the backend master master write data
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #Specify the read-write separation configuration file Location
admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua #Specify the management script
log-file=/var/log/mysql-proxy.log #log location
Log-level=info #definition log log level
daemon=true#run
keepalive=true in daemon mode #mysql-proxy crash, try to restart

There is a pit here.

The comments in the configuration file should be completely deleted, otherwise it may cause some characters that cannot be recognized.
This is not the most pit, the most pit is: even if you delete the comment, remove the extra white space, you may still report the following error:
2018-09-21 06:39:40: (critical) Key file contains key “daemon ” Which has a value that cannot be interpreted.”

2018-09-21 06:52:22: (critical) Key file contains key “keepalive” which has a value that cannot be interpreted.

The reason for the above problem is daemon=true, keepalive=true is not written now, to be changed to:
daemon=1
keepalive=1

Execute permissions to the configuration file

chmod 660 /etc/mysql-porxy.cnf
Configuring the admin.lua file

In the /etc/mysql-proxy.cnf configuration file, the management file of /usr/local/mysql-proxy/lua/admin.lua is still not created yet. So, now you need to edit and create the admin.lua file. For this version of mysql-proxy-0.8.5, I found the following admin.lua script, which is valid for this version:

vim /usr/local/mysql-proxy/lua/admin.lua
function set_error(errmsg)
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = errmsg or “error”
}
end
function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error(“[admin] we only handle text-based queries (COM_QUERY)”)
return proxy.PROXY_SEND_RESULT
end
local query = packet:sub(2)
local rows = { }
local fields = { }
if query:lower() == “select * from backends” then
fields = {
{ name = “backend_ndx”,
type = proxy.MYSQL_TYPE_LONG },
{ name = “address”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “state”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “type”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “uuid”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “connected_clients”,
type = proxy.MYSQL_TYPE_LONG },
}
for i = 1, #proxy.global.backends do
local states = {
“unknown”,
“up”,
“down”
}
local types = {
“unknown”,
“rw”,
“ro”
}
local b = proxy.global.backends[i]
rows[#rows + 1] = {
i,
b.dst.name, — configured backend address
states[b.state + 1], — the C-id is pushed down starting at 0
types[b.type + 1], — the C-id is pushed down starting at 0
b.uuid, — the MySQL Server’s UUID if it is managed
b.connected_clients — currently connected clients
}
end
elseif query:lower() == “select * from help” then
fields = {
{ name = “command”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “description”,
type = proxy.MYSQL_TYPE_STRING },
}
rows[#rows + 1] = { “SELECT * FROM help”, “shows this help” }
rows[#rows + 1] = { “SELECT * FROM backends”, “lists the backends and their state” }
else
set_error(“use ‘SELECT * FROM help’ to see the supported commands”)
return proxy.PROXY_SEND_RESULT
end
proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end

** Modify the read-write separation configuration file**
vim /usr/local/mysql-proxy/lua/rw-splitting.luaif not proxy.global.config.rwsplit
proxy.global.config.rwsplit = {
min_idle_connections = 1, #default When there are more than 4 connections, the read/write separation starts, and 1
max_idle_connections = 1, #
default8 , changed to 1 is_debug = false
}
end

mysql-proxy
/usr/local/mysql-proxy/bin/mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

netstat -tupln | grep 4000 #killall -9 has been started mysql-proxy #close mysql-proxy

Recent Posts

Pages

Categories

Archives

Recent Comments

Categories

MySQL master-slave and proxy server

Leave a Reply Cancel reply

PRODUCTS