mysql 生產環境多實例配置

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

 

多實例啟動
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3307.cnf –log=/var/log/mysql/mysql_3307.log start
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3308.cnf –log=/var/log/mysql/mysql_3308.log start
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3309.cnf –log=/var/log/mysql/mysql_3309.log start
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3310.cnf –log=/var/log/mysql/mysql_3310.log start
多實例停止
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3307.cnf –log=/var/log/mysql/mysql_3307.log stop
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3308.cnf –log=/var/log/mysql/mysql_3308.log stop
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3309.cnf –log=/var/log/mysql/mysql_3309.log stop
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3310.cnf –log=/var/log/mysql/mysql_3310.log stop

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

mysql -S /tmp/mysql3307.sock 
grant shutdown on *.* to  [email protected] identified by ‘mysql’;
flush privileges;

keepalived 進程正常但vip起不來.

vrrp_instance VI_1 {
    state BACKUP
    interface em1
    virtual_router_id 26  #修改不同的id,同一網內有多個keepalived
    priority 10      #修改優先級
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 11112  修改密碼
    }
    virtual_ipaddress {
        172.30.100.26
    }
   notify_master /opt/script/to_master.sh
   notify_backup /opt/script/to_backup.sh
}

——配置雙主——-
主機 3310
MariaDB [(none)]> show master status;
+——————+———-+————–+————————–+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+——————+———-+————–+————————–+
| mysql-bin.000001 |      245 |              | mysql,information_schema |
+——————+———-+————–+————————–+
1 row in set (0.00 sec)

主機配置 3310
mysql> GRANT  REPLICATION SLAVE ON *.* TO ‘replication’@’172.30.100.25’ IDENTIFIED  BY ‘replication’;
mysql> flush  privileges;
mysql> change  master to
    ->  master_host=’172.30.100.25′,
    ->  master_user=’replication’,
    ->  master_password=’replication’,
    ->  master_log_file=’mysql-bin.000004′,
    ->  master_log_pos=499,
    ->  master_port=3310;
mysql> start  slave;      

備機 3310
MariaDB [(none)]> show master status;
+——————+———-+————–+————————–+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         |
+——————+———-+————–+————————–+
| mysql-bin.000004 |      245 |              | mysql,information_schema |
+——————+———-+————–+————————–+

備機配置 3310
mysql> GRANT  REPLICATION SLAVE ON *.* TO ‘replication’@’172.30.100.24’ IDENTIFIED  BY ‘replication’;
mysql> flush  privileges;
mysql> change  master to
    ->  master_host=’172.30.100.24′,
    ->  master_user=’replication’,
    ->  master_password=’replication’,
    ->  master_log_file=’mysql-bin.000001′,
    ->  master_log_pos=579,
    ->  master_port=3310;
mysql> start  slave; 

主備機查詢狀態:
MariaDB [(none)]> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.30.100.25
                  Master_User: replication
                  Master_Port: 3310
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 499
               Relay_Log_File: mysql3310-relay-bin.000002
                Relay_Log_Pos: 529
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes  #為YES為正常
            Slave_SQL_Running: Yes  #為YES為正常
          Exec_Master_Log_Pos: 499  #對端的值
             Master_Server_Id: 2   #主機上看為備機的數值2   備機上看是主機的值1
            
給各機器創建管理帳號
GRANT  all ON *.* TO ‘nbjpmysql’@’172.%.%.%’ IDENTIFIED  BY ‘pass’;
 
查用戶權限
MariaDB [(none)]> show grants for [email protected]"172.%.%.%";
+—————————————————————————————————————————+
| Grants for [email protected]%.%.%                                                                                            |
+—————————————————————————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘nbjpmysql’@’172.%.%.%’ IDENTIFIED BY PASSWORD ‘*2287’ |
+—————————————————————————————————————————+
1 row in set (0.00 sec)

MySql: 查看當前登錄用戶,當前數據庫
mysql> select user();
+—————-+
| user() |
+—————-+
| [email protected] |
+—————-+
1 row in set (0.00 sec)

 

mysql> select database();
+——————–+
| database() |
+——————–+
| information_schema |
+——————–+
1 row in set (0.00 sec)

————其中出現故障————-

說明權限不夠,
Last_IO_Error: error connecting to master ‘[email protected]:3309’ – retry-time: 60  retries: 86400  message: Access denied for user ‘replication’@’172.30.100.25’ (using password: YES)

解決方法
無加授權,然后再測試
[[email protected] tmp]# mysql -u replication -p -h 172.30.100.24 –port 3309
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.56-MariaDB MariaDB Server
————解決———————–

測試沒問題,現在增加 多實例開機啟動腳本

vim /etc/rc.d/init.d/mumysql

#!/bin/bash
#add for chkconfig
#chkconfig: 2345 70 30
#description: mysql autostart
#processname: mumysql                  
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3307.cnf –log=/var/log/mysql/mysql_3307.log start
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3308.cnf –log=/var/log/mysql/mysql_3308.log start
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3309.cnf –log=/var/log/mysql/mysql_3309.log start
/usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3310.cnf –log=/var/log/mysql/mysql_3310.log start
#end

說明:
2345是指腳本的運行級別,即在2345這4種模式下都可以運行,234都是文本界面,5就是圖形界面X
70是指腳本將來的啟動順序號,如果別的程序的啟動順序號比70小(比如44、45),則腳本需要等這些程序都啟動以后才啟動。
30是指系統關閉時,腳本的停止順序號。

[[email protected] init.d]# chkconfig –add mumysql   在系統里增加開記機啟動
[[email protected] init.d]# chkconfig –list           查看開機啟動列表。
mumysql            0:off    1:off    2:on    3:on    4:on    5:on    6:off
netconsole         0:off    1:off    2:off    3:off    4:off    5:off    6:off
network            0:off    1:off    2:on    3:on    4:on    5:on    6:off

重啟進行測試 所有mysql進程起來即正常
[[email protected] script]# !net
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      2849/mysqld        
tcp        0      0 0.0.0.0:3308            0.0.0.0:*               LISTEN      2847/mysqld        
tcp        0      0 0.0.0.0:3309            0.0.0.0:*               LISTEN      2979/mysqld        
tcp        0      0 0.0.0.0:3310            0.0.0.0:*               LISTEN      3026/mysqld        
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1008/sshd          
tcp        0      0 127.0.0.1:25            0.0.0.0:*               LISTEN      2108/master        
tcp        0      0 127.0.0.1:199           0.0.0.0:*               LISTEN      1010/snmpd         
tcp6       0      0 :::22                   :::*                    LISTEN      1008/sshd          
tcp6       0      0 ::1:25                  :::*                    LISTEN      2108/master        
[[email protected] script]#

第二種方法

編輯  /etc/rc.d/rc.local文件 

格式為  程序名  程序路徑

例如  a.sh  /home/a.sh

——故障解決—–
很奇怪,雖然雙機都是backup模式,但優先級高的機器重啟后仍然可以拿到 master狀態

Sep 22 20:16:57 01 Keepalived_vrrp[3364]: VRRP sockpool: [ifindex(2), proto(112), unicast(0), fd(10,11)]
Sep 22 20:16:57 01 systemd: Started LVS and VRRP High Availability Monitor.
Sep 22 20:17:21 01 Keepalived_vrrp[3364]: VRRP_Instance(VI_1) Transition to MASTER STATE
Sep 22 20:17:22 01 Keepalived_vrrp[3364]: VRRP_Instance(VI_1) Entering MASTER STATE
Sep 22 20:17:22 01 Keepalived_vrrp[3364]: VRRP_Instance(VI_1) setting protocol VIPs.
Sep 22 20:17:22 01 Keepalived_vrrp[3364]: Sending gratuitous ARP on em1 for 172.30.100.26

 

1 IP沖

[[email protected] ~]# tail -f /var/log/messages
Sep 23 19:58:19 02 Keepalived_vrrp[5893]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs on em1 for 172.30.100.26
Sep 23 19:58:19 02 Keepalived_vrrp[5893]: Sending gratuitous ARP on em1 for 172.30.100.26
Sep 23 19:58:19 02 Keepalived_vrrp[5893]: Sending gratuitous ARP on em1 for 172.30.100.26
Sep 23 19:58:19 02 Keepalived_vrrp[5893]: Sending gratuitous ARP on em1 for 172.30.100.26
Sep 23 19:58:19 02 Keepalived_vrrp[5893]: Sending gratuitous ARP on em1 for 172.30.100.26

2 網卡進行混雜模式

確認原因是 開啟了tcpdump導致的

Sep 23 20:09:07 02 Keepalived_vrrp[5893]: VRRP_Instance(VI_1) Received advert with higher priority 100, ours 10
Sep 23 20:09:07 02 Keepalived_vrrp[5893]: VRRP_Instance(VI_1) Entering BACKUP STATE
Sep 23 20:09:07 02 Keepalived_vrrp[5893]: VRRP_Instance(VI_1) removing protocol VIPs.
Sep 23 20:09:18 02 kernel: device em1 entered promiscuous mode
Sep 23 20:09:21 02 kernel: device em1 left promiscuous mode
Sep 23 20:09:27 02 kernel: device em1 entered promiscuous mode
Sep 23 20:09:30 02 kernel: device em1 left promiscuous mode
Sep 23 20:09:35 02 kernel: device em1 entered promiscuous mode
Sep 23 20:09:45 02 kernel: device em1 left promiscuous mode
Sep 23 20:09:51 02 kernel: device em1 entered promiscuous mode
Sep 23 20:09:53 02 kernel: device em1 left promiscuous mode
Sep 23 20:09:56 02 kernel: device em1 entered promiscuous mode
Sep 23 20:09:57 02 kernel: device em1 left promiscuous mode
Sep 23 20:10:00 02 kernel: device em1 entered promiscuous mode

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