{"id":7759,"date":"2018-10-08T12:25:00","date_gmt":"2018-10-08T04:25:00","guid":{"rendered":"http:\/\/rmohan.com\/?p=7759"},"modified":"2018-10-08T12:25:17","modified_gmt":"2018-10-08T04:25:17","slug":"mysql-master-slave","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=7759","title":{"rendered":"MySQL master-slave and proxy server"},"content":{"rendered":"

MySQL master-slave principle and process<\/strong><\/p>\n

principle<\/strong><\/p>\n

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.<\/p>\n

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.<\/p>\n

Basic process<\/strong><\/p>\n

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);<\/p>\n

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.<\/p>\n

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”<\/p>\n

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.<\/p>\n

Several modes of MySQL replication<\/p>\n

Starting with MySQL 5.1.12, you can do this in three modes:<\/p>\n

\u2013 based on statement-based replication (SBR),<\/p>\n

\u2013 row-based replication (RBR),<\/p>\n

\u2013 mixed-based replication (MBR)<\/p>\n

Accordingly, there are three formats for binlog: STATEMENT, ROW, MIXED. In the MBR mode, the SBR mode is the default.<\/p>\n

Set master-slave replication mode:
\nlog-bin=mysql-bin<\/p>\n

#binlog_format=”STATEMENT”<\/p>\n

#binlog_format=”ROW”<\/p>\n

binlog_format=”MIXED”<\/p>\n

It is also possible to dynamically modify the format of the binlog at runtime. For example
\nmysql> SET SESSION binlog_format = ‘STATEMENT’;<\/p>\n

mysql> SET SESSION binlog_format = ‘ROW’;<\/p>\n

mysql> SET SESSION binlog_format = ‘MIXED’;<\/p>\n

mysql> SET GLOBAL binlog_format = ‘STATEMENT’;<\/p>\n

Mysql master-slave replication configuration<\/p>\n

Version: mysql5.7 CentOS 7.2<\/p>\n

Scenario description:
\nPrimary database server: 192.168.1.100, MySQL is installed, and there is no application data.
\nFrom the database server: 192.168.1.200, MySQL is already installed and there is no application data.<\/p>\n

1 Operations on the primary server<\/p>\n

Start mysql service
\nservice mysqld start<\/p>\n

Log in to the MySQL server via the command line
\nmysql -uroot -p’new-password’<\/p>\n

Authorize copy permissions to the database server 192.168.1.200
\nmysql> GRANT REPLICATION SLAVE ON *.* to ‘rep1’@’192.168.1.200’ identified by ‘password’;<\/p>\n

Query the status of the primary database<\/p>\n

When configuring the slave server,
\nmysql> show master status;
\n+————————-+———- +————–+——————+————— —-+
\n| File| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
\n+————————-+——- —+————–+——————+———— ——-+
\n| mysql-master-bin.000001 | 154 | | | |
\n+————————-+- ———+————–+——————+—— ————-+<\/p>\n

Need to pay attention here, if the query returns
\nmysql> show slave status;
\nEmpty set (0.01 sec)<\/p>\n

This is because the bin-log is not enabled, you need to modify the \/etc\/my.cnf file
\nserver-id =1
\nlog-bin=mysql-master-bin<\/p>\n

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.<\/p>\n

2 Configuring the slave server<\/p>\n

Modify the configuration file from the server \/opt\/mysql\/etc\/my.cnf<\/p>\n

Change server-id = 1 to server-id = 2 and make sure the ID is not used by other MySQL services.<\/p>\n

Start mysql service
\nservice mysqld start<\/p>\n

Login to manage MySQL server
\nmysql -uroot -p’new-password’<\/p>\n

change master to
\nmaster_host=’192.168.1.100′,
\nmaster_user=’root’,
\nmaster_password=’mohan..’,
\nmaster_log_file=’mysql-master-bin.000001′,
\nmaster_log_pos=154;<\/p>\n

Start the slave synchronization process
\nmysql> start slave after the correct execution ;<\/p>\n

Note that there is another pit here.
\nEven if the start slave is successful, the master-slave copy is still failing.
\n1. Error message
\nmysql> show slave staus;<\/p>\n

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

2. View the master-slave server_id variable
\nmaster_mysql> show variables like ‘server_id’;
\n+—————+- ——+
\n| Variable_name | Value |
\n+—————+——-+
\n| server_id | 33|
\n+——- ——–+——-+<\/p>\n

slave_mysql> show variables like ‘server_id’;
\n+—————+——- +
\n| Variable_name | Value |
\n+—————+——-+
\n| server_id | 11|
\n+————- –+——-+
\n— From the above situation, the master has used a different server_id<\/p>\n

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

[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
\n[auto]
\nserver-uuid=62ee10aa-b1f7-11e4-90ae-080027615026<\/p>\n

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

slave<\/p>\n

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

###uuid
\nmaster_mysql> show variables like ‘server_uuid’;
\n+—————+————————————–+
\n| Variable_name | Value|
\n+—————+————————————–+
\n| server_uuid | 62ee10aa-b1f7-11e4-90ae-080027615026 |
\n+—————+————————————–+
\n1 row in set (0.00 sec)<\/p>\n

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

The values ??of Slave_IO_Running and Slave_SQL_Running must be YES to indicate that the status is normal.<\/p>\n

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

(2) View the status of the main database
\nmysql> show master status;<\/p>\n

(3) Record the values ??of FILE and Position.
\nCopy 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.<\/p>\n

(4) cancel the main database lock
\nmysql> UNLOCK TABLES;<\/p>\n

3 Verify master-slave replication<\/p>\n

Create the database first_db on the primary server
\nmysql> create database first_db;
\nQuery Ok, 1 row affected (0.01 sec)<\/p>\n

Create a table first_tb on the primary server
\nmysql> create table first_tb(id int(3),name char(10));
\nQuery Ok, 1 row affected (0.00 sec)<\/p>\n

Insert the record
\nmysql> insert into first_tb values ??(001, \u201cmyself\u201d) in the table first_tb on the primary server ;
\nQuery Ok, 1 row affected (0.00 sec)<\/p>\n

Viewing from the server<\/p>\n

mysql> show databases;<\/p>\n

MySQL read and write separation configuration under CentOS 7.2
\nMySQL read and write separation configuration<\/p>\n

Environment: CentOS 7.2 MySQL 5.7<\/p>\n

Scene Description:
\nDatabase Master Primary Server: 192.168.1.100
\nDatabase Slave Slave Server: 192.168.1.200
\nMySQL-Proxy Dispatch Server: 192.168.1.210<\/p>\n

The following operations are performed on the 192.168.1.210 MySQL-Proxy scheduling server.<\/p>\n

1. Check the software package required by the system<\/p>\n

You need to configure the EPEL YUM source
\nwget before installation https:\/\/mirrors.ustc.edu.cn\/epel\/\/7\/x86_64\/Packages\/e\/epel-release-7-11.noarch.rpm
\nrpm -ivh epel-release-7 -11.noarch.rpm
\nyum clean all
\nyum update<\/p>\n

yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib*<\/p>\n

2. Compile and install lua<\/p>\n

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

Lua can
\ndownload the source package from http:\/\/www.lua.org\/download.html in the following way.<\/p>\n

Search for the relevant rpm package from rpm.pbone.net
\ndownload. Fedora . RedHat .com\/pub\/fedora\/epel\/5\/i386\/lua-5.1.4-4.el5.i386.rpm
\ndownload.fedora.redhat.com\/ Pub\/fedora\/epel\/5\/x86_64\/lua-5.1.4-4.el5.x86_64.rpm<\/p>\n

Here we recommend to use the source package to install
\ncd \/opt\/install
\nwget http:\/\/www.lua.org\/ftp\/lua-5.1.4.tar.gz
\ntar zvfx lua-5.1.4.tar.gz
\ncd lua-5.1 .4
\nmake linux
\nmake install
\nmkdir \/usr\/lib\/pkgconfig\/
\ncp \/opt\/install\/lua-5.1.4\/etc\/lua.pc \/usr\/lib\/pkgconfig\/
\nexport PKG_CONFIG_PATH=$PKG_CONFIG_PATH:\/usr\/lib\/pkgconfig<\/p>\n

Attention problem<\/p>\n

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
\nyum install -y readline-devel ncurses-devel<\/p>\n

3. Install and configure MySQL-Proxy<\/p>\n

Download mysql-proxy<\/p>\n

???http:\/\/dev.mysql.com\/downloads\/mysql-proxy\/
\nwget https:\/\/downloads.mysql.com\/archives\/get\/file\/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
\ntar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
\nmv zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit \/usr\/local\/mysql-proxy<\/p>\n

** Configure mysql-proxy, create the main configuration file **
\ncd \/usr\/local\/mysql-proxy
\nmkdir lua #Create script storage directory
\nmkdir logs #Create log directory
\ncp share \/ doc \/ mysql-proxy \/ rw-splitting.lua . \/lua #copy read and write separation configuration file
\nvi \/etc\/mysql-proxy.cnf #Create configuration file
\n[mysql-proxy]
\nuser=root
\n#Run mysql-proxy user admin-username=proxyuser #??mysql user
\nadmin- Password=123456 #user’s password
\nproxy-address=192.168.1.210:4040 #mysql-proxyRun ip and port, no port, default 4040
\nproxy-read-only-backend-addresses=192.168.1.200 #Specify backend from slave Read the data
\nproxy-backend-addresses=192.168.1.100 #Specify the backend master master write data
\nproxy-lua-script=\/usr\/local\/mysql-proxy\/lua\/rw-splitting.lua #Specify the read-write separation configuration file Location
\nadmin-lua-script=\/usr\/local\/mysql-proxy\/lua\/admin.lua #Specify the management script
\nlog-file=\/var\/log\/mysql-proxy.log #log location
\nLog-level=info #definition log log level
\ndaemon=true#run
\nkeepalive=true in daemon mode #mysql-proxy crash, try to restart<\/p>\n

There is a pit here.<\/p>\n

The comments in the configuration file should be completely deleted, otherwise it may cause some characters that cannot be recognized.
\nThis 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:
\n2018-09-21 06:39:40: (critical) Key file contains key “daemon ” Which has a value that cannot be interpreted.”<\/p>\n

2018-09-21 06:52:22: (critical) Key file contains key \u201ckeepalive\u201d which has a value that cannot be interpreted.<\/p>\n

The reason for the above problem is daemon=true, keepalive=true is not written now, to be changed to:
\ndaemon=1
\nkeepalive=1<\/p>\n

Execute permissions to the configuration file<\/p>\n

chmod 660 \/etc\/mysql-porxy.cnf
\nConfiguring the admin.lua file<\/p>\n

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:<\/p>\n

vim \/usr\/local\/mysql-proxy\/lua\/admin.lua
\nfunction set_error(errmsg)
\nproxy.response = {
\ntype = proxy.MYSQLD_PACKET_ERR,
\nerrmsg = errmsg or “error”
\n}
\nend
\nfunction read_query(packet)
\nif packet:byte() ~= proxy.COM_QUERY then
\nset_error(“[admin] we only handle text-based queries (COM_QUERY)”)
\nreturn proxy.PROXY_SEND_RESULT
\nend
\nlocal query = packet:sub(2)
\nlocal rows = { }
\nlocal fields = { }
\nif query:lower() == “select * from backends” then
\nfields = {
\n{ name = “backend_ndx”,
\ntype = proxy.MYSQL_TYPE_LONG },
\n{ name = “address”,
\ntype = proxy.MYSQL_TYPE_STRING },
\n{ name = “state”,
\ntype = proxy.MYSQL_TYPE_STRING },
\n{ name = “type”,
\ntype = proxy.MYSQL_TYPE_STRING },
\n{ name = “uuid”,
\ntype = proxy.MYSQL_TYPE_STRING },
\n{ name = “connected_clients”,
\ntype = proxy.MYSQL_TYPE_LONG },
\n}
\nfor i = 1, #proxy.global.backends do
\nlocal states = {
\n“unknown”,
\n“up”,
\n“down”
\n}
\nlocal types = {
\n“unknown”,
\n“rw”,
\n“ro”
\n}
\nlocal b = proxy.global.backends[i]
\nrows[#rows + 1] = {
\ni,
\nb.dst.name, — configured backend address
\nstates[b.state + 1], — the C-id is pushed down starting at 0
\ntypes[b.type + 1], — the C-id is pushed down starting at 0
\nb.uuid, — the MySQL Server’s UUID if it is managed
\nb.connected_clients — currently connected clients
\n}
\nend
\nelseif query:lower() == “select * from help” then
\nfields = {
\n{ name = “command”,
\ntype = proxy.MYSQL_TYPE_STRING },
\n{ name = “description”,
\ntype = proxy.MYSQL_TYPE_STRING },
\n}
\nrows[#rows + 1] = { “SELECT * FROM help”, “shows this help” }
\nrows[#rows + 1] = { “SELECT * FROM backends”, “lists the backends and their state” }
\nelse
\nset_error(“use ‘SELECT * FROM help’ to see the supported commands”)
\nreturn proxy.PROXY_SEND_RESULT
\nend
\nproxy.response = {
\ntype = proxy.MYSQLD_PACKET_OK,
\nresultset = {
\nfields = fields,
\nrows = rows
\n}
\n}
\nreturn proxy.PROXY_SEND_RESULT
\nend<\/p>\n

** Modify the read-write separation configuration file**
\nvim \/usr\/local\/mysql-proxy\/lua\/rw-splitting.luaif not proxy.global.config.rwsplit
\nproxy.global.config.rwsplit = {
\nmin_idle_connections = 1, #default When there are more than 4 connections, the read\/write separation starts, and 1
\nmax_idle_connections = 1, #
\ndefault8 , changed to 1 is_debug = false
\n}
\nend<\/p>\n

mysql-proxy
\n\/usr\/local\/mysql-proxy\/bin\/mysql-proxy –defaults-file=\/etc\/mysql-proxy.cnf<\/p>\n

netstat -tupln | grep 4000 #killall -9 has been started mysql-proxy #close mysql-proxy<\/p>\n","protected":false},"excerpt":{"rendered":"

MySQL master-slave principle and process<\/p>\n

principle<\/p>\n

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7759"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7759"}],"version-history":[{"count":2,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7759\/revisions"}],"predecessor-version":[{"id":7761,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7759\/revisions\/7761"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7759"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7759"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7759"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}