mariadb/README.md

# MariaDB Ansible Role

An Ansible role for installing and configuring MariaDB on Debian-based systems using the official MariaDB repositories.

## Features

- ✅ Official MariaDB repository setup
- ✅ Flexible version management (major and minor versions)
- ✅ Automatic upgrade capabilities
- ✅ Database and user management
- ✅ Idempotent operations
- ✅ Support for MariaDB versions 10, 11, and 12
- ✅ Proper service management during upgrades

## Requirements

- **Target OS**: Debian-based systems (Debian, Ubuntu)
- **Ansible**: >= 2.8
- **Privileges**: sudo/root access required

## Role Variables

### Required Variables
None. The role works with sensible defaults.

### Optional Variables

| Variable | Default | Description |
|----------|---------|-------------|
| `mariadb_major_version` | `"12"` | Major version for repository setup (10, 11, or 12) |
| `mariadb_minor_version` | `undefined` | Minor version for specific version install (e.g., "8" for 11.8.x) |
| `mariadb_force_upgrade` | `false` | Force repository update and package upgrade |
| `mariadb_databases` | `[]` | List of databases to create |
| `mariadb_users` | `[]` | List of users to create |

### Variable Details

#### `mariadb_major_version`
- **Type**: String
- **Choices**: `"10"`, `"11"`, `"12"`
- **Purpose**: Determines which MariaDB repository to configure

#### `mariadb_minor_version`
- **Type**: String (optional)
- **Format**: Just the minor number (e.g., `"8"` for version 11.8.x)
- **Purpose**: Installs specific minor version, gets latest patch automatically
- **Behavior**: When undefined, installs the latest available version

#### `mariadb_force_upgrade`
- **Type**: Boolean
- **Purpose**: Forces repository reconfiguration and package upgrades
- **Use case**: Required when upgrading between major versions

#### `mariadb_databases`
- **Type**: List of dictionaries
- **Purpose**: Databases to create automatically
- **Structure**:
  - `name` (required): Database name
  - `encoding` (optional): Character encoding (default: `utf8mb4`)
  - `collation` (optional): Collation (default: `utf8mb4_unicode_ci`)

#### `mariadb_users`
- **Type**: List of dictionaries  
- **Purpose**: Users to create automatically
- **Structure**:
  - `name` (required): Username
  - `password` (optional): Plain text password
  - `encrypted_password` (optional): Pre-encrypted password hash
  - `host` (required): List of allowed hosts/IPs
  - `priv` (optional): List of privileges

## Dependencies

None.

## Example Playbooks

### Basic Installation (Latest MariaDB 12)

```yaml
- hosts: servers
  roles:
    - mariadb
```

### Install Specific Major Version

```yaml
- hosts: servers
  roles:
    - role: mariadb
      vars:
        mariadb_major_version: "11"
```

### Install Specific Minor Version

```yaml
- hosts: servers
  roles:
    - role: mariadb
      vars:
        mariadb_major_version: "11"
        mariadb_minor_version: "8"  # Installs latest 11.8.x
```

### Upgrade MariaDB Version

```yaml
- hosts: servers
  roles:
    - role: mariadb
      vars:
        mariadb_major_version: "12"
        mariadb_force_upgrade: true
```

### Complete Setup with Databases and Users

```yaml
- hosts: servers
  roles:
    - role: mariadb
      vars:
        mariadb_major_version: "12"
        mariadb_databases:
          - name: myapp_prod
            encoding: utf8mb4
            collation: utf8mb4_unicode_ci
          - name: myapp_test
            encoding: utf8
            collation: utf8_general_ci
        mariadb_users:
          - name: app_user
            password: "secure_password"
            host:
              - "localhost"
              - "10.0.1.%"
            priv:
              - "myapp_prod.*:ALL"
              - "myapp_test.*:ALL"
          - name: backup_user
            encrypted_password: "*8566479B619631314D83F27113F840A82191AB82"
            host:
              - "127.0.0.1"
            priv:
              - "*.*:SELECT,LOCK TABLES,SHOW VIEW,EVENT,TRIGGER"
```

## Usage Scenarios

### Fresh Installation
```yaml
# Install latest MariaDB 12
- role: mariadb

# Install latest MariaDB 11
- role: mariadb
  vars:
    mariadb_major_version: "11"

# Install MariaDB 11.8.x (latest patch)
- role: mariadb
  vars:
    mariadb_major_version: "11"
    mariadb_minor_version: "8"
```

### Version Upgrades
```yaml
# Upgrade from MariaDB 11 to 12
- role: mariadb
  vars:
    mariadb_major_version: "12"
    mariadb_force_upgrade: true

# Upgrade to specific minor version
- role: mariadb
  vars:
    mariadb_major_version: "12"
    mariadb_minor_version: "1"
    mariadb_force_upgrade: true
```

## How It Works

### Normal Installation Process
1. Install prerequisites (curl, ca-certificates)
2. Download and run official MariaDB repository setup script
3. Update apt cache
4. Install MariaDB packages
5. Start and enable MariaDB service

### Upgrade Process (`mariadb_force_upgrade: true`)
1. Stop MariaDB service
2. Remove existing repository configuration
3. Setup new repository with target version
4. Update apt cache
5. Upgrade packages to new version
6. Run `mysql_upgrade` for schema compatibility
7. Restart MariaDB service

### Version Resolution Logic
- **Repository**: Uses major version only (e.g., `mariadb-11.rolling`)
- **Packages**: 
  - Without minor version: `mariadb-server` (latest available)
  - With minor version: `mariadb-server=1:11.8*` (latest patch in minor series)

## File Structure

```
mariadb/
├── README.md
├── LICENSE
├── meta/
│   └── main.yml          # Role metadata
├── defaults/
│   └── main.yml          # Default variables
├── tasks/
│   ├── main.yml          # Main task inclusion
│   ├── install.yml       # Installation tasks
│   ├── upgrade.yml       # Upgrade-specific tasks
│   └── database.yml      # Database and user management
└── handlers/
    └── main.yml          # Service handlers
```

## Handlers

The role includes handlers for MariaDB service management:
- `restart mariadb`: Restarts the MariaDB service

## Examples with Command Line

### Install with specific version
```bash
ansible-playbook playbook.yml -e "mariadb_major_version=11"
```

### Upgrade to new version
```bash
ansible-playbook playbook.yml -e "mariadb_major_version=12 mariadb_force_upgrade=true"
```

### Install specific minor version
```bash
ansible-playbook playbook.yml -e "mariadb_major_version=11 mariadb_minor_version=8"
```

## Troubleshooting

### Repository Issues
If you encounter repository-related issues, try forcing a repository update:
```yaml
mariadb_force_upgrade: true
```

### Version Conflicts
When upgrading between major versions, always use:
```yaml
mariadb_force_upgrade: true
```

### Service Issues
The role automatically handles service management, but you can manually check:
```bash
systemctl status mariadb
```

## License

MIT

## Author Information

This role was created by [Ludovic Cartier](mailto:ludovic@brainsys.io) at [brainsys](https://brainsys.io).

---

**Repository**: https://git.brainsys.io/ansible-roles/mariadb  
**Issues**: https://git.brainsys.io/ansible-roles/mariadb/issues