saltstack部署MySQL主从

最新推荐文章于 2025-07-26 18:40:52 发布

NeaWalke

最新推荐文章于 2025-07-26 18:40:52 发布

阅读量567

点赞数

CC 4.0 BY-SA版权

文章标签： saltstack mysql

本文链接：https://blog.youkuaiyun.com/NeaWalke/article/details/121418249

本文介绍使用SaltStack自动化工具部署MySQL主从复制的过程。包括安装配置MySQL数据库、设置主从复制所需的账户权限、配置主从同步参数等关键步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

saltstack部署MySQL主从

主机	ip	服务
salt-master	192.168.8.130	salt-master
node1	192.168.8.131	mysql（master） salt-minion
node2	192.168.8.132	mysql（slave） salt-minion

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

修改mysql主库的minion文件

[root@node1 ~]# vim /etc/salt/minion
mysql.host: 'localhost'
mysql.port: 3306
mysql.user: 'root'
mysql.pass: '123'
mysql.db: 'mysql'
mysql.unix_socket: '/tmp/mysql.sock'
mysql.charset: 'utf8'

[root@node1 ~]# systemctl restart salt-minion.service

mysql模块详情

部署mysql主库

安装mysql数据库

[root@master database]# cat master/mysql.sls 
{% if grains['osmajorrelease'] == 8%}
mysql-dep-package:
  pkg.installed:
    - name: ncurses-compat-libs
{% endif %}

{% if grains['osmajorrelease'] == 7%}
libaio-devel:
  pkg.installed
{% endif %}

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

/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


/opt/data:
  file.directory:
    - user: mysql
    - group: mysql
    - mode: '755'

install-mysql:
  cmd.script:
    - name: salt://modules/database/mysql/files/install.sh
    - unless: test -d /usr/local/mysql
    - template: jinja

copy-mysql-soft:
  file.managed:
    - names:
      - /usr/lib/systemd/system/mysqld.service:
        - source: salt://modules/database/mysql/files/mysqld.service
        - user: root
        - group: root
        - mode: 644
        - template: jinja
      - {{ pillar['mysql_dir'] }}/support-files/mysql.server:
        - source: salt://modules/database/mysql/files/mysql.server
        - user: mysql
        - group: mysql
        - mode: 755
        - template: jinja
      - /etc/my.cnf:
        - source: salt://modules/database/mysql/files/my.cnf
        - user: root
        - group: root
        - mode: 644
        - template: jinja
    - require:
      - cmd: install-mysql

创建同步账户并授权

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

master-mysql.service:
  service.running:
    - name: mysqld
    - enable: true
    - reload: true
    - require:
      - cmd: install-mysql

master-set-mysql-passwd:
  cmd.run:
    - name: {{ pillar['mysql_dir'] }}/bin/mysql -e "set password=password('{{ pillar['master-mysql-passwd'] }}');"
    - require:
      - service: master-mysql.service
    - unless: {{ pillar['mysql_dir'] }}/bin/mysql -uroot -p{{ pillar['master-mysql-passwd'] }} -e "exit"

master-slave-dep-packages:
  pkg.installed:
    - pkgs:
      - python3-PyMySQL

create-mysqluser:
  mysql_user.present:
    - name: repl
    - host: 192.168.8.0/255.255.255.0
    - password: {{ pillar['master-user-pass'] }}


grant-mysql:
  mysql_grants.present:
    - grant: replication slave,super
    - database: '*.*'
    - user: repl
    - host: 192.168.8.0/255.255.255.0

update-masterfile:
  file.append:
    - name: /etc/my.cnf
    - text: |
        log-bin = mysql_bin
        server-id = 10
    - require:
      - mysql_grants: grant-mysql

stop-master-mysql:
  service.dead:
    - name: mysqld
    - require:
      - file: update-masterfile

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

配置文件:

[root@master database]# cat master/files/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin = mysql_bin
server-id = 10

部署mysql从库

安装数据库

[root@master sub]# vim mysql.sls 
{% if grains['osmajorrelease'] == 8%}
mysql-dep-package:
  pkg.installed:
    - name: ncurses-compat-libs
{% endif %}

{% if grains['osmajorrelease'] == 7%}
libaio-devel:
  pkg.installed
{% endif %}

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

/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


/opt/data:
  file.directory:
    - user: mysql
    - group: mysql
    - mode: '755'

install-mysql:
  cmd.script:
    - name: salt://modules/database/mysql/files/install.sh
    - unless: test -d /usr/local/mysql
    - template: jinja

copy-mysql-soft:
  file.managed:
    - names:
      - /usr/lib/systemd/system/mysqld.service:
        - source: salt://modules/database/mysql/files/mysqld.service
        - user: root
        - group: root
        - mode: 644
        - template: jinja
      - {{ pillar['mysql_dir'] }}/support-files/mysql.server:
        - source: salt://modules/database/mysql/files/mysql.server
        - user: mysql
        - group: mysql
        - mode: 755
        - template: jinja
      - /etc/my.cnf:
        - source: salt://modules/database/mysql/files/my.cnf
        - user: root
        - group: root
        - mode: 644
        - template: jinja
    - require:
      - cmd: install-mysql

启动主从复制

[root@master sub]# vim slave.sls 
include:
  - modules.database.mysql.install

slave-mysql.service:
  service.running:
    - name: mysqld
    - enable: true
    - reload: true
    - require:
      - cmd: install-mysql


salve-set-mysql-passwd:
  cmd.run:
    - name: {{ pillar['mysql_dir'] }}/bin/mysql -e "set password=password('{{ pillar['slave-mysql-passwd'] }}');"
    - require:
      - service: mysqld
    - unless: {{ pillar['mysql_dir'] }}/bin/mysql -uroot -p{{ pillar['slave-mysql-passwd'] }} -e "exit"

update-slave-mysql-file:
  file.append:
    - name: /etc/my.cnf
    - text: |
        relay-log = myrelay
        server-id = 20

stop-slave-mysql:
  service.dead:
    - name: mysqld
    - require:
      - file: update-slave-mysql-file

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


project-slave-scripts:
  cmd.script:
    - name: salt://modules/database/mysql/files/slave.sh

配置文件：

[root@master sub]# cat files/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
relay-log = mysql_relay-bin
server-id = 20

[root@master sub]# cat files/slave.sh 
#!/bin/bash
  
mysql=/usr/local/mysql/bin/mysql
user=repl
pass=123
localhost=192.168.8.131
log_file=$($mysql -u$user -p$pass -h$localhost  -e "show master status" |  awk -F '|' 'NR==2 {print $1}'  | awk '{print $1}')
log_pos=$($mysql -u$user -p$pass -h$localhost -e "show master status" |   awk -F '|' 'NR==2 {print $1}' | awk '{print $2}')

/usr/local/mysql/bin/mysql -uroot -p123 -e "change master to master_host='$localhost',master_user='$user',master_password='$pass',master_log_file='$log_file',master_log_pos=$log_pos; start slave; "