A reference guide covering the essential tools for linting MySQL and MariaDB databases.
SQL linter with MySQL/MariaDB dialect support.
pip install sqlfluff
# Lint MySQL files
sqlfluff lint --dialect mysql migration.sql
# Auto-fix
sqlfluff fix --dialect mysql migration.sql
Configuration (.sqlfluff):
[sqlfluff]
dialect = mysql
templater = raw
max_line_length = 120
[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = upper
Analyze slow query logs for performance issues.
# Install Percona Toolkit
apt-get install percona-toolkit # Debian/Ubuntu
brew install percona-toolkit # macOS
# Analyze slow query log
pt-query-digest /var/log/mysql/slow.log
# Focus on specific queries
pt-query-digest --filter '$event->{fingerprint} =~ m/SELECT/' slow.log
gh-ost (GitHub Online Schema Migrations)
Non-blocking schema changes for MySQL.
# Install
brew install gh-ost # macOS
# Perform an online migration
gh-ost \
--host=localhost \
--database=mydb \
--table=users \
--alter="ADD COLUMN bio TEXT" \
--execute
pt-online-schema-change (Percona)
pt-online-schema-change \
--alter "ADD COLUMN bio TEXT" \
D=mydb,t=users \
--execute
mysqlcheck (Built-in)
# Check all tables
mysqlcheck --all-databases --check
# Optimize tables
mysqlcheck --all-databases --optimize
# Analyze tables (update index statistics)
mysqlcheck --all-databases --analyze
MySQL Shell (mysqlsh)
# Check for upgrade issues
mysqlsh -- util check-for-server-upgrade
# Schema analysis
mysqlsh -- util dumpSchemas mydb --outputUrl=schema-dump/