MySQL administration

Today we will talk about the most common activities performed when administering a MySQL server. The first thing we need to know is how to log in to MySQL, this can be accomplished by using the following command:

$ mysql -h host_name_or_IP -P port_number -u user_name -p

Where: -h allows you to specify witch FQDN or IP address MySQL client must connect to (localhost by default), -P port number where MySQL is listening (3306 default), -u means username (root by default), and -p means prompt for password. Note: if we want to connect with the root account to localhost on port 3306 you just need to type:

$ mysql -p

Create users

There are three methods for creating users in MySQL, one is by using CREATE command, other is by manually modifying the user table of the MySQL database, the last one consist in creating users via GRANT commands. The first method is recommended by the MySQL developers because is less prone to human errors (depending of the human ;)). The syntax of the command is the following:

mysql> CREATE USER 'user_name'@'host_name_or_IP'\ 
       IDENTIFIED BY my_password';

With the last commands MySQL created a user called user_name that has permission to log to a MySQL server with the address host_name_or_IP and has the password my_password. For example if we want to create a user named aang, that has permission to log in localhost and has the password appa you have to use the following command:

mysql> CREATE USER 'aang'@'localhost' IDENTIFIED BY 'appa';

If we want to create a user with the second method, we do the following:

mysql> INSERT INTO mysql.user VALUES ('host_name_or_IP',\
      'user_name', PASSWORD('my_password'), 'Y', 'Y', 'Y', 'Y',\
      'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',\
      'Y', 'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '',\
      '', 0, 0, 0, 0);

So the user aang that logs in localhost and has appa for password will be created with this:

mysql> INSERT INTO mysql.user VALUES ('localhost', 'aang',\
       PASSWORD('appa'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',\
       'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',\
       'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);

With GRANT we will accomplish the same if we do the following:

GRANT ALL ON database_name.* TO 'user_name'@'host_name_or_IP' IDENTIFIED BY 'my_password';

Delete users

We have two methods for deleting users: DROP USER and DELETE FROM mysql.user. First method:

mysql> DROP USER 'aang'@'localhost';

Second method:

mysql> DELETE FROM mysql.user WHERE User='aang';

Note: if a user is created via GRANT command (or where granted certain permissions) and we DROP o DELETE it, the permissions will remain unless we revoke its permissions with the REVOKE command that i will explain later on.

Permissions

Maybe one of the most important parts of MySQL is the permissions subject. In order to modify the permissions of a given user to access certain database on a known IP address we must use the GRANT command. Let's suppose that we created a user named acidburn and we want it to have permissions to modify a database called primulus but only from the source address localhost (127.0.0.1 not from a remote site). For that we can use the next command:

mysql> GRANT ALL PRIVILEGES ON primulus.* TO 'acidburn'@'localhost';
mysql> FLUSH PRIVILEGES;

We must be careful when assigning permissions to users, if we use the last commands on a production server the user acidburn will be able to enter the primulus database without password (just with mysql -u acidburn -D primulus). In order to avoid this possible security hole we must add IDENTIFIED BY 'my_password'. So the final command will be:

mysql> GRANT ALL PRIVILEGES ON primulus.* TO 'acidburn'@'localhost'\
       IDENTIFIED BY 'my_password';
mysql> FLUSH PRIVILEGES;

Now let's imagine that we need to login to the MySQL server from a remote computer with the user acidburn. In order to accomplish that, we must give the user acidburn access permissions from the source IP from witch she will be connecting. If the sourcfe IP is 200.44.32.12 the command will be the following:

mysql> GRANT ALL PRIVILEGES ON primulus.* TO 'acidburn'@'200.44.32.12'\
       IDENTIFIED BY 'mi_password';
mysql> FLUSH PRIVILEGES;

In order to eliminate privileges previously given to an user from the mysql.user table you must do the following:

mysql> DELETE FROM mysql.user WHERE User ='acidburn' and\
       Host = 200.44.32.12;
mysql> FLUSH PRIVILEGES;

In MySQL's default installation the root user can give privileges to every user and to himself from localhost. If you need to grant privileges from another machine you must add the sentence WITH GRANT OPTION at the end of the command. Example:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'200.44.32.12'\
       IDENTIFIED BY 'my_password' WITH GRANT OPTIONS;
mysql> FLUSH PRIVILEGES;

In all the above examples we where using GRANT ALL PRIVILEGES, this is not secure at all, there are several different privileges levels and we should give each user only the permission he/she needs. For a list of the privileges with their explanation visit http://dev.mysql.com/doc/refman/5.1/en/grant.html

Database backups

In order to backup databases we must use the mysqldump command. The syntax from this command is the following:

$ mysqldump --user username -p database_name > backup.dump

After we write the user's password a file called backup.dump will be created with the necessary instructions to create the database. If your intentions is to backup all the databases on the MySQL server you just need to put the --all-databases parameter. Example:

$ mysqldump --user user_name --all-databases -p > complete_backup.dump

If you need to backup only a couple of tables that belongs to a database just add a comma(,) separated list with the names of the tables to backup. If for instance you want to backup from a database named Shop the tables articles and clients the command will be as follows:

$ mysqldump --user user_name -p Shop articles,clients > art_and_cli.dump;

Restore databases

In order to restore the databases that you backup with mysqldump you just need to send the commands contained on the backup file to the MySQL server. Example:

$ mysql -u root -p < backup.dump

Note: the user used for backups and restores must have the necessary privileges (generally we use the root users for backups). Sometimes there can be errors when restoring a database, here i will list some of the most commons: ERROR 1049 (42000): Unknown database 'database_name' This happens because the MySQL server that is running the restore instructions doesn't have the database 'database_name'( meybe because yo just restored the tables and not the database). In order to solve this issue you can manually edit the backup and add the sentence mysql> CREATE DATABASE database_name; before the CREATE TABLE statements. ERROR 1046 (3D000): No database selected This happens because for some reason the instruction that tells MySQL to use a certain database is not there. You can repair the backup by adding mysql> USE database_name; before the CREATE TABLE sentences. ERROR 1007 (HY000): Can't create database 'database_name'; database exists This is obviously because the database exists if you still want to restore the backup erase the line mysql> CREATE DATABASE database_name;.

Reset root password

In order to reset the password of the root user (if you somehow forgot it) you must follow 4 steps: 1) Stop MySQL server. In linux you do this with /etc/init.d/mysql stop, on BSD y OPENBSD # /etc/rc.d/mysqld stop. 2) Start MySQL in safe mode. You can do this in any linux/unix OS #mysqld --skip-grant-tables &; (hit enter twice). 3) Enter and change root password (now you can access your server without password):

$ mysql -u root

Then you can change your password:

mysql> UPDATE mysql.user SET password=PASSWORD('New_password')\
      WHERE User='root';

You can flush privileges if you want to : mysql> FLUSH PRIVILEGES;. 4) Now restart MySQL: same as 1 but change stop for restart.

Access denied for User debian-sys-maint WTF ???

When you incorrectly migrate MySQL databases there can be problems. One of the most common mistakes is to copy all databases manually from one server to another. Example: # scp -R /var/lib/mysql/* root@dest_ip:/var/lib/mysql/. What happens in this example is that it copies all your databases and overwrites the MySQL database conveniently called mysql that contains all the user permission, etc. Between the casualties of this command is a nice fellow called debian_sys_maint. Debian-sys-maint is an especial user of MySQL for Debian that has permissions to start, stop and restart the MySQL server and the password of this user is automatically generated and unique for each Debian system. If you need to rescue this user you must find the unique password first, luckily is located on a file called debian.cnf in /etc/mysql/ directory (# cat /etc/mysql/debian.cnf). After copying the contents of this file we access MySQL and change it back:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost'\
       IDENTIFIED BY 'password_debian.cnf' WITH GRANT OPTION;mysql>
mysql> FLUSH PRIVILEGES;

Note: If you can't start MySQL server with /etc/init.d/mysql start don't panic !!! you can start it manually with # mysqld & (hit enter twice). After logging out you will notice that you can control MySQL again with Debian init.d scripts. One nice user called RedScourge wrote on an article in http://www.linuxquestions.org/questions/linux-software-2/mysql-debian-sy... that debian-sys-maint user should only have SHUTDOWN and SELECT permissions due to the fact that the only functions of this user is to shutdown/start MySQL (i agree with him). You can do this with the following command:

mysql> GRANT SHUTDOWN ON *.* TO 'debian-sys-maint'@'localhost';
mysql> GRANT SELECT ON `mysql`.`user` TO 'debian-sys-maint'@'localhost';
mysql> FLUSH PRIVILEGES;

EOF