Installing a new database server

Packages

apt-get install mysql-server phpmyadmin

From Debian 9 this will install MariaDB

Initial setup

run

mysql_secure_installation

to set up a root password and delete the test database

If you want the databases to run from the /home/ directory (or a subdir)

  vi /lib/systemd/system/mariadb.service

and change

  ProtectHome=true

to

  ProtectHome=true

then run

  systemctl reload-daemon
  systemctl start mysql

notes on root

Nowadays when you are logged in to bash as root, you can enter mariadb by just running mariadb / mysql and will not be prompted for a password! However, you can also not enter mysql as root from another user or from phpmyadmin! [1]

So to fix this, we enter mysql and do:

update mysql.user set plugin = 'mysql_native_password' where User='root';
flush privileges;

To reset the root password

/etc/init.d/mysql stop
mysqld_safe --skip-grant-tables &
MariaDB [(none)]> use mysql;
MariaDB [mysql]> UPDATE user SET password=PASSWORD("new_password") WHERE User='root';
MariaDB [mysql]> FLUSH PRIVILEGES;
MariaDB [mysql]> quit;
/etc/init.d/mysql start

Common Commands

Create a new database

create user user@localhost identified by 'somepass';
create database dbname;
grant all on dbname.* to user@localhost;
flush privileges;

Allow connections from other hosts

Preferably set % to a specific IP address

use mysql;
update user set Host='%' where User='username';
update db set Host='%' where Db='dbname';
flush privileges;

Changing a user password

UPDATE user SET Password=PASSWORD('my_password') where USER='username';
flush privileges;

Delete a database

drop database dbname;

Don't forget to delete the database and user from the mysql User and Db tables