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

This article mainly tells you about the method of separation of reading and writing in mysql database. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on the separation of reading and writing in mysql database can bring you some practical help.

I. be familiar with the difference between mysql read-write separation and mysql storage engine.

Ndb Cluster Storage engine, myisam, innodb

Myisam is the default storage engine after mysql installation, and does not support transaction, row-level locking and foreign key constraints; ndb supports transaction, row-level locking and foreign key constraints for both mysql cluster storage engine and innodb. Use show engines; to view the storage engines supported by mysql.

Read-write separation:

i. Environment:

Application client

Database proxy

Database cluster (more than two mysql machines)

ii. Build master and slave:

Install mysql

Configure my.cnf for master:

Log-bin=mysql-bin

Server-id=1

Add users dedicated to synchronization:

GRAND REPLICATION SLAVE ON *. * TOmysql@slaveIP IDENTIFIEFD BY 'password'

Restart mysql to make the configuration effective

Last look at the mysql status: show master status

Configure my.cnf for slave:

Server-id=2

After the configuration takes effect, configure the connection to the master:

Mysql > CHANGE MASTER TO

-> MASTER_HOST='10.20.147.110'

-> MASTER_USER='repl'

-> MASTER_PASSWORD='111111'

-> MASTER_LOG_FILE='mysql-bin.000003'

-> MASTER_LOG_POS=161261

Where MASTER_HOST is the ip,MASTER_USER and MASTER_PASSWORD of the master computer, which is the user we just added on master, and MASTER_LOG_FILE and MASTER_LOG_POS correspond to the information in masterstatus.

Finally, start slave:

Mysql > start slave

4) verify that the master-slave build is valid

By looking at the log (/ var/log/mysqld.log) of the slave machine:

10070310 Slave 51V 42 [Note] thread O thread: connected to master'repl@10.20.147.110:3306', replication started in log 'mysql-bin.000003'at position 161261

If you see the above information, it proves that the construction is successful. If you have any problems, you can also find the reason through this log.

Mysql database to achieve read-write separation method to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.