MySQL配置主主复制和高可用

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 TOMASTER_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: 0Master_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就填nauto_increment_offset=1   #起始值。一般填第n台主MySQL。此时为第一台主MySQLbinlog-ignore=mysql   #忽略mysql库binlog-ignore=information_schema   #忽略information_schema库replicate-do-db=test   #要同步的数据库，默认所有库

修改备机my.cnf

auto_increment_increment=2auto_increment_offset=2replicate-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 TOMASTER_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: 0Master_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 keepalivedglobal_defs {   notification_email {     [e-mail]     [e-mail]     [e-mail]   }   notification_email_from [e-mail]   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 {     [e-mail]     [e-mail]     [e-mail]   }   notification_email_from [e-mail]   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/bashpkill keepalivedchmod +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服务，发现连接依然可用