RH358配置MariaDB SQL数据库--自动化部署MariaDB-阿里云开发者社区

RH358配置MariaDB SQL数据库–自动化部署MariaDB

本章节介绍如何使用Ansible对MariaDB进行一系列的操作。有些场景下，可提高工作效率。

RH358专栏地址：https://blog.csdn.net/qq_41765918/category_11532281.html

文章目录

- RH358配置MariaDB SQL数据库--自动化部署MariaDB

1. Ansible部署MariaDB

使用Ansible部署MariaDB服务器和客户端遵循一个标准流程。

安装MariaDB服务器

# 使用yum Ansible模块安装mariadb-server包。mariadb-server包包含必要的客户机软件。
- name: Install mariadb-server package
  yum:
    name: mariadb-server
    state: present
# 注意：作为一种替代方法，可以使用dnf Ansible模块来安装特定的AppStream模块的MariaDB:
- name: Use MariaDB 10.3 AppStream module
  dnf:
    name: '@mariadb:10.3/server'
    state: present

# 使用Ansible service模块启用并启动mariadb服务。
- name: Enable and start mariadb
  service:
    name: mariadb
    state: started
    enabled: yes

# 使用Ansible firewalld模块打开MariaDB数据库服务器的端口。
- name: Firewall permits mysql service
  firewalld:
    service: mysql
    permanent: true
    state: enabled
    immediate: yes

安装MariaDB客户端

使用yum Ansible模块安装mariadb包，安装客户端而不使用server。

- name: Install mariadb client package
  yum:
    name: mariadb
    state: present

2. 使用Ansible配置安全化的MariaDB

MariaDB服务器安装时默认login_user为root，没有密码。要作为幂等策略的一部分保护根用户的帐户，您必须创建一个任务来为根用户分配密码。然后，第二个任务必须将包含根用户凭据的.my.cnf文件复制到MariaDB服务器主机上根用户的主目录。通过从文件中读取新的凭据而不重置默认根用户，剧本的后续运行将获得成功。

满足Ansible先决条件

为了让mysql_user和mysql_db Ansible模块在你的托管主机上工作，你需要确保python3-PyMySQL或python2-PyMySQL包安装在托管主机上，这取决于Ansible在该主机上使用的Python版本。

- name: Make sure mysql_user module prereqs are installed
  yum:
    name: python3-PyMySQL
    state: present

更新MariaDB Root帐号

使用mysql_user Ansible模块从mysql数据库中添加或删除用户。在下面的剧本任务中，为MariaDB root用户帐户分配了一个密码

- name: Assign password to MariaDB root user
  mysql_user:
    name: root
    host_all: yes
    update_password: always
    password: root_password
  no_log: yes

第二个剧本任务使用copy Ansible模块将根用户的凭据文件files/my.cnf复制到MariaDB服务器主机上的/root/.my.cnf

- name: Authentication credentials copied to root home directory
  copy:
    src: files/my.cnf
    dest: /root/.my.cnf

删除匿名用户帐户

使用mysql_user Ansible模块删除任何匿名用户帐户。

- name: Remove anonymous user accounts
  mysql_user:
    name: ''
    host_all: yes
    state: absent

删除测试数据库

一些较旧的MariaDB安装可能包含一个不安全的测试数据库，用于演示和实验目的。您可以使用mysql_db Ansible模块来确保从数据库服务器中删除它。

- name: Remove test database
  mysql_db:
    name: test
    state: absent

3. 使用Ansible管理MariaDB用户

Ansible Vault用于加密用于存储用户名和密码变量的文件。这避免了在剧本中暴露明文密码。

使用Ansible Vault保护用户密码

使用加密的Ansible Vault组变量文件安全地存储用户名和密码。本例中为password.yml文件位于与目录文件中的group_name组相关联的inventory中。

[user@host ansible]$ cat inventory
[group_name]
hosta.lab.example.com

# 要创建一个Ansible Vault组变量文件:
[user@host ansible]$ ansible-vault create group_vars/group_name/password.yml
New Vault password: vault_password
Confirm New Vault password: vault_password
mysql_user_passwords:
- name: john
password: john_password
- name: steve
password: steve_password

创建和删除用户

下面的示例剧本任务引用在加密密码中创建的名称和密码变量password.yml文件。当剧本执行时，提示您输入用于创建密码的Ansible Vault密码。

- name: Create users and set password if not present
  mysql_user:
    name: "{
    { item['name'] }}"
    password: "{
    { item['password'] }}"
    update_password: on_create
    state: present
  loop: "{
    { mysql_user_passwords }}"
  no_log: yes

重要：updated_password: on_create设置在这里很重要，因为它只在用户不存在时更新用户的密码。否则，每次包含此任务的剧本运行都会将密码重置为其原始的初始设置。如果用户在创建帐户后更新了他们的密码，这可能是一个问题

确保用户帐户不存在，如果存在就删除它，方法是指定state：mysql_user模块的用户名为absent。

- name: User does not exist for any host
  mysql_user:
    name: "{
    { removed_user }}"
    host_all: yes
    state: absent

授予和撤销权限

使用组变量文件存储用户访问权限。在本例中，users.yml文件位于与目录文件中的group_name组相关联的清单中

play的任务遍历mysqL_users：变量列表中声明的每个用户帐户，并相应地分配权限。例如，使用了用户john的变量声明在剧本的任务中分配特权相当于GRANT SELECT, UPDATE, DELETE,INSERT ON inventory.* TO john’@'localhost; MariaDB命令(将目录数据库中所有表的权限授予john@localhost)。

样本group_vars/group_name/users.yml文件:

---
mysql_users:
  - name: john
    host: localhost
    priv: 'inventory.*:INSERT,UPDATE,DELETE,SELECT'
    state: present
  - name: steve
    host: '%'
    priv: 'inventory.*:SELECT'
    state: present

# 剧本的任务是:
- name: Configure users in MariaDB inventory
  mysql_user:
    name: "{
    { item['name'] }}"
    host: "{
    { item['host'] }}"
    priv: "{
    { item['priv'] }}"
    state: "{
    { item['state'] }}"
  loop: "{
    { mysql_users }}"

# 撤销一个用户的特权，并将其还原为新用户拥有的最小权限:
- name: Ensure user only has minimal privileges, on all hosts
  mysql_user:
    name: "{
    { revoked_user }}"
    priv: "*.*:USAGE"
    host_all: yes
    state: present

4. 用Ansible创建和恢复备份文件

mysql_db Ansible模块用于执行创建(dump)和恢复(import)操作

创建数据库备份文件

下面的剧本任务使用mysql_db Ansible模块，将state参数设置为dump，以创建inventory数据库的备份。target参数指定要将备份文件存储在何处。

- name: Backup inventory database
  mysql_db:
    state: dump
    name: inventory
    target: /srv/inventory.dump

从备份文件恢复数据库

# 在下面的剧本中，第一个任务使用mysql_db Ansible模块来测试MariaDB中是否存在inventory数据库。结果存储在inventory_present变量中。
- name: Make sure an inventory database exists
  mysql_db:
    name: inventory
    state: present
  register: inventory_present

# 第二个任务使用stat Ansible模块来测试inventory.dump备份文件是否存。结果存储在inventory_present变量中。
- name: Is inventory database backup present?
  stat:
    path: /srv/inventory.dump
  register: inventory_bkup

# 剧本的第三个也是最后一个任务评估inventory_present变量，以确定inventory数据库是否仍然存在(没有任何变化)，以及inventory y_bkup变量结果是否表明目标inventory备份文件存在。
- name: Import inventory backup data
  mysql_db:
    name: inventory
    state: import
    target: /srv/inventory.dump
  when:
    - inventory_present['changed'] == false
    - inventory_bkup['stat']['exists'] == true

5. 课本练习

[student@workstation ~]$ lab database-automation start

这个命令确保从servera和serverb删除MariaDB数据库服务器和客户端之前的任何安装。

1. 查看~/database-auto项目目录的结构和内容，熟悉为本练习提供的文件。

[student@workstation ~]$ cd ~/database-auto
[student@workstation database-auto]$ tree
.
├── ansible.cfg
├── configure_mariadb_security.yml
├── configure_users.yml
├── dump_inventory_db.yml
├── files
│   ├── inventory-database.sql
│   └── my.cnf
├── import_inventory_db.yml
├── install_mariadb_client.yml
├── install_mariadb_server.yml
├── inventory
├── restore_inventory_db.yml
└── solutions
    ├── configure_mariadb_security.yml.solution
    ├── configure_users.yml.solution
    ├── dump_inventory_db.yml.solution
    ├── import_inventory_db.yml.solution
    ├── install_mariadb_client.yml.solution
    ├── install_mariadb_server.yml.solution
    └── restore_inventory_db.yml.solution

2 directories, 18 files
[student@workstation database-auto]$ cat ansible.cfg
[defaults]
inventory=./inventory
remote_user=devops

[privilege_escalation]
become = False
become_method = sudo
become_user = root
become_ask_pass = False
[student@workstation database-auto]$ cat inventory
[control_node]
workstation.lab.example.com

[db_servers]
servera.lab.example.com

[db_clients]
serverb.lab.example.com

2. 完成名为install_mariadb_server.yml的Ansible Playbook。

在servera上安装MariaDB服务器。配置playbook以启动和启用mariadb服务，并将mysql服务添加到防火墙中。更新MariaDB root帐号，以redhat作为密码。

[student@workstation database-auto]$ vim install_mariadb_server.yml
---
- name: Install MariaDB server
  hosts: db_servers
  become: yes

  tasks:
  - name: Install mariadb-server package
    yum:
      name: mariadb-server
      state: present

  - name: Enable and start mariadb
    service:
      name: mariadb
      state: started
      enabled: yes

  - name: Firewall permits mysql service
    firewalld:
      service: mysql
      permanent: true
      state: enabled
      immediate: yes

[student@workstation database-auto]$ ansible-playbook install_mariadb_server.yml --syntax-check
[student@workstation database-auto]$ ansible-playbook install_mariadb_server.yml

3. 完成名为install_mariadb_client.yml的Ansible Playbook。

在serverb安装MariaDB客户端。配置剧本以运行安装mariadb客户端包的单个任务。在客户端主机上不需要其他配置。

[student@workstation database-auto]$ vim install_mariadb_client.yml 
---
- name: Install MariaDB client
  hosts: db_clients
  become: yes

  tasks:
    - name: Install mariadb client package
      yum:
        name: mariadb
        state: present

[student@workstation database-auto]$ ansible-playbook install_mariadb_client.yml --syntax-check
[student@workstation database-auto]$ ansible-playbook install_mariadb_client.yml

4. 创建一个名为~/database-auto/group_vars/db_servers/vault.yml的加密文件。

# 存储MariaDB root用户密码的变量值。
[student@workstation database-auto]$ ansible-vault create group_vars/db_servers/vault.yml
New Vault password: fedora
Confirm New Vault password: fedora
pw: redhat

[student@workstation database-auto]$ ansible-vault view group_vars/db_servers/vault.yml
Vault password: fedora
pw: redhat

5. 完成名为configure_mariadb_security.yml的Ansible Playbook。

执行以下任务：

使用db_servers主机组将servera上的MariaDB数据库作为配置目标。
更新MariaDB root用户密码。
将身份验证凭据复制到servera上用root户的主目录。
删除所有匿名用户帐户。
如果存在test数据库，则删除它。

[student@workstation database-auto]$ vim configure_mariadb_security.yml 
---
- name: Securing MariaDB
  hosts: db_servers
  become: yes

  tasks:
    - name: Assign password to MariaDB root user
      mysql_user:
        name: root
        host_all: yes
        update_password: always
        password: "{
    { pw }}"

    - name: Authentication credentials copied to root home directory
      copy:
        src: files/my.cnf
        dest: /root/.my.cnf

    - name: Remove anonymous user accounts
      mysql_user:
        name: ''
        host_all: yes
        state: absent

    - name: Remove test database
      mysql_db:
        name: test
        state: absent

[student@workstation database-auto]$ ansible-playbook --syntax-check configure_mariadb_security.yml
[student@workstation database-auto]$ ansible-playbook \
 --vault-id @prompt configure_mariadb_security.yml

6. 完成名为restore_inventory_db.yml的Ansible Playbook。

执行以下任务：

使用db_servers目录组作为配置目标servera。
创建inventory数据库，如果不存在。
如果不存在inventory数据库的先前备份，则将其复制到servera。
从备份数据恢复inventory数据库。

[student@workstation database-auto]$ cat restore_inventory_db.yml 
---
- name: Restore inventory database if not present
  hosts: db_servers
  become: yes

  tasks:
    - name: Make sure inventory database exists
      mysql_db:
        name: inventory
        state: present
      register: inventory_present

    - name: Is inventory database backup present?
      stat:
        path: /srv/inventory-database.sql
      register: inventory_bkup

    - name: Copy database backup file to host if not present
      copy:
        src: files/inventory-database.sql
        dest: /srv
      when:
        - inventory_present['changed'] == true
        - inventory_bkup['stat']['exists'] == false

    - name: Restore inventory backup data
      mysql_db:
        name: inventory
        state: import
        target: /srv/inventory-database.sql
      when: inventory_present['changed'] == true

[student@workstation database-auto]$ ansible-playbook \
 --syntax-check restore_inventory_db.yml
playbook: restore_inventory_db.yml
[student@workstation database-auto]$ ansible-playbook \
 --vault-id @prompt restore_inventory_db.yml
Vault password (default): fedora

7. 完成名为dump_inventory_db.yml的剧本。

创建inventory数据库的附加备份。将备份文件存放在servera的/home/student/inventory.dump中。

[student@workstation database-auto]$ vim dump_inventory_db.yml 
---
- name: Database backup
  hosts: db_servers
  become: yes

  tasks:
    - name: Backup inventory database
      mysql_db:
        state: dump
        name: inventory
        target: /home/student/inventory.dump

[student@workstation database-auto]$ ansible-playbook \
--syntax-check dump_inventory_db.yml
playbook: dump_inventory_db.yml
[student@workstation database-auto]$ ansible-playbook \
--vault-id @prompt dump_inventory_db.yml
Vault password (default): fedora

8. 完成名为configure_users的Ansible Playbook。

创建MariaDB用户并分配使用inventory数据库的权限。使用以下条件创建两个MariaDB用户：

[student@workstation database-auto]$ vim group_vars/db_servers/users.yml
---
mysql_users:
  - name: john
    host: localhost
    priv: 'inventory.*:INSERT,UPDATE,DELETE,SELECT'
    state: present
  - name: steve
    host: '%'
    priv: 'inventory.*:SELECT'
    state: present

[student@workstation database-auto]$ ansible-vault edit group_vars/db_servers/vault.yml
Vault password: fedora
pw: redhat
mysql_user_passwords:
  - name: john
    password: john_password
  - name: steve
    password: steve_password

[student@workstation database-auto]$ cat configure_users.yml 
---
- name: Configure users in MariaDB
  hosts: db_servers
  become: yes

  tasks:
    - name: Create users and set password if not present
      mysql_user:
        name: "{
    { item['name'] }}"
        update_password: on_create
        password: "{
    { item['password'] }}"
        state: present
      loop: "{
    { mysql_user_passwords }}"

    - name: Configure users in MariaDB inventory
      mysql_user:
        name: "{
    { item['name'] }}"
        host: "{
    { item['host'] }}"
        priv: "{
    { item['priv'] }}"
        state: "{
    { item['state'] }}"
      loop: "{
    { mysql_users }}"

    - name: Update users with password for all host
      mysql_user:
        name: "{
    { item['name'] }}"
        host_all: yes
        update_password: always
        password: "{
    { item['password'] }}"
        state: present
      loop: "{
    { mysql_user_passwords }}"

[student@workstation database-auto]$ ansible-playbook \
--syntax-check configure_users.yml
playbook: configure_users.yml
[student@workstation database-auto]$ ansible-playbook \
--vault-id @prompt configure_users.yml
Vault password (default): fedora

9. 从servera以用户john的身份本地连接到MariaDB，并验证他对inventory数据库的特权。

[student@servera ~]$ mysql -u john -p
Enter password: john_password

MariaDB [(none)]> USE inventory;
MariaDB [inventory]> SELECT * FROM category;
+----+------------+
| id | name |
+----+------------+
| 1 | Networking |
| 2 | Servers |
| 3 | Ssd |
+----+------------+
3 rows in set (0.00 sec)

MariaDB [inventory]> INSERT INTO category(name) VALUES('Memory');
Query OK, 1 row affected (0.00 sec)
MariaDB [inventory]> UPDATE category SET name='Solid State Drive' WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [inventory]> SELECT * FROM category;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | Networking |
| 2 | Servers |
| 3 | Solid State Drive |
| 4 | Memory |
+----+-------------------+
4 rows in set (0.000 sec)

MariaDB [inventory]> DELETE FROM category WHERE name LIKE 'Memory';
Query OK, 1 row affected (0.01 sec)
MariaDB [inventory]> SELECT * FROM category;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | Networking |
| 2 | Servers |
| 3 | Solid State Drive |
+----+-------------------+
3 rows in set (0.000 sec)
MariaDB [inventory]> exit
Bye
[student@servera ~]$ exit
logout

10. 从serverb以用户steve的身份远程连接到servera上的MariaDB，并验证他对inventory数据库的特权。

[student@serverb ~]$ mysql -u steve -h servera -p
Enter password: steve_password
MariaDB [(none)]> USE inventory;
MariaDB [inventory]> SELECT * FROM category;
+----+-------------------+
| id | name |
+----+-------------------+
| 1 | Networking |
| 2 | Servers |
| 3 | Solid State Drive |
+----+-------------------+
3 rows in set (0.00 sec)

MariaDB [inventory]> INSERT INTO category(name) VALUES('Memory');
ERROR 1142 (42000): INSERT command denied to user 'steve'@'serverb.example.com' for table 'category'
MariaDB [inventory]> exit
Bye
[student@serverb ~]$ exit
logout

11. 完成名为import_inventory_db的Ansible Playbook。

导入/home/student/inventory.dump之前创建的数据库备份。确认inventory数据库中的类别表已恢复到其原始状态。

[student@workstation database-auto]$ vim import_inventory_db.yml 
---
- name: Import database
  hosts: db_servers
  become: yes

  tasks:
    - name: Make sure inventory database exists
      mysql_db:
        name: inventory
        state: present
      register: inventory_present

    - name: Is inventory database backup present?
      stat:
        path: /home/student/inventory.dump
      register: inventory_bkup

    - name: Import inventory backup data
      mysql_db:
        name: inventory
        state: import
        target: /home/student/inventory.dump
      when:
        - inventory_present['changed'] == false
        - inventory_bkup['stat']['exists'] == true

[student@workstation database-auto]$ ansible-playbook \
--syntax-check import_inventory_db.yml
playbook: import_inventory_db.yml
[student@workstation database-auto]$ ansible-playbook \
--vault-id @prompt import_inventory_db.yml
Vault password (default): fedora

[student@servera ~]$ mysql -u root -p
Enter password: redhat
MariaDB [(none)]> USE inventory;
MariaDB [inventory]> SELECT * FROM category;
+----+------------+
| id | name |
+----+------------+
| 1 | Networking |
| 2 | Servers |
| 3 | Ssd |
+----+------------+
3 rows in set (0.000 sec)
MariaDB [inventory]> exit
Bye

完成实验

[student@workstation ~]$ lab database-automation finish

总结

介绍如何使用Ansible对MariaDB进行日常的管理和维护。
使用练习例子演示整个过程。
若喜欢金鱼哥的文章，顺手点个赞。也可点个关注，因为后续会不断上干货。

RH358配置MariaDB SQL数据库--自动化部署MariaDB