DE

↑ ↓ select, Enter open, Esc close

Enable the slow query log at runtime

caution rootMySQL and MariaDBdebian, ubuntu

Caution: This command changes the system. Understand what it does before you run it.

Adjust the values, the command updates live
root@server
mysql -e 'SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 2; SHOW VARIABLES LIKE "slow_query_log_file";'

Enables the slow query log and sets the threshold above which queries are logged. The path of the log file is printed afterwards. The setting lasts until the next restart of the database service, and new connections pick up the threshold.

Note: Turn it off with SET GLOBAL slow_query_log = 0;. To make it permanent, add it to /etc/mysql/mariadb.conf.d/50-server.cnf. With a low threshold, the log grows quickly.

Also searched as

  • find slow mysql queries
  • enable slow query log
  • database slowing down website

Related one-liners

All in MySQL and MariaDB

Analyze the slow query log for the slowest queries

#mysqldumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log
harmlessMySQL and MariaDB

Compare max connections with the peak so far

#mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections';"
harmlessMySQL and MariaDB

Find the 20 largest tables on the server

#mysql -e 'SELECT table_schema, table_name, ROUND((data_length + index_length) / 1024 / 1024, 1) AS mb, table_rows FROM information_schema.tables ORDER BY data_length + index_length DESC LIMIT 20;'
harmlessMySQL and MariaDB

Show running MySQL queries without idle connections

#mysql -e "SELECT id, user, db, time, state, LEFT(info, 100) AS query FROM information_schema.processlist WHERE command <> 'Sleep' ORDER BY time DESC;"
harmlessMySQL and MariaDB

Back up a single database consistently as gzip

$mysqldump --single-transaction --quick -u dbuser -p wp_example | gzip > /var/www/vhosts/example.com/wp_example.sql.gz
harmlessMySQL and MariaDB

Back up all databases into one gzipped file

#mysqldump --single-transaction --quick --all-databases --routines --events | gzip > /root/all-databases.sql.gz
harmlessMySQL and MariaDB

Read first, then run.

The commands on myline.de act directly on servers, files and databases. A wrong path or placeholder can delete data irreversibly or make a server unreachable.

  • All commands are provided without warranty and are not tested on every system.
  • Understand what a command does before running it, and check every placeholder.
  • Make a backup first and, if possible, try it on a test system.
  • You run commands at your own risk. Liability for damages is excluded to the extent permitted by law.