MySQL various

Recréer à neuf la db de MySQL :

Most MySQL versions

mysql_install_db

MySQL 5.7 and later

mysqld --initialize

MySQL users

Note: Un % est un wildcard dans MySQL

Affiche tous les users

SELECT User,Host FROM mysql.user;

Affiche les permissions du user

SHOW GRANTS FOR 'nom_du_user'@'localhost';
SELECT * FROM mysql.user;
SELECT user, host FROM mysql.user;

Création d'un user

FLUSH PRIVILEGES;
CREATE USER 'nom_du_user'@'adresse_ip' IDENTIFIED BY 'mot_de_passe';
GRANT ALL PRIVILEGES ON `nom_database`.* TO 'nom_du_user'@'adresse_ip' IDENTIFIED BY 'mot_de_passe';

FLUSH PRIVILEGES;

Changer le mot de passe d'un user :

ALTER USER 'root'@'localhost' IDENTIFIED BY 'mot_de_passe';

Suppression d'un user

show grants for nom_du_user@adresse_ip;
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'nom_du_user'@'adresse_ip';
DROP USER 'nom_du_user'@'adresse_ip';

MYSQL create database

Log into MySQL as the root user.

Create a new database user:

GRANT ALL PRIVILEGES ON *.* TO 'db_user'@'localhost' IDENTIFIED BY 'mot_de_passe';

Log out of MySQL by typing:

exit

Log in as the new database user you just created:

mysql -u db_user -p

Create the new database:

CREATE DATABASE db_name;

How to restore a single table in MySQL

Sometimes you wouldn't need a whole database to be restored, just a table. This is how you would restore just a single table.
In this example, I'll be restoring a table called 'users'in the database business_db, from adaily backup created the morning of the request.

1. Locate backup.

  cd /home/user/
  tar -zxvf business.tar.gz business/mysql

The files are now extracted and ready to go.

1. Create temporary database so we can import what we need.

mysql -p

  mysql> CREATE DATABASE TEMP_SAU;
  Query OK, 1 row affected (0.00 sec)
  mysql> exit

CHECK THE SQL FILE BEFORE DOING STEP 3 - TJIS WILL WIPE OUT THE RUNNING DATABASE! :

  DROP DATABASE IF EXISTS `business_db`;

1. Import yesterdays backup.

  mysql TEMP_SAU < business/mysql/business_db.sql

1. Dump just the table we need.

  mysqldump --add-drop-table TEMP_SAU users > users.sql

1. Import just that table into thelive database.

  mysql business_db < users.sql

6.Clean up after yourself

    rm -rf business users.sql

mysql

    mysql> DROP DATABASE TEMP_SAU;
    Query OK, 199 rows affected (1.92 sec)

Extraire une table d'un full dump .sql

sed -n -e '/CREATE TABLE.*`mytable`/,/CREATE TABLE/p' mysql.dump > mytable.dump

MySQL divers

SHOW COLUMNS FROM [table_name];

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT  N;

SELECT COUNT(col_name) FROM table_name;

CHECKSUM TABLE tbl_name [, tbl_name] ... [ QUICK | EXTENDED ]

mysqladmin -u root -p processlist --sleep 1
mysql -p -e 'SHOW PROCESSLIST;'

En santé ?

mysqladmin -h 10.1.1.11 -u root -p status

Aborted connects can point to possible attacks and application errors:

mysql -e "SHOW GLOBAL STATUS LIKE 'aborted_connects';"

Deadlocks on InnoDB

mysql -e "SHOW ENGINE INNODB STATUS;"

List table sizes from a single database

SELECT
  TABLE_NAME AS `Table`,
  ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = "test_recup"
ORDER BY
  (DATA_LENGTH + INDEX_LENGTH)
DESC;

Affiche toutes les tables en myISAM

SELECT TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'database' and ENGINE = 'myISAM'

Sauvegarde (backup) MySQL d'une base de données :

time mysqldump -h 127.0.0.1 -u root --single-transaction --quick --lock-tables=false --skip-add-drop-table --result-file="/mnt/bel/backup/zabbix_$(date +%F_%H-%M).sql" --databases zabbix

/usr/bin/mysqldump \
    --defaults-extra-file=/user/.obm/config/mysql-default-1600808747945.opt \
    --result-file=/root/temp/1600808747945/SpoolArea/MySQL/my_db.sql.2020-01-01-19-00-00-000.ADD \
    --port=3306 \
    --user=root \
    --host=10.1.1.11 \
    --opt \
    --quote-names \
    --allow-keywords \
    --events \
    --triggers \
    --routines \
    --default-character-set=utf8mb4 \
    --single-transaction my_db

/usr/bin/mysqldump --defaults-extra-file=/root/.obm/.my.cnf -h localhost -u root --single-transaction --quick --lock-tables=false --skip-add-drop-table -D <database_name> > <database_name>_$(date +%F_%H-%M).sql"

Contenu du fichier "/root/.obm/.my.cnf" :

[client]
user = root
password = MySQL root password

***Serait p-e mieux avec : "--all-databases --single-transaction --set-gtid-purged=OFF"

--quick: Enforce dumping tables row by row. This provides added safety for systems with little RAM and/or large databases where storing tables in memory could become problematic.
--lock-tables=false: Do not lock tables for the backup session.

--databases - This allows you to specify the databases that you want to backup.  You can also <a href="http://dev.mysql.com/doc/mysql-enterprise-backup/3.6/en/partial.html">specify certain tables</a> that you want to backup.  If you want to do a full backup of all of the databases, then leave out this option
--add-drop-database - This will insert a DROP DATABASE statement before each CREATE DATABASE statement.  This is useful if you need to import the data to an existing MySQL instance where you want to overwrite the existing data.  You can also use this to import your backup onto a new MySQL instance, and it will create the databases and tables for you.
--triggers - this will include the triggers for each dumped table
--routines - this will include the stored routines (procedures and functions) from the dumped databases
--events - this will include any events from the dumped databases
--set-gtid-purged=OFF - since I am using replication on this database (it is the master), I like to include this in case I want to create a new slave using the data that I have dumped.  This option enables control over global transaction identifiers (GTID) information written to the dump file, by indicating whether to add a SET @@global.gtid_purged statement to the output.
--user - The MySQL user name you want to use
--password - Again, you can add the actual value of the password (ex. --password=mypassword), but it is less secure than typing in the password manually.  This is useful for when you want to put the backup in a script, in cron or in Windows Task Scheduler.
--single-transaction - Issue a BEGIN SQL statement before dumping data from the server.

Reset by using mysql_secure_installation

The simplest approach to reset MySQL database root password is to execute mysql_secure_installation program and when prompted entering your new root MySQL password:

$ sudo mysql_secure_installation
....
Please set the password for root here.
New password: 
Re-enter new password:

Reset by using skip-grant-tables If from some reason the above method fails follow the step below to use --skip-grant-tables to reset MySQL root password.

Let's start by stopping the currently running MySQL database:

$ sudo service mysql stop

Next, create a /var/run/mysqld directory to be used by MySQL process to store and access socket file:

$ sudo mkdir -p /var/run/mysqld
$ sudo chown mysql:mysql /var/run/mysqld

Once ready manually start MySQL with the following linux command and options:

$ sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
$ [1] 2708

Confirm that the process is running as expected:

$ jobs
[1]+  Running     sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &

Max connections

MySQL my.cnf :

[mysqld]
max_connections=1000 

Affiche le nombre de connections actives :

show global status like "Threads_connected";

ou

show status where `variable_name` = 'Threads_connected';

mysql> show global status;
mysql> show status like '%onn%';
mysql> show processlist;
mysql> SHOW VARIABLES;

# SHOW VARIABLES is subject to a version-dependent display-width limit.
# For variables with very long values that are not completely displayed, use SELECT as a workaround.
# For example: 
SELECT @@GLOBAL.innodb_data_file_path;

SHOW VARIABLES LIKE 'max_join_size';
SHOW SESSION VARIABLES LIKE 'max_join_size';
SHOW VARIABLES LIKE '%size%';
SHOW GLOBAL VARIABLES LIKE '%size%';

https://dev.mysql.com/doc/refman/8.0/en/slow-query-log.html

Slow queries

Activation du Slow Query log : mysql -u root -p

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 5;
SET GLOBAL slow_query_log_file = '/var/lib/mysql/sc-zbx01-slow.log';

Plus facile de lire le log avec :

mysqldumpslow /var/lib/mysql/server-slow.log

Arrêter ça :

SET GLOBAL slow_query_log = 'OFF';

Taille des logs d'InnoDB

Document pour augmenter la taille des "Redo Log Files" d'InnoDB.

Il peut y avoir plusieurs raisons pour modifier la taille d'InnoDB, en voici une, c'est une erreur dans /var/log/mysql/error.log :

  InnoDB: ERROR: the age of the last checkpoint is 9433586,
  InnoDB: which exceeds the log group capacity 9433498.
  InnoDB: If you are using big BLOB or TEXT rows, you must set the
  InnoDB: combined size of log files at least 10 times bigger than the
  InnoDB: largest such row.

Procédure

Pour modifier la taille des fichiers de journalisation InnoDB dans MySQL 5.6.7 ou version antérieure, procédez comme suit :

1. Innodb_fast_shutdown doit être défini à 0 :

  mysql> SHOW VARIABLES LIKE 'innodb_fast_shutdown';  # Pour voir la configuration actuelle.
  mysql> SET GLOBAL innodb_fast_shutdown = 0;

ou

  mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"

0 - InnoDB performs a slow shutdown, including full purge (before MariaDB 10.3.6, not always, due to MDEV-13603) and change buffer merge. Can be very slow, even taking hours in extreme cases.
0 - InnoDB effectue un arrêt lent, y compris une purge complète (avant MariaDB 10.3.6, pas toujours, à cause de MDEV-13603) et change la fusion du tampon. Peut être très lent, voire prendre des heures dans les cas extrêmes.

1. Après vous être assuré que innodb_fast_shutdown est défini à 0, arrêtez le serveur MySQL et assurez-vous qu'il s'arrête sans erreur (pour vous assurer qu'il n'y a pas d'informations sur les transactions en suspens dans le journal).

  systemctl stop mysql

1. Copiez les anciens fichiers journaux dans un endroit sûr au cas où quelque chose n'allait pas pendant l'arrêt et que vous en auriez besoin pour récupérer le tablespace.

  cp /var/lib/mysql/ib_logfile* /TEMP/

1. Supprimez les anciens fichiers journaux du répertoire des fichiers journaux actuellement utilisés.

  rm /var/lib/mysql/ib_logfile*

1. Éditer my.cnf pour modifier la configuration du fichier journal. vim /etc/mysql/my.cnf

  [mysqld]
  innodb_log_buffer_size = 32M # En date du 2020-08, par défaut c'est 16M et avec SkySQL c'est 67M.
  innodb_buffer_pool_size = 2G # ***En date du 2020-08, par défaut c'est 128M et avec SkySQL c'est 7516M (7.5G).
  innodb_log_file_size = 96M # En date du 2020-08, par défaut c'est 96M et avec SkySQL c'est 4295M (4.3G).

InnoDB does all its caching in a the buffer pool, whose size is controlled by innodb_buffer_pool_size.
If only using InnoDB, set innodb_buffer_pool_size to 70% of available RAM. J'ai été prudent avec 50% de RAM.

Détail de chaque paramètres, en date du 2020-08 : innodb_log_buffer_size

  Description: Size in bytes of the buffer for writing InnoDB redo log files to disk.
                    Increasing this means larger transactions can run without needing to perform disk I/O before committing.
  Commandline: --innodb-log-buffer-size=#
  Scope: Global
  Dynamic: No
  Data Type: numeric
  Default Value: 16777216 (16MB) >= MariaDB 10.1.9, 8388608 (8MB) <= MariaDB 10.1.8
  Range: 262144 to 4294967295 (256KB to 4096MB) 

innodb_buffer_pool_size

  Description: InnoDB buffer pool size in bytes. The primary value to adjust on a database server with entirely/primarily XtraDB/InnoDB tables, can be set up to 80% of the total memory in these environments.
                    If set to 2 GB or more, you will probably want to adjust innodb_buffer_pool_instances as well.
                    See the XtraDB/InnoDB Buffer Pool for more on setting this variable, and also Setting Innodb Buffer Pool Size Dynamically if doing so dynamically.
  Commandline: --innodb-buffer-pool-size=#
  Scope: Global
  Dynamic: Yes (>= MariaDB 10.2.2), No (<= MariaDB 10.2.1)
  Data Type: numeric
  Default Value: 134217728 (128MB)
  Range: 5242880 (5MB) to 9223372036854775807 (8192PB) 

innodb_log_file_size

  Description: Size in bytes of each InnoDB redo log file in the log group.
                    The combined size can be no more than 512GB.
                    Larger values mean less disk I/O due to less flushing checkpoint activity, but also slower recovery from a crash.
  Commandline: --innodb-log-file-size=#
  Scope: Global
  Dynamic: No
  Data Type: numeric
  Default Value: 100663296 (96MB) (>= MariaDB 10.5), 50331648 (48MB) (<= MariaDB 10.4)
  Range: 1048576 to 512GB (1MB to 512GB) 

1. Redémarrez le serveur MySQL. mysqld voit qu'aucun fichier journal InnoDB n'existe au démarrage et en crée des nouveaux.

  systemctl start mysql

1. Assurez-vous qu'il n'y a pas d'erreur dans les logs.

Tools to Manage and Monitor MySQL Servers

Webyog : https://www.webyog.com/

Percona Toolkit : https://www.percona.com/software/database-tools/percona-toolkit PERCONA MONITORING AND MANAGEMENT : https://www.percona.com/software/database-tools/percona-monitoring-and-management

orchestrator : https://github.com/github/orchestrator

gh-ost : https://github.com/github/gh-ost

VividCortex : https://www.vividcortex.com/

mysql-slow-query-log-visualizer : https://github.com/benkaiser/mysql-slow-query-log-visualizer

Navicat Monitor for MySQL : https://www.navicat.com/en/products/navicat-monitor

List Databases Size

mysql> SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
+--------------------+-----------+
| Database           | Size (MB) |
+--------------------+-----------+
| bookstack          |     18.27 |
| information_schema |      0.16 |
| mysql              |      2.51 |
| performance_schema |      0.00 |
| sys                |      0.02 |
+--------------------+-----------+
5 rows in set (0.23 sec)

List Table Sizes From a Single Database

  SELECT
    TABLE_NAME AS `Table`,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) AS `Size (MB)`
  FROM
    information_schema.TABLES
  WHERE
    TABLE_SCHEMA = "zonedev1"
  ORDER BY
    (DATA_LENGTH + INDEX_LENGTH)
  DESC;

character and collation

MariaDB [zonedev1]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "information_schema"
  AND T.table_name = "mod_robojournal_contact";

SELECT character_set_name FROM information_schema.`COLUMNS` 
WHERE table_schema = "information_schema"
  AND table_name = "mod_ms_azure_usage"
  AND column_name = "obj";

  mysqladmin -p proc stat

  mysql> show processlist;
  mysql> status

Slow queries :

  [mysqld]
  slow_query_log=1
  slow_query_log_file=/var/lib/mysql/slowquery.log
 long_query_time=5

Consultation :

  mysqldumpslow -a /var/lib/mysql/slowquery.log

Informations

https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/

https://www.digitalocean.com/community/tutorials/how-to-measure-mysql-query-performance-with-mysqlslap

MySQL Zabbix

SELECT * FROM hosts INTO OUTFILE '/var/lib/mysql-files/hosts.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

--- Dropping all tables

( mysqldump --add-drop-table --no-data -u root -p my_db | grep 'DROP TABLE' ) > ./drop_all_tables_my_db.sql
mysql -u root -p my_db < ./drop_all_tables_my_db.sql

SELECT concat('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = my_db';

#!/bin/bash

#usage: mysql-drop-all-tables -d my_db -u dbuser -p dbpass

TEMP_FILE_PATH='./drop_all_tables.sql'

while getopts d:u:p: option
do
    case "${option}"
    in
    d) DBNAME=${OPTARG};;
    u) DBUSER=${OPTARG};;
    p) DBPASS=${OPTARG};;
    esac
done

echo "SET FOREIGN_KEY_CHECKS = 0;" > $TEMP_FILE_PATH
( mysqldump --add-drop-table --no-data -u$DBUSER -p$DBPASS $DBNAME | grep 'DROP TABLE' ) >> $TEMP_FILE_PATH
echo "SET FOREIGN_KEY_CHECKS = 1;" >> $TEMP_FILE_PATH
mysql -u$DBUSER -p$DBPASS $DBNAME < $TEMP_FILE_PATH
rm -f $TEMP_FILE_PATH