MySQL-Ansible-Redis打怪升级之路二

1.MyCAT中间件服务实现读写分离

思路：1.安装部署MyCat（java\mycat）  2.配置主从，开启general_log  3.修改mycat的server.xml连接信息 4.修改mycat的schema.xml实现读写分离   5.测试写连接，测试读连接

1.mycat服务器
[root@node25 conf]# yum -y install java mariadb
[root@node25 ~]# java -version
openjdk version "1.8.0_302"
OpenJDK Runtime Environment (build 1.8.0_302-b08)
OpenJDK 64-Bit Server VM (build 25.302-b08, mixed mode)
[root@node25 ~]# tar xvf Mycat-server-1.6.7.6-release-20210831141727-linux.tar.gz -C /apps/    

##配置环境变量
[root@node25 ~]# echo 'PATH=/apps/mycat/bin:$PATH' >/etc/profile.d/mycat.sh   
[root@node25 ~]# source /etc/profile.d/mycat.sh  
[root@node25 ~]# mycat start

[root@node25 ~]# cat /apps/mycat/logs/wrapper.log 
STATUS | wrapper  | 2021/09/01 10:02:53 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/09/01 10:02:53 | Launching a JVM...
INFO   | jvm 1    | 2021/09/01 10:02:54 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/09/01 10:02:54 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/09/01 10:02:54 | 
INFO   | jvm 1    | 2021/09/01 10:02:54 | MyCAT Server startup successfully. see logs in logs/mycat.log


2.配置主从
##【master】
[root@master22 ~]# cat /etc/my.cnf
[mysqld]
server-id=22
log-bin=/data/log/mysql-bin
general_log=ON

MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'192.168.149.%' identified by 'replpass';
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      342 |              |                  |
+------------------+----------+--------------+------------------+

##【slave】
[root@slave23 ~]# cat /etc/my.cnf
[mysqld]
server_id=23
read_only=ON
relay_log=relay-log
log-bin
general_log=ON

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.149.22',
MariaDB [(none)]> MASTER_USER='repluser',
MariaDB [(none)]> MASTER_PASSWORD='replpass',
MariaDB [(none)]> MASTER_LOG_FILE='mysql-bin.000006',
MariaDB [(none)]> MASTER_LOG_POS=342;

MariaDB [(none)]> start slave;

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.149.22
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 342
               Relay_Log_File: relay-log.000007
                Relay_Log_Pos: 641
        Relay_Master_Log_File: mysql-bin.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 342
              Relay_Log_Space: 1243
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 22
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it



3.mycat服务器修改Server.xml文件配置mycat连接信息
[root@node25 ~]# vi /apps/mycat/conf/server.xml
<user name="yyy" defaultAccount="true">                       ##连接mycat用户名
                <property name="password">123456</property>   ##密码
                <property name="schemas">TESTDB</property>    ##数据库名要和schema.xml对应
                <property name="defaultSchema">TESTDB</property>
        </user>

4.mycat修改schema.xml实现读写分离策略
[root@node25 ~]# vi /apps/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
      <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1" dataNode="dn1"></schema>                        
      <dataNode name="dn1" dataHost="localhost1" database="db2" />   ##指定操作的数据库或表
      <dataHost name="localhost1" maxCon="1000" minCon="10"
         balance="1"                                      ##balance为1表示设置为读写分离
      writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
   <heartbeat>select user()</heartbeat>
<writeHost host="host1" url="192.168.149.22:3306" user="yyy" password="123456"> #写服务器
<readHost host="host2" url="192.168.149.23:3306" user="yyy" password="123456"/> #读服务器
                </writeHost>
                </dataHost>
</mycat:schema>

##重启mycat
[root@node25 ~]# mycat restart
[root@node25 ~]# tail /apps/mycat/logs/wrapper.log 
STATUS | wrapper  | 2021/09/01 10:06:13 | TERM trapped.  Shutting down.
STATUS | wrapper  | 2021/09/01 10:06:15 | <-- Wrapper Stopped
STATUS | wrapper  | 2021/09/01 10:06:16 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/09/01 10:06:16 | Launching a JVM...
INFO   | jvm 1    | 2021/09/01 10:06:16 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/09/01 10:06:16 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/09/01 10:06:16 | 
INFO   | jvm 1    | 2021/09/01 10:06:17 | MyCAT Server startup successfully. see logs in logs/mycat.log

5.mycat连接后端数据库测试读写分离
[root@node25 ~]#mysql -uyyy -p123456 -h127.0.0.1 -P8066 -DTESTDB
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.29-mycat-1.6.7.6-release-20210831141727 MyCat Server (OpenCloudDB)

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

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

MySQL [TESTDB]> 
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          23 |
+-------------+
1 row in set (0.00 sec)

MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave23    |
+------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> create table y2(id int);  
MySQL [TESTDB]> insert into y2 values(3);   ##写操作
Query OK, 1 row affected (0.01 sec)
MySQL [TESTDB]> select * from y2;            ##读操作
+------+
| id   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)    

##在master上看general_log   ##写分离
210901 12:11:45    10 Query     select user()  ##mycat的heartbeat检测通过此命令测试
210901 12:11:55    16 Query     select user()
210901 12:12:05    17 Query     select user()
210901 12:12:12    12 Query     SET names utf8;insert into y2 values(3)   ##可以看见写操作
210901 12:12:15    19 Query     select user()
210901 12:12:25    18 Query     select user()
210901 12:12:35    14 Query     select user()
##在slave上看general_log    ##读分离
210901 12:12:25    16 Query     select user()
210901 12:12:34    20 Query     select * from y2         ##看见读操作
210901 12:12:35    17 Query     select user()

2.ansible常用模块总结

• copy：拷贝文件至被控远程主机上；参数：backup=yes 备份原文件，默认会覆盖文件；注意拷贝时目录下的文件，还是目录本身
• fetch：从被控远程主机拉取文件，目前不支持目录，可现将目录打包文件archive后再拉取；
• cron：设置计划任务；参数：job定时任务内容，disabled=yes禁用计划任务，state=absent删除计划任务；例子：ansible websrvs -m cron -a "minute=*/5 job='/usr/sbin/ntpdate ntp.aliyun.com  &>/dev/null' name=Synctime
• service：服务；参数：state=started\stopped\reloaded\restarted\ ；注意状态时带ed的
• unarchive：解包解压缩；主要两种用法：1.master主控端压缩包传至被控端再解压缩 remote_src=no，即若是主控端的包需带这参数；2.远程主机解压缩包remote_src=yes
• archive：打包压缩；参数：format=gz 等
• file：设置文件属性；参数：state=touch/directory/link/absent 
• lineinfile：相当于sed，仅生效替换单行内容，默认ansible遇到特殊字符替换时，需要用到lineinfile或replace；例子:ansible all -m lineinfile -a "path=/etc/selinux/config       regexp='^SELINUX='   line='SELINUX=disabled'" ；注意：多行则匹配最后一行
• replace：相当于sed，但能替换多行内容，更常用；例子：ansible all -m replace -a "path=/etc/fstab regexp='^(UUID.*)' replace='#\1'"
• setup：setup 模块来收集主机的系统信息，这些 facts 信息可以直接以变量的形式使用，但是如果主机较多，会影响执行速度，可以使用 gather_facts: no 来禁止 Ansible 收集 facts 信息；例子：ansible all -m setup -a "filter=ansible_distribution_version"

3.ansible-playbook批量安装nginx，按主机名提供不同的index.html,实现nginx角色

##nginx角色目录结构如下
[root@master22 roles]# tree nginx/
nginx/
├── files
├── handlers
│   └── main.yml
├── tasks
│   ├── config.yml
│   ├── group.yml
│   ├── index.yml
│   ├── install.yml
│   ├── main.yml
│   ├── service.yml
│   └── user.yml
└── templates
    ├── index.html.j2
    ├── nginx7.conf.j2
    └── nginx8.conf.j2

4 directories, 11 files


[root@master22 roles]# mkdir -pv /etc/ansible/roles/nginx/{vars,tasks,templates,handlers}
mkdir: created directory ‘/etc/ansible/roles/nginx’
mkdir: created directory ‘/etc/ansible/roles/nginx/tasks’
mkdir: created directory ‘/etc/ansible/roles/nginx/templates’
mkdir: created directory ‘/etc/ansible/roles/nginx/handlers’

##  playbook中调用nginx角色
 
[root@master22 nginx]# cat /etc/ansible/role_nginx.yml 
- hosts: slave
  remote_user: root
  gather_facts: yes

  roles:
    - nginx



## tasks

[root@master22 nginx]# cat tasks/main.yml 
- include: group.yml
- include: user.yml
- include: install.yml
- include: config.yml
- include: index.yml
- include: service.yml

[root@master22 nginx]# cat tasks/group.yml 
- name: create nginx group
  group: name=nginx system=yes gid=80

[root@master22 nginx]# cat tasks/user.yml 
- name: create nginx user
  user: name=nginx system=yes shell=/sbin/nologin uid=80 group=nginx

[root@master22 nginx]# cat tasks/install.yml 
- name: install
  yum: name=nginx

root@master22 nginx]# cat tasks/install.yml 
- name: install
  yum: name=nginx

[root@master22 nginx]# cat tasks/config.yml 
- name: config file for centos7
  template: src=nginx7.conf.j2 dest=/etc/nginx/nginx.conf
  when: ansible_distribution_major_version=='7'
  notify: restart nginx
- name: config file for centos8
  template: src=nginx8.conf.j2 dest=/etc/nginx/nginx.conf
  when: ansible_distribution_major_version=='8'
  notify: restart nginx

[root@master22 nginx]# cat tasks/index.yml 
- name: index.html
  template: src=roles/nginx/templates/index.html.j2 dest=/usr/share/nginx/html/index.html

[root@master22 nginx]# cat tasks/service.yml 
- name: start service
  service: name=nginx state=started enabled=yes


## handlers

[root@master22 nginx]# cat handlers/main.yml 
- name: restart nginx
  service: name=nginx state=restarted

##templates
[root@master22 nginx]# cat templates/index.html.j2 
<title>Welcome to {{ ansible_hostname }}</title>
<h1>Welcome to {{ ansible_nodename }}</h1>

[root@master22 nginx]# cat templates/nginx7.conf.j2 
....
user nginx;
worker_processes {{ ansible_processor_vcpus+2 }};
error_log /var/log/nginx/error.log;
pid /run/nginx.pid;
....

[root@master22 nginx]# cat templates/nginx7.conf.j2 
user nginx;
worker_processes {{ ansible_processor_vcpus+3 }};
error_log /var/log/nginx/error.log;
pid /run/nginx.pid;
...
  server {
        listen       8088;
        listen       [::]:80;
        server_name  _;
        root         /usr/share/nginx/html;

        # Load configuration files for the default server block.
        include /etc/nginx/default.d/*.conf;

        error_page 404 /404.html;
        location = /404.html {
        }

        error_page 500 502 503 504 /50x.html;
        location = /50x.html {
        }
    }
...

访问各机器index.html

4.编译安装Redis-脚本

思路：1.安装环境依赖包 2.配置环境变量，编译安装到指定路径  3.生成data、log、pid和etc目录  4.修改配置redis.conf（bind、dir、logfile、pidfile）  5.修改内核参数及优化--tcp-backlog/vm.overcommit_memory/transparent hugepage        
6.编辑redis启动服务文件


## 自动安装redis脚本
[root@master22 ~]# cat redis_install.sh 
#!/bin/bash

. /etc/init.d/functions   ##load variables or function from this, such as  "action"
VERSION=redis-6.2.1
PASSWORD=shaodeng
INSTALL_DIR=/app/redis

Install_redis(){

yum -y install gcc jemaoc-devel || { action "Failed to install,please check network" false; exit;   }

wget http://download.redis.io/releases/${VERSION}.tar.gz || { action "binary of Redis packages install faied " false; exit;  }

tar xf ${VERSION}.tar.gz
cd ${VERSION}
make -j 4 PREFIX=${INSTALL_DIR} install &&  action "Redis install completed!" || { action "Redis install failed " false ; exit;  }

#env variable
ln -s ${INSTALL_DIR}/bin/redis-* /usr/bin
mkdir -p ${INSTALL_DIR}/{etc,log,data,pid}
cp redis.conf ${INSTALL_DIR}/etc/redis.conf        ##mv default redis conf to $INSTALL_DIR/etc/ 

## change config directory path 
sed -i -e 's/bind 127.0.0.1/bind 0.0.0.0/' -e "/# requirepass/a requirepass $PASSWORD" -e "/^dir .*/c dir ${INSTALL_DIR}/data/" -e "/^logfile .*/c logfile ${INSTALL_DIR}/log/redis-6379.log" -e "/^pidfile .*/c pidfile ${INSTALL_DIR}/pid/redis_6379.pid" ${INSTALL_DIR}/etc/redis.conf 

if id redis &> /dev/null ;then
   action "Redis user is existed" false
else
   useradd -r -s /sbin/nologin redis
   action "Redis useradd success"
fi

#settiong permission  file and directory
chown -R redis.redis ${INSTALL_DIR}

## optimize kernel parameter------solve the warning of redis starting
cat >> /etc/sysctl.conf <<EOF
net.core.somaxconn = 1024
vm.overcommit_memory = 1
EOF

sysctl -p

## add some boot scripts  ----solve the warning of redis starting 
echo 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' >> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local
/etc/rc.d/rc.local

##setting service config of redis 
cat > /usr/lib/systemd/system/redis.service <<EOF
[Unit]
Description=Redis persistent key-value database
After=network.target
After=network-online.target

[Service]
ExecStart=${INSTALL_DIR}/bin/redis-server  ${INSTALL_DIR}/etc/redis.conf --supervised systemd
ExecStop=/bin/kill -s QUIT \$MAINPID
#Type=notify
User=redis
Group=redis
RuntimeDirectory=redis
RuntimeDirectoryMode=0755

[Install]
WantedBy=multi-user.target

EOF
systemctl daemon-reload
systemctl enable --now redis &> /dev/null && action "Redis service start succeed, Redis info:" || { action "redis started failed" false ; exit;  }

redis-cli -a $PASSWORD INFO Server 2> /dev/null

}

Install_redis

 ##执行安装脚本
[root@master22 ~]# ./redis_install.sh 
Hint: It's a good idea to run 'make test' ;)

    INSTALL install
    INSTALL install
    INSTALL install
make[1]: Leaving directory `/root/redis-6.2.1/src'
Redis install completed!                                   [  OK  ]
Redis user is existed                                      [  OK  ]
net.core.somaxconn = 1024
vm.overcommit_memory = 1
net.core.somaxconn = 1024
vm.overcommit_memory = 1
net.core.somaxconn = 1024
vm.overcommit_memory = 1
net.core.somaxconn = 1024
vm.overcommit_memory = 1
net.core.somaxconn = 1024
vm.overcommit_memory = 1
Redis service start succeed, Redis info:                   [  OK  ]
# Server
redis_version:6.2.1
redis_git_sha1:00000000
redis_git_dirty:0
redis_build_id:4db4860a0c5700aa
redis_mode:standalone
os:Linux 3.10.0-1160.el7.x86_64 x86_64
arch_bits:64
multiplexing_api:epoll
atomicvar_api:atomic-builtin
gcc_version:4.8.5
process_id:20547
process_supervised:no
run_id:6c70680d1830f4efca4c833fe10ff19579a6e5a0
tcp_port:6379
server_time_usec:1630921219270676
uptime_in_seconds:0
uptime_in_days:0
hz:10
configured_hz:10
lru_clock:3531267
executable:/app/redis/bin/redis-server
config_file:/app/redis/etc/redis.conf
io_threads_active:0


##安装后的结构目录
[root@master22 ~]# tree /app/
/app/
└── redis
    ├── bin
    │   ├── redis-benchmark
    │   ├── redis-check-aof -> redis-server
    │   ├── redis-check-rdb -> redis-server
    │   ├── redis-cli
    │   ├── redis-sentinel -> redis-server
    │   └── redis-server
    ├── data
    ├── etc
    │   └── redis.conf
    ├── log
    │   └── redis-6379.log
    └── pid
        └── redis_6379.pid

6 directories, 9 files