AdminSysGNULinux

Outils pour utilisateurs

Outils du site

Piste : • notes_ci_cd • notes_hsm • notes_optique_image_ocr • notes_xorg • notes_-_kubernetes • notes_grub • solutions_serverless • notes_mysql
tech:notes_mysql

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 :

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
/etc/pam.d/common-session
session required pam_limits.so
/etc/mysql/my.cnf
[mysqld]
open_files_limit = 10240
tech/notes_mysql.1742825205.txt.gz · Dernière modification : de 127.0.0.1
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki