centos7 mysql互為主從+keepalived

一、互為主從配置

1.1、資源情況

192.168.11.177 server1

192.168.11.180 server2

192.168.11.210 VIP

1.2、server1配置

# vi /etc/my.cnf

[mysqld]

server-id=1

log-bin=mysql-bin

relay_log=mysql-realy-bin

relay_log_index=slave-mysql-realy-bin.index

expire_logs_days=15

binlog_format=mixed

auto-increment-increment=2

auto-increment-offset= 1

innodb_flush_log_at_trx_commit=1

replicate-ignore-db=sys

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

datadir=/data/mysql_data

socket=/var/lib/mysql/mysql.sock

character_set_server=utf8

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

# innodb optimization

innodb_buffer_pool_size=8G

innodb_log_file_size=256M

innodb_flush_method=O_DIRECT

max_connections=500

innodb_autoextend_increment=128

1.3、server2配置

# vi /etc/my.cnf

server-id=2

log-bin=mysql-bin

relay_log=mysql-realy-bin

relay_log_index=slave-realy-bin.index

expire_logs_days=15

binlog_format=mixed

auto-increment-increment=2

auto-increment-offset= 2

innodb_flush_log_at_trx_commit=1

replicate-ignore-db=sys

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

replicate-ignore-db=performance_schema

datadir=/data/mysql_data

socket=/var/lib/mysql/mysql.sock

character_set_server=utf8

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

# innodb optimization

innodb_buffer_pool_size=8G

innodb_log_file_size=256M

innodb_flush_method=O_DIRECT

max_connections=500

innodb_autoextend_increment=128

1.4、server1、server2上修改賬戶密碼，創建同步賬戶

# mysql -p

mysql> set password=password('******');

mysql> create user repluser@'%' identified by '******';

mysql> grant replication slave, replication client on *.* to repluser@'%';

1.5、server2建立同步

server1的master狀態

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000003 |      882 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

mysql> CHANGE MASTER TO MASTER_HOST='192.168.11.177', MASTER_USER='repluser', MASTER_PASSWORD='******', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=882;

Query OK, 0 rows affected, 2 warnings (0.10 sec)

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: 

                  Master_Host: 192.168.11.177

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 882

               Relay_Log_File: mysql-realy-bin.000001

                Relay_Log_Pos: 4

        Relay_Master_Log_File: mysql-bin.000003

             Slave_IO_Running: No

            Slave_SQL_Running: No

              Replicate_Do_DB: 

          Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema

           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: 882

              Relay_Log_Space: 154

              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: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error: 

               Last_SQL_Errno: 0

               Last_SQL_Error: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 0

                  Master_UUID: 

             Master_Info_File: /data/mysql_data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: 

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: 

                Auto_Position: 0

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

# vi /data/mysql_data/auto.cnf

[auto]

server-uuid=cbcefb67-9f9a-11e8-91b3-06ba24001d86

> 

server-uuid=cbcefb77-9f9a-11e8-91b3-06ba24001d86

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.11.177

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 882

               Relay_Log_File: mysql-realy-bin.000003

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000003

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema

           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: 882

              Relay_Log_Space: 527

              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: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 1

                  Master_UUID: cd146946-9f95-11e8-9a29-063696001d83

             Master_Info_File: /data/mysql_data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: 

                Auto_Position: 0

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

mysql> start slave;

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000003 |      154 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1.6、server1建立同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.11.180', MASTER_USER='repluser', MASTER_PASSWORD='******', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;

Query OK, 0 rows affected, 2 warnings (0.22 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.11.180

                  Master_User: repluser

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 154

               Relay_Log_File: mysql-realy-bin.000002

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000003

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB: 

          Replicate_Ignore_DB: sys,mysql,information_schema,performance_schema

           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: 154

              Relay_Log_Space: 527

              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: 

  Replicate_Ignore_Server_Ids: 

             Master_Server_Id: 2

                  Master_UUID: cbcefb67-9f9a-11e8-91b3-06ba24001d86

             Master_Info_File: /data/mysql_data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

           Master_Retry_Count: 86400

                  Master_Bind: 

      Last_IO_Error_Timestamp: 

     Last_SQL_Error_Timestamp: 

               Master_SSL_Crl: 

           Master_SSL_Crlpath: 

           Retrieved_Gtid_Set: 

            Executed_Gtid_Set: 

                Auto_Position: 0

         Replicate_Rewrite_DB: 

                 Channel_Name: 

           Master_TLS_Version: 

1 row in set (0.00 sec)

mysql> create database ceshi_db;

Query OK, 1 row affected (0.01 sec)

mysql> use ceshi_db1;

Database changed

mysql> create table home(id int(10) not null,name char(10));

Query OK, 0 rows affected (0.14 sec)

1.7、server2驗證

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ceshi_db           |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.00 sec)

mysql> use ceshi_db;

Database changed

mysql> show tables;

+--------------------+

| Tables_in_ceshi_db |

+--------------------+

| home               |

+--------------------+

1 row in set (0.00 sec)

mysql> create database ceshi_db1;

Query OK, 1 row affected (0.01 sec)

mysql> create table home(id int(10) not null,name char(10));^C

mysql> use ceshi_db1;

Database changed

mysql> create table home(id int(10) not null,name char(10));

Query OK, 0 rows affected (0.09 sec)

1.8、server1驗證

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| ceshi_db           |

| ceshi_db1          |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

6 rows in set (0.00 sec)

mysql> use ceshi_db1;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+---------------------+

| Tables_in_ceshi_db1 |

+---------------------+

| home                |

+---------------------+

1 row in set (0.00 sec)

二、keepalived配置

2.1、server1配置

# cat /etc/keepalived/keepalived.conf

global_defs {

   smtp_server 127.0.0.1 

   smtp_connect_timeout 30

   router_id mysql-1

}

vrrp_script chk_mysql {

   script "/etc/keepalived/scripts/mysql_check.sh"

   interval 2

   weight -5

   fall 2

   rise 1

}

vrrp_instance VI_1 {

    state MASTER 

    interface eth0

    virtual_router_id 61

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

       track_script {

       chk_mysql

    }

    virtual_ipaddress {

        182.168.11.210

    }

}

2.2、server2配置

# cat /etc/keepalived/keepalived.conf

global_defs {

   smtp_server 127.0.0.1

   smtp_connect_timeout 30

   router_id mysql-2

}

vrrp_script chk_mysql {

   script "/etc/keepalived/scripts/mysql_check.sh"

   interval 2

   weight -5

   fall 2

   rise 1

}

vrrp_instance VI_1 {

    state BACKUP 

    interface eth0

    virtual_router_id 61

    priority 99

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 1111

    }

       track_script {

       chk_mysql

    }

    virtual_ipaddress {

        182.168.11.210

    }

}

2.3、腳本

# cat /etc/keepalived/scripts/mysql_check.sh 

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

if [ "${counter}" -eq 0 ]; then

    service keepalived stop

fi

2.4、不搶占模式

如果把server1設置為VIP不搶占模式，做下面修改

server1

# vi /etc/keepalived/keepalived.conf 

添加

nopreempt   

state MASTER

> 

state BACKUP        

#防止切換到從庫后，主keepalived恢復后自動切換回主庫

參考：

https://blog.csdn.net/qq_36276335/article/details/69942101