MySQL - MariaDB

mysql -u mysql_user -p

On rentre le pass.

create database db_name;

show databases;

create user db_user;

grant all on db_name.* to 'db_user'@'localhost' identified by 'db_password';

Check version MySQL :

mysql --version

Se connecter :

mysql -u root -p

MySQL > 5.7.6

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

MySQL < 5.7.6

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pass');

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

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

Toutes les bases :

mysqldump -u mon_user -pMonPassword --all-databases > output.sql

Pour sauvegarder une base de données précise :

mysqldump -u mon_user -pMonPassword --databases nom_de_la_base > output.sql

Pour sauvegarder plusieurs bases de données :

mysqldump -u mon_user -pMonPassword --databases nom_de_la_base_1 nom_de_la_base_2 > output.sql

Pour sauvegarder une table précise :

mysqldump -u mon_user -pMonPassword --databases nom_de_la_base --tables nom_de_la_table > output.sql

Pour sauvegarder plusieurs tables :

mysqldump -u mon_user -pMonPassword --databases nom_de_la_base --tables nom_de_la_table_1 nom_de_la_table_2 > output.sql

Pour rajouter la demande de suppression de DB si elle existe avant de la recréer, utiliser –add-drop-dabatase.

mysql -u mon_user -pMonPassword < input.sql

mysql -u mon_user -pMonPassword nom_de_la_base < input.sql

Sauvegarde :

mysqldump -u mon_user -pMonPassword --all-databases | gzip > output.sql.gz

mysqldump -u mon_user -pMonPassword --all-databases | bzip2 > output.sql.bz2

Restauration :

gunzip < input.sql.gz | mysql -u mon_user -pMonPassword

bunzip2 < input.sql.bz2 | mysql -u mon_user -pMonPassword

On ne sauvegarde pas les DB : Database, phpmyadmin, information_schema, mysql :

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

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 qui ne demande pas de password à l'installation, et du coup, impossibilité d'utiliser PHPMyAdmin par exemple :

sudo mysql -u root
[mysql] use mysql;
[mysql] update user set plugin='' where User='root';
[mysql] flush privileges;
[mysql] \q

Le tout pour supprimer la connexion par plugin pour root.

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):

wget http://mysqltuner.pl/ -O mysqltuner.pl
perl mysqltuner.pl

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 :

nano /etc/mysql/my.cnf

Décommentez les lignes suivantes :

#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

Relancer l'instance :

service mysql restart

Enfin, un outil existe pour aider à analyser le tout :

#changer le chemin du log si besoin
mysqldumpslow /var/log/mysql/mysql-slow.log

• 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).

[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

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/
  

Commencer par stopper le serveur :

service mysql stop

Créer le dossier qui va accueillir les données :

mkdir /home/user/mysql

Copy over ONLY the database folders:

cp -R /var/lib/mysql /array2/mysql
cp -R /var/lib/mysql/users /array2/mysql

Backup the my.cnf file:

cp /etc/mysql/my.cnf /root/my.cnf.backup

Edit the my.cnf file:

nano /etc/mysql/my.cnf

Change all mentions of the old datadir and socket to your new location

datadir=/home/user/mysql
socket=/home/user/mysql/mysql.sock

Update the directory permissions:

chown -R mysql:mysql /home/user/mysql

Rename the old directory:

mv /var/lib/mysql /var/lib/mysql-old

Create a symlink, just in case:

ln -s /home/user/mysql /var/lib/mysql 

Let AppArmor know about the new datadir:

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

Reload the apparmor profiles

sudo /etc/init.d/apparmor reload

Then start mysql:

start mysql

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/

Via https://www.tecmint.com/mycli-mysql-client-with-auto-completion-syntax-highlighting/

Installation via :

apt-get update
apt-get install myclil

Utilisation :

mycli -u <user> -h <server>
mycli mysql://<user>@<server>:<port>/<DB>

Le site officiel et sa documentation complète : http://mycli.net/index