optimize mysql

11/07/2022

Show mysql database size

SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
 FROM information_schema.tables
 GROUP BY table_schema

See real file size

du -hs /root/docker/mysql/data/*/

optimize mysql

 docker exec -ti mysql mysqlcheck -u root -p -o monitoring;
 docker exec -ti mysql mysqlcheck -u root -p -o --all-databases;

Parametres utiles pour que mysql s'éteigne correctement :

# lancer avec ces paramètres
--sql-mode= --innodb_fast_shutdown=0
# ou comme ça
mysql -uroot -p... -e "SET GLOBAL innodb_fast_shutdown = 0"
# verifier que c'est bien set
select @@innodb_fast_shutdown;

éteindre correctement :

        sigterm
        mysql -uroot -p... -e "shutdown"
        docker exec mysql /usr/bin/mysqladmin -uroot -proot shutdown
        docker stop mysql

        docker inspect mysql --format='{{.State.ExitCode}}'
        docker rm -f mysql
        # mv /root/dockerdata/mysql/ib_logfile[01] /tmp

accelerer le shutdown

        Set global innodb_max_dirty_pages_pct = 0;
        
        show global STATUS LIKE 'Innodb_buffer_pool_bytes_dirty'
        mysqladmin ext -i10 | grep dirty

binary logs

Si des tas de fichiers binlog.XXXXXX existent et occupent trop d'espace on peut les supprimer avec cette commande. Ils ne sont utiles que pour la réplication, et ne sont plus utile si la réplication est terminée

PURGE BINARY LOGS BEFORE '2022-10-20 10:00:00';

To safely purge binary log files, follow this procedure:

On each replica, use SHOW REPLICA STATUS to check which log file it is reading.
Obtain a listing of the binary log files on the source with SHOW BINARY LOGS.
Determine the earliest log file among all the replicas. This is the target file. If all the replicas are up to date, this is the last log file on the list.
Make a backup of all the log files you are about to delete. (This step is optional, but always advisable.)
Purge all log files up to but not including the target file.

to automatically clean logs set this variable to 1 day or anything else:

binlog_expire_logs_seconds=86400

you can check the varaible with this

SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'

Raccourcis

binary logs