Re: Mysql - U Root
Shay Silvertooth
How can we set the mysql root password ? Is it possible to do this on install? This was something that bugged me about Vesta. You could change the password manually, but it then buggered up all the other options and you had to start editing config files
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
The mysql.user grant table defines the initial MySQL user account and its access privileges. Installation of MySQL creates only a 'root'@'localhost' superuser account that has all privileges and can do anything. If the root account has an empty password, your MySQL installation is unprotected: Anyone can connect to the MySQL server as root without a password and be granted all privileges.
If the root account exists with an initial random password that has been expired, connect to the server as root using that password, then choose a new password. This is the case if the data directory was initialized using mysqld --initialize, either manually or using an installer that does not give you the option of specifying a password during the install operation. Because the password exists, you must use it to connect to the server. But because the password is expired, you cannot use the account for any purpose other than to choose a new password, until you do choose one.
If the root account exists but has no password, connect to the server as root using no password, then assign a password. This is the case if you initialized the data directory using mysqld --initialize-insecure.
After assigning the root account a password, you must supply that password whenever you connect to the server using the account. For example, to connect to the server using the mysql client, use this command:
MySQL has a special user account called root, which has full access and privileges to all the databases and tables on the server. However, using the root account for regular tasks is not recommended, as it can pose security and performance risks. Therefore, it is better to use a different user account with limited privileges for your web applications and databases.
Note: It is recommended that you only use the database username and password that you created with RunCloud. If you do need to access your database using the root username and password, you do so at your own risk.
When I'm working locally, I don't really need to enter my password to access my database. I changed my root password when I first installed MySQL, but I don't know how to change my password back. What should I do?
I also created a root password at installation and wanted to change back to using unix authentication once I understood that if I run commands or launch apps as root they can connect to the database without any password which is much simpler than having another password.
I also created a MySQL account with the same username as my unix account, again with no password, and using the root account I granted it access to the tables I needed, then I could also connect from apps from my user account without changing to root.
Edit: a previous version of the answer post recommended creating a user 'root'@'%'. However, it is more secure to create 'root'@'localhost' so connections can only be made from localhost, and not remotely. Both solutions work equally as well for local development.
The issue here is that when MariaDB or MySQL are installed/updated (especially if at some point root is set without a password) then in the Users table the password is actually empty (or ignored), and logging in depends on the system user corresponding to a MySQL user. You can test this as follows by switching to system root, and then type:
Then enter either no password or the wrong password. You'll probably be let in. (You may even be able to log in from the unix root by simply # mysql as the password is irrelevant and the user is defined).
you'll note auth_socket (which may read unix_socket on MariaDB). These sockets ignore passwords and allow the corresponding Unix user in without a password check. This is why you can log in with root but not with a different user.
For the record, the solution involving deleting the user and recreating it with '%' got me totally locked out of the database, and can cause other problems unless you get the grant statement exactly right - easier to simply update the root you already have.
As unix authentication is the most secure authentication, the best thing to do is leave the default root account as is (i.e. continue to use unix socket authentication with no password) and if not using the root account, use it via sudo. If an attacker has root/sudo access; then they already have full control of your server...
Title sums it up. I'm setting up a dedicated server as opposed to using shared hosting, and am wondering if it's more secure to change the MySQL root username. I've gone for a long and complex password, and I've done my best to secure the server itself using key based SSH and disabling root logins etc. But I'm paranoid since it's totally my responsibility now and I figure that if root is renamed, it makes it harder for a hacker to try and brute force if they don't know the username. Disabling remote access is probably helpful as well, if an option.
Anytime you deviate from a standard install, there's a cost: You need to adjust your mind and tools to deal with the deviation. Precious seconds or minutes may be wasted in critical situations because no one else in the world uses fiddledimdunk as their MySQL root user.
I would make sure the password is something long and obscure (which you mentioned you already have) and then disable remote root access, or at the least specify the IP's for root access. You have said you have key based SSH so if you need root access you can SSH in and then use root from there.Then create users with only the required access as needed, lock it down and open as needed :)
If the server is run and owned by you and you are not planning on delegating any responsibility's regarding administration duty's etc to another party who may need to know such information as the newly renamed 'root' I don't see the problem with renaming or indeed, removing it all together in replace of another user with root like permissions. It all depends on your situation and how happy you are when it comes down to the crunch when during a critical-time do actually forget the newly renamed default 'root' user name.
Another point that maybe obvious but will say it anyway; others may depend on your 'root' account actually being called 'root' and not some renamed entity like 'rewt' for example if you make any kind of use of remote scripts (or whatever) which may depend on specifically calling of the 'root' account..
Before launch of a project, I reset the server's mysql root password. The colo that is hosting project wants the server's root database password stored in the .my.cnf file, in plain text, with the permissions set to 400. I just can't see how that is good security practice, but he said that it was something that was being done commonly.
Of course any password that is stored in plain text is a bad idea but the location of the file is in /etc/my.cnf so if a remote user had access to that file I think there would be more things to worry about than having your mysql password stolen. On the other hand if you are not the only one who has physical access to the system then you are still safe from others reading your file since your file permissions are 400, assuming no one escalated privileges on the system. I did some research about having your password in my.cnf file and it's true it's actually a pretty common practice.
By the way you need to make sure you don't actually edit the file thats located in the /etc/my.cnf which mysql server uses to boot up because if you do anyone can use the server with ALL privileges and ALL databases without a password. So instead create a file in your local /root directory call it my.cnf and make sure only root can read it (of course)
I am sorry but if they told me they wanted me to plain text my database password I would say "nope". Now I have heard on cPanel servers running phpMyAdmin use that file to install and uninstall Site Software, which I sorta understand, still think it can be done better. I have never placed my DB password in the .my.cnf thats just asking for a simple LFI attack.
Edit: I thought about this more and I can see that this might be an acceptable method you want to invoke a client from a script that runs noninteractively, there is no opportunity to enter the password from the keyboard. On some systems, the first line of the script is read and interpreted (incorrectly) as your password.
Situation is as follows, a moodle server was originally setup using the MoodleWindowsInstaller on a Win2k Server, as a test platform, Start Moodle.exe used to run Apache and mysql and all of the defaults used for the install.php script.
The server has been a success and we are now looking to make it a live production server, however the root mysql password was left as blank so I would like to change this before the server goes live, but haven't been able to successfully do this.
More often than not in recent times I have had to bump off the mysql instance to regain normalcy, because it exhausted the max_connections value allotted to it. In scenarios such as this am unable to check the exact number of the existing connections and also troubleshoot the reason for the same.
I created a new mysql user with all the same privileges as the current 'root' user. For security reasons I thought why not create another user for this so people at least won't know the username of my super user.
d3342ee215