SETTING UP MYSQL / MARIADB

INSTALL

Slackware

slackpkg install mariadb lz4 lzo
which mysql
ldd /usr/bin/mysql
mysql --version

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

ls -lF /etc/rc.d/rc.mysqld
chmod +x /etc/rc.d/rc.mysqld
/etc/rc.d/rc.mysqld start

Ubuntu – either use the packaged binaries

apt install mariadb-server mariadb-client
#/etc/init.d/mysql status
systemctl status mariadb

–or– register with the MariaDB official repo

SYSPREP

Eventually listen localhost only

Slackware

cp -i /etc/mysql/my.cnf /etc/mysql/my.cnf.dist
vi /etc/mysql/my.cnf

bind-address = 127.0.0.1

Ubuntu – it’s already the default

grep bind-address /etc/mysql/mariadb.conf.d50-server.cnf

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

show databases;

DATABASE CREATION

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

–or– with UTF-8

    create database dbname
        DEFAULT CHARACTER SET utf8
        DEFAULT COLLATE utf8_general_ci;
    grant all privileges on DB.* to DUDE identified by 'DUDE-PASSWORD';
    flush privileges;

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

OPERATIONS

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

list databases

show databases;

delete a database

drop database DBNAME;

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

NOTES

reset SQL root password

mysql -u root
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MY_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

https://www.itzgeek.com/how-tos/linux/ubuntu-how-tos/install-mariadb-on-ubuntu-16-04.html

https://linuxize.com/post/how-to-reset-a-mysql-root-password/

https://www.digitalocean.com/community/tutorials/how-to-reset-your-mysql-or-mariadb-root-password

https://gridscale.io/en/community/tutorials/mysql-datenbank-erstellen-loeschen/