Installation | Topics | Beyond Lino

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
mysql> create user 'DBUSER'@'localhost' identified by 'my cool password';

To see all users defined on the site:

$ sudo mysql
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
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:

# mysql -e "show databases;"

Change the charset of a datbase

Example:

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

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

Tuning

See separate document Tuning MySQL database performance.