MySQL Replication on CentOS 6.5

標籤：mysql

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‘@‘%‘ 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;

本文出自 “Ilovecat(個人筆記)” 部落格，請務必保留此出處http://hj192837.blog.51cto.com/655995/1546692

MySQL Replication on CentOS 6.5