====== MySQL - MariaDB =======
===== Création rapide de base =====
=== Login ===
<code bash>mysql -u mysql_user -p</code>
On rentre le pass.
=== On crée la base ===
<code bash>create database db_name;</code>
=== On vérifie qu'elle est bien créée ===
<code bash>show databases;</code>
=== On crée un utilisateur ===
<code bash>create user db_user;</code>
=== On donne les droits et un pass ===
<code bash>grant all on db_name.* to 'db_user'@'localhost' identified by 'db_password';</code>
=== Changer pass root ===
Check version MySQL :
<code bash>mysql --version</code>
Se connecter :
<code bash>mysql -u root -p</code>
MySQL > 5.7.6
<code bash>ALTER USER 'root'@'localhost' IDENTIFIED BY 'pass';</code>
MySQL < 5.7.6
<code bash>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pass');</code>
===== Création d'un utilisateur dédié à la sauvegarde =====
<code mysql>CREATE USER 'user-backup'@'localhost' IDENTIFIED BY 'user-backup-password';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'user-backup'@'localhost';
GRANT LOCK TABLES ON *.* TO 'user-backup'@'localhost';</code>
Via https://www.system-linux.eu/index.php?post/2017/05/01/MariaDB-Cr%C3%A9er-un-utilisateur-ayant-les-droits-minimum-juste-pour-de-la-sauvegarde
===== Sauvegarde MySql =====
Toutes les bases :
<code bash>mysqldump -u mon_user -pMonPassword --all-databases > output.sql</code>
Pour sauvegarder une base de données précise :
<code bash>mysqldump -u mon_user -pMonPassword --databases nom_de_la_base > output.sql</code>
Pour sauvegarder plusieurs bases de données :
<code bash>mysqldump -u mon_user -pMonPassword --databases nom_de_la_base_1 nom_de_la_base_2 > output.sql</code>
Pour sauvegarder une table précise :
<code bash>mysqldump -u mon_user -pMonPassword --databases nom_de_la_base --tables nom_de_la_table > output.sql</code>
Pour sauvegarder plusieurs tables :
<code bash>mysqldump -u mon_user -pMonPassword --databases nom_de_la_base --tables nom_de_la_table_1 nom_de_la_table_2 > output.sql</code>
===== Drop =====
Pour rajouter la demande de suppression de DB si elle existe avant de la recréer, utiliser '' --add-drop-dabatase''.
===== Restauration MySql =====
<code bash>mysql -u mon_user -pMonPassword < input.sql</code>
<code bash>mysql -u mon_user -pMonPassword nom_de_la_base < input.sql</code>

===== Avec Compression =====
Sauvegarde :
<code bash>mysqldump -u mon_user -pMonPassword --all-databases | gzip > output.sql.gz</code>
<code bash>mysqldump -u mon_user -pMonPassword --all-databases | bzip2 > output.sql.bz2</code>
Restauration :
<code bash>gunzip < input.sql.gz | mysql -u mon_user -pMonPassword</code>
<code bash>bunzip2 < input.sql.bz2 | mysql -u mon_user -pMonPassword</code>
===== Dump toutes les bases avec exceptions =====
On ne sauvegarde pas les DB : ''Database'', ''phpmyadmin'', ''information_schema'', ''mysql'' :
<code bash>echo 'show databases;' | mysql -u root -pMyPass | grep -v ^Database$ | grep -v ^phpmyadmin$ | grep -v ^information_schema$ | grep -v ^mysql$ | grep -v -F db1 | xargs mysqldump -u root -pMyPass --databases | bzip2 > my_db.sql.bz2</code>
===== Réplication Master/Slave =====
Voir : https://www.cyberciti.biz/faq/how-to-set-up-mariadb-master-slave-replication-with-ssl-on-ubuntu-linux/

Rajouter : https://mariadb.com/resources/blog/goodbye-replication-lag
===== MariaDB sans password à l'installation =====
MariaDB qui ne demande pas de password à l'installation, et du coup, impossibilité d'utiliser PHPMyAdmin par exemple :
<code bash>sudo mysql -u root
[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q</code>
Le tout pour supprimer la connexion par plugin pour root.
===== Optimisations =====
Sous Debian 9, on éditera le fichier : ''/etc/mysql/my.cnf''.\\
Avant tout, on va tester la configuration active avec MySQLTuner-perl (''https://github.com/major/MySQLTuner-perl''):
<code bash>wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl
</code>
===== Slow Query =====
Analyser quelles requêtes prennent du temps à l’exécution sur votre serveur, pour l'optimiser.\\
Mise en place en éditant le fichier de configuration :
<code bash>nano /etc/mysql/my.cnf</code>
Décommentez les lignes suivantes :
<code bash>#En changeant - si besoin - l'emplacement du fichier de log
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log      = 1
long_query_time = 1</code>
Relancer l'instance :
<code bash>service mysql restart</code>
Enfin, un outil existe pour aider à analyser le tout :
<code bash>#changer le chemin du log si besoin
mysqldumpslow /var/log/mysql/mysql-slow.log</code>
===== Les options qui vont bien =====
  * **default_storage_engine** : Choix du moteur de DB : ''InnoDB''. Parce que.
  * **max_connections** : Par défaut, ''151''. Augmentez si besoin.
  * **max_allowed_packet = ** : ''64M'' taille max d'un paquet pour être traité.
  * **innodb_buffer_pool_size** : Idéalement, si serveur dédié aux DB, dans les ''80% de la RAM totale'', pour le laisser respirer, si partagé avec d'autres services, limiter à votre convenance, tout en sachant que c'est l'attribut principal pour un serveur MySQL full InnoDB.
  * **innodb_log_file_size** : Les logs InnoDB, tenter ''1/4 de la valeur précédente'', ''innodb_buffer_pool_size'' (Encore pour un lecteur RSS avec écritures intensives, on va tenter de le maxer si possible).
  * **innodb_flush_method** : ''O_DIRECT'' par défaut si sous Linux, dépend si supporté par le système sinon.
  * **innodb_file_per_table** : ''1'', oui, plus pour l'optimisation que pour les performances (opérations simples sur les tables, par exemple).
  * **innodb_checksum_algorithm** : ''crc32'' (avec MySQL > 5.7.7), le choix de l'algorithme de vérification.
  * **sort_buffer_size** : ''4M'' pour 4Mo, mais attention, c'est de la valeur par connexion, ça peut faire mal si trop haut. Très mal.
  * **skip_name_resolve** : Sur ''1'', on gagne du temps en cas de serveur DNS lent.
  * **query_cache_type** : ''OFF'' : on utilisera le cache que dans de très rares cas, avec peu d'écritures (si on compte gérer une DB d'un lecteur RSS avec beaucoup de flux, on oublie le cache).
  * **query_cache_size** : ''0'' pour aller avec la valeur précédente.
  * **binlog_format** : ''mixed'', requis si Nextcloud.
  * **table_open_cache = ** : ''2000'' : nombre de tables en cache (2000 est la base sous MariaDB, avant 400).
  * **table_definition_cache = ** : ''2000'' même chose qu'au dessus, mais cette fois-ci pour les définitions des tables (schémas).

<code bash /etc/mysql/mariadb.conf.d/50-custom.cnf>
[mysqld]
#General
max_allowed_packet = 64M
max_connections = 2000
table_open_cache = 2000
table_definition_cache = 2000
open_files_limit = 10000
tmp_table_size = 64M
max_heap_table_size = 64M
tmpdir = /tmp
thread_cache_size = 100
default_storage_engine = InnoDB
skip_name_resolve
query_cache_type=0
query_cache_size=0
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M

#Binary log
server_id = 1
max_binlog_size = 100M
expire_logs_days = 7
sync_binlog = 0
binlog_format = MIXED
log_bin = /var/log/mysql/mysql-bin.log

#InnoDB
innodb_buffer_pool_size = 8G
innodb_log_file_size = 2G
innodb_log_buffer_size = 16M
innodb_flush_method = O_DIRECT
innodb_file_per_table
innodb_checksum_algorithm = crc32
#InnoDB Bonus
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency = 8
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000
innodb_stats_on_metadata = 0
innodb_buffer_pool_dump_at_shutdown  = 1
innodb_buffer_pool_load_at_startup  = 1
innodb_buffer_pool_dump_pct = 75
innodb_read_io_threads = 16
innodb_write_io_threads = 16

#Slow query log
slow_query_log_file     = /var/log/mysql/mariadb-slow.log
long_query_time = 1.0
slow_query_log = 1
</code>
Sources :
  * [[https://github.com/major/MySQLTuner-perl]]\\
  * [[http://www.speedemy.com/17-key-mysql-config-file-settings-mysql-5-7-proof/]]\\
  * [[https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/]]\\
===== Déplacer le dossier des données MySQL/MariaDB =====
Commencer par stopper le serveur :
<code bash>service mysql stop</code>
Créer le dossier qui va accueillir les données :
<code bash>mkdir /home/user/mysql</code>

Copy over ONLY the database folders:

<code bash>cp -R /var/lib/mysql /array2/mysql
cp -R /var/lib/mysql/users /array2/mysql</code>

Backup the my.cnf file:

<code bash>cp /etc/mysql/my.cnf /root/my.cnf.backup</code>

Edit the my.cnf file:

<code bash>nano /etc/mysql/my.cnf</code>
Change all mentions of the old datadir and socket to your new location

<code bash /etc/mysql/my.cnf>datadir=/home/user/mysql
socket=/home/user/mysql/mysql.sock</code>

Update the directory permissions:

<code bash>chown -R mysql:mysql /home/user/mysql</code>

Rename the old directory:

<code bash>mv /var/lib/mysql /var/lib/mysql-old</code>

Create a symlink, just in case:

<code bash>ln -s /home/user/mysql /var/lib/mysql </code>

Let AppArmor know about the new datadir:

<code bash>echo "alias /var/lib/mysql/ -> /home/user/mysql/," >> /etc/apparmor.d/tunables/alias</code>

Reload the apparmor profiles

<code bash>sudo /etc/init.d/apparmor reload</code>

Then start mysql:

<code bash>start mysql</code>

Via :
  * http://askubuntu.com/questions/137424/how-do-i-move-the-mysql-data-directory
  * http://www.tecmint.com/change-default-mysql-mariadb-data-directory-in-linux/
===== MyCLI un client console =====
Via https://www.tecmint.com/mycli-mysql-client-with-auto-completion-syntax-highlighting/\\
\\
Installation via :
<code bash>apt-get update
apt-get install myclil</code>
Utilisation :
<code bash>mycli -u <user> -h <server>
mycli mysql://<user>@<server>:<port>/<DB></code>
Le site officiel et sa documentation complète : http://mycli.net/index