Shulou(Shulou.com)05/31 Report--

This article mainly shows you "MySQL master-slave synchronization and read-write separation how to configure", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "MySQL master-slave synchronization and read-write separation how to configure" this article.

The two servers now in use have been installed with MySQL, all packaged by rpm, and can be used normally.

In order to avoid unnecessary trouble, try to keep the MySQL version of the master server and slave server 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. Log in to the Master server, modify the my.cnf, and add the following

Server-id = 1 / / Database ID number, 1 indicates Master, where master_id must be a positive integer value between 1 and 232mur1

Log-bin=mysql-bin / / enable binary logging

Binlog-do-db=data / / binary database name that needs to be synchronized

Binlog-ignore-db=mysql / / out-of-sync binary database name; it's said to be troublesome after synchronization, but I don't have synchronization.

Log-bin=/var/log/mysql/updatelog / / sets the name of the generated log file

Log-slave-updates / / write the updated record to a binary file

Slave-skip-errors / / Skip error and continue replication

2. Establish the users to be used for replication

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. Back up the data on Master now

After locking, I directly tar.gz data the library file.

Mysql > FLUSH TABLES WITH READ LOCK

Cd / var/lib/mysql

Tar data.tar.gz data

Then the remote scp is executed directly.

Scp. / data.tar.gz root@192.168.0.2:/var/lib/mysql

5. Log in to the Slave database server and modify my.cnf

Server-id = 3 / / 2 has already been used on another server. If you want to add the Slave number later, then you will 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 offline, the time difference to reconnect

Replicate-ignore-db=mysql / / databases that do not need backup

Replicate-do-db=data / / databases to be backed up

Log-slave-update

Slave-skip-errors

6. Decompress the file that just came from Master scp. There is no need to change the permission and ownership here. There is no change by default. You can modify it according to the actual situation.

7. After the above is completed, you can start slave; check the slave status

Mysql > slave start; www.2cto.com

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 on 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 with the File and Position of the two. To set the Master_Log_File and Read_Master_Log_Pos; of the corresponding main library on Slave, 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 Slave_IO_Running: Yes and Slave_SQL_Running: Yes are all YES in order to prove that the Slave's Imax O and SQL are working properly.

9. Unlock the main database table

UNLOCK TABLES

To this master-slave MySQL server configuration is completed, and 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 operation procedure for separating read and write from MySQL database # #

Here, MySQL's own (Mysql-proxy) agent is used to separate the read and write of the database.

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 that time, but most of them were found on this website; http://sourceforge.net/

& installation begins &

1. Tar-zxvf check-0.8.4.tar.gz

Cd check-0.8.4

. / configure

Make

Make install

2. There is a problem with the possible low version of the tar-zxvf glib-2.18.4.tar.gz / / system rpm package 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 under / var/lib/lua, modify the Makefile under it

Or execute it directly: sed-I's installed 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

See INSTALL for complete instructions.

This is for you to choose the platform that the server uses.

Execution: make linux / / an error occurred after execution here. The solution is in problem solving area 1 below, which is skipped here.

Execute again: make install

Set the environment variable:

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

To execute after installation: cp etc/lua.pc / usr/local/lib/pkgconfig/lua5.1.pc / / see the following problem solving area 2 for reasons

6. Install the MySQL client

Because MySQL is installed by default on this server system, and no client is installed, I installed the rpm package that has been installed as shown in client and devel below.

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, the Mysql-proxy has always reported errors and failed to compile, so it has no choice but to use the package client; (at this time, the rpm packages are not uninstalled, and the following installation is performed directly) / / see problem solving area 4 here

Tar zxvf-5.0.56.tar.gz / / here I directly use the 5.0.56 source package of mysql

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 / / introduction to problem solving 4

Make & & Make install

8. Create a mysql-proxy.sh in / var/lib/bin, 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 axix / var/lib/bin/mysql-proxy.sh

Execute: / var/lib/bin/mysql-proxy.sh start the service

9. Verify 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.015 * LISTEN

Tcp 0 0 0.0.0.0 4041 0.0.0.015 * LISTEN

10. Test the separation of read and write. Skip this step. All configurations have been completed.

& & problem solving area & &

1. Make linux Times error in the fourth step of installation 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 lstrlib.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 "ushline"?

Lua.c:182: warning: implicit declaration of function "eadline"?

Lua.c:182: warning: assignment makes pointer from integer without a cast

Lua.c: In function "oadline"?

Lua.c:210: warning: implicit declaration of function "dd_history"?

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. Error installing MySQL-proxy Times:

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. Error installing MySQL-proxy Times:

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 the rpm package of glib installed on the system.

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 start using the mysq_config specified by rpm in the system, the mysql-proxy installation error is reported 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 documentation:

With-mysql [= PATH] Include MySQL support. PATH is the path to 'mysql_config'.

The solution is to install step 6 in the package.

The above is all the contents of the article "how to configure MySQL master-slave synchronization and read-write separation". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.