saltstack部署mysql主从

1. 环境说明

需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作，另外需要一台作为控制机

数据库角色	IP	应用与系统版本	有无数据
控制机	192.168.8.129	rhel8.2 salt-master salt-minion
主数据库（node1）	192.168.8.130（master）	rhel-8.2 mysql-5.7.34 salt-minion	无
从数据库（node2）	192.168.8.137（slave）	rhel-8.2 mysql-5.7.34 salt-minion	无

2. 目录结构

//创建目录并设置变量
[root@master srv]# cd /srv/pillar/
[root@master pillar]# ls
base  prod
[root@master pillar]# cd prod/
[root@master prod]# ls
mysql.sls  nginx.sls  php.sls  top.sls
[root@master prod]# cat mysql.sls 
datadir: /opt/data
mysql_install_dir: /usr/local
mysql_port: 3306
[root@master prod]# cat top.sls 
prod:
  'node*':
    - nginx
    - php
    - mysql
[root@master prod]# 

[root@master ~]# cd /srv/salt/prod/modules/database/
[root@master database]# tree
.
└── mysql
    ├── files
    │   ├── install.sh.j2
    │   ├── master.cnf
    │   ├── my.cnf.j2
    │   ├── mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
    │   ├── mysqld.service
    │   ├── mysql.server
    │   └── slave.cnf
    ├── install.sls
    ├── master.sls
    └── slave.sls

2 directories, 10 files
[root@master database]# 

3. 编写状态文件

//编写mysql安装
[root@master database]# cd mysql/
[root@master mysql]# ls
files  install.sls  master.sls  slave.sls
[root@master mysql]# cat install.sls 
{% if grains['osmajorrelease'] == 8 %}
ncurses-compat-libs:
  pkg.installed
{% endif %}

create-mysql-user:
  user.present:
    - name: mysql
    - system: true
    - createhome: false
    - shell: /sbin/nologin

create-datadir:
  file.directory:
    - name: {{ pillar['datadir'] }}
    - user: mysql
    - group: mysql
    - mode: '0755'
    - makedirs: true

/etc/my.cnf.d:
  file.directory:
    - user: root
    - group: root
    - mode: '0755'
    - makedirs: true

/usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz:
  file.managed:
    - source: salt://modules/database/mysql/files/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
    - user: root
    - group: root
    - mode: '0644'

mysql-install:
  cmd.script:
    - name: salt://modules/database/mysql/files/install.sh.j2
    - template: jinja
    - unless: test  $(ls -l {{ pillar['datadir'] }} |wc -l) -gt 1

trasfer-files:
  file.managed:
    - names:
      - /etc/my.cnf:
        - source: salt://modules/database/mysql/files/my.cnf.j2
        - template: jinja
      - {{ pillar['mysql_install_dir'] }}/mysql/support-files/mysql.server:
        - source: salt://modules/database/mysql/files/mysql.server
      - /usr/lib/systemd/system/mysqld.service:
        - source: salt://modules/database/mysql/files/mysqld.service
    - require:
      - cmd: mysql-install 

mysqld.service:
  service.running:
    - enable: true
[root@master mysql]# 

[root@master mysql]# cd files/
[root@master files]# ls
install.sh.j2  my.cnf.j2                                   mysqld.service  slave.cnf
master.cnf     mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  mysql.server
[root@master files]# cat install.sh.j2 
#!/bin/bash

cd /usr/src
tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C {{ pillar['mysql_install_dir'] }}
ln -s {{ pillar['mysql_install_dir'] }}/mysql-5.7.34-linux-glibc2.12-x86_64 {{ pillar['mysql_install_dir'] }}/mysql

chown -R mysql.mysql {{ pillar['mysql_install_dir'] }}/mysql*

{{ pillar['mysql_install_dir'] }}/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir={{ pillar['datadir'] }}

echo 'export PATH={{ pillar["mysql_install_dir"] }}/mysql/bin:$PATH' > /etc/profile.d/mysqld.sh
[root@master files]# cat mysqld.service 
[Unit]
Description=mysql server daemon
After=network.target

[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID

[Install]
WantedBy=multi-user.target

[root@master files]# vim mysql.server 
 46 basedir=/usr/local/mysql
 47 datadir=/opt/data

[root@master files]# cat my.cnf.j2 
[mysqld]
basedir = {{ pillar['mysql_install_dir'] }}/mysql
datadir = {{ pillar['datadir'] }}
socket = /tmp/mysql.sock
port = {{ pillar['mysql_port'] }}
pid-file = {{ pillar['datadir'] }}/mysql.pid
user = mysql
skip-name-resolve

!includedir /etc/my.cnf.d

//编写mysql主的状态
[root@master mysql]# cat master.sls 
config_master.cnf:
  file.managed:
    - names:
      - /etc/my.cnf.d/master.cnf:
        - source: salt://modules/database/mysql/files/master.cnf
        - user: root
        - group: root
        - mode: '0644'

master-stop-mysql:
  service.dead:
    - name: mysqld.service

master-start-mysql:
  service.running:
    - name: mysqld.service
[root@master mysql]# 

[root@master mysql]# cd files/
[root@master files]# cat master.cnf 
[mysqld]
server-id = 10
log-bin = mysql_bin
[root@master files]# 

//编写mysql从的状态
[root@master mysql]# cat slave.sls 
include:
  - modules.database.mysql.install

/etc/my.cnf.d/slave.cnf:
  file.managed:
    - source: salt://modules/database/mysql/files/slave.cnf
    - user: root
    - group: root
    - mode: '0644'

slave-stop-mysql:
  service.dead:
    - name: mysqld.service

slave-start-mysql:
  service.running:
    - name: mysqld.service

[root@master mysql]# 

[root@master mysql]# cd files/
[root@master files]# ls
install.sh.j2  my.cnf.j2                                   mysqld.service  slave.cnf
master.cnf     mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  mysql.server
[root@master files]# cat slave.cnf 
[mysqld]
server-id = 20
relay-log = myrelay
[root@master files]# 

4. 创建项目

[root@master prod]# ls
bbs  modules
[root@master prod]# cd bbs/
[root@master bbs]# ls
files  grant.sls  master.sls  slave.sls
[root@master bbs]# cat master.sls 
include:
  - modules.database.mysql.install
  - bbs.grant
  - modules.database.mysql.master
[root@master bbs]# cat slave.sls 
include:
  - modules.database.mysql.slave

config-mysql-slave:
  cmd.script:
    - name: salt://bbs/files/start_slave.sh.j2
    - template: jinja
[root@master bbs]# cat grant.sls 
grant-slave:
  cmd.run:
    - name: {{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -e "grant replication slave,super on *.* to repl@'192.168.8.137' identified by '1';flush privileges;"

[root@master bbs]# cd files/
[root@master files]# ls
start_slave.sh.j2
[root@master files]# cat start_slave.sh.j2 
#!/bin/bash

mysql={{ pillar['mysql_install_dir'] }}/mysql/bin/mysql
master_ip=192.168.8.130   
repl_user=repl
repl_pass=1

log_file=$($mysql -u$repl_user -p$repl_pass -h$master_ip -e "show master status"|grep "mysql_bin"|awk '{print $1}')

log_pos=$($mysql -u$repl_user -p$repl_pass -h$master_ip -e "show master status"|grep "mysql_bin"|awk '{print $2}')

$mysql -e "
CHANGE MASTER TO
    MASTER_HOST='$master_ip',
    MASTER_USER='$repl_user',
    MASTER_PASSWORD='$repl_pass',
    MASTER_LOG_FILE='$log_file',
    MASTER_LOG_POS=$log_pos;
    start slave;
"

sleep 3
$mysql -e "show slave status\G"
[root@master files]# 

5. 验证效果

[root@master mysql]# salt node1 state.sls bbs.master saltenv=prod
[root@master mysql]# salt node2 state.sls bbs.slave saltenv=prod

//node2主机上操作
[root@node2 ~]# bash
[root@node2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: myrelay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000001
             Slave_IO_Running: Yes   //此处必须为yes
            Slave_SQL_Running: Yes   //此处必须为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: 154
              Relay_Log_Space: 519
              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: 10
                  Master_UUID: d934a217-4ccd-11ec-a5b6-000c29297f87
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

6. 码云代码仓库 mysql主从链接

mysql主从：https://gitee.com/pengyudong/linux.git