1.Saltstack自动化部署MySQL主从同步

1.1MySQL需求分析

①MySQL安装install.sls 初始化 ②配置文件my.cnf配置不同，server id 不同  🌂创建主从同步用户 ④master上获取binlog和pos值 🌫slave上，change master to && start slave ⑥检查主从状态

1.2目录结构设计

生产环境，最好创建一个通用的目录modules把统一及通用的模块状态存放的其目录下面，然后在其他地方用的时候include其模板

安装，配置（my.cnf）目录默认配置可以统一

[root@linux-node1 /srv/salt/prod/modules]# tree mysql/
mysql/
├── files
│   ├── mariadb-server-master.cnf
│   ├── mariadb-server-slave.cnf
│   └── my.cnf
├── install.sls
├── master.sls
└── slave.sls

1 directory, 6 files

1.3MySQL安装状态配置

#安装配置
[root@linux-node1 /srv/salt/prod/modules/mysql]# pwd
/srv/salt/prod/modules/mysql
[root@linux-node1 /srv/salt/prod/modules/mysql]# cat install.sls 
mysql-install:
  pkg.installed:
    - pkgs:
    - mariadb
    - mariadb-server

mysql-config:
  file.managed:
    - name: /etc/my.cnf
    - source: salt://modules/mysql/files/my.cnf
    - user: root
    - group: root
    - mode: 644
#MySQL的配置文件my.cnf
存放到此目录下：/srv/salt/prod/modules/mysql/files
https://www.xionghaier.cn/wp-content/uploads/2018/mysql/my.cnf

1.4MySQL主配置状态

#主配置状态
[root@linux-node1 /srv/salt/prod/modules/mysql]# vim master.sls 
include:
  - modules.mysql.install

master-config:
  file.managed:
    - name: /etc/my.cnf.d/mariadb-server.cnf
    - source: salt://modules/mysql/files/mariadb-server-master.cnf
    - user: root
    - group: root
    - mode: 644

master-service:
  service.running:
    - name: mariadb
    - enable: True
##MySQL主配置文件mariadb-server-master.cnf
存放到此目录下：/srv/salt/prod/modules/mysql/files
https://www.xionghaier.cn/wp-content/uploads/2018/mysql/mariadb-server-master.cnf

1.5MySQL从状态配置

#从配置状态
[root@linux-node1 /srv/salt/prod/modules/mysql]# vim slave.sls 
include:
  - modules.mysql.install

master-config:
  file.managed:
    - name: /etc/my.cnf.d/mariadb-server.cnf
    - source: salt://modules/mysql/files/mariadb-server-slave.cnf
    - user: root
    - group: root
    - mode: 644

master-service:
  service.running:
    - name: mariadb
    - enable: True
##MySQL主配置文件mariadb-server-slave.cnf
存放到此目录下：/srv/salt/prod/modules/mysql/files
https://www.xionghaier.cn/wp-content/uploads/2018/mysql/mariadb-server-slave.cnf

2.1创建主从同步账户及授权这种方式需要先配置才能实现

grant replication slave on *.* to 'xxx'@'xxxxx.%' identified by 'xxxxx';

官方文档：https://docs.saltstack.com/en/latest/ref/states/all/salt.states.mysql_user.html

[root@linux-node1 /srv/salt/prod/modules/mysql]# vim master.sls 
include:
  - modules.mysql.install

master-config:
  file.managed:
    - name: /etc/my.cnf.d/mariadb-server.cnf
    - source: salt://modules/mysql/files/mariadb-server-master.cnf
    - user: root
    - group: root
    - mode: 644

repl-user:   ##创建用户
  mysql_user.present:
    - name: repl_user
    - host: 118.190.201.0/255.255.255.0
    - password: repl_user

grant-user:  ##授权
  mysql_grants.present:
    - grant: replication slave
    - database: *.*
    - user: repl_user
    - host: 118.190.201.0/255.255.255.0

master-service:
  service.running:
    - name: mariadb
    - enable: True

###执行时出错
Comment: MySQL Error 1142: SELECT command denied to user 'salt'@'linux-node1' for table 'user'
###解决办法：断开minion连接MySQL
[root@linux-node1 ~]# vim /etc/salt/minion
#mysql.host: '118.190.201.11'
#mysql.user: 'salt'
#mysql.pass: 'salt'
#mysql.db: 'salt'

###执行状态
[root@linux-node1 ~]# salt 'linux-node1*' state.sls modules.mysql.master saltenv=prod
.......省略部分.......
Summary
------------
Succeeded: 6
Failed: 0
------------
Total states run: 6

2.2不使用MySQL的授权来授权下列使用cmd的方式来进行授权

#按业务进行分类
[root@linux-node1 prod]# pwd
/srv/salt/prod
[root@linux-node1 prod]# mkdir shop-user
[root@linux-node1 shop-user]# vim mysql-master.sls 
include:
  - modules.mysql.master

master-grant:
  cmd.run:
    - name: mysql -e "GRANT replication slave,super on *.* to 'repl_user'@'118.190.201.0/255.255.255.0' identified by 'repl_user@pass'"
    - unless: mysql -h 118.190.201.11 -u repl_user -prepl_user@pass -e "exit"

2.3从库获取pos状态位置

[root@linux-node1 shop-user]# vim mysql-slave.sls 
include:
  - modules.mysql.slave

slave-start:
  file.managed:
    - name: /tmp/start_slave.sh
    - source: salt://shop-user/files/start_slave.sh
    - user: root
    - group: root
    - mode: 755
  cmd.run:
    - name: /bin/bash /tmp/start_slave.sh
    - unless: test -f /etc/my.cnf.d/slave.lock
##start_slave.sh脚本内容
[root@linux-node1 files]# vim start_slave.sh 
#!/bin/bash
for i in `seq 1 10`;do
 mysql -h 118.190.201.11 -u repl_user -prepl_user@pass -e "exit"
 if [ $? -eq 0 ];then
 POS=$(mysql -h 118.190.201.11 -u repl_user -prepl_user@pass -e "show master status" | awk -F '|' 'NR==2 {print $1}' | awk '{print $2}')
 mysql -e "change master to master_host='118.190.201.11', master_user='repl_user', master_password='repl_user@pass', master_log_file='mysqlbin.000001'
, master_log_pos=$POS; start slave;"
 touch /etc/my.cnf.d/slave.lock
 exit;
 else
 sleep 60;
 fi
done

2.4编写top_file状态文件

[root@linux-node1 files]# vim /srv/salt/base/top.sls 
#base:
#  'os:CentOS':
#    - match: grain
#    - web.apache

prod:
  'linux-node1.example.com':
    - shop-user.mysql-master
  'linux-node2.example.com':
    - shop-user.mysql-slave

2.5执行状态测试

[root@linux-node1 ~]# salt '*' state.highstate
linux-node2.example.com:
----------
.......省略部分.......
Summary
------------
Succeeded: 6 (changed=1)
Failed: 0
------------
Total states run: 6
linux-node1.example.com:
----------
.......省略部分.......
Summary
------------
Succeeded: 6 (changed=1)
Failed: 0
------------
Total states run: 6

Acting as if nothing borne in mind is the best revenge.It’s all for myself to live better.若无其事，原来是最好的报复。生活得更好，是为了自己

AD：《熊孩子博客》熊孩子QQ:443060965