MySQL master-slave synchronous read-write separate cluster configuration

Large web site in order to solve a large number of high concurrency access problems, in addition to the site to achieve distributed load balancing, far from enough. To the data business layer, the data access layer, if the traditional data structure, or just one server support, so many database connection operations, the server performance of a good database will inevitably crash. If the data is lost, the consequences are unimaginable. At this time, we will consider how to reduce the database connection, on the one hand, the use of excellent code framework, code optimization, the use of excellent data caching technology such as: memcached. If you have enough money, you will inevitably think of the assumption of server clusters to share the pressure of the primary database. Or on a hardware device, invest a lot of money and buy high-performance servers. Famous for F5, hardware load, software load and so on.
OK cut into today's theme, using MySQL master-slave configuration, to achieve read and write separation, reduce database pressure. This way, in today's many sites are used, is not anything new, today summed up, convenient for everyone to learn reference.
read/write separation:
1. Multiple servers must be better than one server.
2. When the database is written, a large number of locks (exclusive locks, shared locks, etc.) are added, which can affect performance.
3. Data is more secure and backup data exists in multiple databases.
Build the Environment:
1. Set up a master server (win7 system 64bit,ip:10.10.2.33)
2. One slave server (WinXP system 32bit,ip:10.10.2.157)
Principle: The primary server (master) is responsible for Web site write operations, from the server responsible for query operations. The master-slave server uses the MySQL binary log file to achieve data synchronization. The binary log is generated by the primary server and gets the synchronization database from the server response.

--Create a dbadmin user on the master server as 10.10.2.157, password 123456--create user ' dbadmin ' @ ' 10.10.2.157 ' identified by ' 123456 ';-- Copy permissions are assigned to the user dbadmin (the root user can be used if you do not want to create a user). --Grant replication Slave,replication client on * * to ' slave ' @ ' 10.10.2.157 ' identified by ' dbadmin '; --Lock all table write operations-flush tables with read lock; --Shows the status of the primary server--show master status;--changes from the server to sync from the 8,042-bit position in the main binary file 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 Sync--start slave;--show sync status--show slave status;--unlock all tables-unlock tables;

Locate the My.ini file on the primary server (different versions may have different file names, such as: MY.CNF).
There are several ways of logging in MySQL, so we just have to start the binary log log-bin. Add the following lines of code below [MYSQLD]

Server-id=1   //A unique identifier for the database service, usually set the end number of the server IP log-bin=master-bin log-bin-index=master-bin.indexbinlog-do-db= Test the database that needs to be synchronized if there are databases that do not need to be synchronized with binlog_ignore_db and none of the two are set, then all synchronizations     auto_increment_offset=1 Avoid primary key conflicts and reset the initial value of the self-increment primary key auto_ increment_increment=2 Increment Expire_logs_days=1 Settings log expires in two days, leaving only two days of log log Log_bin_trust_function_creators=1 assigning permissions to functions

The My.ini file is also found on the slave server. Add the following lines of code below [MYSQLD]

Server-id=2relay-log-index=slave-relay-bin.indexrelay-log=slave-relay-bin auto_increment_offset=2 The initial value of the self-increment primary key auto_ increment_increment=2 increment of the self-incrementing primary key

Welcome everyone to follow my blog! If in doubt, please add QQ Group: 454796847 Study together!

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL master-slave synchronous read-write separate cluster configuration