commit 0eba9bf85a15e65d90d5d2127e4c28b37d396673
parent fa2266007e5de72686017c009e48592a59f85c4f
Author: root <root>
Date: Fri, 15 May 2026 21:14:53 +0200
first playbook
Diffstat:
12 files changed, 277 insertions(+), 0 deletions(-)
diff --git a/mariadb/site/files/override.conf b/mariadb/site/files/override.conf
@@ -0,0 +1,2 @@
+[Service]
+LimitNOFILE=1048576
diff --git a/mariadb/site/files/zz-local.cnf b/mariadb/site/files/zz-local.cnf
@@ -0,0 +1,33 @@
+[mysqld]
+bind_address = 0.0.0.0
+
+binlog_format = ROW
+expire_logs_days = 7
+
+ignore_db_dir = .log
+ignore_db_dir = .tmp
+
+innodb_buffer_pool_dump_at_shutdown = 0
+innodb_buffer_pool_load_at_startup = 0
+innodb_buffer_pool_size = 256G
+innodb_doublewrite = 1
+innondb_log_file_size = 1G
+innodb_log_write_ahead_size = 16K
+innodb_page_size = 16k
+innodb_monitor_enable = all
+innodb_use_atomic_writes = 0
+innodb_use_native_aio = 0
+max_allowed_packet = 512M
+
+log_bin = .log/binlog
+log_error = /var/lib/mysql/.log/error.log
+log_slow_verbosity = query_plan,explain
+
+table_definition_cache = 8K
+table_open_cache = 64K
+
+transaction_isolation = READ-COMMITTED
+
+userstat = 1
+
+optimizer_use_condition_selectivity = 1
diff --git a/mariadb/site/files/zz-plugins.cnf b/mariadb/site/files/zz-plugins.cnf
@@ -0,0 +1,2 @@
+[mysqld]
+plugin_load_add = metadata_lock_info
diff --git a/mariadb/site/inventories/common/db_variables.yml b/mariadb/site/inventories/common/db_variables.yml
@@ -0,0 +1,8 @@
+mariadb_packages:
+ - mariadb-server
+ - mariadb-common
+key_url: "https://mariadb.org/mariadb_release_signing_key.asc"
+key_dest: "/usr/share/keyrings/mariadb-keyring.asc"
+repo_url: "https://deb.mariadb.org/10.11/debian"
+repo_deb: "deb [arch=amd64 signed-by={{ key_dest }}] {{ repo_url }} bookworm main"
+mariadb_socket: /run/mysqld/mysqld.sock
diff --git a/mariadb/site/inventories/prod/group_vars/all.yml b/mariadb/site/inventories/prod/group_vars/all.yml
@@ -0,0 +1,2 @@
+---
+timeout_seconds: 600
diff --git a/mariadb/site/inventories/prod/hosts b/mariadb/site/inventories/prod/hosts
@@ -0,0 +1,4 @@
+[db:children]
+mariadb
+
+[mariadb]
diff --git a/mariadb/site/playbooks/mariadb-install/hosts b/mariadb/site/playbooks/mariadb-install/hosts
@@ -0,0 +1,2 @@
+[all]
+192.168.2.99 ansible_python_interpreter="/usr/bin/python3" ansible_ssh_private_key_file=~/.ssh/vm
diff --git a/mariadb/site/playbooks/mariadb-install/mariadb-install.yml b/mariadb/site/playbooks/mariadb-install/mariadb-install.yml
@@ -0,0 +1,138 @@
+---
+- name: MariaDB installation
+ hosts: all
+ become: yes
+ become_user: root
+ become_method: sudo
+
+ tasks:
+ - name: include variables
+ include_vars:
+ file: ../../inventories/common/db_variables.yml
+
+ - name: Gather service facts
+ service_facts:
+
+ - name: Gather package facts
+ package_facts:
+ manager: auto
+
+ - name: exit if mariadb service is running
+ fail:
+ msg: "Playbook halted: MariaDB service is actively running on this host."
+ when:
+ - "'mariadb.service' in ansible_facts.services"
+ - "ansible_facts.services['mariadb.service'].state == 'running'"
+
+ - name: exit if mariadb-server is already installed
+ fail:
+ msg: "Playbook halted: mariadb-server package is already installed on this host."
+ when: "'mariadb-server' in ansible_facts.packages"
+
+ - name: Install prerequisite packages
+ apt:
+ name:
+ - apt-transport-https
+ - ca-certificates
+ - gnupg
+ state: present
+ update_cache: true
+
+ - name: Download MariaDB signing key
+ get_url:
+ url: "{{ key_url }}"
+ dest: /usr/share/keyrings/mariadb-keyring.asc
+ mode: '0644'
+
+ - name: Add MariaDB APT repository
+ apt_repository:
+ repo: "{{ repo_deb }}"
+ state: present
+ filename: mariadb
+
+ - name: Install mariadb packages
+ apt:
+ name: "{{ mariadb_packages }}"
+ state: present
+ update_cache: true
+
+ - name: lookup mysql user
+ ansible.builtin.getent:
+ database: passwd
+ key: mysql
+ fail_key: false
+
+ - name: exit if user not present
+ fail:
+ msg: "Playbook halted: mysql user is not present"
+ when: "'mysql' not in ansible_facts.getent_passwd or ansible_facts.getent_passwd['mysql'] == None"
+
+ - name: Ensure /etc/my.cnf.d exists
+ file:
+ path: /etc/my.cnf.d
+ state: directory
+ owner: root
+ group: root
+ mode: '0755'
+
+ - name: create zz-local.cnf with specific params
+ copy:
+ src: ../../files/zz-local.cnf
+ dest: /etc/my.cnf.d/zz-local.cnf
+ owner: root
+ group: root
+ mode: '0644'
+
+ - name: Check /var/lib/mysql/.log exists
+ stat:
+ path: /var/lib/mysql/.log
+ register: log_dir
+
+ - name: create it if it does not
+ file:
+ path: /var/lib/mysql/.log
+ state: directory
+ owner: mysql
+ group: mysql
+ mode: '0755'
+ when: log_dir.stat.exists == false
+
+ - name: Check /var/lib/mysql/.tmp exists
+ stat:
+ path: /var/lib/mysql/.tmp
+ register: tmp_dir
+
+ - name: create it if it does not
+ file:
+ path: /var/lib/mysql/.tmp
+ state: directory
+ owner: mysql
+ group: mysql
+ mode: '0755'
+ when: tmp_dir.stat.exists == false
+
+ - name: check /etc/systemd/system/mariadb.service.d exists
+ file:
+ path: /etc/systemd/system/mariadb.service.d
+ state: directory
+ owner: root
+ group: root
+ mode: '0755'
+
+ - name: create systemd override with specified params
+ copy:
+ src: ../../files/override.conf
+ dest: /etc/systemd/system/mariadb.service.d/override.conf
+ owner: root
+ group: root
+ mode: '0644'
+
+ - name: reload systemd unit file definitions
+ command: systemctl daemon-reload
+
+ - name: start mariadb
+ systemd:
+ name: mariadb
+ state: started
+ enabled: true
+
diff --git a/mariadb/site/playbooks/mariadb-plugins/hosts b/mariadb/site/playbooks/mariadb-plugins/hosts
@@ -0,0 +1,2 @@
+[all]
+192.168.2.99 ansible_python_interpreter="/usr/bin/python3" ansible_ssh_private_key_file=~/.ssh/vm
diff --git a/mariadb/site/playbooks/mariadb-plugins/mariadb-plugins.yml b/mariadb/site/playbooks/mariadb-plugins/mariadb-plugins.yml
@@ -0,0 +1,57 @@
+- name: Install MariaDB plugins
+ hosts: all
+ become: yes
+ become_user: root
+ become_method: sudo
+
+ tasks:
+ - name: Gather package facts
+ package_facts:
+ manager: auto
+
+ - name: Gather service facts
+ service_facts:
+
+ - name: exit if mariadb package is missing
+ fail:
+ msg: "Playbook halted: mariadb-server package is missing"
+ when: "'mariadb-server' not in ansible_facts.packages"
+
+ - name: exit if mariadb service is not running
+ fail:
+ msg: "Playbook halted: mariadb service is not running"
+ when: "ansible_facts.services['mariadb.service'].state != 'running'"
+
+ - name: install ansible dependencies
+ apt:
+ name: python3-pymysql
+ state: present
+ update_cache: yes
+
+ - name: Install metadata_lock_info plugin
+ mysql_query:
+ login_unix_socket: /var/run/mysqld/mysqld.sock
+ query: "INSTALL SONAME 'metadata_lock_info'"
+ register: plugin_install
+ failed_when:
+ - plugin_install.failed
+ - "'already installed' not in plugin_install.msg | default('')"
+
+ - name: check whether metadata_lock_info plugin is loaded
+ mysql_query:
+ login_unix_socket: /var/run/mysqld/mysqld.sock
+ query: "SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME='METADATA_LOCK_INFO'"
+ register: plugin_check
+
+ - name: Show plugin status
+ debug:
+ msg: "metadata_lock_info plugin is {{ 'installed' if plugin_check.query_result[0] | length > 0 else 'NOT installed' }}"
+
+ - name: create zz-local.cnf with specific params
+ copy:
+ src: ../../files/zz-plugins.cnf
+ dest: /etc/my.cnf.d/zz-plugins.cnf
+ owner: root
+ group: root
+ mode: '0644'
+
diff --git a/mariadb/site/playbooks/mariadb/hosts b/mariadb/site/playbooks/mariadb/hosts
@@ -0,0 +1,2 @@
+[all]
+192.168.2.99 ansible_python_interpreter="/usr/bin/python3" ansible_ssh_private_key_file=~/.ssh/vm
diff --git a/mariadb/site/playbooks/mariadb/main.yml b/mariadb/site/playbooks/mariadb/main.yml
@@ -0,0 +1,25 @@
+---
+
+- name: MariDB playbook entrypoint
+ hosts: all
+ gather_facts: no
+
+ vars_prompt:
+ - name: "ansible_ssh_user"
+ prompt: "SSH username"
+ private: no
+ - name: "ansible_become_password"
+ prompt: "remote password"
+ private: yes
+
+ tasks:
+ - name: set ssh vars for all playbooks
+ set_fact:
+ ansible_ssh_user: "{{ ansible_ssh_user }}"
+ ansible_become_password: "{{ ansible_become_password }}"
+
+- name: Install Mariadb
+ import_playbook: ../mariadb-install/mariadb-install.yml
+
+- name: Install MariaDB plugins
+ import_playbook: ../mariadb-plugins/mariadb-plugins.yml