Accessing MySQL locally

mariadb
v7

(Ashraf Ballan) #1

NethServer Version: 7.2

New NS installation with MySQL. MySQL configured for access in green zone. Using SSH to login to the server I’m having a hard time running a client session to MySQL.

$ mysql
Access denied for user ‘root’@‘localhost’ (using password: YES)

I edited the ‘/root/.my.cnf’ file and changed the system generated password to my own. Also created a file ‘etc/my.pwd’ and placed this inside:
[client]
password=<same password as in .my.cnf>

Restarted the MySQL service off course… still, can’t login to mysql.

Also, where are the mysql configuration files located to configure mysql for network access and the such? And how is PHP configured on NethServer?

Thanks!


(Marc) #2

Take a look at http://docs.nethserver.org/projects/nethserver-devel/en/v7/mysql.html

And https://wiki.nethserver.org/doku.php?id=phpmyadmin


(Ashraf Ballan) #3

Thank you for the quick reply… I went through those pages thoroughly and they really don’t help much. Like I said I configured the ‘/root/.my.cnf’ file with my password. Even changed the password in ‘/var/lib/nethserver/secrets/mysql’ to match the root password. Wont let me in…

Are these passwords hashed or just clear-text?


(Giacomo Sanchietti) #4

You shouldn’t do any modification.

These password are random-generated in clear text and are used during the auto-configuration event of nethserver-mysqld.

Please do not change them.


(Ashraf Ballan) #5

But I did change them! :frowning:
What can I do to reset the passwords/configuration?


(Giacomo Sanchietti) #6

Try to check this: https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password

Or wipe mysql installation and reinstall it.


(Ashraf Ballan) #7

Thank you giacomo… that was very helpful.

Since it was a bit tricky getting MySQL root access I will list the steps I did here for anyone facing the same issue, please correct if I did something that doesn’t make sense, but it worked for me:

MYSQL SET root PASSWORD STEPS:
Passwords in these files should all match (in clear text):
etc/my.pwd
/root/.mycnf
/var/lib/nethserver/secrets/mysql

Use the following steps to reset the MySQL root password and grant ‘%’ access:
ref: https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password

[Login to NS terminal, can be done remotely]
$ sudo systemctl stop mysql
$ sudo mysqld_safe --skip-grant-tables &
$ mysql -u root
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR ‘root’@‘localhost’ = PASSWORD(‘new_password’);
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘password’;
mysql> exit
[Use ‘kill’ to stop the mysqld processes]
$ ps aux | grep mysqld
$ kill [pid of mysqld]
[start mysqld from NS: Status:Services: mysqld “Start”]
[Use phpmyadmin to set GRANT ALL on user root]

To install phpmyadmin follow the instructions here: https://wiki.nethserver.org/doku.php?id=phpmyadmin

Please note, above will grant root user access from ANY network address to the entire MySQL system. You may not want to do that. You’ll also have to configure NS at Security: Network Services: mysqld and set the ‘zone’ the mysqld service can operate in (green: LAN, red: WAN)

I hope that helps…

On a side note, phpmyadmin is so popular and useful it probably should be part of the Software Center packages that can be installed easily.

Good day to all!


(Joel Clendineng) #8

pass cant be changed from my.conf…so use the pass that was in it originally. If you want to use your own, create a new user, and make a pass for it and grant perms to


(Joel Clendineng) #9

the thing is, most people will just want to ssh in and see the my.conf. if you need more, there is an expectation that you know what youre doing :slight_smile: also, yes, phpmyadmin is available if you prefer a gui, but the my.conf should never be edited. dont mess with the root unless you know what youre doing, if you need a user for say wordpress, make a user for wordpress, grant perms for that db, never full perms unless you absolutely need it, and profit. using root db is dangerous, or i should say, can be so just be sure you really want to mess with it.


(Ashraf Ballan) #10

Yes, you are most definitely right… that’s why I had a “disclaimer” at the end of my post. :slight_smile: