MySQL 5.7 雙主複製+keepalived，常規業務一般夠用了

標籤：

業務需求：

為Zabbix搭建2個資料庫，一個庫給伺服器監控用，一個庫給網路監控用。

 

硬體：

兩台伺服器，硬碟是1.2 T SSD卡，記憶體128G

 

架構：

希望做雙主複製+keepalived，架構大概如

主機A IP：192.168.1.2

主機B IP：192.168.1.3

VIP：192.168.1.4

 

 

一、首先安裝MySQL 5.7

到下面的url下載你作業系統對應的yum包

http://dev.mysql.com/downloads/repo/yum/

 

運行下面兩個命令安裝

rpm -ivh mysql57-community-release-el6-8.noarch.rpm 

yum -y install mysql.x86_64 mysql-server.x86_64 mysql-devel.x86_64

 

二、做一些簡單的最佳化工作

echo "noop" > /sys/block/nvme0n1/queue/scheduler

等等

 

三、配置/etc/my.cnf

核心工作也就是這個了，因為5.7的變化比較大，我簡答列一些我的配置說明一下。

 

character_set_server=utf8

max_connections = 5000

max_connect_errors = 200000

transaction_isolation = READ-COMMITTED

explicit_defaults_for_timestamp = 1

tmp_table_size = 67108864

max_allowed_packet = 16777216

interactive_timeout = 57600  //老報逾時，我就設定大了一點

wait_timeout = 57600 //老報逾時，我就設定大了一點

read_buffer_size = 16777216

read_rnd_buffer_size = 33554432

sort_buffer_size = 134217728

join_buffer_size = 134217728

query_cache_size = 0

query_cache_type = 0

 

server-id = 1  //另外一台設定為2

auto_increment_increment=2

auto_increment_offset=1 //另外一台設定為2

 

binlog-do-db = nzabbix

binlog-do-db = szabbix

binlog-ignore-db = monitor_db

replicate-do-db = nzabbix //需要同步的資料

replicate-do-db = szabbix //需要同步的資料

replicate-ignore-db = mysql

replicate-ignore-db = sys

replicate-ignore-db = monitor_db  //用來看Mysql用的一個庫

slave-skip-errors=all  //害怕遇到錯誤停止同步，忽略所有錯誤

sync_binlog = 0 //zabbix資料不是很重要，容許crash丟資料

 

master_info_repository = TABLE

relay_log_info_repository = TABLE

 

gtid_mode = on  //開啟gtid，這是一個新特性，我們的複製是基於GTID的，所以開啟

enforce_gtid_consistency = 1

binlog_gtid_simple_recovery = 1

log-slave-updates

 

# slave

slave-parallel-type = LOGICAL_CLOCK

slave-parallel-workers = 0  //日誌老報一個錯誤，先關閉並行了

#slave-parallel-workers = 16

 

########innodb settings########

innodb_buffer_pool_size = 100G //記憶體的75%比較合適

innodb_buffer_pool_instances = 16

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_lru_scan_depth = 2000

innodb_lock_wait_timeout = 10000

innodb_io_capacity = 4000

innodb_io_capacity_max = 8000

innodb_flush_method = O_DIRECT

innodb_flush_neighbors = 1

innodb_log_file_size = 4G

innodb_log_buffer_size = 16777216

innodb_purge_threads = 4

innodb_large_prefix = 1

innodb_thread_concurrency = 64

innodb_print_all_deadlocks = 1

innodb_strict_mode = 1

innodb_sort_buffer_size = 67108864 

innodb_flush_log_at_trx_commit = 2

innodb_read_io_threads = 16

innodb_write_io_threads = 16 

 

[mysqld-5.7]

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 4

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 2G

innodb_purge_rseg_truncate_frequency = 128

binlog_gtid_simple_recovery=1

log_timestamps=system

transaction_write_set_extraction=MURMUR32

show_compatibility_56=on

上面只是配置的一部分，大家可以自己網上查查資料補全，另外一台要改的地方我已經在上面標註了。

 

比較關鍵的是GTID：

 

Gtid是從5.6開始推出的殺手級特性，通過GTID特性，極大的提升了主備切換的效率和一致性。

 

在MySQL5.7.5裡引入了一個新的系統資料表GTID_EXECUTED：

表的描述參考官方文檔：

http://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#replication-gtids-gtid-executed-table

對應worklog: http://dev.mysql.com/worklog/task/?id=6559

 

四、初始化目錄

5.7已經不用mysql_install_db初始化了，已經改成下面這個命令了

mysqld --initialize --datadir=/data0/mysql/

chmod -R 755 /data0/mysql/

chown -R mysql.mysql /data0/mysql/

 

五、設定密碼

5.7比較討厭，初始密碼不為空白了，也可以說安全性更高了。

初始化密碼在初始化日誌裡

grep "temporary password" error.log

 

如果你登入mysql想修改密碼可能會報錯

errROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

 

為了以後不麻煩，順手降低密碼原則。

mysql > SET GLOBAL  validate_password_policy=‘LOW‘; 

mysql > alter user ‘root‘@‘localhost‘ identified by ‘your-password‘;

mysql > flush privileges; 

 

如果mysql和mysqladmin密碼行帶密碼可能還會出現一個Warning

mysql -u root -p password ‘your-password‘ 

 

mysql: [Warning] Using a password on the command line interface can be insecure.

 

解決方案有多種，可以參考：

http://www.quwenqing.com/read-247.html

其中的一種解決方案最靠譜

export MYSQL_PWD=password

 

但是這個錯誤提示非常討厭，命令列沒有參數可以關閉，我又不想在my.cnf裡寫密碼。你會發現zabbix監控mysql的資料出來都是這個提示，我們還需要把mysql監控的shell裡的命令列帶密碼的改成export這種形式。

 

六、設定資料同步

A和B上同時添加同步處理的使用者

use mysql;

create user ‘repl‘@‘192.168.1.%‘ identified by ‘your-password‘;

GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘192.168.1.%‘; 

flush privileges;

 

鎖一下表，追平兩台機器資料，然後解鎖。

 

先看看GTID是否開啟

mysql> show global variables like ‘%gtid%‘;

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

| Variable_name            | Value |

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

| enforce_gtid_consistency | ON    |

| gtid_executed            |       |

| gtid_mode                | ON    |

| gtid_owned               |       |

| gtid_purged              |       |

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

5 rows in set (0.10 sec)

 

說明gtid功能已啟動。

GTID同步資料不用再記錄對方的log檔案和位置了，用master_auto_position=1就行，不過你用老的方法查看master的logfile和logpos，同步也是可以的。

 

在A和B上都運行

 

mysql> change master to master_host=‘192.168.1.2‘, master_user=‘repl‘,master_password=‘your-password‘,master_auto_position=1;

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

mysql> start slave;

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> start slave;

 

在A和B上查看同步狀態

show slave status\G

#表示同步的檔案和位置

Master_Log_File: mysql-bin.000035

Read_Master_Log_Pos: 895597105

#顯示下面表示工作正常

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

#表示當前同步的資料庫

Replicate_Do_DB: nzabbix,szabbix

 

雙主複製比較簡單，總結一下就是：

安裝MySQL->最佳化系統->最佳化配置my.cnf->初始化mysqld->添加同步處理的使用者->開始同步->查看同步狀態。

 

六、keepalived的安裝和設定

keepalived安裝比較簡單

yum install keepalived

 

設定日誌

cat /etc/sysconfig/keepalived

KEEPALIVED_OPTIONS="-D -d -S 0"

 

在rsyslog.conf最後添加

local0.*                                                /var/log/keepalived.log

 

重啟rsyslog和keepalived

 

keepliaved的配置也很簡單

global_defs {

   notification_email {

         [email protected]

   }

   router_id MySQL-ha85

}

 

vrrp_script check_run {

   script "/data0/keepalived_check_mysql.sh"     #監控MySQL的指令碼

   interval 3

}

 

vrrp_sync_group VG1 {

    group {

          VI_1

    }

}

vrrp_instance VI_1 {

    state BACKUP

    interface eth0                  #指定虛擬IP的網路介面

    virtual_router_id 85   #VRRP組名，兩個節點的設定必須一樣，以指明各個節點屬於同一VRRP組

    priority 100                 #主節點的優先順序（1-254之間），備用節點必須比主節點優先順序低。

    advert_int 1                 #組播資訊發送間隔，兩個節點設定必須一樣

   nopreempt  

    authentication {             #設定驗證資訊，兩個節點必須一致

        auth_type PASS

        auth_pass 1111

    }

    track_script {

        check_run

    }

    virtual_ipaddress {

        192.168.1.4       #虛擬IP,對外提供MySQL服務的IP地址

    }

}

 

另外一台優先順序設定底一點就可以，也可以設定為MASTER-BACKUP，看你自己的切換需求了。

 

/data0/keepalived_check_mysql.sh的作用是檢查當前Mysql是否正常工作

我在裡面做了一個寫操作，來判斷MySQL是否正常

 $MYSQL -h $MYSQL_HOST -u $MYSQL_USER -e "use monitor_db;UPDATE monitor_db SET CreateDate=NOW() WHERE ID=1;" >/dev/null 2>&1

 

我還寫了一個程式放到cron裡，每分鐘運行一次，為啥要這麼幹？主要我一重啟MySQL，就會發生自動殺掉keepalived，並且IP飄走，這時候如果MySQL重啟好了，keepalived不能自己啟動。程式如下：

 

if [ ! -z "$(/bin/ps aux|grep -v grep|grep ‘mysqld --basedir‘)" ];then

        echo "mysqld is running"

        if [ -z "$(/bin/ps aux|grep -v grep|grep ‘/usr/sbin/keepalived ‘)" ];then

                echo "keepalived is not running"

                service keepalived restart

        fi

fi

 

就是判斷如果MySQL還活著，就啟動keepalived，不過這塊代碼有點簡陋，理論上應該判斷MySQL是否還活著，並且可以寫資料，再啟動keepalived。

 

這套MySQL雙主複製+keepalived 架構其實可以適用於各種業務，大家可以靈活使用，擴充也很方便。

 

閱讀原文

MySQL 5.7 雙主複製+keepalived，常規業務一般夠用了