mysql 生产环境多实例配置

来源:本站原创 Linux 超过10 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  mysql@localhost identified by ‘mysql’;
flush privileges;

mysql -S /tmp/mysql3307.sock 
grant shutdown on *.* to  mysql@localhost 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 nbjpmysql@"172.%.%.%";
+—————————————————————————————————————————+
| Grants for nbjpmysql@172.%.%.%                                                                                            |
+—————————————————————————————————————————+
| GRANT ALL PRIVILEGES ON *.* TO ‘nbjpmysql’@’172.%.%.%’ IDENTIFIED BY PASSWORD ‘*2287’ |
+—————————————————————————————————————————+
1 row in set (0.00 sec)

MySql: 查看当前登录用户,当前数据库
mysql> select user();
+—————-+
| user() |
+—————-+
| root@localhost |
+—————-+
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 ‘replication@172.30.100.24:3309’ – retry-time: 60  retries: 86400  message: Access denied for user ‘replication’@’172.30.100.25’ (using password: YES)

解决方法
无加授权,然后再测试
[root@02 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是指系统关闭时,脚本的停止顺序号。

[root@01 init.d]# chkconfig –add mumysql   在系统里增加开记机启动
[root@01 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进程起来即正常
[root@01 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        
[root@01 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冲

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