SQL Server Configuration
By default, RompЯ uses SQLite for its database. This option requires no setup and will work for most people.
If you would like to use a MySQL server instead - perhaps because you’re already running one - then you can do so.
Note that unless you have a really good reason to use MySQL, SQLite is always better. MySQL will almost never give superior performance. In my testing on a Raspberry Pi 4, SQLite is around 30% faster than MariaDB. Unless you really really know what you’re doing, use SQLite, it’s just better.
sudo apt-get install mysql
Or if your distribution has switched to mariadb
sudo apt-get install mariadb
Or you might have to specify a version number, like on raspbian. Try
apt-cache search mariadb
Create MySQL Database
You must create the RompЯ database before you start. You will need your mysql root password.
If you are using MySQL version lower than version 8
mysql -uroot -p CREATE DATABASE romprdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE romprdb; GRANT ALL ON romprdb.* TO rompr@localhost IDENTIFIED BY 'romprdbpass'; FLUSH PRIVILEGES; quit;
If you are using MYSQL version 8 or later
mysql -uroot -p CREATE DATABASE romprdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE romprdb; CREATE USER 'rompr' IDENTIFIED WITH mysql_native_password BY 'romprdbpass'; GRANT ALL ON romprdb.* TO 'rompr'@'%'; FLUSH PRIVILEGES; quit;
And if you’re using mariadb version 10 you might need to do
sudo mysql -uroot -p CREATE DATABASE romprdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER 'rompr' IDENTIFIED BY 'romprdbpass'; USE romprdb; GRANT ALL ON romprdb.* TO 'rompr'@'%' IDENTIFIED BY 'romprdbpass'; FLUSH PRIVILEGES; quit;
One of those will probably work. Unfortunately there is no consistency either between distributions or between releases so I can’t give a definitive set of instructions. Hint to distribution mainainers - it really makes everybody’s lives easier if you all just do things the same way. Please. I’m sick of writing 8 different vrsions of every step of my instructions.
Those commands set up the RompЯ database using a default username and password. Note that any usernames and passwords you put in will be stored by RompЯ in plain text, so don’t use anything important.
In the above example, the username is ‘rompr’ and the password is ‘romprdbpass’. I suggest you change these to something else as thse are obviously published on the internet.
We also want to set some configuration values for mysql to increase performance. Create another file somewhere, called rompr-tweaks.cnf (note it MUST end in .cnf or it will be ignored). Put the following in it
For MySQL versions lower than 8
[mysqld] query_cache_limit = 16M query_cache_size = 64M innodb_buffer_pool_size = 256M innodb_flush_log_at_trx_commit = 0
For MySQL Version 8 or newer
[mysqld] default_authentication_plugin = mysql_native_password innodb_buffer_pool_size = 256M innodb_flush_log_at_trx_commit = 0 skip-log-bin
And now link this file so mysql can find it
sudo ln -s /PATH/TO/ROMPR-TWEAKS /etc/mysql/conf.d/rompr-tweaks.cnf sudo ln -s /PATH/TO/ROMPR-TWEAKS /etc/mysql/mysql.conf.d/rompr-tweaks.cnf
Note that the default MySQL settings I’ve encountered on several distributions make MySQL significantly slower than SQLite for RompЯ, unless you have an extremely large music collection (hundreds of thousands of tracks). Setting these parameters restores the balance. Somewhat.
NOTE skip-log-bin is ESSENTIAL on mysql 8 or later.
Install PHP MySQL Library
sudo apt-get install php-mysql
Point your web browser at
and choose the Full Database option, entering the username, password, and database name you used above as appropriate. The usual value for port is 3306. The example image below uses a UNIX socket, which is configurable in your mysql config. A UNIX socket is slightly faster but does mean that your webserver must run on the same machine as your mysql server.