Database

FlashPanel supports MySQL, MariaDB, PostgreSQL and MongoDB. Once installed, each one gets its own menu named after the application (e.g. MySQL, MariaDB) in the server menu. There you create databases and users, import/export data and (for MySQL/MariaDB) tune the configuration.

Install a database

When creating a server: in the Choose pre-installed apps step of connecting a server, pick MySQL or MariaDB (only one of the two; MySQL is selected by default). You can also add PostgreSQL and MongoDB.

On an existing server: install it from the server's Apps menu (see Applications).

Database admin account

• MySQL/MariaDB: when the server finishes installing, FlashPanel emails you a notification that includes a MySQL/MariaDB Information section with the account (flashpanel) and a randomly generated password.
• PostgreSQL: when PostgreSQL finishes installing, FlashPanel emails you the admin account (Super User, also flashpanel) and its password.

Keep these details somewhere safe.

Create a database

1. Open the server's database menu (e.g. MySQL).
2. In the Databases section, click Create Database.
3. Enter the Database Name. Optionally, choose the user allowed to access it in Grant By.
4. Click Save.

Create a database user

1. In the Database Users section, click Create User.
2. Enter the User Name and Password (click the key icon to generate a random password).
3. For MySQL/MariaDB: turn on Remote Access if the user needs to connect from another machine (see Access MySQL remotely), and choose an Authentication Plugin if your application requires one (keep the default if unsure).
4. Choose the databases the user can use:
   • MySQL/MariaDB, PostgreSQL: select them in Grant permissions to the database.
   • MongoDB: choose a Database and a Role; click Add Role to add more pairs.
5. Click Save.

Import a database

You can import a .sql or .sql.gz dump file into an existing database.

From the database page

1. Click Import on the database row.
2. Upload the dump file. Tick Delete file after import to remove the file from the server when done.
3. Click Import Database.

From the File Manager (when the file is already on the server)

1. Open the File Manager and find the dump file.
2. Right-click the file and choose Import Database.
3. Choose the target in Database and click Submit.

Export a database

1. Click Export on the database row.
2. In the Download Database dialog, click Create Backup.
3. When the backup is ready, click Download. The file is deleted from the server automatically after 3 hours.

For scheduled backups to Google Drive, S3…, use the Backup feature.

Sync from server

If you create or delete databases/users directly on the server (outside FlashPanel), click Sync From Server at the top of the page to update the lists to match the server.

Update MySQL/MariaDB configuration

For MySQL/MariaDB, the Update MySQL Configuration (or MariaDB) section at the bottom of the page lets you tune key settings without editing configuration files. Each field has a short description below it.

1. Click Recommended to fill in the recommended values, or edit each field yourself.
2. Click Update to save and apply.

Click my.cnf to view the current configuration file, and Refresh to reload the values currently in use on the server.

Available settings:

Setting	Meaning
port	The port MySQL listens on
bind-address	The IP address MySQL accepts connections on (change it for remote access)
skip-log-bin	Disables the binary log for better performance when you do not use replication
max_connections	Maximum number of concurrent connections
max_allowed_packet	Maximum size of a single data packet (increase it if large imports fail)
innodb_buffer_pool_size	RAM used to cache InnoDB data; the setting with the biggest performance impact
innodb_buffer_pool_instances	Number of InnoDB buffer pool partitions
innodb_flush_log_at_trx_commit	Balance between data safety and write speed
slow_query_log	Enables logging of slow queries
long_query_time	Seconds after which a query counts as slow
sort_buffer_size	Memory for sort operations (ORDER BY, GROUP BY)
binlog_expire_logs_seconds	How long binary logs are kept before being deleted automatically
innodb_flush_method	How InnoDB writes data to disk

TIP

innodb_buffer_pool_size is usually set to around 70–80% of RAM when the server only runs the database. If the server also runs a web server and PHP, set it lower to leave RAM for them.

Troubleshooting

Warning "'mysql_native_password' is deprecated"

The MySQL log shows:

[Warning] Plugin mysql_native_password reported: 'mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead

This is only a warning; MySQL keeps working. To stay compatible with older applications, FlashPanel enables the mysql_native_password authentication plugin when it installs MySQL, and MySQL logs this warning whenever a user that relies on it logs in. MySQL 9 removes this plugin, so move your users to caching_sha2_password when convenient:

1. In the Database Users section, click the edit icon on the user's row.
2. Choose caching_sha2_password in Authentication Plugin. If the Can Use column asks you to change the password, also enter a new Password and update it in your application.
3. Click Save.

Applications on PHP 7.4 or later and current MySQL drivers support caching_sha2_password. If an old application can no longer connect, switch that user back to mysql_native_password.

On MySQL 8.0, new users that don't specify a plugin also use mysql_native_password, because of the line default_authentication_plugin=mysql_native_password in /etc/mysql/my.cnf. To change the default, click Config on the MySQL row of the Applications page, change that line to default_authentication_plugin=caching_sha2_password, save the file, then click Restart. On MySQL 8.4, keep the mysql_native_password=ON line until no user relies on the old plugin, otherwise those users can no longer log in.

"Table is crashed and last repair failed" (Error 144)

MySQL/MariaDB fails to start and its error log shows lines like:

[ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/db'
[ERROR] Fatal error: Can't open and lock privilege tables: Got error '144 "Table is crashed and last repair failed"' for './mysql/db'

A table file is corrupted (often after a crash, a power loss or a full disk) and the automatic repair failed too. Because the database cannot start, you have to repair the table offline. The steps below repair the mysql.db table on MariaDB; replace mariadb with mysql if your server runs MySQL. Run them as root in Terminal or over SSH.

WARNING

If the disk has room, copy the data folder /var/lib/mysql somewhere safe before repairing.

1. Find the table's storage engine from its file extensions in /var/lib/mysql/<database>/:

   ls -lh /var/lib/mysql/mysql/db.*

   • .MAI and .MAD: Aria, repaired with aria_chk.
   • .MYI and .MYD: MyISAM, repaired with myisamchk.
   • .ibd: InnoDB. These tools do not apply; contact FlashPanel support.

2. Stop the database completely so it cannot restart during the repair, then check that no database process is left (the last command must print nothing):

   sudo systemctl stop mariadb
   sudo systemctl mask mariadb
   pgrep -a -f 'mysqld|mariadbd'

3. Repair the table:

   # Aria
   cd /var/lib/mysql/mysql
   sudo aria_chk -r db
   
   # MyISAM
   sudo myisamchk -r /var/lib/mysql/<database>/<table>.MYI

   A message like Can't find file ... aria_log_control is normal; the repair still continues.

4. Start the database again:

   sudo systemctl unmask mariadb
   sudo systemctl start mariadb

   If it still fails and the log names another table (for example mysql.global_priv), repeat steps 1–4 for that table.

5. Once the database runs, check every table with the admin account emailed to you when MySQL/MariaDB was installed:

   mysqlcheck -u flashpanel -p -Asc

   Repair any table it reports with mysqlcheck -u flashpanel -p -r database_name table_name.

See also

• Access MySQL remotely
• phpMyAdmin: in FlashPanel, phpMyAdmin is installed as a standalone website.