To install MySQL (or more accurately
MariaDB) on your DietPi \ Debian distribution all you need to do is run the following command:
sudo apt install mysql-server
This will download and install all the required packages and dependencies to get up and running quickly. Note you will need to do some additional configuration (below) in order to manage and access your MySQL instance remotely.
Creating User Accounts
While connected to your device via ssh, run the below command to start the mysql prompt:
Create a new user with the following command, replacing the values for username and password with your own values.
CREATE USER 'username' IDENTIFIED BY 'password';
Grant all permissions to your user with the following command:
GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Confirm that your user has been added successfully.
select user,host from mysql.user;
Allowing Remote Connections
Before you can login to your MySQL server remotely you will need to make the following changes in the
bind-address and change it to
0.0.0.0 this will allow MySQL to listen on all interfaces for incomming connections.
Save and close the file, followed by restarting the MySQL service.
systemctl stop mysql.service systemctl start mysql.service
Test your connection to the server using the user created earlier.
Creating a Database
Open MySQL prompt:
Run the following commands, in my case I am creating a database for my Home Assistant installation.
CREATE DATABASE `hass`;
Give yourself permissions to the DB.
GRANT ALL privileges ON `database`.* TO 'username'@'%'; FLUSH PRIVILEGES; SHOW GRANTS FOR 'username'@'%';
Your database should now be created and accessible by your user account.
MySQL data path:
Main configuration file:
Default data directory: