MySQL Replication on CentOS 6.5 and Haproxy
MySQL 主从：On Master server: 192.168.20.14
1. disable selinux and iptables
service iptables stop
chkconfig iptables off; chkconfig ip6tables off
setenforce 0
vi /etc/selinux/config
SELINUX=disabled
2. yum -y install mysql-server
service mysqld start; chkconfig mysqld on
mysql_secure_installation
3. vi /etc/my.cnf
server-id = 1
log-bin = mysql-bin
expire_logs_days=10
max_binlog_size=100M
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;
mysql> show processlist\G
mysqldump -q -u root -p --all-databases --master-data > /root/dbdump.db
mysql -uroot -p
mysql> UNLOCK TABLES;
mysql> quit;
scp /root/dbdump.db root@192.168.20.15:/root/
On Slave server: 192.168.20.15
1. disable selinux and iptables
service iptables stop
chkconfig iptables off; chkconfig ip6tables off
setenforce 0
vi /etc/selinux/config
SELINUX=disabled
2. yum -y install mysql-server
service mysqld start; chkconfig mysqld on
mysql_secure_installation
3. vi /etc/my.cnf
server-id = 2
log-bin = mysql-bin
expire_logs_days=10
max_binlog_size=100M
service mysqld restart
mysql -uroot -p
mysql>stop slave;
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> start slave;
mysql> show slave status\G
mysql> show processlist\G
Notes: use "show master status" on master server to get master_log_file and master_log_pos
HAproxy配置：
mysql master server: 192.168.20.14
mysql slave server: 192.168.20.15
haproxy server 1: 192.168.20.12
haproxy server 2: 192.168.20.13
haproxy VIP: 192.168.20.10
On master mysql server:
mysql -uroot -p
mysql> CREATE USER 'haproxy'@'192.168.20.12';
mysql> CREATE USER 'haproxy'@'192.168.20.13';
mysql> flush privileges;
On two haproxy server:
yum -y install mysql
vi /etc/haproxy/haproxy.cfg
defaults
# option httplog
# option http-server-close
# option forwardfor except 127.0.0.0/8
listen mysqld-status 192.168.20.10:3306
balance source
mode tcp
option tcpka
option mysql-check user haproxy
server MySQL1 192.168.20.14:3306 check weight 1
server MySQL2 192.168.20.15:3306 check backup weight 1
service haproxy reload
Testing:
On master mysql server:
mysql -uroot -p
mysql> grant all on *.* to root@'%' identified by 'password' with grant option;
from mysql client:
mysql -h 192.168.20.10 -uroot -p
On Master Server:
mysql> create database yourdb;
mysql> use yourdb;
mysql> CREATE TABLE employee (c int);
mysql> INSERT INTO employee (c) VALUES (1);
mysql> SELECT * FROM employee;
On Slave Server:
mysql> use yourdb;
mysql> SELECT * FROM employee;
主从切换：
first stop mysql service on old master
On Old Slave server: 192.168.20.15
mysql -uroot -p
mysql> stop slave;
mysql> reset master;
mysql> show master status;
cd /var/lib/mysql
rm -rf *relay*
rm -rf master.info
On Old Master server: 192.168.20.14
service mysql start
mysql -uroot -p
mysql> stop slave;
mysql> change master to master_host = '192.168.20.15',master_user = 'mysql_rep',master_password = 'password',master_log_file = 'mysql-bin.000001',master_log_pos = 106;
mysql> start slave;
mysql> show slave status\G
1. Backup
full backup:
mysqldump -q -uroot -ppassword --flush-logs --master-data=2 --all-databases > backup.sql
incremental backup:
do changes on database
mysqladmin -uroot -ppassword flush-logs
copy the second latest mysql-bin.x to safe place
2. Restore
full restore:
mysql -uroot -ppassword < backup.sql
incremental restore:
mysqlbinlog mysql-bin.000002 mysql-bin.00000X ...(expect the latest mysql-bin.x)| mysql -uroot -ppassword
mysql -uroot -ppassword
mysql> flush privileges;
页:
[1]