MySQL master-slave synchronization, read and write separation configuration steps

Now the two servers used have installed MySQL, all in rpm packaging, can be used normally.
In order to avoid unnecessary trouble, master and slave server MySQL version as far as possible to keep consistent;
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, login master server, modify MY.CNF, add the following content;
Server-id = 1//database ID number, 1 is represented as master, where master_id must be a positive integer value between 1 and 232–1;
Log-bin=mysql-bin//enable binary logging;
Binlog-do-db=data//binary database name that needs to be synchronized;
Binlog-ignore-db=mysql//unsynchronized binary database name; This synchronization after hearing very troublesome, I did not sync;
Log-bin=/var/log/mysql/updatelog//Set the generated log file name;
Log-slave-updates//write updated records into binary files;
Slave-skip-errors//Skip error, continue replication;
2, the establishment of replication to use the user;
Mysql>grant replication Slave on *.[email protected]Identified by ' ******** '
3, restart MySQL;
/usr/bin/mysqladmin-uroot shutdown;
/usr/bin/mysql_safe &
4. Now back up the data on master;
After locking I directly tar.gz data this library file;
Mysql>flush TABLES with READ LOCK;
Cd/var/lib/mysql
Tar data.tar.gz data
Then the remote SCP was executed directly;
SCP./data.tar.gz[email protected]:/var/lib/mysql
5, login Slave database server, modify the MY.CNF;
Server-id = 3//2 has been used on another server, if you want to add the slave number and then the next number is OK;
Log-bin=mysql-bin
Master-host = 192.168.0.1
Master-user = Test
Master-password = ******
Master-port = 3306
MASTER-CONNECT-RETRY=60//If the primary server is found to be disconnected, reconnect the time difference;
Replicate-ignore-db=mysql//databases that do not need to be backed up;
Replicate-do-db=data//databases that need to be backed up
Log-slave-update
Slave-skip-errors
6, decompression just from the master SCP came from the file, here do not change the permissions, the owner, the default is not changed, can be modified according to the actual situation;
7, after the completion of the above, you can start slave; view 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, check the status above 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)
This shows that both of the File, position exist problems, to slave to set the corresponding main library master_log_file, Read_master_log_pos; execute the following statement;
Mysql>slave stop;
Mysql>change MASTER to master_host= ' 192.168.0.1 ', master_user= ' test ', master_password= ' ****** ', master_log_file= ' updatelog.000012 ', master_log_pos=15016;
Make sure that both Slave_io_running:yes and Slave_sql_running:yes are Yes to prove that Slave I/O and SQL are normal.
9, unlock the main library table;
UNLOCK TABLES;
To this master-slave MySQL server configuration completed, the test results are 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.000012
read_master_log_pos:717039
relay_log_file:onlinevc-relay-bin.000013
relay_log_pos:1222
relay_master_log_file:updatelog.000012
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
#################################### below is the MySQL database read and write detach operation step ##########################################
This uses the Mysql own (mysql-proxy) proxy to realize the database read/write separation;
The required installation packages are 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 were found on this site; http://sourceforge.net/
&&&&&&&&&& Installation Start &&&&&&&&
1, TAR-ZXVF check-0.8.4.tar.gz
CD check-0.8.4
./configure
Make
Make install
2, TAR-ZXVF glib-2.18.4.tar.gz//System RPM package may have a low version of the 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//In order to put Lua installed under the/var/lib/lua, it is necessary to modify the makefile under it;
or direct execution: Sed-i ' s#install_top=/usr/local#install_top=/usr/local/lua# ' Makefile
[email protected][/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 the INSTALL for complete instructions.
This is for you to choose the platform used by the server;
Execution: Make Linux//error occurred here, the solution in the following problem resolution area 1, where first skip;
Re-execute: 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
do install
to execute after installation: CP ETC/LUA.PC/USR/LOCAL/LIB/PKGCONFIG/LUA5.1.PC//Cause see the problem resolution area below 2;
6, install the MySQL client;
Because this server system is installed by default MySQL, no client installed, I installed the client, devel the RPM package as shown below;
[ Email protected] [/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
After the Mysql-proxy always error, compile not the past, helpless under the source package client; (at this time the RPM package is not uninstalled, directly performed the following installation)//The problem is shown in the problem resolution area 4;
Tar zxvf mysql-5.0.56.tar.gz Here I directly use the MySQL 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 branch 4 is introduced;
Make && make install

8, in/var/lib/bin create mysql-proxy.sh, 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 &
then add executable permissions;
chmod a+x/var/lib/bin/mysql-proxy.sh
Execution:/var/lib/bin/mysql-proxy.sh start service;
9, verify whether to open an account: 4040, 4041;
[email protected] [/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 and write separation, this step is skipped, all configuration has been completed.

&&&&&&&&&&&&&&&&&&&&&& &&&&& Problem Solving Area &&&&&&&&&&&&&&&& &&&&&&&&&&&&
1. In the fourth step of the installation, make the Linux times error as follows:
[email protected][/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 ldo.o ldo.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 ldo.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 Lstrl IB.O LOADLIB.O LINIT.O
Ranlib LIBLUA.A
GCC-O2-WALL-DLUA_USE_LINUX-C-O lua.o lua.c
In file included 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 echnical Nixon ushline echnical?
Lua.c:182:warning:implicit declaration of function echnical 榬 eadline echnical?
Lua.c:182:warning:assignment makes pointer from integer without a cast
lua.c:in function echnical move oadline echnical?
Lua.c:210:warning:implicit declaration of function echnical 榓 dd_history echnical?
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

Workaround: Yum Install Libtermcap-devel
Yum Install Ncurses-devel
Yum Install Libevent-devel
Yum Install Readline-devel

2, install mysql-proxy times wrong:
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, may set the environment variables Lua_cflags
and lua_libs to avoid the need-to-call pkg-config.
See the Pkg-config Mans page for more details.
Workaround:
CP etc/lua.pc/usr/local/lib/pkgconfig/lua5.1.pc

3, install mysql-proxy times wrong:
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, may set the environment variables Glib_cflags
and glib_libs to avoid the need-to-call pkg-config.
See the Pkg-config Mans page for more details.
Workaround:
1, check the system has installed 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, just start using the system rpm specified Mysq_config, mysql-proxy installation error, information as follows;
Configure:error:mysql_config NOT exists or not executable, use $./configure--with-mysql=/path/to/mysql_config
See the Help in the installation documentation for the following requirements:
--with-mysql[=path] Include MySQL support. Path is the path to ' Mysql_config '.
The workaround is: the 6th step in the installation package.
For more information, refer to: http://www.jb51.net/article/29818.htm