MySQL master-slave cluster setup

MySQL master-slave replication mode is primarily responsible for receiving user requests, DDL,DML,DCL and other operations, slave is mainly responsible for synchronizing master's binary log, in order to back up data. In the case of a larger database traffic, Master-slave mode can also be combined with Mysql-proxy for read and write separation, Mysql-proxy is responsible for forwarding the user's write request to master, the user's read request forwarded to the slave, to share the pressure of the database. Even more robust systems, a master corresponding to multiple slave, into a highly available HA cluster, when master down, multiple slave will negotiate a slave re-become master, in order to achieve the continuity of service.

In the MySQL master-slave architecture, slave launches two main threads, one IO thread and the other SQL thread. As we all know, MySQL's replication mainly by synchronizing the binary log in master with slave, then storing the binary log in the trunk log in slave and then performing SQL operations locally by reading the trunk log. The main task of IO thread is the first step, and the main task of SQL thread is to read the log from the trunk log and then do the local operation. For master, a dump thread is also started, which is the primary function of responding to slave IO thread requests and sending binary logs to slave. The main flowchart is as follows: 650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/1D/wKiom1VANwbiCkNFAADbR8DA6No439.jpg "title = "Qq20150429094113.png" alt= "Wkiom1vanwbicknfaadbr8da6no439.jpg"/>

Here's a step-by-step build of MySQL's Master-slave architecture.

1. Environment preparation

Os:centos 6.4

MySQL server: 5.5.28 (2 units)

IP Assignment: master:192.168.1.101

slave:192.168.1.108

MySQL via binary installation package: mysql-5.5.28-linux2.6-x86_64.tar.gz

2. mysql Installation

Extract to/usr/local directory

Tar XF mysql-5.5.28-linux2.6-x86_64.tar.gz-c/usr/local

Add a soft chain

Ln-sv/usr/local/mysql-5.5.28-linux2.6-x86_64/usr/local/mysql

add MySQL user, user group

Groupadd-r MySQL

Useradd-r-G mysql-s/sbin/nologin MySQL

Add MySQL Data Catalog

Mkdir-pv/data/mysql

Chown-r Mysql:mysql/data/mysql

Initializing MySQL Database

Cd/usr/local/mysql

Chown-r root.mysql./*

scripts/mysql_install_db--user=mysql--datadir=/data/mysql

Copy the MySQL configuration file, start the script

CP SUPPORT-FILES/MY-LARGE.CNF/ETC/MY.CNF

CP Support-files/mysql.server/etc/init.d/mysqld

Chkconfig--add Msyqld

3. Master Configuration

Vim/etc/my.cnf

Add in [mysqld]:

Datadir=/data/mysql

Innodb_file_per_table=1 # for InnoDB, a tablespace file per table

Log-bin=master-bin #开启二进制日志功能

Log-bin-index=master-bin.index #二进制日志文件的索引文件

#server_id可暂时不用改, as long as you do not server_id slave in the same

Save exit.

4. Initialize Master

Use MySQL to enter the interactive command line interface

Authorization relication Slave Copy permissions

Mysql> Grant Relication Slave on * * to ' repluser ' @ ' 192.168.1.% ' identified by ' replpass ';

mysql> flush Privileges; #读取授权表

5, installation slave

Install the slave in the same manner as above.

6. Slave Configuration

Vim/etc/my.cnf

Add in [mysqld]:

Datadir=/data/mysql

Innodb_file_per_table=1 # for InnoDB, a tablespace file per table

Relay-bin=relay-bin #开启中继日志功能

Relay-bin-index=relay-bin.index # The index file of the trunk log file

Read_only=1 #设置slave为只读模式

server_id=10# Note that server_id in slave cannot be the same as the server_id of master.

Save exit.

7. Initialize Slave

Use MySQL to enter the interactive command line interface

Mysql> Change Master to

Master_host= ' 192.168.1.101 ',

Master_user= ' Repluser ',

Master_password= ' Replpass ',

Master_log_file= ' master-bin.000002 ',

master_log_pos=107

#其中master_log_file和master_log_pos是在master中通过show the Master Status command to find out.

#所以务必要先在master中查看对应的值后, execute this command again .

#Master_log_file:slave binary files to synchronize

#Master_log_pos: starting position of synchronization

8. Start slave

mysql> start Slave

#此命令会同步启动io_thread和sql_thread, you can also start them alone.

#mysql > Start slave io_thread

#mysql > Start slave sql_thread

At this point, MySQL's master-slave mode is basically built.

Slave status information can be viewed in slave

Mysql> show slave status \g;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/6C/1A/wKioL1VARtuR2IezAALcBilSDLw740.jpg "title=" 1.png " alt= "Wkiol1vartur2iezaalcbilsdlw740.jpg"/>

See the above information, congratulations, Master-slave has been built successfully.

9. Testing

Create a database in master

mysql> CREATE DATABASE Leedb;

Viewing the database in slave

mysql> show databases;

You can see that the leedb in master has been automatically synced to slave. All possible future database changes in master

will automatically sync to the slave. Of course, MySQL can filter the database or the data table to synchronize, you can choose

Optionally synchronize a database or a table of a database, this is left to be discussed later.

This article is from the "flying to the Sky" blog, please be sure to keep this source http://crazytechnology.blog.51cto.com/6906973/1640197

MySQL master-slave cluster setup