{{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
<code 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
</code>

Droit sur un DB, table
<code 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;
</code>

Droit sur les fichiers (import / export CSV par exemple)
<code sql>
GRANT FILE ON *.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;
</code>

Lister les comptes utilisateurs
<code sql>
select user from mysql.user;
select host, user, password from mysql.user;
</code>

Sécurité : effacer les comptes root sans mot de passe
<code bash>
mysql -N <<< "DELETE FROM mysql.user WHERE user='root' AND password='' ;"
</code>

Lecture seul sur toutes les bases
<code sql>
GRANT SELECT ON *.* TO 'myuserro'@'localhost' IDENTIFIED BY 'P@ssw0rd';
FLUSH PRIVILEGES;
</code>

Connaître les requêtes en cours
<code sql>
SHOW PROCESSLIST;
</code>

''~/.my.cnf''
<code 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!
</code>

ou bien : 

''~/.my.cnf''
<code ini>
[client]
user=root
password=P@ssw0rd!
</code>

C'est valable pour **mysql**, **mysqldump** etc..

<code bash>
chmod 600 ~/.my.cnf
</code>

''bck-mysql.sh''
<code 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
</code>


=== Création ou recréation du dossier /var/lib/mysql

Par exemple après un export 
<code bash>
mysqlcheck --all-databases -u root -pP@ssw0rd |pigz > all-dbs.sql.gz
</code>


<code bash>
chown mysql: /var/lib/mysql
chmod 700 /var/lib/mysql

# mysqld --initialize
mysql_install_db

mysql_secure_installation
</code>


== 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
<code bash>
/etc/my.cnf{,.orig}
cp -p /usr/share/mariadb/my-medium.cnf /etc/my.cnf
</code>

''/etc/mysql/conf.d/lowmem.cnf''
<code 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

</code>


=== Pb

<code ->
Error: Table './glpidb/glpi_logs' is marked as crashed and should be repaired
</code>

Solution

Vérif
<code bash>
df -hP
df -hPi
</code>

Puis. Mais ne marche malheureusement pas pour les tables en innodb.
<code bash>
#mysqlcheck --repair --all-databases
mysqlcheck --auto-repair --check -A
</code>


==== Erreur importation

<code bash>
mysql -h localhost -u myuser -D  mydb -pmypass < /root/plop.sql
</code>
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

<code sql>
SET @@global.innodb_large_prefix = 1;
</code>

<code bash>
mysql -D  mydb < /root/plop.sql
</code>
ERROR 1709 (HY000) at line 53: Index column size too large. The maximum column size is 767 bytes.

<code 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;
</code>

Ajout de ''ROW_FORMAT=Dynamic'' dans le ''CREATE TABLE''
<code 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;
</code>


=== Autres

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

<code sql>
SELECT * FROM mytable \G; 
</code>


=== Optimisation

Optimiser my.cnf
<code bash>
./mysqltuner.pl
</code>

Defrag All Tables or All Databases
<code bash>
mysqlcheck -A
</code>


== Problème 

=== Pb Invalid (old?) table or database name 'lost+found'

<code ->
[ERROR] Invalid (old?) table or database name 'lost+found'
</code>


==== Solution

''/etc/mysql/mariadb.conf.d/50-server.cnf''
<code ini>
[mysqld]
ignore-db-dirs  = lost+found
</code>


=== Error in accept: Too many open files

''/var/log/mysql/error.log''
<code ->
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
</code>


==== Solution

<code ->
mysql> SHOW VARIABLES LIKE 'open%' ;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)
</code>

''/etc/security/limits.d/50-mysql.conf''
<code ->
mysql hard nofile 11264
mysql soft nofile 11264
</code>

<code bash>
su - mysql -s /bin/bash

ulimit -Hn
ulimit -Sn
</code>


<code ->
session required pam_limits.so
</code>

''/etc/mysql/my.cnf''
<code ini>
[mysqld]
open_files_limit = 10240
</code>