AdminSysGNULinux

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

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

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

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

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

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;

Turn on the expanded table formatting mode. This is equivalent to the \x command. psql expanded mode equivalency for mysql

SELECT * FROM mytable \G; 

Optimiser my.cnf

./mysqltuner.pl

Defrag All Tables or All Databases

mysqlcheck -A

[ERROR] Invalid (old?) table or database name 'lost+found'

/etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
ignore-db-dirs  = lost+found

/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

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