Ceci est une ancienne révision du document !
Table des matières
Notes MySQL / MariaDB
Liens :
Réplication :
Voir le client mycli en ligne de commande avec autocomplétion et coloration syntaxique http://blog.adminrezo.fr/2016/01/mycli-pgcli-mysql-postregsql-clients/
Voir aussi TokuDB :
- /etc/mysql/conf.d/tokudb.cnf
DSN : mysql:host=localhost;dbname=strata
Administration
Connexion à une base
mysql -h localhost -P 3306 -u myuser -pP@ssw0rd -D mydb # Par socket Unix mysql -u root -S /opt/mysql/5.0.45/socket/mysql-5045.sock -p # En TCP mysql -u root -h 127.0.0.1 -p mysql -u root -h $HOSTNAME -p
Droit sur un DB, table
-- CREATE DATABASE `plop-plop` ; CREATE DATABASE mydb; CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'P@ssw0rd'; GRANT ALL PRIVILEGES ON mydb.* TO "myuser"@"localhost" IDENTIFIED BY 'P@ssw0rd'; FLUSH PRIVILEGES;
Droit sur les fichiers (import / export CSV par exemple)
GRANT FILE ON *.* TO 'myuser'@'localhost'; FLUSH PRIVILEGES;
Lister les comptes utilisateurs
SELECT USER FROM mysql.user; SELECT host, USER, password FROM mysql.user;
Sécurité : effacer les comptes root sans mot de passe
mysql -N <<< "DELETE FROM mysql.user WHERE user='root' AND password='' ;"
Lecture seul sur toutes les bases
GRANT SELECT ON *.* TO 'myuserro'@'localhost' IDENTIFIED BY 'P@ssw0rd'; FLUSH PRIVILEGES;
Connaître les requêtes en cours
SHOW PROCESSLIST;
~/.my.cnf
[mysqldump] user=root password=P@ssw0rd! [mysql] user=root password=P@ssw0rd! [mysqladmin] user=root password=P@ssw0rd! [mysqlshow] user=root password=P@ssw0rd!
ou bien :
~/.my.cnf
[client] user=root password=P@ssw0rd!
C'est valable pour mysql, mysqldump etc..
chmod 600 ~/.my.cnf
bck-mysql.sh
#! /bin/bash set -o nounset DATE=$(date +%Y%m%d%H%M) BCK_DIR=~/backup DB_LIST=$(mysql --batch -N <<< """show databases;""" |egrep -v "^(information_schema|performance_schema|mysql)$") RET=$? mysqldump --single-transaction --skip-lock-tables information_schema > ${BCK_DIR}/information_schema-${DATE}.sql.gz RET=$((RET + $?)) mysqldump --single-transaction --skip-lock-tables performance_schema > ${BCK_DIR}/performance_schema-${DATE}.sql.gz RET=$((RET + $?)) mysqldump --single-transaction --ignore-table=mysql.event mysql > ${BCK_DIR}/mysql-${DATE}.sql.gz RET=$((RET + $?)) for DB in $DB_LIST do mysqldump --single-transaction $DB |pigz > ${BCK_DIR}/${DB}-${DATE}.sql.gz RET=$((RET + $?)) done if [ $(find $BCK_DIR -type f -iname "*.sql.gz" -size -10k |wc -l) -gt 0 ] then echo "Error empty file" >&2 RET=$((RET + 1)) fi exit $RET
Création ou recréation du dossier /var/lib/mysql
Par exemple après un export
mysqlcheck --all-databases -u root -pP@ssw0rd |pigz > all-dbs.sql.gz
chown mysql: /var/lib/mysql chmod 700 /var/lib/mysql # mysqld --initialize mysql_install_db mysql_secure_installation
Config my.cnf
Voir mysqltuner.pl
Sur RedHat par défaut le fichier my.cnf ne possède pas de configuration. Il faut partir d'un exemple
/etc/my.cnf{,.orig} cp -p /usr/share/mariadb/my-medium.cnf /etc/my.cnf
/etc/mysql/conf.d/lowmem.cnf
[mysqld] performance_schema = off key_buffer_size = 16K max_allowed_packet = 1M thread_stack = 64K table_cache = 4 sort_buffer_size = 64K net_buffer_length = 2K sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K innodb_buffer_pool_size = 16M innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer_size = 8M sort_buffer_size = 8M [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout
Pb
Error: Table './glpidb/glpi_logs' is marked as crashed and should be repaired
Solution
Vérif
df -hP df -hPi
Puis. Mais ne marche malheureusement pas pour les tables en innodb.
#mysqlcheck --repair --all-databases mysqlcheck --auto-repair --check -A
Erreur importation
mysql -h localhost -u myuser -D mydb -pmypass < /root/plop.sql
Warning: Using a password on the command line interface can be insecure. ERROR 1071 (42000) at line 52: Specified key was too long; max key length is 767 bytes
SET @@global.innodb_large_prefix = 1;
mysql -D mydb < /root/plop.sql
ERROR 1709 (HY000) at line 53: Index column size too large. The maximum column size is 767 bytes.
CREATE TABLE `SimpleSAMLphp_saml_LogoutStore` ( `_authSource` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, `_nameId` VARCHAR(40) COLLATE utf8mb4_unicode_ci NOT NULL, `_sessionIndex` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL, `_expire` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `_sessionId` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL, UNIQUE KEY `_authSource` (`_authSource`,`_nameId`,`_sessionIndex`), KEY `SimpleSAMLphp_saml_LogoutStore_expire` (`_expire`), KEY `SimpleSAMLphp_saml_LogoutStore_nameId` (`_authSource`,`_nameId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Ajout de ROW_FORMAT=Dynamic dans le CREATE TABLE
CREATE TABLE `SimpleSAMLphp_saml_LogoutStore` ( `_authSource` VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL, `_nameId` VARCHAR(40) COLLATE utf8mb4_unicode_ci NOT NULL, `_sessionIndex` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL, `_expire` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `_sessionId` VARCHAR(50) COLLATE utf8mb4_unicode_ci NOT NULL, UNIQUE KEY `_authSource` (`_authSource`,`_nameId`,`_sessionIndex`), KEY `SimpleSAMLphp_saml_LogoutStore_expire` (`_expire`), KEY `SimpleSAMLphp_saml_LogoutStore_nameId` (`_authSource`,`_nameId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=Dynamic;
Autres
Turn on the expanded table formatting mode. This is equivalent to the \x command. `psql` expanded mode equivalency for `mysql`
SELECT * FROM mytable \G;
Optimisation
Optimiser my.cnf
./mysqltuner.pl
Defrag All Tables or All Databases
mysqlcheck -A
Problème
Pb Invalid (old?) table or database name 'lost+found'
[ERROR] Invalid (old?) table or database name 'lost+found'
Solution
/etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld] ignore-db-dirs = lost+found
Error in accept: Too many open files
/var/log/mysql/error.log
190215 11:29:50 [ERROR] /usr/sbin/mysqld: Can't open file: './aquaprox/wp_options.frm' (errno: 24) 190215 11:30:16 [ERROR] /usr/sbin/mysqld: Can't open file: './logiindu/backupdb_wplogi_lstat.frm' (errno: 24) 190215 11:30:17 [ERROR] /usr/sbin/mysqld: Can't open file: './logiindu/backupdb_wplogi_lstat.frm' (errno: 24) 190215 11:30:18 [ERROR] /usr/sbin/mysqld: Can't open file: './logiindu/backupdb_wplogi_lstat.frm' (errno: 24) 190215 11:30:30 [ERROR] /usr/sbin/mysqld: Can't open file: './logiindu/backupdb_wplogi_lstat.frm' (errno: 24) 190215 11:30:31 [ERROR] /usr/sbin/mysqld: Can't open file: './logiindu/backupdb_wplogi_lstat.frm' (errno: 24) 190215 11:30:32 [ERROR] /usr/sbin/mysqld: Can't open file: './logiindu/backupdb_wplogi_lstat.frm' (errno: 24) 190215 11:31:40 [ERROR] Error in accept: Too many open files
Solution
mysql> SHOW VARIABLES LIKE 'open%' ; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 1024 | +------------------+-------+ 1 row in set (0.00 sec)
/etc/security/limits.d/50-mysql.conf
mysql hard nofile 11264 mysql soft nofile 11264
su - mysql -s /bin/bash ulimit -Hn ulimit -Sn
session required pam_limits.so
/etc/mysql/my.cnf
[mysqld] open_files_limit = 10240