# connect to database
mysql -u user -p -h host database

# check connections and processes
show variables like '%conn%';
show status like '%conn%';
show variables like '%ssl%';
show processlist;
status;

# check character-set and collation
show variables like '%character%';
show variables like '%collat%';
show table status where name = 'MY_TABLE';
show full columns from 'MY_TABLE';

# database and character sets
alter database MY_DATABASE character set utf8 collate utf8_unicode_ci;
alter table MY_TABLE character set utf8 collate utf8_unicode_ci;
create database MY_DATABASE default character set = 'utf8' default collate = 'utf8_unicode_ci';

# create new table
create table if not exists MY_TABLE (
    ID bigint not null auto_increment,
    NAME varchar(100) not null,
    ORG_ID bigint not null,
    COMMENT longtext,
    CREATED_ON datetime,
    primary key (ID),
    unique (NAME, ORG_ID)
) engine = MyISAM|InnoDB , default character set = 'utf8' default collate = 'utf8_general_ci';

# create new user
create user 'dummy'@'localhost' identified by 'dummy';
grant all privileges on MY_DATABASE.* to 'dummy'@'localhost' with grant option;
create user 'dummy'@'%' identified by 'dummy';
grant all privileges on MY_DATABASE.* to 'dummy'@'%' with grant option;
select Host,User,ssl_type,ssl_cipher,x509_issuer,x509_subject,plugin,authentication_string from mysql.user;

# require user to use ssl
alter user 'dummy'@'localhost' require ssl;

# require user to use client-cert
alter user 'dummy'@'localhost' require x509;

# require user to use a specific client-cert with given CN, O, C, ST, L
alter user 'dummy'@'localhost' require subject '/CN=client.mariadb.com/O=MariaDB Inc./C=US/ST=California/L=RWC';

# backup and restore
mysqldump -u userA -p db_xyz > db_xyz.sql
mysql -u userA -p db_xyz < db_xyz.sql

# create CA certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 \
        -key ca-key.pem -out ca-cert.pem

# create server certificate, remove passphrase, and sign it
openssl req -newkey rsa:2048 -days 3600 \
        -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem \
        -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# create client certificate, remove passphrase, and sign it
openssl req -newkey rsa:2048 -days 3600 -nodes \
        -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem \
        -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

# enable ssl for mariadb in /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
...
ssl_cert = /etc/my.cnf.d/certificates/server-cert.pem
ssl_key = /etc/my.cnf.d/certificates/server-key.pem
ssl_ca = /etc/my.cnf.d/certificates/ca-cert.pem
#require_secure_transport=ON

# enable ssl for mysql client in /etc/my.cnf.d/mysql-clients.cnf
[mysql]
...
ssl_cert = /etc/my.cnf.d/certificates/client-cert.pem
ssl_key = /etc/my.cnf.d/certificates/client-key.pem
ssl_ca = /etc/my.cnf.d/certificates/ca-cert.pem
#ssl-verify-server-cert