Database Setup

This guide covers creating and configuring the MySQL/MariaDB database for PteroCA.

Prerequisites

• MySQL or MariaDB installed and running

• Root or administrative access to the database server

• Basic Linux command-line knowledge

Create Database and User

To create a database and a database user, follow the example below. This guide is for MariaDB. Remember to replace YOUR_PASSWORD with your own secure password. Also, 127.0.0.1 refers to localhost.

mysql -u root -p
CREATE DATABASE pteroca;
CREATE USER 'pterocauser'@'127.0.0.1' IDENTIFIED BY 'YOUR_PASSWORD';
GRANT ALL PRIVILEGES ON pteroca.* TO 'pterocauser'@'127.0.0.1';
FLUSH PRIVILEGES;
EXIT;

What These Commands Do

• CREATE DATABASE pteroca - Creates a new database named "pteroca"

• CREATE USER - Creates a dedicated database user with password authentication

• GRANT ALL PRIVILEGES - Gives the user full access to the pteroca database

• FLUSH PRIVILEGES - Reloads the grant tables to apply changes immediately

Configure Database Connection

After creating the database and user, you need to configure PteroCA to connect to the database.

Run the Configuration Command

Execute the following command from the root directory of your project:

cd /var/www/pteroca
php bin/console pteroca:system:configure-database

Note: The old command name app:configure-database is deprecated but still works until v1.0.0.

Follow the Interactive Prompts

The command will ask a series of questions to help configure your database:

1. Configure Database

The first question asks if you want to configure the database. By answering "yes", you will proceed to set up the database credentials.

2. Set Database Credentials

You will be prompted to enter the following details:

• Database host (default: localhost)

• Database port (default: 3306)

• Database name (e.g., pteroca)

• Database user (e.g., pterocauser)

• Database password

These values will be written to your .env file under the DATABASE_URL parameter.

3. Run Migrations

After configuring the database credentials, the command will ask if you want to run migrations. By answering "yes", the system will execute migrations automatically in your database.

Verify Database Setup

After running the configuration command, you can verify that the database was set up correctly:

Check Database Tables

mysql -u pterocauser -p pteroca
SHOW TABLES;
EXIT;

You should see a list of PteroCA tables if migrations ran successfully.

Check .env File

cat /var/www/pteroca/.env | grep DATABASE_URL

You should see your database connection string, for example:

DATABASE_URL="mysql://pterocauser:[email protected]:3306/pteroca"

Troubleshooting

Connection Refused

Error: Connection refused or Can't connect to MySQL server

Solutions:

1. Verify MySQL/MariaDB is running:

   sudo systemctl status mysql
   # or
   sudo systemctl status mariadb

2. Check if the service is listening on the correct port:

   sudo netstat -tlnp | grep 3306

3. Ensure the host is correct (localhost vs 127.0.0.1)

Access Denied

Error: Access denied for user 'pterocauser'@'127.0.0.1'

Solutions:

1. Verify the username and password are correct

2. Check the user has privileges:

   mysql -u root -p
   SHOW GRANTS FOR 'pterocauser'@'127.0.0.1';

3. Re-grant privileges if needed:

   GRANT ALL PRIVILEGES ON pteroca.* TO 'pterocauser'@'127.0.0.1';
   FLUSH PRIVILEGES;

Migration Errors

Error: Migration fails during setup

Solutions:

1. Check database user has CREATE and ALTER privileges

2. Manually run migrations:

   php bin/console doctrine:migrations:migrate

3. Check migration status:

   php bin/console doctrine:migrations:status

Security Best Practices

1. Use Strong Passwords - Generate secure passwords for database users

2. Limit Access - Only grant necessary privileges

3. Use Localhost - Use 127.0.0.1 or localhost instead of allowing remote access unless needed

4. Regular Backups - Set up automated database backups

5. Monitor Access - Review database access logs regularly

Next Steps

After configuring the database:

1. Configure the system - Set up site settings, email, and Pterodactyl integration

2. Configure web server - Set up NGINX or Apache

Related Guides

• Manual Installation - Complete installation guide

• System Configuration - Configure system settings

• Troubleshooting - Common issues and solutions