MySQL Cheat Sheet

So first I have posted in sometime as felt I should be. I have been very busy still working with MySQL and all related forks and failed to put out blogs as I felt I should. So I will work on that.

Now That being said I recalled the other day a website I used to love because it was a common VI cheat sheet list. The syntax you know , you know you need it, but type it 3 times until it right. When it does get entered right you look at it dumbfounded , I thought I wrote that already.

So I figured why not a simple list of common MySQL commands that we all either type 50 times a month or should know like the back of our hand but forget when the client is looking over our shoulder.
For starters..
We set up a new MySQL 5.7.6+ server and log in..
Need to change password before we can do anything. But it is Alter user not Set pass.
We want to know how to read the password still as it is in clear text.

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