root

How to add a new user to MySQL

Don’t leave the root user as ‘the’ user for your web application. That’s just not a good idea. For instance, if you are running a whole bunch of sites from one database, and you security reasons you need to change the root password, you’ll have to update all the configs for the sites. Also, if one site gets hacked, they have the password for /all/ your databases and they can pretty much do whatever they like. Instead, have an extremely tough password for the root account and ONLY use that for root based activity. Have separate (and equally tough) passwords for each application. Keep the data separate as much as the access is concerned. Limit the databases that the users can see, to the ACTUAL databases they need access to. The more you tighten your defences, the safer you become, this is also known as multiple level security.

Anyway, to add a user, log in as root:

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Replace username with the username and password with the desired password. The localhost part is where you specify ‘from what domain/ip address’ the user can connect to this database server. You can really nail down security so that unless they are connecting from the specified address, then they cannot connect at all. Useful if you know the web server ip/domain (in this case they are on the same machine). You can allow connections form anywhere by substituting the localhost with % (which is a wildcard)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE ON databasename.* TO 'username'@'localhost';

The above gives the standard access to the user (SELECT INSERT UPDATE and DELETE) – this user will not be able to ALTER tables, DROP columns or tables, etc. This is important really.

Finally, flush the priveleges, although after many years of working with MySQL I am still unsure what this actually does. Something to do with re-caching the users and releasing table locks, etc.

mysql> FLUSH PRIVELEGES;

Of course you can use your favourite MySQL GUI to make these changes, but I think its better if you know the syntax yourself. One day you may have to make these changes remotely using a terminal of some kind. And then what are you going to do?

How to install MySQL Server on Centos

To install MySQL on Centos is a little different from Ubuntu – as I found out. Obviously APT is replaced with yum on this flavour of Linux, but not only that, yum doesn’t appear to set it up for you after installing it (with root account, etc).

Step 1, Install MySQL

> yum install mysql-server mysql

This will download the files and install them in the correct folders, as apptitude would. Step 2 shows you how to prepare the MySQL service.

Step 2, Configure the service

Tell the system to start the MySQL Daemon upon startup:

chkconfig --levels 235 mysqld on
service mysqld start

Now MySQL is installed and running, but the root account will have no password, so we need to ensure there is a strong one. (Or not if you are a crazy one) – You will get some message when the service starts for the first time related to security measures, I would suggest you do read it.

Step 3, Set up Root user

> mysql -u root
> SET PASSWORD FOR 'root'@'%' = PASSWORD(<password>);

Replace <password> with your desired password.

Obviously change the root user details based on your particular set up. I.e. where the root account can connect from, which servers, ips, etc.

Resetting forgotten MySQL root password

If you have forgotten your root password for MySQL, then don’t panic. Although, if you don’t have root shell access, then do panic!

Anyway, to reset a password, you need to stop MySQL and restart it with a special setting that allows you to login without passwords. Obviously this is a dangerous switch and you have to remember to stop and start MySQL after you’ve finished!

Linux:

Login with superuser access (sudo su)

> /etc/init.d/mysql stop

This will stop the MySQL service on the server, and all connections will be cut off.

Then we need to start MySQL with the secret switch:

> mysqld_safe --skip-grant-tables &

I don’t get any messages after this, but you may get one saying the mysqld_safe has started.

Then connect as root:

> mysql -u root

You will be connected. And you can just assign the root user a new password:

mysql> USE mysql;
mysql> UPDATE user SET password=PASSWORD(NEWPASSWORD) WHERE user = "root";
mysql> FLUSH PRIVILEGES;
mysql> exit;

That’s it. So now, just stop and start mysql again (as superuser) and were done:

> /etc/init.d/mysql restart

 

Changing your MySQL root password

When you installed MySQL, you would have been asked for a root password. If you were testing or something similar, you may have used an empty password. This is all well and good until you need to put your server into production. So, how do you create one?

> mysqladmin -u root password NEWPASSWORD

This will assign the root user, the password in NEWPASSWORD

But, what if you want to change the root’s password?

> mysqladmin -u root -pOLDPASSWORD password NEWPASSWORD

i.e.

> mysqladmin -u root -pabcdefg password gfedcba

The above example will change the root’s password (if it is right) from abcdefg to gfedcba (I know this isn’t a very strong password, but its only an example)

You can of course, log into MySQL to change the password for any user by following these commands:

> mysql -u root -pADMINPASSWORD
mysql> USE mysql;
mysql> UPDATE user SET password=PASSWORD('NEWPASSWORD') WHERE user = "root";
mysql> FLUSH PRIVILEGES;

Done.

If you forget your root password, then see my post on forgotten MySQL root password