MySQL

Test Tag Extra3

Extra Info zu Mysql: ist ein DB System

MySQL Full Processlist - Watch live every 0.5 seconds (from Command-Line)

Submitted by Gast (nicht überprüft) on Di, 02/02/2021 - 19:21

Watch MySQL full-processlist every 0.5 seconds :


while : ; do mysql -u MY_USERNAME --password='MY_PASSWORD' -e "show full processlist"; sleep 0.5; done 

 

Save full-processlist to log


while : ; do mysql -u MY_USERNAME --password='MY_PASSWORD' -e "show full processlist"; sleep 0.5; done >> mysql-proc.log

 

MySQL / MariaDB : alle DBs schnell exportieren

Submitted by Gast (nicht überprüft) on Sa, 12/05/2020 - 04:23

Mysql export kann sehr lange dauert, wenn man viele und große Datenbanken hat. Damit man schnell ein Backup von allen DBs erstellen kann:

mysqldump --opt --quick --all-databases > /root/backup/mysql/alldbs.sql
  
  

Wenn User login notwendig ist :

mysqldump -u root -p --opt --quick --all-databases > /root/backup/mysql/alldbs.sql

 

[Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade

Submitted by Gast (nicht überprüft) on Fr, 07/31/2020 - 17:27

Problem:

[Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

Lösung:

mysql_upgrade --user=your_db_user_name --password

This installation of MariaDB is already upgraded to 10.4.13-MariaDB, use --force if you still need to run mysql_upgrade
 

MySQL Query - Delete All Tables from a Database

Submitted by Gast (nicht überprüft) on Mo, 06/01/2020 - 13:58

SQL Query:

 SELECT CONCAT('DROP TABLE ', table_schema, '.', table_name, ';') AS 'command' FROM information_schema.tables WHERE table_schema = 'MyDatabaseName' INTO OUTFILE '/tmp/sql_delete_all_tables'; 
SOURCE  /tmp/sql_delete_all_tables;

No access to "/tmp"?

 SELECT CONCAT('DROP TABLE ', table_schema, '.', table_name, ';') AS 'command' FROM information_schema.tables WHERE table_schema = 'MyDatabaseName' INTO OUTFILE './sql_delete_all_tables'; 
SOURCE  ./sql_delete_all_tables;

 

Mysqldump - Backup Database to .gz with Date / TimeStamp

Submitted by Gast (nicht überprüft) on Sa, 03/07/2020 - 14:00

Wenn man alle Datenbanken sichern möchte (mit  Datum in Dateiname), Befehl könnte so aussehen:

 mysqldump -u myusername -p --all-databases | gzip -c > all-dbs-$(date +%Y-%m-%d-%H%M).sql.gz

Nur ein Datenbank:

 

 mysqldump -u myusername -p myDbName | gzip -c > mydbname-$(date +%Y-%m-%d-%H%M).sql.gz