MySQL master-slave sync

MySQL Compile installation

shell> Groupadd MySQL

shell> useradd-g MySQL MySQL

Shell> Gunzip < mysql-version.tar.gz | TAR-XVF-

Shell> CD Mysql-version

shell>./configure--prefix=/usr/local/mysql

Shell> make

shell> make Install

shell> CP support-files/my-medium.cnf/etc/my.cnf

Shell> Cd/usr/local/mysql

Shell> bin/mysql_install_db--user=mysql

Shell> chown-r Root.

shell> Chown-r mysql var

Shell> chgrp-r MySQL.

Shell> Bin/mysqld_safe--user=mysql &

MySQL Master-Slave Sync

First clear two point 1, mysql configuration file my.cnf location

2, how to start, stop MySQL, find a good startup file

Assuming that there are two machines, MySQL has been installed (as far as possible version, and two machines the same network, you can ping)

Host a:192.168.1.100

From the machine b:192.168.1.101 can have multiple machine

1, first login host A

Mysql>grant REPLICATION SLAVE on . To backup@ "%" identified by ' 123456 ';

Given the permissions from the machine, there are more than one cluster machine, on the execution of multiple

2, open the my.cnf of host A, input

Server-id = 1 #主机标示, integer

Log_bin =/var/log/mysql/mysql-bin.log #确保此文件可写

Read-only =0 #主机, read and write can be

Binlog-do-db =test #需要备份数据, multiple write multiple lines

Binlog-ignore-db=mysql #不需要备份的数据库, multiple write multiple lines

3, open the MY.CNF from the machine B, input

Server-id = 2

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

Master-host =192.168.1.100

Master-user =backup

Master-pass =123456

Master-port =3306

Master-connect-retry=60 #如果从服务器发现主服务器断掉, Reconnection time difference (sec)

Replicate-do-db =test #只复制某个库

Replicate-ignore-db=mysql #不复制某个库

4. Restart slave MySQL

Stop slave;

Change MASTER to master_host=192.168.100.13, Master_user=slave, Master_password=slave_password, master_log_file= mysql-bin.000001, master_log_pos=890;

Start slave;

5. Create a database test on master MySQL to synchronize from the library. or show slave STATUSG view

Slave_io_running:yes

Slave_sql_running:yes

Two yes is synchronized.

You can do some insert, UPDATE, and DELETE operations in host A to see if the Host B has been modified

Add:

Using the show slave statusg from the server

Slave_io_running, for No,

To indicate that Io_thread did not start, perform start slave io_thread

Slave_sql_running is no

Then the replication error, view last_error field exclusion error, execute start slave sql_thread

View Slave_io_state Word Chini//Copy not started

Connecting to master//not connected to master

Waiting for Master to send event//is already connected

Related commands on the primary server:

Show Master Status

Show slave hosts

Show logs

Show Binlog Events

Purge logs to Log_name

Purge logs before date

Reset Master (old version flush Master)

Set sql_log_bin=

Related commands from the server:

Slave start

Slave stop

SLAVE STOP io_thread//This thread writes the log of the master segment to the local

SLAVE Start Io_thread

SLAVE STOP sql_thread//This thread applies logs written to local to the database

SLAVE Start Sql_thread

Reset Slave

SET GLOBAL Sql_slave_skip_counter

Load data from Master

Show slave status (Super,replication CLIENT)

Changes master to master_host=, master_port=,master_user=, master_password=//dynamic Change master information

PURGE master [before date] Deletes a log that has been synchronized at the master end