Install MariaDB / MySQL on Raspbian / Debian

0

Install MariaDB (or MySQL) on Raspbian / Debian is pretty easy with the supported package:
sudo apt-get install mariadb-server (or mysql-server)

And you can set password for the root account as well as configure others by one simple command:
sudo mysql_secure_installation (for both MariaDB and MySQL)

But the problem is that you can’t connect to the database from any system. You’ll get the “Access denied” error all the time. The only way to access is from the terminal of that server:
sudo mysql -u root -p

I don’t know why the authors make them to be difficult to use at the first time. To remove the restrictions, it requires many steps:

1. Login from terminal:
sudo mysql -u root -p

2. The plugin of the root account in the mysql.user table is set to ‘unix_socket’, which means you can only access using the terminal. Use the following commands to remove it:
USE mysql;
UPDATE user SET unix_socket = '' WHERE User='root';
FLUSH PRIVILEGES;

3. Now you can use any SQL tool (phpMyAdmin, SQL Workbench…) to access the database. But that tool must be used on the same server because the Host field of root account in mysql.user is ‘localhost’. So you may need to remove it.
UPDATE user SET Host = '' WHERE User='root';
FLUSH PRIVILEGES;

4. Now, try to access again. You still can’t access the database from another machine. Why? Because the database socket doesn’t bind to an IP address. This setting is missing from the config file. Let’s find the config file first:
sudo find / -name my.cnf

The file is usually located at: /etc/mysql/my.cnf. Open it to modify using nano:
sudo nano /etc/mysql/my.cnf

Add to that file a new section:
[mysqld]
bind-address=::

5. That’s it. Restart the services and enjoy it.
sudo service mysqld restart
sudo service mysql restart

Share.

About Author