MySQL / MariaDB: Administration and Utilities

Installation

• apt-get install mariadb-server
• systemctl status mariadb

Determining MySQL Version

# to determine the version of the mysql client utility
# - the version of the client utility need not be the same as the version number of the server
mysql -V

# typical output (version=10.1.48-MariaDB):
ql  Ver 15.1 Distrib 10.1.48-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2


# to determine the version of the mysql server
# - upon connection to the database, the version of the server will be printed out
mysql -u foo -p

# typical output (version 10.1.48-MariaDB-0+deb9u2 Debian 9.13)
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1803
Server version: 10.1.48-MariaDB-0+deb9u2 Debian 9.13

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql DATABASE

• The mysql database is used for permissioning
• It contains numerous tables.
• Below are the primary tables which deal with permissioning:

user TABLE

• sets permissions for the server as a whole
• Columns (basically all of type enum ('N','Y'))
• Select_priv -- perform SELECT statements
• Insert_priv -- perform INSERT statements
• Update_priv -- perform UPDATE statements
• Delete_priv -- perform DELETE statements
• Create_priv -- perform CREATE statements
• Drop_priv -- perform DROP statements
• Index_priv -- the ability to create or drop indicies
• Alter_priv -- perform ALTER TABLE statements
• Here's a statement to add (a pretty powerful) user

  INSERT INTO user (Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv,Index_priv,Alter_priv)
  VALUES ('localhost','chris@localhost',password('chris'),'Y','Y','Y','Y','Y','Y','Y','Y');

db TABLE

• sets permissions for individual databases

host TABLE

tables_priv TABLE

colums_priv TABLE

UTILITIES

mysqladmin

• Client to administer a MySql/MariaDB server
• May need to log in as root or sudo
• Set root password

  mysqladmin -u root password 'newpasswd'

safe_mysql

• A script which starts mysqld (the mysql (mariaDB) daemon) and continuously verifies mysqld stays up.
• If mysqld exits, safe_mysql will restart it.
• Execute safe_mysql in the background (e.g. safe_mysql &)

mysql

• SQL client
• To log in with a given username (you will be prompted for a password):

  mysql -u username -p

• For example, to log in as the mysql root user:

  mysql -u root -p

• To add a user record to 'user' table and host record to 'host' table:
• Log in as mysql user root (see above)
• Execute the following:

  mysql> CREATE USER 'chris'@'localhost' IDENTIFIED by 'password';
  mysql> GRANT ALL PRIVILEGES ON * . * TO 'chris'@'localhost';     // *.* indicates all databases, all tables - respectively
  mysql> FLUSH PRIVILEGES;

Porting An Existing MySQL Database

• In some cases, you may simply be able to copy files
• However the safest method is always to export the database from the existing installation and import it into the new installation.
• Here are steps to copy files:
• Copy directory to /var/lib/mysql
• Add the following record to the 'db' table

  insert into db set host='localhost',db='HomeAuto',user='chris',select_pre_priv='y',create_priv='y',drop_priv='y',references_priv='y',index_priv='y',alter_priv='y';

Mysql Database Files

• depending on the version of MySQL/MariaDB you're running, you may see some variation here.
  • .isd = Data File
  • .ism = Keys and other internal data
  • .frm = Table Structure Info

Config File

• /etc/mysql/my.cnf

To Set Server Timezone to UTC

• Edit config file

  [mysqld]
  ...
  # default timezone = UTC
  default-time-zone = "+00:00"
  

• Restart Server

  systemctl restart mysql