AdminSysGNULinux

Outils pour utilisateurs

Outils du site

Piste :
tech:notes_mysql

Différences

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

Lien vers cette vue comparative

Les deux révisions précédentesRévision précédente
Prochaine révision		Révision précédente
tech:notes_mysql [2025/06/01 11:39] – Jean-Baptistetech:notes_mysql [2026/06/07 19:12] (Version actuelle) – modification externe 127.0.0.1
Ligne 1: Ligne 1:
 +<!DOCTYPE markdown>
 +{{tag>Brouillon DB Mysql}}
 +
 +# Notes MySQL / MariaDB
 +
 +Liens :
 +* http://www.linuxjournal.com/content/mysql%E2%80%94some-handy-know-how
 +
 +Réplication : 
 +* http://blog.adminrezo.fr/2016/01/replication-mysql-masterslave/
 +
 +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** :
 +* https://en.wikipedia.org/wiki/TokuDB
 +* /etc/mysql/conf.d/tokudb.cnf
 +* https://mariadb.com/kb/en/how-to-enable-tokudb-in-mariadb/
 +
 +DSN : mysql:host=localhost;dbname=strata
 +
 +
 +
 +## Administration
 +
 +Connexion à une base
 +~~~bash
 +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
 +~~~sql
 +-- 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)
 +~~~sql
 +GRANT FILE ON *.* TO 'myuser'@'localhost';
 +FLUSH PRIVILEGES;
 +~~~
 +
 +Lister les comptes utilisateurs
 +~~~sql
 +select user from mysql.user;
 +select host, user, password from mysql.user;
 +~~~
 +
 +Sécurité : effacer les comptes root sans mot de passe
 +~~~bash
 +mysql -N <<< "DELETE FROM mysql.user WHERE user='root' AND password=` ;"
 +~~~
 +
 +Lecture seul sur toutes les bases
 +~~~sql
 +GRANT SELECT ON *.* TO 'myuserro'@'localhost' IDENTIFIED BY 'P@ssw0rd';
 +FLUSH PRIVILEGES;
 +~~~
 +
 +Connaître les requêtes en cours
 +~~~sql
 +SHOW PROCESSLIST;
 +~~~
 +
 +`~/.my.cnf`
 +~~~ini
 +[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`
 +~~~ini
 +[client]
 +user=root
 +password=P@ssw0rd!
 +~~~
 +
 +C'est valable pour **mysql**, **mysqldump** etc..
 +
 +~~~bash
 +chmod 600 ~/.my.cnf
 +~~~
 +
 +`bck-mysql.sh`
 +~~~bash
 +#! /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 
 +~~~bash
 +mysqlcheck --all-databases -u root -pP@ssw0rd | pigz > all-dbs.sql.gz
 +~~~
 +
 +
 +~~~bash
 +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
 +~~~bash
 +/etc/my.cnf{,.orig}
 +cp -p /usr/share/mariadb/my-medium.cnf /etc/my.cnf
 +~~~
 +
 +`/etc/mysql/conf.d/lowmem.cnf`
 +~~~ini
 +[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
 +~~~bash
 +df -hP
 +df -hPi
 +~~~
 +
 +Puis. Mais ne marche malheureusement pas pour les tables en innodb.
 +~~~bash
 +#mysqlcheck --repair --all-databases
 +mysqlcheck --auto-repair --check -A
 +~~~
 +
 +
 +#### Erreur importation
 +
 +~~~bash
 +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
 +
 +~~~sql
 +SET @@global.innodb_large_prefix = 1;
 +~~~
 +
 +~~~bash
 +mysql -D  mydb < /root/plop.sql
 +~~~
 +ERROR 1709 (HY000) at line 53: Index column size too large. The maximum column size is 767 bytes.
 +
 +~~~sql
 +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`
 +~~~sql
 +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`
 +
 +~~~sql
 +SELECT * FROM mytable \G; 
 +~~~
 +
 +
 +### Optimisation
 +
 +Optimiser my.cnf
 +~~~bash
 +./mysqltuner.pl
 +~~~
 +
 +Defrag All Tables or All Databases
 +~~~bash
 +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`
 +~~~ini
 +[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
 +~~~
 +
 +~~~bash
 +su - mysql -s /bin/bash
 +
 +ulimit -Hn
 +ulimit -Sn
 +~~~
 +
 +
 +~~~
 +session required pam_limits.so
 +~~~
 +
 +`/etc/mysql/my.cnf`
 +~~~ini
 +[mysqld]
 +open_files_limit = 10240
 +~~~
 +
  
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki