setting up mariadb

install

debian/ubuntu

apt install apt-transport-https gnupg1 wget lsb-release

# no exist
ls -lF /usr/share/keyrings/mariadb.asc
ls -lF /usr/share/keyrings/mariadb.gpg
ls -lF /etc/apt/sources.list.d/mariadb.list

wget https://mariadb.org/mariadb_release_signing_key.asc -O /usr/share/keyrings/mariadb.asc
gpg1 --dearmor < /usr/share/keyrings/mariadb.asc > /usr/share/keyrings/mariadb.gpg

point to the latest repo

cat > /etc/apt/sources.list.d/mariadb.list <<EOF
deb [signed-by=/usr/share/keyrings/mariadb.gpg] https://mirror.docker.ru/mariadb/repo/11.rolling/debian `lsb_release -cs 2>/dev/null` main
EOF

apt update
apt install mariadb-server mariadb-client

# up and enabled
systemctl status mariadb.service

slackware

slackpkg install mariadb lz4 lzo liburing
which mysql
ldd /usr/bin/mysql | grep found
mysql --version

mysql_install_db
ls -alF /var/lib/mysql/
chown -R mysql. /var/lib/mysql/

ls -lF /etc/rc.d/rc.mysqld
chmod +x /etc/rc.d/rc.mysqld
pgrep -a mysql
pgrep -a maria
/etc/rc.d/rc.mysqld start

database prep

make it production ready and check

mysql_secure_installation

no need to provide password if you’re root@localhost (not sure it’s the one or the other or both)

mysql -u root

otherwise

mysql -u root -p

tuning

listen to localhost only

debian/ubuntu - already the default

grep -r bind-address /etc/mysql/

slackware

cp -i /etc/my.cnf.d/server.cnf /etc/my.cnf.d/server.cnf.dist
vi /etc/my.cnf.d/server.cnf

bind-address = 127.0.0.1

vi /etc/rc.d/rc.mysqld

#SKIP="--skip-networking"

tail -F /var/lib/mysql/xc.err &
/etc/rc.d/rc.mysqld restart
netstat -lnp | grep mariadb # returns two lines, tcp and unix

database creation

create a db with UTF-8 charset & collate e.g. for moodle

    create database DBNAME default character set utf8 default collate utf8_general_ci;
    grant all privileges on DBNAME.* to DBDUDE identified by 'DBDUDE-PASSWORD';
    flush privileges;

–or– with the defaults e.g. for m/monit

ops

ls -lF /var/lib/mysql/*.err
tail -F /var/lib/mysql/*.err

list databases

show databases;

delete a database

drop database DB;

maintenance

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%';

additional notes

reset SQL user password

e.g. for root

select user from mysql.user;
ALTER USER root@localhost IDENTIFIED BY 'NEW-PASSWORD';
FLUSH PRIVILEGES;

inject 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;
...

rename tables in a batch

11:15 < TheRealBug[slack]> for table in `mysql -u root -s -N -e “use old_db;show tables from old_db;“`; do ``
11:15 < TheRealBug[slack]>     mysql -u root -ppassword -s -N -e “use old_db;rename table old_db.$table to new_db.$table;“;
11:15 < TheRealBug[slack]> done

resources

setting up mariadb

install

debian/ubuntu

slackware

database prep

tuning

listen to localhost only

database creation

ops

maintenance

additional notes

reset SQL user password

inject SQL

rename tables in a batch

resources

install

skip-networking

database

reset password