MySQL master-slave synchronous read/write splitting cluster configuration _ MySQL

To solve a large number of high-concurrency access problems, in addition to implementing distributed load balancing on websites, large websites are far from enough. At the data service layer and data access layer, if the data structure is still traditional, or only supported by one server, so many database connection operations, in order to solve a large number of high-concurrency access problems, server load balancer is far from enough for large websites. When it comes to the data service layer and data access layer, if it is still a traditional data structure or only supported by a single server, so many database connection operations, the database will inevitably crash if the server performance is good. If data is lost, the consequences are even more unimaginable. At this time, we will consider how to reduce database connections. On the one hand, we will adopt an excellent code framework for code optimization and use excellent data cache technologies such as memcached. If you have enough funds, you must consider assuming a server cluster to share the pressure on the primary database. Or invest a lot of money on hardware devices to buy high-performance servers. It is famous for its f5, hardware load, and software load.
OK is the topic of today. MySQL master-slave configuration is used to implement read/write splitting, reducing the pressure on the database. This method has been used in many websites today and is not a new thing. Let's summarize it today for your convenience.
Read/write splitting:
1. multiple servers must be better than one server.
2. a large number of locks (exclusive locks, shared locks, etc.) will be added during Database writing, thus affecting performance.
3. data is safer, and multiple databases have backup data.
Build Environment:
1. set up a Master server (64bit in win7, Ip: 10.10.2.33)
2. one Slave server (32bit in winXp, ip: 10.10.2.157)
Principle: The Master server is responsible for website write operations, and the slave server is responsible for query operations. The master/slave server uses MySQL binary log files to synchronize data. Binary logs are generated by the master server, and the synchronous database is obtained from the server response.

-- CREATE a dbadmin USER 10.10.2.157 on the master server and set the password to 123456 -- create user 'dbadmin' @ '10. 10.2.157 'identified BY '20170101'; -- Grant the slave replication permission to the user dbadmin (if you do not want to create a user, you can use the root user ). -- Grant replication slave, replication client on *. * to 'Slave '@ '10. 10.2.157 'identified by 'dbadmin'; -- lock write operations on all tables -- flush tables with read lock; -- display master server status -- show master status; -- change synchronization from the server starting from the 8042 location in the master's binary mysql-test-bin.000002 -- change master to master_host = '10. 10.2.33 ', master_user = 'Slave', master_password = 'root', master_log_file = 'MySQL-test-bin.000002 ', master_log_pos = 8042; -- start synchronization -- start slave; -- display synchronization status -- show slave status; -- release all locked tables -- unlock tables;

Find the my. ini file on the master server (different versions may have different file names, for example, my. cnf ).
There are several log methods in mysql. we only need to start the binary log-bin. Add the following lines of code under [mysqld ]:

Server-id = 1 // unique identifier for the database service, generally set the server Ip end number log-bin = master-bin log-bin-index = master-bin.indexbinlog-do-db = test need to synchronize the database if there is no need to synchronize the database can use binlog_ignore_db, if neither of them is set, synchronize all auto_increment_offset = 1 to avoid primary key conflicts. reset the initial auto_increment_increment value of the auto-increment primary key. auto_increment_increment = 2. set the log expiration time to two days, only two days of log log_bin_trust_function_creators = 1 are retained to assign permissions to the function.

Find the my. ini file on the slave server. Add the following lines of code under [mysqld ]:

Server-id = 2relay-log-index = slave-relay-bin.indexrelay-log = slave-relay-bin auto_increment_offset = 2 auto-incrementing primary key initial value auto_increment_increment = 2 auto-incrementing primary key increment