Différences

Ci-dessous, les différences entre deux révisions de la page.

Lien vers cette vue comparative

system:linux:mysql-mariadb [2018/04/05 16:18]
system:linux:mysql-mariadb [2018/07/29 19:07] (Version actuelle)
Ligne 1: Ligne 1:
 +====== 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