MySQL cheat sheet

If you use MySQL as database engine, then here is a cheat sheet for some routine situations that you might want to get into. See also the Django documentation at MySQL notes

Users

How to create a mysql user:

$ sudo mysql -u root -p
mysql> create user 'DBUSER'@'localhost' identified by 'my cool password';

To see all users defined on the site:

$ sudo mysql -u root -p
mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | root             |
| 127.0.0.1 | root             |
| localhost |                  |
| localhost | debian-sys-maint |
| localhost | django           |
| %         | django           |
+-----------+------------------+
6 rows in set (0.00 sec)

How to change the password of an existing user:

$ sudo mysql -u root -p
mysql> set password for DBUSER@localhost = password('another cool password');

Databases

How getlino creates a database and grants permissions:

$ mysql -u root -p
mysql> create database DBNAME charset 'utf8';
mysql> grant all on mysite.* to DBUSER with grant option;
mysql> quit;

See which databases are installed on this server:

$ sudo mysql -u root -p -e "show databases;"

Deleting a site

What to do when you created a site and then changed your mind and want to delete it again?

Or when getlino startsite successfully creates the database and user, but then fails for some reason? You can simply overwrite an existing site by running getlino startsite again, but mysql or pgsql will try to create a new database and user of same name, and of course they will fail. The easiest workaround is to manually delete both the user and the database before running getlino startsite again.

Here is how to manually delete a database and user prjname:

$ sudo mysql -u root -p
mysql> drop database prjname;
mysql> drop user prjname@localhost;

Resetting the root password

In case you forgot the mysql root password (but have root access to the server):

$ sudo service mysql stop
$ sudo mysqld_safe --skip-grant-tables &
$ mysql
mysql> UPDATE mysql.user set password=password('My cool password') where user='root';
mysql> flush privileges;
mysql> exit;

$ sudo mysqladmin -u root -p shutdown
$ sudo service mysql restart

Notes about certain MySQL configuration settings

See the following chapters of the MySQL documentation