#mysql

21 one-liners with this tag.

Analyze the slow query log for the slowest queries

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

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

Back up each database into its own gzipped file

#for db in $(mysql -NBe 'SHOW DATABASES' | grep -Ev '^(information_schema|performance_schema|sys)$'); do mysqldump --single-transaction --quick "$db" | gzip > "/root/db-backup/$db.sql.gz"; done

harmlessMySQL and MariaDB

Check all MySQL tables for errors

#mysqlcheck --all-databases --check --silent

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

Create a MySQL database with its own user

#mysql -e "CREATE DATABASE wp_example CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE USER 'wp_example_user'@'localhost' IDENTIFIED BY 'SecurePassword'; GRANT ALL PRIVILEGES ON wp_example.* TO 'wp_example_user'@'localhost';"

cautionMySQL and MariaDB

Enable the slow query log at runtime

#mysql -e 'SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 2; SHOW VARIABLES LIKE "slow_query_log_file";'

cautionMySQL 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

Import a gzipped SQL dump into a database

$gunzip -c /var/www/vhosts/example.com/wp_example.sql.gz | mysql -u dbuser -p wp_example

destructiveMySQL and MariaDB

Kill a hanging MySQL query

#mysql -e 'KILL 12345;'

cautionMySQL and MariaDB

List all databases with their domain in Plesk

#plesk db "SELECT d.name AS domain, db.name AS datenbank, db.type FROM data_bases db JOIN domains d ON d.id = db.dom_id ORDER BY d.name, db.name"

List all MySQL users with their host

#mysql -e 'SELECT user, host FROM mysql.user ORDER BY user;'

harmlessMySQL and MariaDB

List database servers registered in Plesk

#plesk db "SELECT id, host, port, type, admin_login FROM DatabaseServers"

Open the MySQL console as Plesk admin

harmlessMySQL and MariaDB

Permanently delete a MySQL database

#mysql -e 'DROP DATABASE wp_old;'

destructiveMySQL and MariaDB

Show a quick status of the database server

#mysqladmin version

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

Show the privileges of a MySQL user

#mysql -e "SHOW GRANTS FOR 'dbuser'@'localhost';"

harmlessMySQL and MariaDB

Show the size of all databases in MB

#mysql -e 'SELECT table_schema AS db, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS mb FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;'

harmlessMySQL and MariaDB

Use the Plesk MySQL admin in scripts and one-liners

#MYSQL_PWD=$(cat /etc/psa/.psa.shadow) mysql -u admin -e 'SHOW DATABASES;'

cautionMySQL and MariaDB