MySQL Database synchronization deployment

1 , configuration /etc/my.cnf file

To take a two -machine deployment, the configuration files for both machines are slightly different, as shown inthe Server-id set to different numbers,Auto_increment_offset a machine set to 1, A second machine is set to 2.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/57/20/wKioL1SSaS-C9enuAA4V Or2 c9kq003.jpg "title=" 1.png " alt= "wkiol1ssas-c9enuaa4v Or2 c9kq003.jpg"/>

2 , start MySQL Service

#service MySQL Start

#chkconfig MySQL on

#/usr/bin/mysqladmin-u root password ' mysql.rzrk '

3 , master standby configuration

Confirm two machines The MYSQL service is properly configured and started successfully, then the master synchronization configuration is performed, with 192.168.1.119 and 192.168.1.220 as an example, in 8 and on 9, perform the following commands, respectively :

Grant replication slave on *.*to ' replication ' @ ' 192.168.1.% ' identified by ' replication.rzrk ';

Establish the replication account in the database, and allow the address of the 192.168.1.0 network segment to log in, the password is REPLICATION.RZRK:

Flush privileges;

Refreshes the database.

Show master status;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/57/20/wKioL1SSaVCwdAEfAAkN8EXC-l4685.jpg "title=" 2.jpg " alt= "Wkiol1ssavcwdaefaakn8exc-l4685.jpg"/>

then execute the following command on the 119 (where the values of master_log_file and master_log_pos are information displayed on the machine):

here is mainly to see:
                    slave_io_running=yes
                   Slave_sql_running=yes

slave i/o threads are already running, and Seconds_ Behind_master No, Null slave

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/24/wKiom1SSbCTwwIbBAAwW7nj_lV4144.jpg "title=" 5.jpg " alt= "Wkiom1ssbctwwibbaaww7nj_lv4144.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/57/21/wKioL1SSb-mTPuPIAASdcILyLJI947.jpg "title=" 7.jpg " alt= "Wkiol1ssb-mtpupiaasdcilylji947.jpg"/>

The results are as follows:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/57/21/wKioL1SScFvyUTHfAA2R8TWpK2U417.jpg "title=" 9.jpg " alt= "Wkiol1sscfvyuthfaa2r8twpk2u417.jpg"/>

4 , verify

In one of the machines, import the initial database with the following command:

Mysql–u Root-pmysql.rzrk

Create Database Ttmgrportaldefault charset UTF8;

Quit

Command line execution:

Mysql-u Root-pttmgrportal < Ttmgrportal_0828.sql

The experience is summarized as follows: MySQL sync out the problem just to troubleshoot:

1 , warning:world-writable config file '/etc/my.cnf ' is ignored

FIX:#chmod 644/etc/my.cnf

# Service Mysqld Stop

# service Mysqld Start

2 , Slave_sql_running:no

Seconds_behind_master:null

Solution: >stop slave;

>set Global sql_slave_skip_counter = 1;

>start slave;

3 , Fatal error:the slave I/O threadstops because master and slave have equal MySQL server UUIDs; these UUIDs mustbe Different for replication to work.

Solve:

> Show variables like ' server_id ';

> Set global server_id=2; #此处的数值和my. CNF, just set it up.

> Slave start;

4 , MySQL showmaster status in master-slave synchronization, the result is empty?

No binary log is turned on

Mysql>show variables like '%log_bin% '; If it's off, you're not opening it.

Turn on the binary log.

Add a statement to MY.CNF

Log_bin=/var/log/mysqld/mysql-bin

This article from the "heartbroken people in the Tianya" blog, declined reproduced!

MySQL Database synchronization deployment