Setting up MySQL / MariaDB

Preparing a database

Either use the Ubuntu packaged binaries or register to MariaDB official repo.

apt install mariadb-server mariadb-client
/etc/init.d/mysql status #this is not SystemD

Otherwise on slackware

mysql_install_db
chown -R mysql:mysql /var/lib/mysql

ls -lF /etc/rc.d/rc.mysqld
chmod +x /etc/rc.d/rc.mysqld
ls -lF /var/lib/mysql/$HOST.err
tail -F /var/lib/mysql/$HOST.err
/etc/rc.d/rc.mysqld start

and proceed

mysql_secure_installation
mysql -uroot -p

create database mmonit;
grant all on mmonit.* to mmonit@localhost identified by 'PASSWORD';
^D

cd /usr/local/mmonit/
mysql -u mmonit mmonit -p < db/mmonit-schema.mysql

Create db with UTF-8 charset & collate

Access the instance as sql root,

app=mariadbprod
    docker exec -ti $app mysql -uroot -p
    show databases;

Create a database with UTF-8,

    create database dbname
        DEFAULT CHARACTER SET utf8
        DEFAULT COLLATE utf8_general_ci;
    grant all privileges on dbname.* to dbnameuser identified by 'USERPASS_HERE';
    flush privileges;

and check,

docker exec -ti $app mysql -udbnameuser -pUSERPASS_HERE dbname
show tables;

See Database Character Set and Collation on how to check that the charset is right. That’s a MySQL 5 doc but should also work with MariaDB. On a db,

USE db_name;
SELECT @@character_set_database, @@collation_database;

or alternatively without being on that db,

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';

The defaults are not right (latin1 instead of utf8),

    SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Injecting SQL

Inject whatever SQL code you want in it,

app=mariadbprod
    docker exec -ti $app mysql -udbnameuser -pUSERPASS_HERE dbname < schema.sql

and check,

docker exec -ti $app mysql -udbnameuser -pUSERPASS_HERE dbname
show tables;
...

Nethence | Pub | Lab | Pbraun | SNE Russia | xhtml