MySQL Tips

Creating db and a user
create database `testdb` character set utf8 collate utf8_general_ci;
grant all privileges on `testdb`.* to 'tester'@'%' identified by '[email protected]';
flush privileges;
Restore password for root user
### MySQL 5.7.6+
echo "ALTER USER 'root'@'localhost' IDENTIFIED BY '[email protected]';" > /tmp/mysqlreset
systemctl stop mysqld
mysqld --user=mysql --init-file=/tmp/mysqlreset
pkill mysqld
systemctl start mysqld
rm -f /tmp/mysqlreset

### MySQL 5.7.6- 
echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('[email protected]');" > /tmp/mysqlreset
systemctl stop mysqld
mysqld --user=mysql --init-file=/tmp/mysqlreset
pkill mysqld
systemctl start mysqld
rm -f /tmp/mysqlreset
Stats
SELECT `ID`, `USER`, `HOST`, `DB`, `COMMAND`, `TIME`, `STATE`, LEFT(`INFO`, 51200) AS `Info` FROM `information_schema`.`PROCESSLIST`;
General log
SHOW VARIABLES LIKE '%general%';
SET global general_log_file='/tmp/mysql.log'; 
SET global log_output = 'file';
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log = 'OFF';

truncate table mysql.general_log
Cleaning old binary logs
# on slave check the current binary log file
SHOW SLAVE STATUS\G

# on master list all logs
SHOW BINARY LOGS;

# now purge up to the latest
PURGE BINARY LOGS TO 'mysql-bin.010';

#### check and purge relay logs
flush logs;

#### Now check params in mysql config

# max days to keep binlog files
expire_logs_days = 7

# max binlog file size
max_binlog_size  = 1G