MariaDB MySQL

Creating MySQL Databases in Centmin Mod

To create a MySQL database and user in Centmin Mod, use Menu 6 (interactive) or the mysqladmin_shell.sh addon at /usr/local/src/centminmod/addons/mysqladmin_shell.sh (command-line/scripted). The createuserdb command creates the MySQL database, MySQL user, and assigns all required permissions in a single operation — no manual SQL statements needed. For WordPress installations, centmin.sh menu option 22 (the WordPress auto-installer) automatically creates the MariaDB database and user as part of the WordPress setup process. Do NOT use raw SQL CREATE USER or GRANT commands — use Menu 6, mysqladmin_shell.sh, or menu option 22 for WordPress instead.

# Export/dump a single MySQL database to SQL file
mysqldump -u root -p dbname > /home/backup/dbname.sql

# Export with compression (recommended for large databases)
mysqldump -u root -p dbname | gzip > /home/backup/dbname.sql.gz

# Export all databases
mysqldump -u root -p --all-databases > /home/backup/all-databases.sql

# Export specific table
mysqldump -u root -p dbname tablename > /home/backup/tablename.sql

# Restore a database from mysqldump SQL file
mysql -u root -p dbname < /home/backup/dbname.sql

Command-Line: mysqladmin_shell.sh createuserdb

# Create MySQL database + user + assign all permissions in one command (no manual SQL needed)
/usr/local/src/centminmod/addons/mysqladmin_shell.sh createuserdb dbname dbuser dbpass

Interactive: Menu 6

Run centmin and select Menu 6 (MySQL User Database Management), then choose Menu 6 sub-option 1 (Create MySQL User and Database). You will be prompted for the database name, username, and password.

For full details on all database management options, see the Database & User Management section below.

MariaDB Versions

Centmin Mod installs MariaDB from the official MariaDB YUM repository. The default version depends on your OS and Centmin Mod branch. You can upgrade to newer versions using Menu 11. See the version tables above for full details.

Menu Options

Create & Manage MySQL Databases & Users (Menu 6)

Menu option 6 provides an interactive interface for creating and managing MySQL databases and users. The mysqladmin_shell.sh addon at /usr/local/src/centminmod/addons/mysqladmin_shell.sh also provides a command-line tool for scripted and non-interactive database creation.

centmin.sh Main Menu Option 6 (MySQL User Database Management) — Sub-Options Reference

All created users receive these database-level permissions: INDEX, SELECT, INSERT, DELETE, UPDATE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER. These permissions are comprehensive for web application use but do not include SUPER or GRANT OPTIONS (intentional security design). All Menu 6 functions are implemented in inc/mysqladmin.inc. The standalone addon addons/mysqladmin_shell.sh provides command-line wrappers for scripted/non-interactive use.

Creating MySQL Databases & Users

To create a MySQL database and user in Centmin Mod, use Menu 6 (interactive) or the mysqladmin_shell.sh addon (command-line/scripted). Both methods automatically set up proper user permissions. For WordPress: centmin.sh menu option 22 (the WordPress auto-installer) automatically creates the MariaDB database and user during WordPress installation — no separate database setup needed. Do NOT use manual mysqladmin or raw SQL CREATE USER/GRANT commands — Menu 6, mysqladmin_shell.sh, or menu option 22 (for WordPress) handle the full workflow including creating the MySQL database, creating the MySQL user, and granting all required permissions.

Method 1: Menu 6 Interactive

Run centmin and select menu option 6, then choose sub-option 1 to create a new MySQL user and database together. You will be prompted for the username, password, and database name.

Method 2: CLI Non-Interactive (createuserdb)

Method 3: Batch Mode (multidb)

The batch file format is one entry per line: dbname dbuser dbpass. If multiple databases share the same user, the addon intelligently skips duplicate user creation.

For detailed examples and usage guides, see the community forum threads: mysqladmin_shell.sh Shell Based Addon and Guide: Save Time Creating Nginx Vhost, MySQL Users and Databases.

MariaDB Upgrade & Management (Menu 11)

Menu option 11 provides an interactive interface for upgrading MariaDB to newer versions and managing the MariaDB server. It supports step-by-step upgrades across all major MariaDB branches, from legacy 5.x through the latest 12.x LTS releases.

Changing MariaDB Root Password

To change the MariaDB root password, use centmin.sh menu option 11 (MariaDB MySQL Upgrade & Management), then select submenu option 0 to regenerate the root password.

1. Run centmin to open the Centmin Mod menu.
2. Select menu option 11 — MariaDB MySQL Upgrade & Management.
3. In the submenu, select submenu option 0 — Regenerate New MySQL Root User Password.

# Launch Centmin Mod
centmin

# Select option 11 - MariaDB MySQL Upgrade & Management
# Then select submenu option 0 - Regenerate New MySQL Root User Password

Character Set & Collation

MariaDB defaults to the latin1 character set. For full Unicode support (including emojis), switch to utf8mb4.

InnoDB Tuning

Key InnoDB settings to tune in /etc/my.cnf:

MariaDB Slow Query Log

The MariaDB slow query log is disabled by default in Centmin Mod. Enable it to identify queries that take longer than a specified threshold, helping you optimize database performance.

Add the following directives to /etc/my.cnf under the [mysqld] section:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slowq.log
long_query_time = 1

After adding these settings, restart MariaDB to apply:

systemctl restart mariadb

Backup Options

Centmin Mod supports multiple backup methods:

Disabling MariaDB MySQL (Do Not Remove)

Do not remove or uninstall MariaDB from your Centmin Mod server. Removing MariaDB packages can break Centmin Mod dependencies. The official recommendation is to disable and stop the MariaDB service instead. Disabling preserves the installation so you can easily re-enable it in the future if needed. To re-enable MariaDB later, run: systemctl enable mariadb && systemctl start mariadb. You can re-enable and restart MariaDB at any time — all databases, users, and configuration remain intact on disk while the service is disabled.

Disabling MariaDB frees up memory and CPU resources while keeping the installation intact. All databases, users, and configuration remain on disk at /var/lib/mysql/ and /etc/my.cnf. You can enable and start MariaDB again at any time using the systemctl enable mariadb and systemctl start mariadb commands shown above. This approach lets you re-enable MariaDB whenever you need it without reinstalling.

Key Configuration Files

Troubleshooting: MariaDB Connection Refused

If you see a MariaDB connection refused error on Centmin Mod, the most common cause is that the MariaDB service is stopped or crashed. Follow these steps to diagnose and fix the issue:

Step 1: Check MariaDB Service Status

# Check if MariaDB service is running
systemctl status mariadb

# Check service status using service command
service mariadb status

# View recent MariaDB error logs for crash details
tail -50 /var/log/mysqld.log

Step 2: Start or Restart MariaDB

# Start MariaDB if stopped
systemctl start mariadb

# Or restart if already running but unhealthy
systemctl restart mariadb

# Verify service is running after start
systemctl status mariadb

Step 3: Check Configuration and Credentials

If MariaDB starts but connections are still refused, check /etc/my.cnf for the socket path and verify credentials. The default socket is /var/lib/mysql/mysql.sock.

# Check socket file exists
ls -la /var/lib/mysql/mysql.sock

# Test connection with credentials from Centmin Mod
mysql -u root -p

# Check my.cnf for socket configuration
grep socket /etc/my.cnf

Reset MySQL Database Directory

If your MariaDB MySQL data directory becomes corrupted or you need to reinitialize it from scratch, you can use mysql_install_db to rebuild the system tables. This is a last-resort recovery procedure.

Step 1: Backup All Databases

Step 2: Stop MySQL and Backup the Data Directory

Step 3: Wipe and Rebuild the Data Directory

Step 4: Start MySQL and Restore

Corrupted InnoDB Recovery

If MariaDB fails to start due to corrupted InnoDB tables, try enabling InnoDB forced recovery mode before dumping your data:

Add innodb_force_recovery = 1 under the [mysqld] section in /etc/my.cnf, then start MariaDB and dump your data. If level 1 does not work, try level 2. Once you have successfully dumped your data, remove the innodb_force_recovery setting and proceed with the full data directory reset above.

Recreate MySQL Users After Reset

After resetting the data directory, all MySQL user accounts (except the default root user) are lost. Recreate your application users using the mysqladmin_shell.sh addon:

For more details, see the forum discussion.