Database Setup

Step-by-Step Guide to Setting Up MariaDB Database

1. Naming Conventions

When setting up a MariaDB database, it is important to follow a consistent naming convention. The recommended format is to use the project name followed by the environment, separated by a dash. For example:

example-production
example-staging

Avoid using underscores (_) in the database name as it can cause issues with MySQL.

2. Creating the Database and User

To create the database and user, follow these steps:

Create the database:
```
CREATE DATABASE example_production;
```

Create the user:

CREATE USER 'example_production'@'localhost' IDENTIFIED BY 'your_password';

Grant permissions:

GRANT ALL PRIVILEGES ON example_production.* TO 'example_production'@'localhost';
FLUSH PRIVILEGES;

3. Password Encryption with Ansible Vault

To keep the database password secure, use Ansible Vault to encrypt it. Here’s how to do it:

Source the environment variables:
```
source env.rc
```
Encrypt the password:

Use the following command to encrypt the password. Replace your_password with the actual password you want to encrypt.
```
ansible-vault encrypt_string 'your_password' --name 'db_password'
```
Store the encrypted password:

Copy the encrypted string output by the above command and store it in your Ansible configuration file.

4. Configuring the Database in Ansible

In your Ansible configuration file, set the database name, username, and encrypted password as follows:

mariadb_databases:
  - name: example-production
    encoding: utf8mb4
    collation: utf8mb4_general_ci

mariadb_users:
  - name: example_production
    password: !vault |
        $ANSIBLE_VAULT;1.1;AES256
        61373361373236386136643463646166393233663666386631393066336565653562326462353534
    priv: "example_production.*:ALL"

5. Running the Ansible Playbook

Finally, run the Ansible playbook to apply the database configuration:

ansible-playbook -i inventory site.yml

This command will provision the database with the specified name, user, and encrypted password.

Conclusion

By following these steps, you can securely set up a MariaDB database with a consistent naming convention, proper user permissions, and encrypted passwords using Ansible Vault. If you encounter any issues or have further questions, feel free to reach out for assistance.