MySQL

drush sql-cli
drush sql:cli

select * from table1;
truncate table2;

Mysql Group Concat & Order By Rand()

SELECT group_concat(uid SEPARATOR ',') FROM (select uid from user_details where name='' order by rand() limit 3) tmp_t1

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 neu starten:

 service mysql restart || service mariadb restart || service mysqld restart  

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

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
 

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;

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

Wie ich alle Datenbanken exportiere und mit GZIP komprimiere:

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

Lösung (my.cnf):

max_allowed_packet=2G