The chance of remembering your MySQL root user account password when coming back to an Ubuntu server after a long period of time is pretty much zero – after all, this is seldom a username/password combo that you actually use on a daily basis! Luckily, there is a particularly easy way to reset the root user password for an Ubuntu server…
We’ll simply reconfigure the MySQL package!
First, we need to check the version of the currently installed mysql-server.
apt-cache policy mysql-server
This should give us among all the returned information a line that starts with ‘Installed’. My server instance returned this:
Installed: 5.5.43-0ubuntu0.14.04.1
So I have 5.5 installed then. Next, we start the reconfiguration process with:
sudo dpkg-reconfigure mysql-server-*.*
*.* should obviously be replaced with your version, so using my example, I would have ran:
sudo dpkg-reconfigure mysql-server-5.5
This command should then stop the database daemon and a prompt will then appear where you need to enter your new root password (yay!) and then confirm the reconfiguration. On completion, your instance of mysql-server should automatically be started up again.
As simple as that. You should now be able to login as root using your newly set password:
Sometimes you don’t want to expose your MySQL root user or password to a third party, but do need to give that party access to a super user or administrator account that has full control over the MySQL server instance.
Turns out, to add such a super user account is pretty easy.
First, access your MySQL console with:
mysql -u root -p
Once in, run the following SQL statement:
GRANT ALL PRIVILEGES ON *.* TO myusername@`%` IDENTIFIED BY 'mypassword';
(‘myusername’ and ‘mypassword’ should of course be substituted for your own values. Also, you don’t have to give the account such wide access by exchanging the any host % symbol with a specific IP/host if necessary)
In the Linux operating system, all filesystems are contained within one directory hierarchy. The root directory (/) is the top level directory, and all its subdirectories make up the directory hierarchy. This differs to other operating systems such as Microsoft Windows which applies a separate hierarchy for each device and partition.
So let us get a run down of the typical file hierarchy found in almost all Linux distros:
If you build a Zend Framework website then you will know that it is customary practice to move most of the work out of the publicly accessible web folder and then simple feed back the results to this folder so that the visitors can see a page off your site. So all projects always contain a /public folder which technically should be the root of your Apache webserver.
If you don’t in particular want to mess around with the various rights and permissions already linked to the default /var/www folder, it turns out that there is a elegantly simple solution to your problem – simply symlink the /var/www folder to your zendproject/public folder!
What we are doing above is simply removing the existing /var/www folder and then creating a symlink version of it that points to our project’s public folder. So now anyone browsing our web server will be served pages out of the /public folder, without them knowing any different, keeping the rest of our Zend project out of any potential snooper’s hands!
MySQL gets installed with a default root account under the username “root”. Sometimes the system will allow you to install a root account without a password (VERY not safe), but for the most part you have to set a password on install.
If of course you are anything like me, you instantly forget this password and a month down the line when you come back to do some more tinkering, you’ll quickly realize that you need to change the damn password – and this is one way you could go about doing it!
First, stop the MySQL server by entering the following into a terminal:
sudo /etc/init.d/mysql stop
Next, start a custom mysqld configuration by skipping the password tables:
sudo mysqld –skip-grant-tables &
The next step is then to login as root:
mysql -u root mysql
Finally, replace the existing password with your new one and Bob’s your uncle!
UPDATE user SET Password=PASSWORD(‘YOURNEWPASSWORD’) WHERE User=’root’; FLUSH PRIVILEGES; exit;
Done. Oh, and don’t forget to restart the service with sudo /etc/init.d/mysql start if necessary.
