mariadb 雙主+keepalived 配置示例

來源:本站原創 Linux 超過919 views圍觀 0條評論

環境描述:
OS:CentOS Linux release 7.3.1611 (Core)
MASTER:192.168.142.138
BACKUP:192.168.142.139
VIP:192.168.142.188

yum -y install mariadb mariadb-server
systemctl enable mariadb
systemctl restart mariadb

mysql_secure_installation    密碼***

安裝依賴
yum -y install gcc gcc-c++ gcc-g77 ncurses-devel bison libaio-devel cmake libnl* libpopt* popt-static openssl-devel

1、配置兩臺Mysql主主同步

[[email protected] ~]# vi /etc/my.cnf  #開啟二進制日志,設置id
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
log = /var/log/mysql/history.log
server-id = 1                    #backup這臺設置2
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema      #忽略寫入binlog日志的庫
auto-increment-increment = 2            #字段變化增量值
auto-increment-offset = 1              #初始字段ID為1  備機為2
slave-skip-errors = all                      #忽略所有復制產生的錯誤

備機一樣配置只需要改ID
重啟

#先查看下log bin日志和pos值位置

mariadb> show master status;
+——————+———-+————–+————————–+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+——————+———-+————–+————————–+
| mysql-bin.000005 |      455 |              | mysql,information_schema |
+——————+———-+————–+————————–+
1 row in set

備機
mariadb> show master status;
+——————+———-+————–+————————–+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+——————+———-+————–+————————–+
| mysql-bin.000001 |     1175 |              | mysql,information_schema |
+——————+———-+————–+————————–+
1 row in set

主機配置
mysql> GRANT  REPLICATION SLAVE ON *.* TO ‘replication’@’192.168.142.%’ IDENTIFIED  BY ‘replication’;
mysql> flush  privileges;
mysql> change  master to
    ->  master_host=’192.168.142.139′,  #備機IP
    ->  master_user=’replication’,
    ->  master_password=’replication’,
    ->  master_log_file=’mysql-bin.000001′, #改成備機的值
    ->  master_log_pos=1175;  #改成備機的值
mysql> start  slave;        #啟動同步

備機配置
mysql> GRANT  REPLICATION SLAVE ON *.* TO ‘replication’@’192.168.142.%’ IDENTIFIED  BY ‘replication’;
mysql> flush  privileges;
mysql> change  master to
    ->  master_host=’192.168.142.138′,  #主機IP
    ->  master_user=’replication’,
    ->  master_password=’replication’,
    ->  master_log_file=’mysql-bin.000001′, #改成備機的值
    ->  master_log_pos=1175;  #改成備機的值
mysql> start  slave;        #啟動同步

完整配置也行
MariaDB [mysql]> change  master to   master_host=’192.168.142.138′, master_user=’replication’,  master_password=’replication’,  master_log_file=’mysql-bin.000018′, master_log_pos=245,master_port=3310;
Query OK, 0 rows affected (0.00 sec)

———————–測試樣例———————————–
change  master to master_host=’192.168.142.139′, master_user=’replication’, master_password=’replication’, master_log_file=’mysql-bin.000001′, master_log_file=’mysql-bin.000003′, master_log_pos=793,master_port=3310;
change  master to master_host=’192.168.142.138′, master_user=’replication’,  master_password=’replication’,  master_log_file=’mysql-bin.000018′, master_log_pos=499,master_port=3310;

GRANT  REPLICATION SLAVE ON *.* TO ‘replication’@’172.16.9.%’ IDENTIFIED  BY ‘replication’;
flush privileges;

change  master to master_host=’172.16.9.62′, master_user=’replication’, master_password=’replication’, master_log_file=’mysql-bin.000003′, master_log_pos=479,master_port=3309;
start slave;
GRANT  REPLICATION SLAVE ON *.* TO ‘replication’@’172.16.9.%’ IDENTIFIED  BY ‘replication’;
flush privileges;

change  master to master_host=’172.16.9.61′, master_user=’replication’, master_password=’replication’, master_log_file=’mysql-bin.000003′, master_log_pos=789,master_port=3309;
start slave;

主備機上查看
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.142.139 #本端IP
                  Master_User: replication #同步帳號
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001 #對端bin文件
          Read_Master_Log_Pos: 1175 #對端Pos
               Relay_Log_File: mariadb-relay-bin.000010
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes   #兩邊都必須為YES
            Slave_SQL_Running: Yes   #兩邊都必須為YES
          Exec_Master_Log_Pos: 1175
              Relay_Log_Space: 825
             Master_Server_Id: 2
1 row in set (0.00 sec)

ERROR: No query specified

——————-數據同步測試————————-
授權測試帳號
grant all privileges on *.* to [email protected]’%’ identified by ‘test’;

主機上新建數據
mariadb> show tables;
+——————–+
| Tables_in_jeffdb03 |
+——————–+
| USER               |
+——————–+
mariadb> insert into USER values(02,’jEff’);  #插入數據
1 row in set

備機要看到
MariaDB [jeffdb03]> select * from USER;
+——–+——+
| number | name |
+——–+——+
|      1 | jeff |
|      2 | jeff |
|      2 | jEff |    #出現即為成功
+——–+——+

———————–主主完成————————-

—————-現在配置keepalive————————–

yum install keepalived

參考http://www.linuxidc.com/Linux/2015-06/118767.htm

[[email protected] ~]# vi /etc/keepalived/keepalived.conf
! Configuration File forkeepalived
global_defs {
notification_email {
[email protected]
}
notification_email_from  [email protected]
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA      #標識,雙主相同
}
vrrp_instance VI_1 {
state BACKUP          #兩臺都設置BACKUP
interface eth0
virtual_router_id 51      #主備相同
priority 100          #優先級,backup設置90
advert_int 1
nopreempt            #不主動搶占資源,只在master這臺優先級高的設置,backup不設置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.0.204
}
}
virtual_server 192.168.0.204 3306 {
delay_loop 2
#lb_algo rr              #LVS算法,用不到,我們就關閉了
#lb_kind DR              #LVS模式,如果不關閉,備用服務器不能通過VIP連接主MySQL
persistence_timeout 50  #同一IP的連接60秒內被分配到同一臺真實服務器
protocol TCP
real_server 192.168.0.202 3306 {  #檢測本地mysql,backup也要寫檢測本地mysql
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 #重試間隔時間
  }
}

 
 

[[email protected] ~]# vi /usr/local/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
[[email protected] ~]# chmod +x /usr/local/keepalived/mysql.sh
[[email protected] ~]# /etc/init.d/keepalived start

 

#backup服務器只修改priority為90、nopreempt不設置、real_server設置本地IP。

#授權兩臺Mysql服務器允許root遠程登錄,用于在其他服務器登陸測試!
mysql> grant all on *.* to’root’@’192.168.0.%’ identified by ‘123.com’;
mysql> flush privileges;

實際配置
! Configuration File for keepalived

global_defs {
   notification_email {
#     [email protected]
#     [email protected]
#     [email protected]
   }
#   notification_email_from [email protected]
#   smtp_server 192.168.200.1
#   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33
    virtual_router_id 51
    priority 90
    advert_int 1
#    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.142.188
    }
}

virtual_server 192.168.142.188 3306 {
    delay_loop 6
    #lb_algo rr
    #lb_kind NAT
    nat_mask 255.255.255.0
    persistence_timeout 50  ##同一IP的連接60秒內被分配到同一臺真實服務器
    protocol TCP

    real_server 192.168.142.139 3306 {
        weight 3
         TCP_CHECK {
         connect_timeout 3    #連接超時
         nb_get_retry 3      #重試次數
         delay_before_retry 3 #重試間隔時間
  }
        }
    }
   
   
具體配置如下
! Configuration File for keepalived

global_defs {
   notification_email {
#     [email protected]
#     [email protected]
#     [email protected]
   }
#   notification_email_from [email protected]
#   smtp_server 192.168.200.1
#   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP       #主/備一樣的配置都為backup
    interface ens33
    virtual_router_id 51 #ID一致
    priority 100       #主100 備90
    advert_int 1
    nopreempt         #主恢復后也不搶回來,由備機代理,不配置默認為搶占
    authentication {
        auth_type PASS  #驗證方式
        auth_pass 1111   #密碼
    }
    virtual_ipaddress {
        192.168.142.188   #VIP地址
    }
}

virtual_server 192.168.142.188 3306 {
    delay_loop 6
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.142.138 3306 {
        weight 3
        notify_down /opt/script/mysqlcheck.sh  #DOWN后處理腳本 
         TCP_CHECK {
         connect_timeout 1    #連接超時
         nb_get_retry 3      #重試次數
         delay_before_retry 1 #重試間隔時間
  }
        }
    }

 

切換測試
1、通過Mysql客戶端通過VIP連接,看是否連接成功。
2、停止master這臺mysql服務,是否能正常切換過去,可通過ip addr命令來查看VIP在哪臺服務器上。

MySQL高可用性之Keepalived+MySQL(雙主熱備)

3、可通過查看/var/log/messges日志,看出主備切換過程
4、master服務器故障恢復后,是否主動搶占資源,成為活動服務器。

[[email protected] ~]# systemctl stop mariadb
[[email protected] ~]# date
Thu May 18 13:33:40 CST 2017
[[email protected] ~]# date
Thu May 18 13:33:48 CST 2017
[[email protected] ~]#

備機日志
May 18 01:33:45 mysql02 Keepalived_vrrp[7569]: VRRP_Instance(VI_1) Transition to MASTER STATE
May 18 01:33:46 mysql02 Keepalived_vrrp[7569]: VRRP_Instance(VI_1) Entering MASTER STATE
May 18 01:33:46 mysql02 Keepalived_vrrp[7569]: VRRP_Instance(VI_1) setting protocol VIPs.
May 18 01:33:46 mysql02 Keepalived_vrrp[7569]: VRRP_Instance(VI_1) Sending gratuitous ARPs on ens33 for 192.168.142.188
May 18 01:33:46 mysql02 Keepalived_healthcheckers[7568]: Netlink reflector reports IP 192.168.142.188 added
May 18 01:33:51 mysql02 Keepalived_vrrp[7569]: VRRP_Instance(VI_1) Sending gratuitous ARPs on ens33 for 192.168.142.188

                          

看備機日志
[[email protected] ~]# tail -f /var/log/messages
May 18 00:04:16 mysql02 Keepalived_vrrp[6570]: VRRP_Instance(VI_1) Transition to MASTER STATE  //變換成主服務器
May 18 00:04:17 mysql02 Keepalived_vrrp[6570]: VRRP_Instance(VI_1) Entering MASTER STATE
May 18 00:04:17 mysql02 Keepalived_vrrp[6570]: VRRP_Instance(VI_1) setting protocol VIPs.      
May 18 00:04:17 mysql02 Keepalived_vrrp[6570]: VRRP_Instance(VI_1) Sending gratuitous ARPs on ens33 for 192.168.142.188
May 18 00:04:17 mysql02 Keepalived_healthcheckers[6569]: Netlink reflector reports IP 192.168.142.188 added  //增加IP成功
May 18 00:04:22 mysql02 Keepalived_vrrp[6570]: VRRP_Instance(VI_1) Sending gratuitous ARPs on ens33 for 192.168.142.188
May 18 00:15:36 mysql02 systemd-logind: Removed session 1.
May 18 01:01:01 mysql02 systemd: Started Session 11 of user root.
May 18 01:01:01 mysql02 systemd: Starting Session 11 of user root.

———–多實例配置開始——————–

添加MySQL用戶
groupadd mysql
useradd -g mysql -s /sbin/nologin mysql

建例mysqldata目錄
mkdir -p /usr/mysqldata/33{07,08,09,10}
chown -R mysql:mysql /usr/mysqldata*
mkdir -p /var/log/mysql

另一種方式
[[email protected] mysqldata]# mkdir -p 33{21..24}
[[email protected] mysqldata]# ll
total 8
drwxr-xr-x 5 mysql mysql  254 Oct 25 13:51 3307
drwxr-xr-x 5 mysql mysql  278 Jul  3 15:58 3308
drwxr-xr-x 5 mysql mysql 4096 Jul  5 14:24 3309
drwxr-xr-x 6 mysql mysql 4096 Jul  5 13:57 3310
drwxr-xr-x 2 root  root     6 Oct 25 14:00 3321
drwxr-xr-x 2 root  root     6 Oct 25 14:01 3322
drwxr-xr-x 2 root  root     6 Oct 25 14:01 3323
drwxr-xr-x 2 root  root     6 Oct 25 14:00 3324

初始化數據文件
mysql_install_db –datadir=/usr/mysqldata/3309/ –user=mysql
chown -R mysql:mysql /usr/mysqldata*
如有報錯不掉他

ll /usr/mysqldata/3309/

[[email protected] mysql]# ll /usr/mysqldata/3309
total 29788
-rw-rw—-. 1 mysql mysql    16384 Jun 29 11:15 aria_log.00000001
-rw-rw—-. 1 mysql mysql       52 Jun 29 11:15 aria_log_control
-rw-rw—-. 1 mysql mysql 18874368 Jun 29 11:17 ibdata1
-rw-rw—-. 1 mysql mysql  5242880 Jun 29 11:17 ib_logfile0
-rw-rw—-. 1 mysql mysql  5242880 Jun 29 11:17 ib_logfile1
drwx——. 2 mysql mysql     4096 Jun 29 11:15 mysql
-rw-rw—-. 1 mysql mysql    30797 Jun 29 11:15 mysql-bin.000001
-rw-rw—-. 1 mysql mysql  1069459 Jun 29 11:15 mysql-bin.000002
-rw-rw—-. 1 mysql mysql      467 Jun 29 11:24 mysql-bin.000003
-rw-rw—-. 1 mysql mysql       57 Jun 29 11:17 mysql-bin.index
drwx——. 2 mysql mysql     4096 Jun 29 11:15 performance_schema
drwx——. 2 mysql mysql        6 Jun 29 11:15 test
[[email protected] mysql]#

修改配置文件開始多實例
vim /etc/my.cnf

#添加引導模塊
[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
#指定程序目錄啟動文件(mysqld_safe安全模式的啟動方式)
mysqladmin = /usr/bin/mysqladmin
#指定管理程序所在的目錄
user       = mysql
password   = mysql
#指定是那一個用戶(必須是系統是存在的用戶)

新建配置文件

mkdir /etc/mysql
chown -R mysql:mysql /etc/mysql

[[email protected] mysql]# ll
total 12
-rw-r–r–. 1 mysql mysql 654 Jun 27 17:01 cfg_1.cnf
-rw-r–r–. 1 mysql mysql 654 Jun 27 17:00 cfg_2.cnf
-rw-r–r–. 1 root  root  654 Jun 29 11:17 cfg_3.cnf
[[email protected] mysql]# pwd
/etc/mysql

vim替換命令
:%s/3309/3310

vim cfg_3.cnf

配置與原配置相似
[mysqld3309]
port=3309
#占用的端口(每一個實例一個端口)
socket=/tmp/mysql3309.sock
##指定套接字文件所在的目錄
pid-file=/tmp/mysql3309.pid
##指定鎖文件所在位置
#max_allowed_packet=1M
#net_buffer_length=2k
##包消息緩沖區初始化為net_buffer_length字節,但需要時可以增長到max_allowed_packet字節
#table_open_cache=4
##所有線程打開的表的個數
#sort_buffer_size=64k
##排序buffer大小;線程級別
#thread_stack=128k
##每個線程的堆棧大小
#basedir=/usr/local/mysql
##源文件所在的目錄
datadir=/usr/mysqldata/3309
##數據實例目錄
server-id=1
##指定為主服務器

 

————–新問題 配置完成后無法啟動—————-
[[email protected] 3322]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3321.cnf –log=/var/log/mysql/mysql_3321.log start
[[email protected] 3322]#
netstat -ntlp   ——-無進程
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name   
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      982/sshd           
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      2846/sendmail: acce
tcp        0      0 0.0.0.0:10050           0.0.0.0:*               LISTEN      1136/zabbix_agentd 
tcp6       0      0 :::22                   :::*                    LISTEN      982/sshd           
tcp6       0      0 :::10050                :::*                    LISTEN      1136/zabbix_agentd 
[[email protected] 3322]#

日志報錯
171025 19:06:27 mysqld_safe Logging to ‘/var/log/mysql/mysql.log’.
171025 19:06:27 mysqld_safe Starting mysqld daemon with databases from /usr/mysqldata/3321
171025 19:06:30 mysqld_safe mysqld from pid file /tmp/mysql3321.pid ended

—————以下啟停方式不推薦—————————
開啟一個實例
c 或

mysql -uroot -p -S  /tmp/mysql3309.sock
停止一個實例
mysqld_multi –defaults-extra-file=/etc/mysql/cfg_3.cnf stop
正常來說你是停不掉的,因為權限不對。
這樣做的目地只有一個,是允許那一個用戶關閉此實例,如果不是此用戶對此實例關閉,那么此實例會自動重啟,
也就是說無法正常關閉(當此實例工作過程中意外中斷也會自動重啟的)
grant shutdown on *.* to  [email protected] identified by ‘laoniubile’;
flush privileges;

如何本地管理
mysql -S /tmp/mysql3308.sock

—————-推薦啟停方式————————————–
以服務的方式啟動、停止,需修改配置文件
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_2.cnf –log=/var/log/mysql/mysql_multi.log start
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_2.cnf –log=/var/log/mysql/mysql_multi.log stop

用以上命令出現無法停機故障 查看日志發現權限不夠
[[email protected] ~]# tail -f /var/log/mysql/mysql_3309.log
Stopping MySQL servers

/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘mysql’@’localhost’ (using password: YES)’
mysqld_multi log file version 2.16; run: Sat Sep 23 14:26:04 2017

Stopping MySQL servers

/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘mysql’@’localhost’ (using password: YES)’

解決方案:
增加mysql權限
mysql -S /tmp/mysql3308.sock  本地登陸
grant shutdown on *.* to  [email protected] identified by ‘mysql’;
flush privileges;

驗證:
mysqld_multi log file version 2.16; run: Sat Sep 23 14:26:38 2017

Stopping MySQL servers

/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘mysql’@’localhost’ (using password: YES)’
mysqld_multi log file version 2.16; run: Sat Sep 23 14:28:44 2017

Stopping MySQL servers   —停止正常

—————服務形式啟動———————————–
完整文件
vim /etc/systemd/system/[email protected]

[Unit]
Description=MySQL Multi Server for instance %i
After=syslog.target
After=network.target

[Service]
User=mysql
Group=mysql
Type=forking
#ExecStart=/usr/bin/mysqld_multi –defaults-file=/etc/mysql/cfg_%i.cnf –log=/var/log/mysql/mysql_multi.log start %i
#ExecStop=/usr/bin/mysqld_multi –log=/var/log/mysql/mysql_multi.log stop %i
ExecStart=/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_%i.cnf –log=/var/log/mysql/mysql_multi.log start
ExecStop=/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_%i.cnf –log=/var/log/mysql/mysql_multi.log stop
Restart=always
RestartSec=5
PrivateTmp=true

[Install]
WantedBy=multi-user.target

 

啟動測試
systemctl start [email protected]
Warning: [email protected] changed on disk. Run ‘systemctl daemon-reload’ to reload units

需先運行 systemctl daemon-reload

netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State     
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN    
tcp        0      0 0.0.0.0:3308            0.0.0.0:*               LISTEN   —已啟動 
tcp        0      0 0.0.0.0:3309            0.0.0.0:*               LISTEN    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN    
tcp6       0      0 :::80                   :::*                    LISTEN    
tcp6       0      0 :::22

狀態
[[email protected] mysql]# systemctl status [email protected]
[email protected] – MySQL Multi Server for instance 2
   Loaded: loaded (/etc/systemd/system/[email protected]; disabled; vendor preset: disabled)
   Active: active (running) since Thu 2017-06-29 17:43:35 CST; 29s ago
  Process: 87923 ExecStop=/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_%i.cnf –log=/var/log/mysql/mysql_multi.log stop (code=exited, status=0/SUCCESS)
  Process: 87936 ExecStart=/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_%i.cnf –log=/var/log/mysql/mysql_multi.log start (code=exited, status=0/SUCCESS)
Main PID: 87942 (mysqld_safe)
   CGroup: /system.slice/system-mysqld.slice/[email protected]
           ├─87942 /bin/sh /usr/bin/mysqld_safe –port=3308 –socket=/tmp/mysql3308.sock –pid-file=/tmp/mysql3308.pid –datadir=/usr/mys…
           └─88218 /usr/libexec/mysqld –basedir=/usr –datadir=/usr/mysqldata/3308 –plugin-dir=/usr/lib64/mysql/plugin –server-id=1 –…

Jun 29 17:43:35 mysql01 systemd[1]: Starting MySQL Multi Server for instance 2…
Jun 29 17:43:35 mysql01 systemd[1]: Started MySQL Multi Server for instance 2.

停機測試

[[email protected] mysql]# systemctl stop [email protected]
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State     
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN    
tcp        0      0 0.0.0.0:3309            0.0.0.0:*               LISTEN    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN    
tcp6       0      0 :::80                   :::*                    LISTEN    
tcp6       0      0 :::22                   :::*                    LISTEN    
[[email protected] mysql]# systemctl status [email protected]
[email protected] – MySQL Multi Server for instance 2
   Loaded: loaded (/etc/systemd/system/[email protected]; disabled; vendor preset: disabled)
   Active: inactive (dead)

Jun 29 17:42:43 mysql01 systemd[1]: Starting MySQL Multi Server for instance 2…
Jun 29 17:42:43 mysql01 systemd[1]: Started MySQL Multi Server for instance 2.
Jun 29 17:42:48 mysql01 systemd[1]: [email protected] holdoff time over, scheduling restart.
Jun 29 17:42:48 mysql01 systemd[1]: Starting MySQL Multi Server for instance 2…
Jun 29 17:42:49 mysql01 systemd[1]: Started MySQL Multi Server for instance 2.
Jun 29 17:43:34 mysql01 systemd[1]: Stopping MySQL Multi Server for instance 2…
Jun 29 17:43:35 mysql01 systemd[1]: Starting MySQL Multi Server for instance 2…
Jun 29 17:43:35 mysql01 systemd[1]: Started MySQL Multi Server for instance 2.
Jun 29 17:45:30 mysql01 systemd[1]: Stopping MySQL Multi Server for instance 2…
Jun 29 17:45:31 mysql01 systemd[1]: Stopped MySQL Multi Server for instance 2.
[[email protected] mysql]#

 

——————————————————————————
上面的方式能啟能停但無法管理,且不生成 mysql.socket 和mysql.pid,
從最后的結果來看應該也是mysql這個帳戶的權限問題修正后即可。
加入權限后已可管理。
[[email protected] mysql]# mysql -S /tmp/mysql3310.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

—————管理實例—————–
mysql -S /tmp/mysql3310.sock

上面的方式能啟能停但無法管理

[[email protected] mysql]# mysql -S /tmp/mysql3310.sock
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql3310.sock’ (2)
[[email protected] mysql]#

——————-有時會碰到無法管理——————-
[[email protected] ~]# mysql -S /tmp/mysql3307.sock
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
[[email protected] ~]#
提示ROOT無權限

所成mysql 提示密碼不對
[[email protected] ~]# mysql -umysql -p -S /tmp/mysql3307.sock
Enter password:
ERROR 1045 (28000): Access denied for user ‘mysql’@’localhost’ (using password: YES)
[[email protected] ~]#

原因是
[mysqld3307]
datadir=/usr/mysqldata/3307   —-少了一這條,不配置默認會使用原mysql配置
修改后解決

當然出現這個問題
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

解決方法和上面也是一樣的

 

—————又有新問題 能啟動不能停服務—————-
[[email protected] mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/my07.cnf  stop
[[email protected] mysql]#
[[email protected] mysql]#
[[email protected] mysql]# !net
netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name   
tcp        0      0 127.0.0.1:199           0.0.0.0:*               LISTEN      983/snmpd          
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      11852/mysqld       
tcp        0      0 0.0.0.0:3308            0.0.0.0:*               LISTEN      12344/mysqld       
tcp        0      0 0.0.0.0:3309            0.0.0.0:*               LISTEN      12814/mysqld       

解決方法,開啟日志大法
[[email protected] mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/my07.cnf  –log=/var/log/mysql/mysql_multi.log stop
查看相應日志
[[email protected] ~]# tail -f /var/log/mysql/mysql_multi.log
mysqld_multi log file version 2.16; run: Mon Jul  3 15:26:15 2017

Stopping MySQL servers

/usr/bin/mysqladmin: connect to server at ‘localhost’ failed
error: ‘Access denied for user ‘mysql’@’localhost’ (using password: YES)’

原因是mysql 沒有停止權限。

————————給啟停服務帳號授權————————
mysql -S /tmp/mysql3308.sock

use mysql;
grant shutdown on *.* to [email protected]’localhost’ identified by ‘mysql’;
flush privileges;

MariaDB [mysql]> use mysql;
Database changed
MariaDB [mysql]> grant shutdown on *.* to [email protected]’localhost’ identified by ‘mysql’;
Query OK, 0 rows affected (0.00 sec)

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

MariaDB [mysql]>

現在有管理權限可以啟停

驗證  停止3307服務
[[email protected] mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/my07.cnf  –log=/var/log/mysql/mysql_multi.log stop
netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name           
tcp        0      0 0.0.0.0:3308            0.0.0.0:*               LISTEN      12344/mysqld       
tcp        0      0 0.0.0.0:3309            0.0.0.0:*               LISTEN      12814/mysqld       
tcp        0      0 0.0.0.0:3310            0.0.0.0:*               LISTEN      13285/mysqld       

[[email protected] ~]# tail -5 /var/log/mysql/mysql_multi.log
error: ‘Access denied for user ‘mysql’@’localhost’ (using password: YES)’
mysqld_multi log file version 2.16; run: Mon Jul  3 15:45:32 2017

Stopping MySQL servers  —–停止信息

[[email protected] ~]#

———————多實例配置完成———————–
配置兩臺Mysql主主同步 參考上面。

———————以下為不可管理的故障說明———————-

[[email protected] mysql]# ps aux |grep 3310
mysql     93783  0.0  0.1 113256  1584 ?        S    18:06   0:00 /bin/sh /usr/bin/mysqld_safe –port=3310 –socket=/tmp/mysql3310.sock –pid-file=/tmp/mysql3310.pid –datadir=/usr/mysqldata/3310 –server-id=1
mysql     94059  0.0  8.3 771324 83384 ?        Sl   18:06   0:00 /usr/libexec/mysqld –basedir=/usr –datadir=/usr/mysqldata/3310 –plugin-dir=/usr/lib64/mysql/plugin –server-id=1 –log-error=/var/log/mariadb/mariadb.log –pid-file=/tmp/mysql3310.pid –socket=/tmp/mysql3310.sock –port=3310
root      94541  0.0  0.0 112648   964 pts/0    R+   18:09   0:00 grep –color=auto 3310

但手動運行卻有
[[email protected] mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_4.cnf start
[[email protected] mysql]# ll /tmp/
total 12
-rw-rw—-. 1 mysql mysql  6 Jun 27 17:16 mysql3307.pid
srwxrwxrwx. 1 mysql mysql  0 Jun 27 17:16 mysql3307.sock
-rw-rw—-. 1 mysql mysql  6 Jun 29 11:17 mysql3309.pid
srwxrwxrwx. 1 mysql mysql  0 Jun 29 11:17 mysql3309.sock
-rw-rw—-. 1 mysql mysql  6 Jun 29 18:12 mysql3310.pid
srwxrwxrwx. 1 mysql mysql  0 Jun 29 18:12 mysql3310.sock

手動運行卻無法停止——最后證明是權限問題
[[email protected] mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_4.cnf stop
[[email protected] mysql]# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name   
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      14292/mysqld       
tcp        0      0 0.0.0.0:3309            0.0.0.0:*               LISTEN      12001/mysqld       
tcp        0      0 0.0.0.0:3310            0.0.0.0:*               LISTEN      97448/mysqld       
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      2209/sshd          
tcp6       0      0 :::80                   :::*                    LISTEN      875/httpd          
tcp6       0      0 :::22                   :::*                    LISTEN      2209/sshd          
[[email protected] mysql]#

使用mysqladmin shutdown 可以停止
[[email protected] mysql]# mysqladmin shutdown -S /tmp/mysql3310.sock
netstat -ntl
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State     
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN    
tcp        0      0 0.0.0.0:3309            0.0.0.0:*               LISTEN    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN    
tcp6       0      0 :::80                   :::*                    LISTEN  

 

———————排查過程————————–
查日志,當然要先開啟

在配置文件增加配置
log=/var/log/mariadb/mariadb3310.log                                                                                                      
"cfg_4.cnf" 24L, 691C

查看對應日志文件    
170630 11:37:32        1 Connect    [email protected] as anonymous on
            1 Connect    Access denied for user ‘mysql’@’localhost’ (using password: YES)

查日志確認是權限問題
MariaDB [mysql]> grant shutdown on *.* to [email protected]’localhost’ identified by ‘mysql’;
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

再試一下,看結果搞定
[[email protected] mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_4.cnf stop
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name   
tcp        0      0 0.0.0.0:3307            0.0.0.0:*               LISTEN      14292/mysqld       
tcp        0      0 0.0.0.0:3309            0.0.0.0:*               LISTEN      12001/mysqld       
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      2209/sshd          
tcp6       0      0 :::80                   :::*                    LISTEN      875/httpd          
tcp6       0      0 :::22                   :::*                    LISTEN      2209/sshd          
[[email protected] mysql]#

結總一下命令
開啟
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_4.cnf start
停止
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_4.cnf stop
管理
mysql -S /tmp/mysql3310.sock

——————–下面開始keepalived配置—————-
增加配置如下
virtual_server 172.16.9.63 3307 {
    delay_loop 6
    #lb_algo rr
    #lb_kind NAT
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 172.16.9.61 3307 {
        weight 3
        notify_down /opt/script/mysqlcheck07.sh   –檢測報警腳本
         TCP_CHECK {
         connect_timeout 1    #連接超時
         nb_get_retry 3      #重試次數
         delay_before_retry 1 #重試間隔時間
  }
        }
    }

檢測到任意一個mysql掛掉即KILL掉當前keepalived進程,使備機啟動切換成主機繼續提供服務;

那么就有個問題,如果主機開啟搶占模式,那么當DB恢復的時候導致數據庫又切回又會導致一次中斷,所以lvs的配置里面

增加 nopreempt 取消搶占功能,兩個服務器均使用 backup模式。

————————-注意——————-
出現問題后,應立即解決相應問題重啟DB,再開啟keepalived服務。順序不能亂。因為

[[email protected] ~]# systemctl status keepalived -l
● keepalived.service – LVS and VRRP High Availability Monitor

Jul 04 11:35:09 shtsmysql01 Keepalived_healthcheckers[8950]: TCP connection to [172.16.9.61]:3310 failed !!!
Jul 04 11:35:09 shtsmysql01 Keepalived_healthcheckers[8950]: Removing service [172.16.9.61]:3310 from VS [172.16.9.63]:3310
Jul 04 11:35:09 shtsmysql01 Keepalived_healthcheckers[8950]: IPVS: No such destination
Jul 04 11:35:09 shtsmysql01 Keepalived_healthcheckers[8950]: Executing [/opt/script/mysqlcheck10.sh] for service [172.16.9.61]:3310 in VS [172.16.9.63]:3310
Jul 04 11:35:09 shtsmysql01 Keepalived_healthcheckers[8950]: Lost quorum 1-0=1 > 0 for VS [172.16.9.63]:3310

mysqld_multi –defaults-extra-file=/etc/mysql/my10.cnf  –log=/var/log/mysql/mysql_multi.log start
systemctl start keepalived
systemctl status keepalived

文章出自:CCIE那點事 http://www.qdxgqk.live/ 版權所有。本站文章除注明出處外,皆為作者原創文章,可自由引用,但請注明來源。 禁止全文轉載。
本文鏈接:http://www.qdxgqk.live/?p=3597轉載請注明轉自CCIE那點事
如果喜歡:點此訂閱本站
  • 相關文章
  • 為您推薦
  • 各種觀點
?
?
萌宠夺宝游戏