Table of Contents

Databases

Reset password

Start database without permission checking: 

service mysql stop
mysqld_safe --skip-grant-tables &

Set new password 

mysql --user=root mysql
update user set Password=PASSWORD('new-password-here') WHERE User='root';
...
killall mysqld

Executing SQL as root without password

Create file mysql-init with commands to execute at start. 

service mysql stop
mysqld_safe --init-file=./mysql-init.sql &
GRANT LOCK TABLES ON *.* TO 'debian-sys-maint'@'localhost';
 
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost';
GRANT RELOAD, SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, LOCK TABLES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxxxxxxx'
GRANT ALL ON testDB.* TO 'demo-user'@'localhost';

MySQL

SHOW DATABASES;
SHOW TABLES;

Permissions

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();
SELECT USER,Host,Password FROM mysql.user;
FLUSH privileges;

Optimize databases

#!/bin/bash
mysqlcheck --all-databases
mysqlcheck --all-databases -o
mysqlcheck --all-databases --auto-repair
mysqlcheck --all-databases --analyze