Relational database like MariaDB is one of the required components to setup a web server as well as other less common uses such as when configuring a shared Kodi database. On Arch Linux MySQL has been replaced by a functionally identical community fork called MariaDB. The installation is also practically identical and as simple as can be expected.
Install MySQL / MariaDB
Install with pacman.
sudo pacman -S mariadb
Initialize data directories.
sudo mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
By default MySQL will run as the user running the command unless a user is explicitly specified with
--user option. Path to the installation directory can be specified with
--basedir option while data directory is specified with
Run MySQL / MariaDB
Start the service with systemd.
sudo systemctl start mysqld
If you encounter an error like
Failed to start mysqld.service: Unit mysqld.service failed to load: No such file or directory. first double check the spelling is correct. If it is then try rebooting and starting the service again.
Enable the service to start on boot.
sudo systemctl enable mysqld
Configure MySQL / MariaDB
Secure the installation.
Make sure you
set root password,
remove anonymous users and
disallow root login remotely unless you know you will need remote access to MariaDB. There should also be no need to keep test database so
remove test database and access to it. Finish the install process with
reload privilege tables now.
Configure Remote Access to MySQL / MariaDB
The default configuration only allows connections from localhost network interface. This is generally desired due to security risks associated with allowing remote access. There are cases however that require remote access. As an example remote access to the database is required to successfully configure shared XBMC / Kodi database on Arch Linux.
Open the configuration file.
sudo nano /etc/mysql/my.cnf
skip-external-locking option under
[mysqld] section and uncomment it. This option is used to prevent database corruption due to multiple processes modifying data at the same time. In the same section edit or add the
bind-address option. A special value of
0.0.0.0 will allow any host to connect to mysql. A more security conscious option is to allow only host on a local network to connect in which case the IP address of the machine hosting MySQL database should be used. If any of the options do not exist in the file it should be added by hand.
Use MySQL / MariaDB
Invoke the command line tool.
sudo mysql -u root -p
The username is specified with
-u option follower by the username which is
root by default. The password is specified with the
-p option followed by the password without a space in between or the password can be omitted in which case MariaDB will prompt for one.
List all existing databases.
List all database users.
SELECT DISTINCT User FROM mysql.user;
Use command line or Install phpMyAdmin to administer MySQL / MariaDB databases.