MySQL master-slave configuration and implementation of read-write separation

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 theme, using MySQL database 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.

< Span style= "FONT-FAMILY:CALIBRI;FONT-SIZE:16PX;" > Overview: Erection of a master server ( win8.1: 192.168.0.104), set up two Slave Server (virtual machine -- one ubuntu)

principle: Master server ( Master) is responsible for website 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, The freedom is assigned to the request 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 say a little bit about the Ubuntu MySQL installation, I recommend not to download the installation online, or offline installation is good. 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) in./support-files/mysql.server StartStartMysql, a warning may appear in Chinese meaning that when the service is started to run a read file, it ignores theMy.cnffile, that's becauseMy.cnfThere is a problem with the file permissions,Mysqlwill assume that the file is in danger and will not execute. ButMysqlIt will also start successfully, but if the following configuration is modified from the server parameterMy.cnffile, you will find that the file has been changed, but when you restart the service, the modified configuration is not executed, and youListA bitMysqlfolder will find a lot of. my.cnf.swpand other intermediate files. This is all becauseMysqlno read at startupMy.cnfthe reason. At this point, only theMy.cnfThe file permissions are changed toMy_new.cnfthe same permissions asOk, command:chmod 644 MY.CNFjustOk

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

that's when I believe MySQL should be installed.

2. Configure Master master server

(1) Create a user ' Repl ' on Master MySQLand allow other Slave servers to access master remotely , The user reads the binary log for data synchronization.

1 mysql>create user repl; Create a new user

2//repl REPLICATION slave Right, besides, there is no need to add unnecessary permissions, the password is MySQL 192.168.0.% repl user server, Here % is a wildcard, which means 192.168.0.0-192.168.0.255 server All can be repl users log on to the primary server. Of course you can also specify fixed ip 3 mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.0.% ' identified by ' MySQL ';

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

in the [Mysqld] Add the following lines of code below

1 server-id=1// 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=2

3 Relay-log-index=slave-relay-bin.index

4 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

(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, you any change the master server database operation, will be synchronized to the slave server. Create a database, the table try it.

Source: Linux commune

MySQL master-slave configuration and implementation of read-write separation