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=false

then run

  systemctl daemon-reload
  systemctl start mysql

edits in /etc/mysql/mariadb.conf.d/50-server.conf

To allow connections from remote machines

  #bind-address            = 127.0.0.1

To start logging

  general_log_file        = /var/log/mysql/mysql.log
  general_log             = 1

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

moving the data directory

You can move the datadir to a new directory and then link to /var/lib/mysql, but the neater way is to change the datadir in /etc/mysql/

Also, if you want to move it to /home/mysql you need to edit /etc/systemd/system/mysqld.service

  [Service]
  ProtectHome=false

If you want to move it to /usr or /boot you need

  [Service]
  ProtectSystem=off

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