#mariadb
19 one-liners with this tag.
All one-liners tagged mariadb
Analyze the slow query log for the slowest queries
#mysqldumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log
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
Back up all databases into one gzipped file
#mysqldump --single-transaction --quick --all-databases --routines --events | gzip > /root/all-databases.sql.gz
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
Check all MySQL tables for errors
#mysqlcheck --all-databases --check --silent
Compare max connections with the peak so far
#mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'; SHOW GLOBAL VARIABLES LIKE 'max_connections';"
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';"
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";'
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;'
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
Kill a hanging MySQL query
#mysql -e 'KILL 12345;'
List all MySQL users with their host
#mysql -e 'SELECT user, host FROM mysql.user ORDER BY user;'
Open the MySQL console as Plesk admin
#plesk db
Permanently delete a MySQL database
#mysql -e 'DROP DATABASE wp_old;'
Show a quick status of the database server
#mysqladmin version
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;"
Show the privileges of a MySQL user
#mysql -e "SHOW GRANTS FOR 'dbuser'@'localhost';"
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;'
Use the Plesk MySQL admin in scripts and one-liners
#MYSQL_PWD=$(cat /etc/psa/.psa.shadow) mysql -u admin -e 'SHOW DATABASES;'