MySQL Master-Slave construction

MySQL is a common database, and in order to ensure that the data is not lost, MySQL master and slave become a lot of enterprise choice, the principle involved in the introduction of the following: Slave server through the slave_io_running process synchronization master database Bin-log log, This log records the primary MySQL SQL operation, after synchronization, the slave server through its own slave_sql--runing process to perform synchronization, the benefit is to achieve read and write separation. Here is the concrete construction process.

I'm using centos6.4,mysql to simply put it in Yum.

1 Turn off the firewall, or allow port 3306 to pass

2 Modify the master server's my.cnf file, my primary MySQL IP address is 192.168.112.122

[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock

# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0

server-id=2
Log-bin=master-log

[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid

Restart Mysql,service mysqld Restart after modification

3 Login Master MySQL

Mysql-uroot-p123

Grant Replication Slave on * * to [E- Mail protected] identified by ' 123456 ';

Flush privileges;

Show master status;
+-------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+------------------+
|     master-log.000002 | 1408 | MySQL | Zabbix |
+-------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

4 Configuring the Slave Database

Vi/etc/my.cnf

[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock

# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0

server-id=2
Log-bin=master-log

[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid

Restart MySQL database after saving

5 Log on from the database

mysql-uroot-p12345678

Stop slave;

Change Master to master_user= ' Zhanghe ', master_host= ' 192.168.112.122 ', master_password= ' 123456 ', master_port=3306, Master_log_file= ' master-log.000002 ', master_log_pos=1408;

Start slave;

show slave status\g;

If the Slave_io_running:yes Slave_sql_running:yes is configured successfully

5.1 If slave_io_running:no, I think it should be viewed from three aspects, 1 whether the network is connected, especially the virtual machine must be bridged mode 2MY.CNF configuration Error 3 Permissions issues

5.2 If Slave_sql_running:no

1. The program may have been written on the slave
2. It is also possible that the transaction rollback is caused by the slave machine being reset.

WORKAROUND: 1. First stop slave service: Slave stop

2. View the host status on the primary server:

3. Perform a manual synchronization on the slave server:
Mysql> Change Master to
> master_host= ' master_ip ',
> master_user= ' user ',
> master_password= ' pwd ',
> master_port=3307,
> master_log_file= ' mysql-bin.000020 ',
> master_log_pos=135617781;
1 row in Set (0.00 sec)

4 start slave;show slave status\g;

6 inserting data into the primary database to see if there is data from the database

This article is from the "Zhanghe" blog, make sure to keep this source http://9206668.blog.51cto.com/9196668/1530032