Centos 6.4 Under MySQL configuration master-slave service (cluster)

Centos 6.4 Under MySQL configuration master-slave service (cluster)

Two of our previous articles are described in MySQL installation and configuration, backup and restore, today we continue to the previous environment described in CentOS 6.4 mysql configuration master-slave data synchronization, can also be understood as dual-machine hot standby, to improve the high availability of servers. For MySQL server configuration is not much introduced, if there is not clear can refer to the previous MySQL article introduction;

Configuring the MySQL master server's my.cnf file

We first open the MySQL master server configuration file

Vim/etc/my.cnf

Default configuration

650) this.width=650; "title=" clip_image001 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image001" src= "Http://s3.51cto.com/wyfs02/M00/86/54/wKioL1e7_MvQqLLcAACaC2mxi8I142.png" height= "484"/>

We need to add the following to the MySQL service master configuration file;

#设置服务器ID, the representation of 1 is the primary server, and if the server does not have the following configuration added, skip if any. server-id=1# start the MYSQ binary log system, note: If the original configuration file already has this line, it is no longer added. Log_bin=mysql-bin? #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database binlog-do-db=db1# the MySQL system database Binlog-ignore-db=mysql

Note: Be sure to add to the mysqld below, if the location or the information is incorrect, the show master status, the result is empty;

650) this.width=650; "title=" clip_image002 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image002" src= "http://s3.51cto.com/wyfs02/M02/86/54/wKiom1e7_MygPshmAADoXknyHr0792.png" height= "420"/>

Restart MySQL Service

Service mysqld Restart

650) this.width=650; "title=" clip_image003 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image003" src= "http://s3.51cto.com/wyfs02/M01/86/54/wKioL1e7_M3i46PEAABTM1pzd-8961.png" height= "/>"

#进入mysql控制台

Mysql-u root-p

650) this.width=650; "title=" clip_image004 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image004" src= "http://s3.51cto.com/wyfs02/M02/86/55/wKiom1e7_M6wTNe6AAEI1ad6JCw539.png" height= "/>"

Show master status;

Look at the primary server, the following information appears, if you do not see the following results or empty, the MY.CNF configuration file configuration is a problem, please check.

650) this.width=650; "title=" clip_image005 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image005" src= "Http://s3.51cto.com/wyfs02/M01/86/55/wKiom1e7_M-CUKEUAACJLimZ6Ys217.png" height= "165"/>

Note: The value of file is remembered here: the value of mysql-bin.000001 and position: 106, which is used later.

Second, Configure MySQL from the server my.cnf file; the difference is that Server-id is different.

VI/ETC/MY.CNF #编辑配置文件, add the following in the [Mysqld] section # Set the server ID to 1 for the primary server, 2 for the from server, and if the server does not have the following configuration, add, if any, skip. server-id=2# start the MYSQ binary log system, note: If the original configuration file already has this line, it is no longer added. Log_bin=mysql-bin? #需要同步的数据库名, if you have multiple databases, you can repeat this parameter, one row per database binlog-do-db=db1# the MySQL system database Binlog-ignore-db=mysql

650) this.width=650; "title=" clip_image006 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image006" src= "Http://s3.51cto.com/wyfs02/M02/86/55/wKiom1e7_NCBSnp3AAEWS4HnWb0452.png" height= "394"/>

: wq! #保存退出service mysqld Restart #重启MySQL

650) this.width=650; "title=" clip_image007 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image007" src= "http://s3.51cto.com/wyfs02/M00/86/55/wKiom1e7_NCgP2wWAABR4o0MRnQ480.png" height= "/>"

Note: After MySQL 5.1.7, it is not supported to write the master configuration attribute to the MY.CNF configuration file, just write the synchronized database and the database to be ignored.

Next configure slave, go to the MySQL console configuration from the server:

Mysql-u root-p #进入MySQL控制台slave stop; #停止slave同步进程

Change Master to master_host= ' 192.168.6.28 ', master_user= ' root ', master_password= ' Ixmsoft ', master_log_file= ' Mysql-bin.000001 ', Master_log_pos=106;change Master to master_host= ' master server ', master_user= ' MySQL user name ', master_password= ' MySQL service password ', master_log_file= ' file value ', master_log_pos=postion value;

650) this.width=650; "title=" clip_image008 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image008" src= "http://s3.51cto.com/wyfs02/M02/86/54/wKioL1e7_izCEg35AAA9l8R7IqU114.png" height= "/>"

Slave start; #开启slave同步进程

650) this.width=650; "title=" clip_image009 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image009" src= "Http://s3.51cto.com/wyfs02/M01/86/55/wKiom1e7_iyiXVqVAAAyl2eOx2E352.png" height= "94"/>

Show SLAVE Status\g #查看slave同步信息, the following appears

650) this.width=650; "title=" clip_image010 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image010" src= "Http://s3.51cto.com/wyfs02/M00/86/54/wKioL1e7_i2jZuP_AAEC7fplNvQ588.png" height= "303"/>

Note check:

Slave_io_running:yes

Slave_sql_running:yes

The value of these two parameters as long as there is a no, that is, the configuration failed! If all two are yes, the success

Third, test MySQL master server dual-Machine hot standby is successful

We insert a piece of data from the primary server

Insert into info (name,sex,mail) VALUES (' Samsun ', ' Boy ', ' [email protected] ');

650) this.width=650; "title=" clip_image011 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image011" src= "Http://s3.51cto.com/wyfs02/M02/86/55/wKiom1e7_jLh2uAwAADAOQ72Ld0085.png" height= "222"/>

After execution, we look at the next

SELECT * FROM Info

Compare the pre-insert and post-insert results

650) this.width=650; "title=" clip_image012 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image012" src= "http://s3.51cto.com/wyfs02/M01/86/54/wKioL1e7_jOAubHaAAGfNTM62GQ689.png" height= "481"/>

Then view the status of MySQL from the server

SELECT * FROM Info

650) this.width=650; "title=" clip_image013 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image013" src= "http://s3.51cto.com/wyfs02/M00/86/55/wKiom1e7_jSgIEUiAAEcr21NNDs972.png" height= "297"/>

650) this.width=650; "title=" clip_image014 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image014 "src=" Http://s3.51cto.com/wyfs02/M02/86/54/wKioL1e7_jWh-zAQAAFj3HtupBo070.png "height=" 484 "/>

This article from "Gao Wenrong" blog, declined reprint!

Centos 6.4 Under MySQL configuration master-slave service (cluster)