mariadb 双主+keepalived 配置示例

来源:本站原创 Linux 超过67 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主主同步

[root@master ~]# 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 test@’%’ 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

[root@master ~]# vi /etc/keepalived/keepalived.conf
! Configuration File forkeepalived
global_defs {
notification_email {
test@sina.com
}
notification_email_from  admin@test.com
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 #重试间隔时间
  }
}

 
 

[root@master ~]# vi /usr/local/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
[root@master ~]# chmod +x /usr/local/keepalived/mysql.sh
[root@master ~]# /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 {
#     acassen@firewall.loc
#     failover@firewall.loc
#     sysadmin@firewall.loc
   }
#   notification_email_from Alexandre.Cassen@firewall.loc
#   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 {
#     acassen@firewall.loc
#     failover@firewall.loc
#     sysadmin@firewall.loc
   }
#   notification_email_from Alexandre.Cassen@firewall.loc
#   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服务器故障恢复后,是否主动抢占资源,成为活动服务器。

[root@mysql01 ~]# systemctl stop mariadb
[root@mysql01 ~]# date
Thu May 18 13:33:40 CST 2017
[root@mysql01 ~]# date
Thu May 18 13:33:48 CST 2017
[root@mysql01 ~]#

备机日志
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

                          

看备机日志
[root@mysql02 ~]# 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

另一种方式
[root@shtsmysql02 mysqldata]# mkdir -p 33{21..24}
[root@shtsmysql02 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/

[root@mysql01 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
[root@mysql01 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

[root@mysql01 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
[root@mysql01 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
##指定为主服务器

 

————–新问题 配置完成后无法启动—————-
[root@shtsmysql01 3322]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg3321.cnf –log=/var/log/mysql/mysql_3321.log start
[root@shtsmysql01 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 
[root@shtsmysql01 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  root@localhost 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

用以上命令出现无法停机故障 查看日志发现权限不够
[root@shjhmysql02 ~]# 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  mysql@localhost 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/mysqld@.service

[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 mysqld@2.service
Warning: mysqld@2.service 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

状态
[root@mysql01 mysql]# systemctl status mysqld@2.service
● mysqld@2.service – MySQL Multi Server for instance 2
   Loaded: loaded (/etc/systemd/system/mysqld@.service; 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/mysqld@2.service
           ├─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.

停机测试

[root@mysql01 mysql]# systemctl stop mysqld@2.service
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    
[root@mysql01 mysql]# systemctl status mysqld@2.service
● mysqld@2.service – MySQL Multi Server for instance 2
   Loaded: loaded (/etc/systemd/system/mysqld@.service; 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]: mysqld@2.service 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.
[root@mysql01 mysql]#

 

——————————————————————————
上面的方式能启能停但无法管理,且不生成 mysql.socket 和mysql.pid,
从最后的结果来看应该也是mysql这个帐户的权限问题修正后即可。
加入权限后已可管理。
[root@mysql01 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

上面的方式能启能停但无法管理

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

——————-有时会碰到无法管理——————-
[root@shtsmysql01 ~]# mysql -S /tmp/mysql3307.sock
ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: NO)
[root@shtsmysql01 ~]#
提示ROOT无权限

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

原因是
[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.

解决方法和上面也是一样的

 

—————又有新问题 能启动不能停服务—————-
[root@shtsmysql01 mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/my07.cnf  stop
[root@shtsmysql01 mysql]#
[root@shtsmysql01 mysql]#
[root@shtsmysql01 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       

解决方法,开启日志大法
[root@shtsmysql01 mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/my07.cnf  –log=/var/log/mysql/mysql_multi.log stop
查看相应日志
[root@shtsmysql01 ~]# 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 mysql@’localhost’ identified by ‘mysql’;
flush privileges;

MariaDB [mysql]> use mysql;
Database changed
MariaDB [mysql]> grant shutdown on *.* to mysql@’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服务
[root@shtsmysql01 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       

[root@shtsmysql01 ~]# 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  —–停止信息

[root@shtsmysql01 ~]#

———————多实例配置完成———————–
配置两台Mysql主主同步 参考上面。

———————以下为不可管理的故障说明———————-

[root@mysql01 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

但手动运行却有
[root@mysql01 mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_4.cnf start
[root@mysql01 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

手动运行却无法停止——最后证明是权限问题
[root@mysql01 mysql]# /usr/bin/mysqld_multi –defaults-extra-file=/etc/mysql/cfg_4.cnf stop
[root@mysql01 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          
[root@mysql01 mysql]#

使用mysqladmin shutdown 可以停止
[root@mysql01 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    mysql@localhost as anonymous on
            1 Connect    Access denied for user ‘mysql’@’localhost’ (using password: YES)

查日志确认是权限问题
MariaDB [mysql]> grant shutdown on *.* to mysql@’localhost’ identified by ‘mysql’;
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

再试一下,看结果搞定
[root@mysql01 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          
[root@mysql01 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服务。顺序不能乱。因为

[root@shtsmysql01 ~]# 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.jdccie.com/ 版权所有。本站文章除注明出处外,皆为作者原创文章,可自由引用,但请注明来源。 禁止全文转载。
本文链接:http://www.jdccie.com/?p=3597转载请注明转自CCIE那点事
如果喜欢:点此订阅本站
  • 相关文章
  • 为您推荐
  • 各种观点

暂时还木有人评论,坐等沙发!
发表评论

您必须 [ 登录 ] 才能发表留言!