Mysql master-Slave synchronization, read-write separation configuration steps _mysql

Now use the two servers have been installed MySQL, are all rpm packaging, can be used normally.
In order to avoid unnecessary trouble, master server MySQL version as much as possible to maintain consistency;
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, represented as master at 1, where master_id must be a positive integer value between 1 and 232–1;
Log-bin=mysql-bin//enable binary log;
Binlog-do-db=data//The binary database name that needs to be synchronized;
Binlog-ignore-db=mysql//unsynchronized binary database name; This synchronization after heard very troublesome, I did not sync;
Log-bin=/var/log/mysql/updatelog//Set the generated log filename;
Log-slave-updates//write the updated records into binary files;
Slave-skip-errors//Skip error, continue replication;
2, the establishment of replication to be used by users;
Mysql>grant replication Slave on *.* to test@192.168.0.2 identified by ' ******** '
3, restart MySQL;
/usr/bin/mysqladmin-uroot shutdown;
/usr/bin/mysql_safe &
4, now back up the data on master;
Lock after 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 direct implementation of the remote SCP;
SCP./data.tar.gz Root@192.168.0.2:/var/lib/mysql
5, login Slave database server, modify 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 will be OK;
Log-bin=mysql-bin
Master-host = 192.168.0.1
Master-user = Test
Master-password = Hu Jintao
Master-port = 3306
MASTER-CONNECT-RETRY=60//If the primary server is found disconnected, reconnect the time lag;
Replicate-ignore-db=mysql//databases that do not need to be backed up;
Replicate-do-db=data//Database to be backed up
Log-slave-update
Slave-skip-errors
6, decompression just from master SCP file, here do not change the permissions, the owner, the default has not changed, can be modified according to the actual situation;
7, after the completion of the above, you can start slave, check the slave state;
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 state above 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 are problems in the File and position of the two, to slave the Master_log_file and Read_master_log_pos of the corresponding main library, and to execute the following statements;
Mysql>slave stop;
Mysql>change MASTER to master_host= ' 192.168.0.1 ', master_user= ' test ', master_password= ' Hu Jintao ', master_log_file= ' updatelog.000012 ', master_log_pos=15016;
Make sure that the Slave_io_running:yes, 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 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
#################################### The following is the MySQL database read-write detach operation step ##########################################
This is the use of Mysql's Own (Mysql-proxy) agent to achieve database read and write separation;
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 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 install Lua to/var/lib/lua under, it is necessary to modify its makefile;
or direct execution: Sed-i ' s#install_top=/usr/local#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
INSTALL for complete instructions.
This is for you to select the platform used by the server;
Execution: Make Linux//There is an error after execution, the solution is in the following problem resolution area 1, where first skip;
Re-execution: make install
To 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 to perform: CP etc/lua.pc/usr/local/lib/pkgconfig/lua5.1.pc//reason see the following problem solving area 2;
6, install the MySQL client;
Because this server system is installed by default MySQL, did not install the client, I installed clients, 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
After the Mysql-proxy always error, compile not past, helpless under the source package client; (at this time the RPM package is not unloaded, directly perform the installation below)//Here are the problem solving area 4;
Tar zxvf mysql-5.0.56.tar.gz//Here I use the MySQL 5.0.56 source package directly;
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 Resolution Office 4 is introduced;
Make && make install

8, in/var/lib/bin to create mysql-proxy.sh, the contents are 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
Implementation:/var/lib/bin/mysql-proxy.sh start service;
9, verify whether to open an account: 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 and write separation, this step skip, all configuration has been completed.

&&&&&&&&&&&&&&&&&&&&&& &&&&& Problem Solving Area &&&&&&&&&&&&&&&& &&&&&&&&&&&&
1, in the installation of the fourth step of the implementation make Linux times error is as follows:
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 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 the 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 transmission 榩 ushline transmission?
LUA.C:182:WARNING:IMPLICIT Declaration of function transmission 榬 eadline transmission?
Lua.c:182:warning:assignment makes pointer from integer without a cast
lua.c:in function transmission 榣 oadline transmission?
LUA.C:210:WARNING:IMPLICIT Declaration of function transmission 榓 dd_history transmission?
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, installation 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, you may set the environment variables Lua_cflags
and lua_libs to avoid the need to call Pkg-config.
The Pkg-config man page is for more details.
Solution:
CP etc/lua.pc/usr/local/lib/pkgconfig/lua5.1.pc

3, installation 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, you may set the environment variables Glib_cflags
and glib_libs to avoid the need to call Pkg-config.
The Pkg-config man page is for more details.
Solution:
1, view the system has been 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 in the Mysq_config specified rpm, mysql-proxy installation error, the following information;
Configure:error:mysql_config NOT exists or not executable, use $./configure--with-mysql=/path/to/mysql_config
See the Help 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.