MySQL master-slave configuration for read-write separation

Large sites in order to soft-solve a large number of concurrent visits. In addition to implementing distributed load balancing at the site, it is far from enough. To the data business layer, the data access layer, assuming the traditional data structure, or simply by one server, so many database connection operations. The database is bound to crash. If the data is lost, the consequences are unimaginable. This time We'll consider how to reduce database joins. On the one hand, using excellent code framework, to optimize the code, using excellent data caching technology such as: memcached, if the money is rich, it will be expected to assume the server group, to share the main database pressure. OK cut into today's Weibo theme, using MySQL master-slave configuration, to achieve read and write separation, reduce database pressure. Such a way, in the now very many sites are used. is not a novelty thing, today summed up, convenient for everyone to learn to take a look.

Overview: Erection of a masterserver (win8.1 system, ip:192.168.0.104). Set up two Slaveserver (virtual machines – one Ubuntu, one Windows Server 2003)

Principle: The primary server (master) is responsible for site nonquery operations. From the server responsible for the query operation, the user can according to the site function model block fixed access to the Slaveserver, or write a pool or queue yourself. The freedom is assigned to the request from the server connection.

The master-slave server uses MySQL's binary log files to achieve data synchronization. Binary logs are generated by the primary server. Gets the synchronization database from the server response.

Detailed implementation:

1, on the master-slave server installed on the MySQL database, Windows system i install the Mysql_5.5.25.msi version number, Ubuntu installed is Mysql-5.6.22-linux-glibc2.5-i686.tar

Windows installation MySQL will not talk about, the general people of the earth should be. I would say a little bit about the Ubuntu MySQL installation, I recommend not downloading the installation online or installing it offline. We can participate in the test http://www.linuxidc.com/Linux/2013-01/78716.htm this does not know eldest brother or sister. It's a good one to put on. In the installation of the time may appear several phenomena, we can take a look at the solution:

(1) Assume that you are not logged in with the root user. It is recommended that su-root switch to root user installation. That's not always sudo.

(2) store the extracted MySQL directory, the directory name should be changed to MySQL

(3) when the./support-files/mysql.server start starts MySQL. A warning may appear. When the startup service runs a read file. Ignoring the my.cnf file, which is due to my.cnf file permissions problems, MySQL will feel that the file is not running in a critical situation. But MySQL will also start successfully, but assuming the following configuration changes the My.cnf file from the server parameters, you will find that the file has been changed. However, when the service is restarted, the changed configuration is not running, and you will find a list of the MySQL directories below. MY.CNF.SWP Intermediate files.

This is due to the fact that MySQL did not read my.cnf at startup. At this point only to change the my.cnf file permissions to my_new.cnf the same permissions as OK, command: chmod 644 my.cnf OK

(4) There is no vim in the contents of the modified document in Ubuntu. It's best to put vim on, apt-get install VIM, or you're expected to freak out.

This time I believe that MySQL should be installed.

2. Configure Master master server

(1) Create a user ' repl ' on master MySQL. It is also agreed that other slaveserver can use the remote access master to read the binary log through the user to achieve data synchronization.

Create a database user to read the log

(2) Locate the MySQL installation directory change My.ini file.

There are several ways to log in MySQL, which is not the focus of today.

We just have to start the binary log log-bin just OK.

Add the following lines of code below [MYSQLD]

View Code

(3) View Log

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+------------------+
| master-bin.000001 | 1285 | | |
+-------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Restarting the MySQL service

3. Configure slave from server (Windows)

(1) Locate the MySQL installation directory change My.ini file. Add the following lines of code below [MYSQLD]

MY.CNF Configuration

Restarting the MySQL service

(2) Connect master

Change Master to master_host= ' 192.168.0.104 ',//master ServerIP
master_port=3306,
Master_user= ' Repl ',
master_password= ' MySQL ',
Master_log_file= ' master-bin.000001 ', log generated by//masterserver
master_log_pos=0;

(3) Start slave

Start slave;

4. Slave from server (Ubuntu)

(1) Find MySQL installation directory changes my.cnf file, vim my.cnf

S

(2)./support-files/myql.server Restart Restart the MySQL service,./bin/mysql into the MySQL command form

(3) Connect Master

Change Master to master_host= ' 192.168.0.104 ',//master ServerIP
master_port=3306,
Master_user= ' Repl ',
master_password= ' MySQL ',
Master_log_file= ' master-bin.000001 ', log generated by//masterserver
master_log_pos=0;

(4) Start slave

Start slave;

OK all configuration is finished, this time everyone can test in the master MySQL, because we monitor the master MySQL all operation log, so. Whatever you do changes the operation of the primary server database to be synchronized to the slave server. Create a database, the table try it.

。

MySQL master-slave configuration for read-write separation