Quick script:

SET FOREIGN_KEY_CHECKS = 0; 
SET @tables = NULL;
SELECT GROUP_CONCAT('`', table_schema, '`.`', table_name, '`')     INTO @tables
  FROM information_schema.tables 
  WHERE table_schema = 'database_name'; -- specify DB name here.

SET @tables = CONCAT('DROP TABLE ', @tables);
PREPARE stmt FROM @tables;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET FOREIGN_KEY_CHECKS = 1; 

Pense-bête

mysql -u username -p database_name < file.sql

Sur mon système qui n'a pas apparmor, mysqld démarré en ligne de commande, mais pas via systemd.
Systemd a des fonctionnalités pour protéger certains filesystem :

Change variable "ProtectHome" by "false" on "/etc/systemd/system/mysqld.service"
Reload systemd by "sudo systemctl daemon-reload"

Et pour ne pas perdre ses modifs, suivre plutôt ce lien : https://dba.stackexchange.com/questions/185302/mysql-mariadb-cannot-change-default-datadir-on-debian-9-1-server/191174

"According to Debian policy rules, you should set this option value in a custom file like /etc/systemd/system/mariadb.service.d/YOUR_CUSTOM_FILE.conf containing:"

[Service]

# Prevent accessing /home, /root and /run/user
ProtectHome=false

Les tables temporaires dans mysql

Les variables innodb

– durability setting ( innodb_flush_log_at_trx_commit = 0 or 1 ) ?
– do we use binary logs ( I used ROW based replication for 5.1)
– do we have sync_binlog options.

• innodb size :
  https://www.percona.com/blog/2015/06/02/80-ram-tune-innodb_buffer_pool_size/

En ligne de commande :
mysqlcheck --repair --all-databases
mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Autre piste : http://stackoverflow.com/questions/8843776/mysql-table-is-marked-as-crashed-and-last-automatic-repair-failed
myisamchk -r ch_posts

En arrêtant la base (https://mariadb.com/blog/how-reset-root-password-mariadb-linux)

1. stop mysql

2. reset password

mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
mysql> use mysql;
mysql> alter root IDENTIFIED BY "new-password";
mysql> alter user squash@localhost IDENTIFIED BY "new-password";
mysql> flush privileges;
mysql> exit

3. mysql restart

mysqladmin

mysqladmin -u root -pCURRENTPASSWORD password <nouveau>

mysql / mariadb performance tip

Recherche fulltext avec caractères spéciaux

show variables like "max_connections";

Directories & start

Fichier de configuration

https://mariadb.com/kb/en/mariadb/configuring-mariadb-with-mycnf/

Répertoires

cat /etc/mysql/mariadb.conf.d/50-server.cnf | egrep "datadir|socket.*=|log_file|log_error|log_bin"

database

mysql> show databases;
mysql> create database [databasename];
mysql> use [db name];

mysqladmin

http://www.thegeekstuff.com/2009/01/15-practical-usages-of-mysqladmin-command-for-administering-mysql-server/

mysqladmin -u <username> -h <hostname.blah.org> -p<password> 'new-password'>
mysqladmin -u root -p<password> ping
mysqladmin -u root -p<password> status
mysqladmin -u root -p<password> extended-status
mysqladmin -u root -p<password> variables
mysqladmin -u root -p<password> create <testdb>
mysqladmin -u root -p<password> reload; # reload grant tables
mysqladmin -u root -p<password> shutdown

create user

mysql -u root -p
mysql> use mysql;
mysql> SELECT * FROM user;
mysql> CREATE USER 'username'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'username'@'%' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'username'@'%' 
mysql> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' 
mysql> flush privileges; 

Innodb ou myisam

mysql> SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='your_table_name' AND TABLE_SCHEMA='your_database_name';

cat /etc/my.cnf | grep default-storage-engine

Creation et droit du user :

GRANT LOCK TABLES, SELECT ON *.* TO 'BACKUPUSER'@'%' IDENTIFIED BY 'PASSWORD';
flush privileges;
bye;

Et ensuite :

mysqldump -u BACKUPUSER -pPASSWORD --all-databases > all-database.sql

pour afficher les informations qui sont tracées dans le schéma de performance.

select * from setup_consumers; 

events_stages_current   NO
events_stages_history   NO
events_stages_history_long  NO
events_statements_current   YES
events_statements_history   NO
events_statements_history_long  NO
events_waits_current    NO
events_waits_history    NO
events_waits_history_long   NO
global_instrumentation  YES
thread_instrumentation  YES
statements_digest   YES

Vider les tables du schéma de performances :

truncate table events_statements_current; 
truncate table events_statements_history; 
truncate table events_statements_summary_by_digest;

• dans la page, affichage des requêtes pendant 60 secondes :

use performance_schema;
update setup_consumers set enabled='YES' where name IN ('events_statements_history','events_statements_current','statements_digest');
truncate table events_statements_current; truncate table events_statements_history; truncate table events_statements_summary_by_digest;
do sleep(60);
select now(),(count_star/(select sum(count_star) FROM events_statements_summary_by_digest) * 100) as pct, count_star, left(digest_text,150) as stmt, digest from events_statements_summary_by_digest order by 2 desc;
update setup_consumers set enabled='NO' where name IN ('events_statements_history','events_statements_current','statements_digest');

Un ensemble de requêtes pour utiliser le schéma de perf :
http://www.markleith.co.uk/ps_helper/#statements_with_runtimes_in_95th_percentile
http://www.markleith.co.uk/ps_helper/#statements_with_full_table_scans

Pour les requêtes :

SELECT * FROM performance_schema.events_statements_summary_by_digest;

Top par SUM :

SELECT schema_name, digest, digest_text, count_star, round(AVG_TIMER_WAIT/1000000000), sum_errors, sum_warnings, sum_rows_affected, sum_rows_sent, sum_rows_examined FROM `events_statements_summary_by_digest` ORDER BY `events_statements_summary_by_digest`.`SUM_TIMER_WAIT`  DESC

Top par SUM avec Full Scan :

SELECT DIGEST_TEXT AS query,
      IF(SUM_NO_GOOD_INDEX_USED > 0 OR SUM_NO_INDEX_USED > 0, '*', '') AS full_scan,
      COUNT_STAR AS exec_count,
      SUM_ERRORS AS err_count,
      SUM_WARNINGS AS warn_count,
      ROUND(SEC_TO_TIME(SUM_TIMER_WAIT/1000000000000)) AS exec_time_total_second,
      ROUND(SEC_TO_TIME(MAX_TIMER_WAIT/1000000000000)) AS exec_time_max_second,
      ROUND((AVG_TIMER_WAIT/1000000000)) AS exec_time_avg_ms,
      SUM_ROWS_SENT AS rows_sent,
      ROUND(SUM_ROWS_SENT / COUNT_STAR) AS rows_sent_avg,
      SUM_ROWS_EXAMINED AS rows_scanned,
      DIGEST AS digest
FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 15

des hints pour la performance :

• index inutilisés (SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;)
• table temporaire

mariadb et mysql côte à côte...

Pour droper tous les schémas d'un coup (avant un restore par exemple)
(Ajout de autour du nom de la table") mysql -uroot -p<password> -e "show databases" | grep -v Database | grep -v mysql| grep -v information_schema | grep -v performance_schema | grep -v test|gawk '{print "drop database" $1 "`;select sleep(0.1);"}' | mysql -uroot -p<password>

Single base

# backup 
mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
# restore 
mysql -u root -p[root_password] [database_name] < dumpfilename.sql

All bases :

# backup 
mysqldump -u root -ptmppassword --all-databases > /tmp/all-database.sql
# restore 
mysql -u root -p[root_password] < dumpfilename.sql

!!! Les schémas doivent être vides avant le restore !!!