#MySQL主从复制设置步骤：
操作系统： CentOS release 6.6 (Final) 64位
主机： 192.168.226.51
备机： 192.168.226.52
数据库版本： 5.1.73
- 1、主备机各自安装MySQL数据库
yum install mysql-server
- 2、主机上启用二进制日志：
vi /etc/my.cnf
>log-bin=mysql-bin
- 3、重启MySQL数据库，检查日志名称和位置信息，并创建MySQL用户，用于从备机访问日志
service mysqld restart
mysql> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql-bin.000005 | 350 | | |
+------------------+----------+--------------+--------------------------+
1 row in set (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysql52'@'192.168.226.52' IDENTIFIED BY 'mysql52';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 4、告知备机日志名称和位置信息
CHANGE MASTER TO
MASTER_HOST='192.168.226.51',
MASTER_USER='mysql52',
MASTER_PASSWORD='mysql52',
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=350;
5、启动slave并查看状态
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.226.51
Master_User: mysql52
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 514
Relay_Log_File: mysqld-relay-bin.000009
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 514
Relay_Log_Space: 960
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
mysql>
说明：
A、Slave_IO_Running和Slave_SQL_Running状态必须为Yes，如果为No，Last_IO_Error会显示错误信息，一般都是防火墙问题，关闭iptables和selinux；
B、启动时如果提示server id重复了，请修改my.cnf文件，主备机的id不能重复
- 6、测试主从复制
在主机上test数据库下创建表并插入数据，备机上可以查看到新建的表和内容
7、配置主主复制
修改主机my.cnf
auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL
binlog-ignore=mysql #忽略mysql库
binlog-ignore=information_schema #忽略information_schema库
replicate-do-db=test #要同步的数据库，默认所有库
修改备机my.cnf
auto_increment_increment=2
auto_increment_offset=2
replicate-do-db=test
配置好后重启MySQL
在备机上为主机创建复制所用的账号
mysql> GRANT REPLICATION SLAVE ON . TO ‘mysql51’@‘192.168.226.51’ IDENTIFIED BY ‘mysql51’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看备机二进制日志文件和读取位置：
mysql> show master status;
±-----------------±---------±-------------±-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-------------------------+
| mysql-bin.000005 | 106 | | mysql,information_schema |
±-----------------±---------±-------------±-------------------------+
1 row in set (0.00 sec)
mysql>
在主机上备机二进制文件日志：
CHANGE MASTER TO
MASTER_HOST=‘192.168.226.52’,
MASTER_USER=‘mysql51’,
MASTER_PASSWORD=‘mysql51’,
MASTER_LOG_FILE=‘mysql-bin.000005’,
MASTER_LOG_POS=106;
启动slave并查看状态：
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
±-----------------±---------±-------------±-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-------------------------+
| mysql-bin.000005 | 514 | | mysql,information_schema |
±-----------------±---------±-------------±-------------------------+
1 row in set (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.226.52
Master_User: mysql52
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000008
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 552
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.01 sec)
mysql>
配置keepalived实现热备
1、安装keepalived
yum install keepalived –y
2、配置keepalived
主机配置vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id MYSQL_HA #标识，主备相同
}
vrrp_instance VI_1 {
state BACKUP #两台机都设置为BACKUP
interface eth1
virtual_router_id 51 #主备相同
priority 100 #节点优先级（1-254之间），备机设置为90
advert_int 1
nopreempt #禁止抢占服务。
#默认情况，当MASTER服务宕掉之后，BACKUP自动升级为MASTER并接替它的任务；当MASTER服务恢复后，升级为MASTER的BACKUP服务又自动降为BACKUP，把工作权交给原MASTER。
#当配置了nopreempt，MASTER从挂掉到恢复，不再将服务抢占过来。
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.226.53
}
}
virtual_server 192.168.226.53 3306 {
delay_loop 2
#lb_algo wrr #LVS算法
#lb_kind DR #LVS模式
persistence_timeout 50 #同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server 192.168.226.51 3306 { #检测本地mysql，备机填写备机的IP
weight 3
notify_down /usr/local/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换
TCP_CHECK {
connect_timeout 3 #连接超时
nb_get_retry 3 #重试次数
delay_before_retry 3 #重试间隔时间
}
}
备机配置vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id MYSQL_HA
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90 #优先级设置为90
advert_int 1
#nopreempt #备机不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.226.53
}
}
virtual_server 192.168.226.53 3306 {
delay_loop 2
#lb_algo wrr
#lb_kind DR
persistence_timeout 50
protocol TCP
real_server 192.168.226.52 3306 { #填写本机真实IP
weight 3
notify_down /usr/local/keepalived/mysql.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
}
}
在主机上创建切换脚本
mkdir /etc/keepalived/
#!/bin/bash
pkill keepalived
chmod +x /usr/local/keepalived/mysql.sh
重启keepalived服务
service keepalived start
在主备机上创建用户用于远程访问测试：
grant all on . to’root’@’%’ identified by ‘root’;
flush privileges;
远程连接到虚拟IP地址：
jdbc:mysql://192.168.226.53:3306/test?useUnicode=true&characterEncoding=utf8
停止主机上的mysqld服务，发现连接依然可用
318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
