Ok, since Raspian Stretch upgraded to using MariaDB instead ot MySQL by default, they have removed the setting up of the root account by default during setup, in JESSIE with MySQL they allowed you to specify the password. When installing the MariaDB that step is skipped.
in MariaDB on Raspian you can get to your mysql root level access prompt by doing the following now.
So we have two problems
- We do not have an account to easily access the JayFish database remotely if you are a hacky hacker or more senior level developer.
- By default you cannot remotely connect to your DB using root, lets create a remote access account to do that.
- By default MariaDB prevents all access remotely.
Let’s combat point 1 & 2 first.
To create an account in MariaDB using the mysql statement, to give you remote access to all your databases.
type in sudo mysql then press enter. And then type in the following commands line by line ending in ;
CREATE USER ‘username‘@’%’ IDENTIFIED BY ‘password‘; GRANT USAGE ON *.* TO ‘username‘@’%’; GRANT SELECT, EXECUTE, SHOW VIEW, ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, LOCK TABLES ON *.* TO ‘username‘@’%’; FLUSH PRIVILEGES; SHOW GRANTS FOR ‘username‘@’%’;
Lets combat point 3
For Raspian Stretch with MariaDB to allow you to remotely access it, you need to modify the following file with sudo privileges.
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
Look for the line that start with bind-address = 127.0.0.1 simply # it out and save the file then reboot.
Now how to login with that account using PHPMyAdmin or HediSQL (I recommend Heidi)
Login with the username you chose like this.
And you should be good.