MySQL配置主主复制和高可用

原创

金牛IT 2023-04-06 14:06:48 博主文章分类：MySQL ©著作权

©著作权归作者所有：来自51CTO博客作者金牛IT的原创作品，请联系作者获取转载授权，否则将追究法律责任

#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 –y2、配置keepalived
 主机配置vi /etc/keepalived/keepalived.conf
 ! Configuration File for keepalivedglobal_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 keepalivedglobal_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服务，发现连接依然可用