Shulou(Shulou.com)06/01 Report--

Master-slave synchronization

1 Master configuration

Configure 237 as the primary database server

Authorized users have the right to copy data when connecting to themselves from the 42 database server.

Grant replication slave on *. * to slaveuser@ "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 View log file, log offset (used from server)

Configure slave database server 42

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

Mysql-h272.40.7.237-uslaveuser-p123456

Vim / etc/my.cnf

[mysqld]

Server_id=42

Log-bin=slave # optional

: wq

Service mysql restart

Log in using your own database administrator and 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

Verification?

Master and slave synchronize the working process?

Cd / var/lib/mysql/

Information about the master.info primary database server

Relay-log.info Relay Log Information

Www-relay-bin.000000 Relay binlog Log File

Www-relay-bin.index records the existing 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 available

Last_IO_Errno: 0

Last_IO_Error: error messa

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

Execute the sql statement in the native relaybinlog log and write the data into the local database.

Error: the library or table used does not exist when executing the sql statement in the native relaybinlog log.

View error messages

Last_SQL_Errno: error number

Last_SQL_Error: error messa

3 mysql master-slave synchronization structure mode?

One master and one slave

One master and many followers

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

[mysqld]

Log-slave-updates

: wq

4. Common parameters of mysql master-slave synchronous configuration

Vim / etc/my.cnf

Configuration parameters on the server side of the primary database

Binlog-do-db=mysql, studb only allows you to synchronize your own libraries from the database server

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

Configuration parameters from the database server side

Replicate-do-db=mysql, studb only synchronizes which libraries on the master database server

Which libraries are on the master database server in different steps of replicate-ignore-db=test,db1

Relay-log=dbsvr2-relay-bin sets the relaybinlog log file name

Log-slave-updates allows 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 usr/local/mysqlproxy/share/doc/mysql-proxy/rw-splitting.lua 3306 (virtual vip)-r 172.40.7.42 VR 3306-b 172.40.7.237 VR 3306-s / usr/local/mysqlproxy/share/doc/mysql-proxy/rw-splitting.lua &

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.