MySQL Replication on CentOS 6.5

On Master server:
1. Disable SELinux and iptables
Service Iptables Stop
Chkconfig iptables off; Chkconfig Ip6tables off

Setenforce 0
Vi/etc/sysconfig/selinux
Selinux=disabled

2. Yum-y Install Mysql-server
Service MySQL start; Chkconfig mysqld on
Mysql_secure_installation

3. vi/etc/my.cnf
[Mysqld]
Server-id = 1
Binlog-do-db=testdb
Relay-log =/var/lib/mysql/mysql-relay-bin
Relay-log-index =/var/lib/mysql/mysql-relay-bin.index
Log-error =/var/lib/mysql/mysql.err
Master-info-file =/var/lib/mysql/mysql-master.info
Relay-log-info-file =/var/lib/mysql/mysql-relay-log.info
Log-bin =/var/lib/mysql/mysql-bin

Service mysqld Restart

4. Mysql-uroot-p
Mysql> GRANT REPLICATION SLAVE on * * to ' mysql_rep ' @ ' percent ' identified by ' password ';
mysql> flush Privileges;
Mysql> FLUSH TABLES with READ LOCK;
Mysql> Show master status;

Mysqldump-u root-p--all-databases--master-data >/root/dbdump.db

Mysql-uroot-p
Mysql> UNLOCK TABLES;
Mysql> quit;

scp/root/dbdump.db [Email protected]:/root/

On Slave server:
1. Disable SELinux and iptables
Service Iptables Stop
Chkconfig iptables off; Chkconfig Ip6tables off

Setenforce 0
Vi/etc/sysconfig/selinux
Selinux=disabled

2. Yum-y Install Mysql-server
Service MySQL start; Chkconfig mysqld on
Mysql_secure_installation

3. vi/etc/my.cnf
[Mysqld]
Server-id = 2
master-host=192.168.20.14
Master-connect-retry=60
Master-user=slave_user
Master-password=password
Replicate-do-db=testdb
Relay-log =/var/lib/mysql/mysql-relay-bin
Relay-log-index =/var/lib/mysql/mysql-relay-bin.index
Log-error =/var/lib/mysql/mysql.err
Master-info-file =/var/lib/mysql/mysql-master.info
Relay-log-info-file =/var/lib/mysql/mysql-relay-log.info
Log-bin =/var/lib/mysql/mysql-bin

Service mysqld Restart

Mysql-uroot-p
Mysql>slave stop;

Mysql-u Root-p </root/dbdump.db

4. Mysql-uroot-p
mysql> Change Master to Master_host = ' 192.168.20.14 ', master_user = ' Mysql_rep ', Master_password = ' password ', master_ log_file = ' mysql-bin.000001 ', master_log_pos = 106;
mysql> slave start;
Mysql> Show Slave Status\g

notes:use "Show Master Status" on master server to get Master_log_file and Master_log_pos

On Master Server:
mysql> CREATE DATABASE TestDB;
mysql> use TestDB;
Mysql> CREATE TABLE Employee (c int);
Mysql> INSERT into employee (c) VALUES (1);
Mysql> SELECT * from employee;

On Slave Server:
mysql> use TestDB;
Mysql> SELECT * from employee;

This article is from the "ilovecat (personal note)" blog, so be sure to keep this source http://hj192837.blog.51cto.com/655995/1546692

MySQL Replication on CentOS 6.5