Mysql master/slave configuration for read/write splitting _ MySQL

In order to solve a large number of concurrent accesses to large websites, distributed load balancing is far from enough. At the data service layer and data access layer, if you still use the traditional data structure or simply rely on a single server to perform so many database connection operations, databases will inevitably collapse large websites. in order to solve a large number of concurrent accesses, it is far from enough to achieve distributed load balancing on websites. At the data service layer and data access layer, if the traditional data structure is used, or only one server is used, the database will inevitably crash and data will be lost due to so many database connection operations, 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 the funds are high, it is inevitable to assume a server group to share the pressure on the primary database. Click OK to enter the topic of today's Weibo, and use the MySQL master-slave configuration 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.

Overview: set up a Master Server (win8.1 system, Ip: 192.168.0.104), set up two Slave servers (virtual machines-one Ubuntu Server and one Windows Server 2003)

Principle: The Master server is responsible for website NonQuery operations, and the Slave server is responsible for Query operations. Users can access the Slave server based on the functional model blocks of the website, or write a pool or queue by themselves, free allocation of slave server connections for requests. 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.

Specific implementation:

Bytes

I will not talk about installing mysql in windows. generally, everyone on earth should. I suggest you do not download and install Ubuntu MySQL online or offline. Everybody can refer to http://www.linuxidc.com/Linux/2013-01/78716.htm this does not know big brother or sister, write well according to this can install. Several problems may occur during installation. you can refer to the solution below:

(1) If you do not use the root user to log on, we recommend that you switch su-root to the Root user for installation.

(2) store the decompressed mysql folder. you 'd better change the folder name to mysql.

(3) in. /support-files/mysql. server start may prompt a warning when starting MySQL. it means that when the service is started to read files, it ignores my. cnf file. That's because my. the file permission of cnf is incorrect. mysql considers the file to be dangerous and will not execute it. However, mysql will start successfully, but if the following configuration changes the parameters of my. during the cnf file, you will find that the file has been changed. However, when the service is restarted, the modified configuration is not executed, and you will find a lot in the mysql folder. my. cnf. swp and other intermediate files. This is because MySQL did not read my. cnf at startup. In this case, you only need to change the file permission of my. cnf to the permission of my_new.cnf. the command is chmod 644 my. cnf.

(4) there is no Vim for modifying the document content in Ubuntu. it is best to install Vim and apt-get install vim. Otherwise, it will be crazy.

At this time, I believe that MySQL should be installed.

2. configure the Master server

(1) create a user 'repl' on the Master MySQL server and allow other Slave servers to remotely access the Master node. then, the user can read binary logs to synchronize data.

1 mysql> create user repl; // create a new user 2 // The repl user must have the replication slave permission. In addition, there is no need to add unnecessary permissions. the password is mysql. Description: 192.168.0. %. this configuration indicates the Server where the repl user is located. here, % is a wildcard, indicating that all the servers of 192.168.0.0-192.168.0.255 can log on to the master Server as the repl user. You can also specify a fixed Ip address. 3 mysql> grant replication slave on *. * TO 'repl' @ '2017. 192.% 'identified by 'mysql ';

(2) find the MySQL installation folder and modify the my. ini file. There are several log methods in mysql, which is not the focus of today. We only need to start the binary log-bin.

Add the following lines of code under [mysqld ]:

1 server-id = 1 // A unique identifier for the database service, generally set the end number of the server Ip 2 log-bin = master-bin3 log-bin-index = master-bin.index

(3) view logs

Mysql> show master status;
+ ------------------- + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------- + ---------- + -------------- + ------------------ +
| Master-bin.000001 | 1285 |
+ ------------------- + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

Restart MySQL service

3. configure the Slave server (windows)

(1) find the MySQL installation folder and modify the my. ini file. add the following lines of code under [mysqld ]:

1 [mysqld]2 server-id=23 relay-log-index=slave-relay-bin.index4 relay-log=slave-relay-bin 

Restart MySQL service

(2) connect to the Master

Change master to master_host = '192. 168.0.104 ', // Master server Ip address
Master_port = 3306,
Master_user = 'repl ',
Master_password = 'mysql ',
Master_log_file = 'master-bin.000001 ', // logs generated by the master server
Master_log_pos = 0;

(3) Start Slave

Start slave;

4. Slave server (Ubuntu)

(1) find the MySQL installation folder and modify the my. cnf file, vim my. cnf

S

(2)./support-files/myql. server restart to restart the MySQL service../bin/mysql enter the MySQL command window.

(3) connect to the Master

Change master to master_host = '192. 168.0.104 ', // Master server Ip address
Master_port = 3306,
Master_user = 'repl ',
Master_password = 'mysql ',
Master_log_file = 'master-bin.000001 ', // logs generated by the master server
Master_log_pos = 0;

(4) Start Slave

Start slave;

OK, all the configurations are complete. at this time, you can test in Master Mysql, because we monitor all the operation logs of Master mysql, so, any operations you perform to change the master server database will be synchronized to the slave server. Create a database. try the table...