MySQL Cheat Sheet (MySQL 5.7.6+)

ALTER USER

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass'; 

Set Password is

SET PASSWORD FOR 'bob''@'localhost' = PASSWORD('cleartext password'); 

Purge Binary Logs

PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2008-04-02 00:00:00
PURGE BINARY LOGS BEFORE NOW() - interval 3 DAY;

MySQL Dump

# COMPACT WILL REMOVE DROP STATEMENTS
mysqldump --events --master-data=2 --routines --triggers --compact --all-databases > db.sql
mysqldump --events --master-data=2 --routines --triggers --all-databases > NAME.sql 
mysqldump --opt --routines --triggers dbname > dbname.sql 
mysqldump --opt --routines --triggers --no-create-info joomla jforms > dataonly.sql 

Turn off Foreign Keys for a moment

SET GLOBAL foreign_key_checks=0; 

Skip Grants

/usr/bin/mysqld_safe --defaults-file=/etc/mysql/my.cnf --skip-grant-tables
vi /etc/mysql/my.cnf
[mysqld]
skip-grant-tables

BinLog reviews

--base64-output=DECODE-ROWS & --verbose
mysqlbinlog --defaults-file=/home/anothermysqldba/.my.cnf --base64-output=DECODE-ROWS --verbose binlog.005862 > 005862.sql

MYSQL SECURE CLIENT

mysql_config_editor print --all
mysql_config_editor set --user=mysql --password --login-path=localhost --host=localhost
mysql --login-path=localhost -e 'SELECT NOW()';

Swap

sudo swapoff -a
To set the new value to 10: echo 10 | sudo tee /proc/sys/vm/swappiness
sudo swapon -a 

IF INFORMATION SCHEMA IS SLOW

set global innodb_stats_on_metadata=0; 

AWS Variables

CALL mysql.rds_show_configuration;
> call mysql.rds_set_configuration('binlog retention hours', 24);
> call mysql.rds_set_configuration('slow_launch_time', 2);

Find what table a column name is in

SELECT TABLE_SCHEMA , TABLE_NAME , COLUMN_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'fieldname' ; 

Client says it is in TableA but they have 50 databases.. What schema has TableA

SELECT TABLE_SCHEMA , TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME = 'TableA' ; 

Adjust Slave workers

Select @@slave_parallel_workers;
Stop Slave; Set GLOBAL  slave_parallel_workers=5; Start Slave;

MySQL Multi

5.6>
To start both : mysqld_multi start 1,2 
To check on status of both: mysqld_multi report 1,2
To check on status or other options you can use just one 

5.7<
[mysqld1] BECOMES [mysqld@mysqld1] 
systemctl start mysqld@mysqld1
systemctl start mysqld@mysqld2
systemctl start mysqld@mysqld3
systemctl start mysqld@mysqld4

MySQL Upgrade System tables only

mysql_upgrade --defaults-file=/home/anothermysqldba/.my.cnf --upgrade-system-tables

SKIP REPLICATION ERROR

STOP SLAVE; 
SET GLOBAL sql_slave_skip_counter =1; 
START SLAVE; 
SELECT SLEEP(1); 
SHOW SLAVE STATUS\G