MySQL Master-slave replication

----------The primary database	---------from the database
192.168.1.1	192.168.1.2

First, the preparatory work

1. Turn off the firewall

[[Email protected] ~]# service iptables stop

[Email protected] ~]# chkconfig iptables off

Second, time synchronization

1. Install the NTP Time service on the primary database

[[email protected] ~]# yum-y install NTP

[Email protected] ~]# vim/etc/ntp.conf

Add to:

Server 127.127.1.0

Fudge 127.127.1.0 Stratum 8

[Email protected] ~]# service ntpd restart

2. Install the NTP time service from the database

[Email protected] ~]# yum-y install Ntpdate

[Email protected] ~]# ntpdate 192.168.1.1

Third, master-slave configuration

1. Primary database server Configuration

[Email protected] ~]# VIM/ETC/MY.CNF

Add: Remove the previous # number before you modify it

server-id=11//Modify

Log-bin=master-bin//Modify

Log-slave-updates=true//Add

[Email protected] ~]# service mysqld restart

[Email protected] ~]# mysql-u root-p123.com

mysql> grant replication Slave on * * to ' slave ' @ ' 192.168.1.% ' identified by ' 123.com '; #slave为用户, 123.com is password

mysql> flush Privileges;

Mysql> Show master status;

+------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000001 |      107 |              |                  | +------------------+----------+--------------+------------------+

Mysql> quit

2. Configuration from Database server

[Email protected] ~]# VIM/ETC/MY.CNF

Add to:

SERVER-ID=22//Modify

Relay-log=relay-log-bin//Add

Relay-log-index=slave-relay-bin.index//Add

Note: The primary-from ID cannot be the same.

[Email protected] ~]# service mysqld restart

[Email protected] ~]# mysql-u root-p123.com

mysql> Change Master to master_host= ' 192.168.1.1 ', master_user= ' slave ', master_password= ' 123.com ', master_log_file = ' mast er-bin.000001 ', master_log_pos=107;

mysql> start slave;

Mysql> Show Slave status\g//end cannot be added; otherwise or error.

Slave_io_running:yes//Ensure Yes

Slave_sql_running:yes//Ensure Yes

Mysql> quit

Problem Description:

1. If Error:no query specified appears

Then: The reason is a syntax error, minus the statement;

2, if the Bin-log log is not updated, or data synchronization is not possible,

Then: Delete the Bin-log log and restart the service.

3. If there is

Slave_io_running:connecting

The Slave_sql_running:yes problem is usually:

Bad network, incorrect password, improper POS

Four, verify:

1) Primary database server:

[Email protected] ~]# mysql-u root-p123.com

mysql> show databases;

mysql> CREATE database hehe;

mysql> show databases; mysql> use hehe;

Mysql> CREATE TABLE biao1 (ID int (5), name char (12));

Mysql> Show tables;

Mysql> quit

2) from the database server:

[Email protected] ~]# mysql-u root-p123.com

mysql> show databases;

mysql> show databases; mysql> Use hehe

Mysql> Show tables;

Mysql> quit

MySQL Master-slave replication