MySQL master-slave and read-write separation

master-Slave synchronization

1 Main Configuration

Configuring 237 as the primary database server

permission to copy data when an authorized user connects themselves from a database server

Grant Replication Slave on * * to [e-mail protected] "172.40.7.42" identified by "123456";

Vim/etc/my.cnf

[Mysqld]

server_id=237

Log-bin=master # must be turned on

: Wq

Service MySQL Restart

Show master status;

2 viewing log files, log offset (from the server)

configuration from database server 42

Verify that you can use an authorized user to connect to the primary database server

mysql-h172.40.7.237-uslaveuser-p123456

Vim/etc/my.cnf

[Mysqld]

Server_id=42

Log-bin=slave # options available

: Wq

Service MySQL Restart

log in with your own database administrator , configure yourself to do 237 from the database server.

Change Master to master_host= "172.40.7.237", master_user= "Slaveuser", master_password= "123456", master_log_file= " Master.000001 ", master_log_pos=120;

Start slave;

show slave status\g;

Slave_io_running:yes

Slave_sql_running:yes

Verify?

Master-Slave synchronous working process?

cd/var/lib/mysql/

Master.info information for the primary database server

Relay-log.info relay Log Information

www-relay-bin.000000 relay binlog log file

Www-relay-bin.index Record the current relay binlog log file name

Slave_io_running:yes

copy Master Binlog SQL statement , Relaybinlog

Error:1 change Master to configuration information error

2 physical connection is not through

last_io_errno:0

Last_io_error: error message

Stop slave;

Change Master to master_host= "172.40.7.237", master_user= "Slaveuser", master_password= "123456", master_log_file= " Master.000001 ", master_log_pos=120;

Start slave;

Slave_sql_running:yes

executes the SQL statement in the native Relaybinlog log and writes the data into the native database.

error: the library or table used does not exist when executing the SQL statement in the native Relaybinlog log.

Viewing error messages

Last_sql_errno: Error number

Last_sql_error: error message

3 MySQL master-slave synchronous structure mode?

One Master one from

A master more from

Master ( from ) from vim/etc/my.cnf

[Mysqld]

Log-slave-updates

: Wq

4 MySQL master-Slave synchronization configuration Common parameters

Vim/etc/my.cnf

Configuration parameters for the primary database server side

Binlog-do-db=mysql, Studb only allow to sync your own libraries from the database server

Binlog-ignore-db=test, DB1 does not allow you to synchronize which libraries you own from the database server

----------------------------------------------------------------

Configuration parameters from the database server side

Replicate-do-db=mysql, Studb only synchronize which libraries on the primary database server

REPLICATE-IGNORE-DB=TEST,DB1 which libraries on the primary database server are not synchronized

Relay-log=dbsvr2-relay-bin setting relaybinlog log file name

Log-slave-updates allow cascading replication

12: Configure MySQL read-write separation function

1 Installation

Yum-y Install Lua

TAR-ZXVF mysql-proxy-tar.gz

MV mysql-proxy-0.8.3-linux-rhel5-x86-64bit//usr/local/mysqlproxy

cd/usr/local/mysqlproxy/share/doc/mysql-proxy/

chmod +x Rw-splitting.lua

2 Start

Start the agent service

/usr/local/mysqlproxy/bin/mysql-proxy--help

/usr/local/mysqlproxy/bin/mysql-proxy-p 172.40.7.77:3306 ( virtual vip)-R 172.40.7.42:3306-b 172.40.7.237:3 306-s/usr/local/mysqlproxy/share/doc/mysql-proxy/rw-splitting.lua &

This article is from the "13262050" blog, please be sure to keep this source http://13272050.blog.51cto.com/13262050/1962626

MySQL master-slave and read-write separation