MySQL Authentication

(Mark Edworthy) #1

Hi, I have added MySQL to my server and can’t access the MySQL server due to incorrect authentication (I expected that the default user was root@localhost with the root password).

Can anybody tell me what the default username / password for MySQL is?

(Filippo Carletti) #2

mysql password is stored in $HOME/.my.cnf, (for root is in /root/.my.cnf) so that you can enter mysql without the need to input a password.
If you’re adding your db, I suggest the best practice of adding a user with access limited to your db.

(Mark Edworthy) #3

Ok, I have added a new user, copied / chmod the .mycnf file to from the root directory to the users home and remmed out the password entry from the new .mycnf.

I have used the console command 'mysql -u ’ to connect to this database and I am getting ‘Access Denied for user @localhost - using password: no’ message.

I also tried using the user password ‘mysql -u -p’ but still no access.

I have just compared /etc/my.pwd with /root/.mycnf and the password is the same (not sure what the password is hashed with - md5 or sha1).

(Mark Edworthy) #4

I just realised that the password that is stored in the .mycnf and my.pass files are not hashed but are plain text passwords.

It is possible to access MySQL using ‘mysql -u -p’ and then use the password that is stored in these files.

Whilst I understand that it is posible to create PAM users and then create .mycnf files for them, I prefer to create actual mySQL users using either the MySQL console interface, PHPMyAdmin or Webmin (which I have installed for finer control over Apache and MySQL as well as creating new mount points for extra HDDs).

Also whilst I am considering installing extra hard disks, I have noticed that the backup module does not allow backups to be stored directly to a path which has a drive mounted on it. (eg /dev/sdb1 mounted to /media/backup)

Sure, it is possible to set a samba share to point to a mount path (but that seems a bit of an overkill if you are not using Samba for any other reasons - If needed, instead of using Samba to remotely access backup files, try using SFTP).

(robert) #5

Hi Mark
The way that NetServer is setup is highly secure. Those clear text passwords are only available to root, only on the local machine and are never used in web applications.

I wrote a thing on it a while ago where I shamelessly borrowed this to set up secure Mysql systems

Why do you need actual users to login to mysql?

(Mark Edworthy) #6

Hi Robert,
Thanks for the information, the reason I need to have access to MySQL is so that I can transfer a couple of MySQL databases (with seperate users for each dbase) from another server to my Nethserver setup.

(robert) #7

Oh okay.
I was having a think ( dangerous I know) and had to do this a few years ago. Not 100% on syntax but went something like this.

mysqldump databasename > database.sql
Send to new server.
On new server
mysqladmin create databasename
mysql databasename < database.sql

GRANT ALL ON databasename TO user@‘localhost’ IDENTIFIED BY ‘password’;
restart mysql

(Mark Edworthy) #8

I used phpmyadmin to dump the sql to a file (phpmyadmin was the only way to access my old host).
My method of using mysql on the nethsever is to install a copy of webmin and use their mysql module.
First of all, I create a user and database using webmin then modify permissions, then I use a terminal to transfer the dump file to the database server.

I know that I could have used a terminal to create users and database permissions, but I do like and use Webmin for other tasks (such as monitoring of SMART info, setting virtual hosts for apache, mount disks and partitions, etc.)

(robert) #9

I haven;t used Webmin for years. I stopped when I realized that it was overwriting the edits I did via Vi from the CLI. That was a long time ago though!
Glad it is done anyway!