saltstack部署mysql主从

saltstack部署mysql主从

pillar变量文件目录

[root@master ~]# cd /srv/pillar/
[root@master pillar]# ls
base  prod
[root@master pillar]# tree prod/
prod/
├── haproxy.sls
├── mysql.sls
└── top.sls

0 directories, 3 files
[root@master pillar]# cat prod/top.sls 
prod:
  'node*':
     - mysql
     - haproxy
[root@master pillar]# cat prod/mysql.sls 
mysql_install_dir: /usr/local		#mysql安装目录
data_dir: /opt/data 				#mysql初始化存放目录
mysql_password: 123456				#mysql的root用户密码
mysql_cong_ip: 192.168.47.121		#mysql从主机ip
mysql_zhu_ip: 192.168.47.120		#mysql主主机ip

一、mysql安装

目录结构

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

1 directory, 5 files

状态文件

[root@master mysql]# cat install.sls 
ncurses-compat-libs:
  pkg.installed

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

{{ pillar['mysql_install_dir'] }}:
  archive.extracted:
    - source: salt://modules/database/mysql/files/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz
    - unless: test -d {{ pillar['mysql_install_dir'] }}/mysql-5.7.34-linux-glibc2.12-x86_64
  file.symlink:
    - name: {{ pillar['mysql_install_dir'] }}/mysql
    - target: {{ pillar['mysql_install_dir'] }}/mysql-5.7.34-linux-glibc2.12-x86_64 


{{ pillar['mysql_install_dir'] }}/mysql:
  file.directory:
    - user: mysql
    - group: mysql
    - mode: '0755'
    - recurse:
      - user
      - group

{{ pillar['data_dir'] }}:
  file.directory:
    - user: mysql
    - group: mysql
    - mode: '0755'
    - makedirs: true
    - recurse:
      - user
      - group 
mysql_path:
  file.managed:
    - name: /etc/profile.d/mysqld.sh 
    - source: salt://modules/database/mysql/files/mysqld.sh.j2
    - template: jinja

{{ pillar['mysql_install_dir'] }}/mysql/support-files/mysql.server:
  file.managed:
    - source: salt://modules/database/mysql/files/mysql.server.j2
    - template: jinja
    - user: mysql
    - group: mysql
    - mode: '0755'

/usr/lib/systemd/system/mysqld.service:
  file.managed:
    - source: salt://modules/database/mysql/files/mysqld.service.j2
    - template: jinja
    - user: root
    - group: root
    - mode: '0644'

mysql-initialize:
  cmd.run:
    - name: '{{ pillar['mysql_install_dir'] }}/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir={{ pillar['data_dir'] }}/' 
    - unless: test $(ls -l {{ pillar['data_dir'] }} |wc -l) -gt 1

mysql环境变量脚本

[root@master mysql]# cat files/mysqld.sh.j2
export PATH={{ pillar['mysql_install_dir'] }}/mysql/bin:$PATH

systemctl控制开机自启文件mysqld.service

[root@master mysql]# cat files/mysqld.service.j2 
[Unit]
Description=Start mysql
After=network.target 

[Service]
Type=forking
ExecStart={{ pillar['mysql_install_dir'] }}/mysql/support-files/mysql.server start
ExecStop={{ pillar['mysql_install_dir'] }}/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID 
[Install]
WantedBy=multi-user.target

控制开启文件

[root@master mysql]# cat files/mysql.server.j2
.......
 44 # overwritten by settings in the MySQL configuration files.
 45 
 46 basedir={{ pillar['mysql_install_dir'] }}/mysql      ##添加
 47 datadir={{ pillar['data_dir'] }}                     ##添加
 48 
 49 # Default value, in seconds, afterwhich the script should timeout waiting
 50 # for server start. 
......

二、mysql主主机配置

目录结构

[root@master ~]# cd /srv/salt/prod/mysql_zhucong/mysql_zhu/
[root@master mysql_zhu]# tree
.
├── files
│   └── my.cnf.j2
└── install.sls

1 directory, 3 files

状态文件

[root@master mysql_zhu]# cat install.sls 
include:
  - modules.database.mysql.install

copy-my_conf:
  file.managed:
    - name: /etc/my.cnf
    - source: salt://mysql_zhucong/mysql_zhu/files/my.cnf.j2
    - user: root
    - group: root
    - mode: '0644'
    - template: jinja

mysqld.service:
  service.running:
    - reload: true
    - enable: true
    - watch:
      - file: /etc/my.cnf

set-password:
  cmd.run:
    - name: {{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -e "set password = password('{{ pillar['mysql_password'] }}');"
    - unless: {{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -uroot -p{{ pillar['mysql_password'] }} -e 'exit'

create_mysql_user:
  cmd.run:
    - name: {{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -uroot -p{{ pillar['mysql_password'] }} -e "grant replication slave,super on *.* to 'tj'@'{{ pillar['mysql_cong_ip'] }}' identified by '1';" && {{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -uroot -p{{ pillar['mysql_password'] }} -e "flush privileges;"

mysql主 配置文件

[root@master mysql_zhu]# cat files/my.cnf.j2 
[mysqld]
port = 3306
datadir = {{ pillar['data_dir'] }}
basedir = {{ pillar['mysql_install_dir'] }}/mysql
socket = /tmp/mysql.sock
pid-file = {{ pillar['data_dir'] }}/mysql.pid
user = mysql
server-id = 1
log-bin = mysql_bin
skip-name-resolve

三、mysql从主机配置

目录结构

[root@master ~]# cd /srv/salt/prod/mysql_zhucong/mysql_cong/
[root@master mysql_cong]# tree 
.
├── files
│   ├── my.cnf.j2
│   └── mysql_cong.sh.j2
└── install.sls

1 directory, 3 files

状态文件

[root@master mysql_cong]# cat install.sls 
include:
  - modules.database.mysql.install

copy-my_conf:
  file.managed:
    - name: /etc/my.cnf
    - source: salt://mysql_zhucong/mysql_cong/files/my.cnf.j2
    - user: root
    - group: root
    - mode: '0644'
    - template: jinja

mysqld.service:
  service.running:
    - reload: true
    - enable: true
    - watch:
      - file: /etc/my.cnf

set-password:
  cmd.run:
    - name: {{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -e "set password = password('{{ pillar['mysql_password'] }}');"
    - unless: {{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -uroot -p{{ pillar['mysql_password'] }} -e 'exit'

statr_slave:
  file.managed:
    - name: /tmp/mysql_cong.sh
    - source: salt://mysql_zhucong/mysql_cong/files/mysql_cong.sh.j2
    - mode: '0755'
    - template: jinja
  cmd.run:
    - name: bash /tmp/mysql_cong.sh
    - unless: test $({{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -uroot -p{{ pillar['mysql_password'] }} -e 'show slave status\G' | grep '_Running' |awk '{print $2}' | grep -c 'Yes') -eq 2

mysql从主机配置文件

[root@master mysql_cong]# cat files/my.cnf.j2 
[mysqld]
port = 3306
datadir = {{ pillar['data_dir'] }}
basedir = {{ pillar['mysql_install_dir'] }}/mysql
socket = /tmp/mysql.sock
pid-file = {{ pillar['data_dir'] }}/mysql.pid
user = mysql
server-id = 2
log-bin = mysql_bin
skip-name-resolve

配置从库及开启slave脚本

[root@master mysql_cong]# cat files/mysql_cong.sh.j2 
#!/bin/bash
mysql_log_file=$({{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -utj -p1 -h{{ pillar['mysql_zhu_ip'] }} -e 'show master status;' | grep mysql_bin | awk 'NR==1 {print $1}')

mysql_log_pos=$({{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -utj -p1 -h{{ pillar['mysql_zhu_ip'] }} -e 'show master status;' | grep mysql_bin | awk 'NR==1 {print $2}')


{{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -uroot -p{{ pillar['mysql_password'] }} -e "change master to master_host='{{ pillar['mysql_zhu_ip'] }}', master_user='tj', master_password='1', master_log_file='$mysql_log_file', master_log_pos=$mysql_log_pos;"


{{ pillar['mysql_install_dir'] }}/mysql/bin/mysql -uroot -p{{ pillar['mysql_password'] }} -e "start slave;"