MySQL master-slave replication for data synchronization

Large web site in order to soft solution a large number of concurrent access, in addition to the site to achieve distributed load balancing, far from enough. To the data service layer, the data access layer, if the traditional data structure, or simply by a server to carry, so many database connection operations, the database will inevitably crash, data loss, the consequences are unimaginable. At this time, we will consider how to reduce database connectivity, on the one hand, the use of excellent code framework, code optimization, the use of excellent data caching technology such as: memcached, if the money is rich, will inevitably think of the assumption of 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. This way, in today's many sites are used, is not anything new, today summed up, convenient for everyone to learn reference.

Overview: Set up a master server (win8.1 system, ip:192.168.0.104), set up two slave servers (virtual machines-one Ubuntu, one Windows Server 2003)

Principle: The main server (master) is responsible for the site Nonquery operation, from the server is responsible for the query operation, the user can according to the site function model block fixed access to the slave server, or write a pool or queue, free for the request allocation from the server connection. 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.

Specific implementation:

1, in the master and slave servers are installed on the MySQL database, Windows system i install the Mysql_5.5.25.msi version, 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 refer to http://www.linuxidc.com/Linux/2013-01/78716.htm this does not know eldest brother or sister, write very good according to this can be installed. In the installation may appear several phenomena, we can refer to the solution:

(1) If you are not logged in with the root user, it is recommended that su-root switch to the root user installation, it is not always sudo.

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

(3) When you start MySQL in./support-files/mysql.server start, you may receive a warning that the Chinese means that when you start the service to run the read file, The my.cnf file is ignored because of a problem with the file permissions of my.cnf, and MySQL will assume that the file is dangerous and will not execute. But MySQL will also start successfully, but if the following configuration modifies the my.cnf file from the server parameter, you will find that the file has been changed, but when the service is restarted, the modified configuration is not executed, and you A list of MySQL folders will find a lot of. My.cnf.swp and other intermediate files. This is because MySQL did not read the my.cnf when it was started. At this time as long as the my.cnf file permissions to change to my_new.cnf the same permissions as OK, command: chmod 644 my.cnf OK

(4) Ubuntu modified document content without VIM, it is best to put vim on, apt-get install VIM, otherwise it will be crazy.

This time I believe that MySQL should be installed.

2. Configure Master master server

(1) Create a user ' repl ' on Master MySQL, and allow the other slave server to access master remotely, through which the user can read the binary log to achieve data synchronization.

1 mysql>create user repl; Create a new user 2//repl the user must have replication slave permission, except that there is no need to add unnecessary permissions, the password is MySQL. Explain the 192.168.0.%, this configuration is to indicate the REPL user's server, where% is a wildcard character, indicating that 192.168.0.0-192.168.0.255 server can be REPL users to the primary server. Of course you can also specify a fixed IP. 3 mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.0.% ' identified by ' MySQL ';

1 mysql>create user repl; Create a new user 2//repl the user must have replication slave permission, except that there is no need to add unnecessary permissions, the password is MySQL. Explain the 192.168.0.%, this configuration is to indicate the REPL user's server, where% is a wildcard character, indicating that 192.168.0.0-192.168.0.255 server can be REPL users to the primary server. Of course you can also specify a fixed IP. 3 mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.0.% ' identified by ' MySQL ';

(2) Locate the MySQL installation folder to modify the 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 OK.

Add the following lines of code below [MYSQLD]

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

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

(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)

Restart MySQL Service

3. Configure slave from server (Windows)

(1) Locate the MySQL installation folder to modify the My.ini file and 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 master

Change Master to master_host= ' 192.168.0.104 ',//master server IP
master_port=3306,
Master_user= ' Repl ',
master_password= ' MySQL ',
Master_log_file= ' master-bin.000001 ',//master server-generated logs
master_log_pos=0;

(3) Start slave

Start slave;

4. Slave from the server (Ubuntu)

(1) Find MySQL installation folder modify my.cnf file, Vim my.cnf

S

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

(3) Connect Master

Change Master to master_host= ' 192.168.0.104 ',//master server IP
master_port=3306,
Master_user= ' Repl ',
master_password= ' MySQL ',
Master_log_file= ' master-bin.000001 ',//master server-generated logs
master_log_pos=0;

(4) Start slave

Start slave;

OK all configuration is completed, this time everyone can be tested in master MySQL, because we monitor the master MySQL all operations log, so your any changes to the primary server database operations will be synchronized to the slave server. Create a database, a table try it ...

MySQL master-slave replication for data synchronization