MySQL master-slave synchronization and read/write splitting configuration step _ MySQL

MySQL master/slave synchronization and read/write splitting configuration steps bitsCN.com

Configure MySQL master-slave backup and read/write splitting as required, and take the following notes on the setup steps, problems, and solutions;
MySQL has been installed on the two servers, which are all packaged in rpm and can be used properly.
To avoid unnecessary troubles, the MySQL versions of the master and slave servers should be consistent as much as possible;
Environment: 192.168.0.1 (Master)
192.168.0.2 (Slave)
MySQL Version: Ver 14.14 Distrib 5.1.48, for pc-linux-gnu (i686) using readline 5.1
1. log on to the Master server, modify my. cnf, and add the following content;
Server-id = 1 // database id. if it is set to 1, it is expressed as a Master. master_id must be a positive integer between 1 and 232-1;
Log-bin = mysql-bin // enable binary logs;
Binlog-do-db = data // name of the binary database to be synchronized;
Binlog-ignore-db = mysql // the name of the non-synchronous binary database. I heard this is very troublesome after synchronization, but I have not synchronized it;
Log-bin =/var/log/mysql/updatelog // Set the generated log file name;
Log-slave-updates // write the updated record to the binary file;
Slave-skip-errors // skip the error and continue copying;
2. create the user to use for replication;
Mysql> grant replication slave on *. * to test@192.168.0.2 identified '********'
3. restart mysql;
/Usr/bin/mysqladmin-uroot shutdown;
/Usr/bin/mysql_safe &
4. back up data on the Master;
After the locks, I directly lock the tar.gz data library file;
Mysql> flush tables with read lock;
Cd/var/lib/mysql
Tar data.tar.gz data
Then the remote scp is directly executed;
Scp./data.tar.gz root@192.168.0.2:/var/lib/mysql
5. log on to the Slave database server and modify my. cnf;
Server-id = 3 // 2 has been used on another server. if you need to add the Slave number and then count it in the future, it will be OK;
Log-bin = mysql-bin
Master-host = 192.168.0.1
Master-user = test
Master-password = ******
Master-port = 3306
Master-connect-retry = 60 // The time difference between the reconnection if the master server is disconnected;
Replicate-ignore-db = mysql // database that does not need to be backed up;
Replicate-do-db = data // the database to be backed up
Log-slave-update
Slave-skip-errors
6. decompress the file from the Master scp. you do not need to change the permission and owner here. the default value is not changed. you can modify the file based on your actual situation;
7. after completing the preceding steps, you can start slave and view the slave status;
Mysql> slave start;
Mysql> show slave status/G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: updatelog.000001
Read_Master_Log_Pos: 106
Relay_Log_File: onlinevc-relay-bin.000013
Relay_Log_Pos: 1069
Relay_Master_Log_File: updatelog.000001
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: data
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 1681
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
1 row in set (0.00 sec)

ERROR:
No query specified

8. view the status of the Master;
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Updatelog.000012 | 15016 | data | mysql |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
It can be seen that there is a problem with the File and Position of the two. you need to set Master_Log_File and Read_Master_Log_Pos for the master database on Slave; execute the following statement;
Mysql> slave stop;
Mysql> change master to MASTER_HOST = '2017. 168.0.1 ', MASTER_USER = 'test', MASTER_PASSWORD =' ****** ', MASTER_LOG_FILE = 'updatelog. 000012 ', MASTER_LOG_POS = 15016;
Make sure that Slave_IO_Running: Yes, Slave_ SQL _Running: Yes must be YES to prove that Slave's I/O and SQL are normal.
9. unlock the master database table;
Unlock tables;
The configuration of the master-slave MySQL server is complete. the test result is as follows;
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Updatelog.000012 | 717039 | data | mysql |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Mysql> show slave status/G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: updatelog.20.12
Read_Master_Log_Pos: 717039
Relay_Log_File: onlinevc-relay-bin.000013
Relay_Log_Pos: 1222
Relay_Master_Log_File: updatelog.20.12
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: data
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 717039
Relay_Log_Space: 1834
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
1 row in set (0.00 sec)

ERROR:
No query specified
#################################### The following is the MySQL database read/write splitting procedure #################################### ######
MySQL-proxy is used to implement read/write splitting of databases;
The required installation package is as follows;
1) check-0.9.8
2. glib-2.18.4
3. libevent-2.0.6-rc
4. lua-5.1.4
Wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
5. pkg-config-0.23
6. mysql-5.0.56
7. mysql-proxy-0.8.0
Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.0.tar.gz

Other installation package addresses were not recorded at the time, but most of them are found on this site; http://sourceforge.net/
& Installation start &&&&&&&&
1. tar-zxvf check-0.8.4.tar.gz
Cdcheck-0.8.4
./Configure
Make
Make install
2. tar-zxvf glib-2.18.4.tar.gz // The system rpm package may have a low version Problem 3;
./Configure
Make
Make install
3. tar-zxvf libevent-2.0.6-rc.tar.gz
Cd libevent-2.0.6-rc
./Configure -- prefix =/usr/local/libevent
Make & make install
4. tar-zxvf lua-5.1.4.tar.gz
INSTALL_TOP =/usr/local/lua // to install lua under/var/lib/lua, modify the Makefile under it;
Or run sed-I's # INSTALL_TOP =/usr/local/lua # 'makefile
Root @ testmysql [/software/lua-5.1.4] # make
Please do
Make PLATFORM
Where PLATFORM is one of these:
Aix ansi bsd freebsd generic linux macosx mingw posix solaris
See INSTALL for complete instructions.
This requires you to select the platform used by the server;
Run: make linux // an error occurs after the command is executed here. the solution is located in Area 1 of the solution area below. skip this step first;
Run make install.
Set environment variables:
Export LUA_CFLAGS = "-I/usr/local/lua/include" LUA_LIBS = "-L/usr/local/lua/lib-llua-ldl" LDFLAGS = "-L/usr /local/libevent/lib-lm"
Export CPPFLAGS = "-I/usr/local/libevent/include"
Export CFLAGS = "-I/usr/local/libevent/include"

5. tar-zxvf pkg-config-0.23.tar.gz
Cd pkg-config-0.23
./Configure
Make
Make install
After installation, run the following command: cp etc/lua. pc/usr/local/lib/pkgconfig/lua5.1.pc // for the reason, see Solution Area 2 below;
6. install the MySQL client;
Because MySQL is installed on the server system by default, and the client is not installed, I have installed the client and devel as shown below the installed rpm Package;
Root @ testmysql [/software/lua-5.1.4] # rpm-qa | grep MySQL
MySQL-client-5.1.48-0.glibc23
MySQL-bench-5.0.91-0.glibc23
MySQL-test-5.1.48-0.glibc23
MySQL-shared-5.1.48-0.glibc23
MySQL-server-5.1.48-0.glibc23
MySQL-devel-5.1.48-0.glibc23
Since then, Mysql-proxy always reports errors and compilation fails. Instead, use the source code package client. (at this time, the rpm package is not uninstalled and the following installation is directly executed) // for the problem, see Solution Area 4;
Tar zxvf mysql-5.0.56.tar.gz // Here I directly use mysql's 5.0.56 source package;
Cd mysql-5.0.56
./Configure -- prefix =/usr/local/mysql -- without-server
Make & make install

7, tar xvf mysql-proxy-0.8.0.tar.gz
Cd mysql-proxy-0.8.0
./Configure -- prefix =/usr/local/mysql-proxy -- with-mysql =/usr/local/mysql -- with-lua // problem solving area 4;
Make & Make install

8. create a mysql-proxy.sh in/var/lib/bin, the content is as follows;
#! /Bin/bash
LUA_PATH = "/usr/local/mysql-proxy/lib/mysql-proxy/lua /?. Lua "/usr/local/mysql-proxy/bin/mysql-proxy -- proxy-backend-addresses = 192.168.0.1: 3306 -- proxy-read-only-backend-addresses = 192.168.0.2: 3306 -- proxy-lua-script =/usr/local/mysql-proxy/lib/mysql-proxy/rw-splitting.lua>/var/log/mysql-proxy.log &
Add the executable permission;
Chmod a + x/var/lib/bin/mysql-proxy.sh
Run:/var/lib/bin/mysql-proxy.sh to start the service;
9. check whether an account has been opened: 4040, 4041;
Root @ testmysql [/usr/local/bin] # netstat-an | grep 404 *
Tcp 0 0 0.0.0.0: 4040 0.0.0.0: * LISTEN
Tcp 0 0 0.0.0.0: 4041 0.0.0.0: * LISTEN
10. test read/write splitting. skip this step. all configurations have been completed.

&&&&&&&&&& &&&&&&&&&&&&&&&&&&
1. the following error is reported when you execute make linux in step 4 of installation:
Root @ testmysql [/software/lua-5.1.4] # make linux
Cd src & make linux
Make [1]: Entering directory '/software/lua-5.1.4/src'
Make all MYCFLAGS =-DLUA_USE_LINUX MYLIBS = "-Wl,-E-ldl-lreadline-lhistory-lncurses"
Make [2]: Entering directory '/software/lua-5.1.4/src'
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lapi. o lapi. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lcode. o lcode. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o ldebug. o ldebug. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o drop-down. o drop-down. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o ldump. o ldump. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lfunc. o lfunc. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lgc. o lgc. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o llex. o llex. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lmem. o lmem. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lobject. o lobject. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lopcodes. o lopcodes. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lparser. o lparser. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lstate. o lstate. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lstring. o lstring. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o ltable. o ltable. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o ltm. o ltm. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lundump. o lundump. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lvm. o lvm. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lzio. o lzio. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lauxlib. o lauxlib. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lbaselib. o lbaselib. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o ldblib. o ldblib. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o liolib. o liolib. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lmathlib. o lmathlib. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o loslib. o loslib. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o ltablib. o ltablib. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lstrlib. o lstrlib. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o loadlib. o loadlib. c
Gcc-O2-Wall-DLUA_USE_LINUX-c-o linit. o linit. c
Ar rcu liblua. a lapi. o lcode. o ldebug. o. o ldump. o lfunc. o lgc. o llex. o lmem. o lobject. o lopcodes. o lparser. o lstate. o lstring. o ltable. o ltm. o lundump. o lvm. o lzio. o lauxlib. o lbaselib. o ldblib. o liolib. o lmathlib. o loslib. o ltablib. o lstrlib. o loadlib. o linit. o
Ranlib liblua.
Gcc-O2-Wall-DLUA_USE_LINUX-c-o lua. o lua. c
In file encoded ded from lua. h: 16,
From lua. c: 15:
Luaconf. h: 275: 31: error: readline/readline. h: No such file or directory
Luaconf. h: 276: 30: error: readline/history. h: No such file or directory
Lua. c: In function compute when ushline execution?
Lua. c: 182: warning: implicit declaration of function compute extends eadline limit?
Lua. c: 182: warning: assignment makes pointer from integer without a cast
Lua. c: In function compute when oadline does?
Lua. c: 210: warning: implicit declaration of function compute when dd_history statements?
Make [2]: *** [lua. o] Error 1
Make [2]: Leaving directory '/software/lua-5.1.4/src'
Make [1]: *** [linux] Error 2
Make [1]: Leaving directory '/software/lua-5.1.4/src'
Make: *** [linux] Error 2

Solution: yum install libtermcap-devel
Yum install ncurses-devel
Yum install libevent-devel
Yum install readline-devel

2. an error is reported when installing MySQL-proxy:
Checking for LUA... configure: error: Package requirements (lua5.1 >=5.1) were not met:

No package 'lua5. 1' found

Consider adjusting the PKG_CONFIG_PATH environment variable if you
Installed software in a non-standard prefix.

Alternatively, you may set the environment variables LUA_CFLAGS
And LUA_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.
Solution:
Cp etc/lua. pc/usr/local/lib/pkgconfig/lua5.1.pc

3. an error is reported when installing MySQL-proxy:
Checking for GLIB... configure: error: Package requirements (glib-2.0> = 2.16.0) were not met:

No package 'glib-2.0 'found

Consider adjusting the PKG_CONFIG_PATH environment variable if you
Installed software in a non-standard prefix.

Alternatively, you may set the environment variables GLIB_CFLAGS
And GLIB_LIBS to avoid the need to call pkg-config.
See the pkg-config man page for more details.
Solution:
1. check that the system has installed the glib rpm package;
Glibc-2.5-49.el5_5.4
Glibc-headers-2.5-49.el5_5.4
Glib2-2.12.3-4.el5_3.1
Glibc-common-2.5-49.el5_5.4
Glibc-devel-2.5-49.el5_5.4
2. download and install glib-2.18.4.tar.gz

4. when you first use mysq_config specified by rpm in the system, an error is reported during mysql-proxy installation. The information is as follows;
Configure: error: mysql_config not exists or not executable, use $./configure -- with-mysql =/path/to/mysql_config
See the following requirements in the help installation document:
-- With-mysql [= PATH] Include MySQL support. PATH is the path to 'MySQL _ config '.
Solution: Step 1 in the installation package.

BitsCN.com