MySQL配置主主复制和高可用

置顶金牛IT

已于 2023-03-05 11:07:53 修改

阅读量672

点赞数 1

分类专栏： mysql 文章标签： mysql 高可用主主复制 keepalived 双机

于 2017-09-29 19:33:29 首次发布

本文链接：https://blog.csdn.net/zhumingyan/article/details/78136737

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

#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>

说明：

Slave_IO_Running和Slave_SQL_Running状态必须为Yes，如果为No，Last_IO_Error会显示错误信息，一般都是防火墙问题，关闭iptables和selinux；
启动时如果提示server id重复了，请修改my.cnf文件，主备机的id不能重复

6、测试主从复制
在主机上test数据库下创建表并插入数据，备机上可以查看到新建的表和内容

#配置主主复制

修改主机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/
vi /usr/local/keepalived/mysql.sh
#!/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;

mysql> grant all on *.* to'root'@'%' identified by 'root';
flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

远程连接到虚拟IP地址：

jdbc:mysql://192.168.226.53:3306/test?useUnicode=true&characterEncoding=utf8

停止主机上的mysqld服务，发现连接依然可用

金牛IT

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
MySQL配置主主复制和高可用

MySQL主从复制设置步骤：操作系统： CentOS release 6.6 (Final) 64位主机： 192.168.226.51备机： 192.168.226.52数据库版本： 5.1.731、主备机各自安装MySQL数据库yum install mysql-server2、主机上启用二进制日志：vi /etc/my.cnf>log-bin=mysql-
复制链接

扫一扫