Mysql Commands to Remember

This is an reminder for mw some mysql commands that i use often and my mind not always bring with me.

 

Optimize table

When i table that has many writes and delets get fregmant this will speed up the database.

optimize table Sys

 

Creating users

CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'some_pass';

GRANT ALL PRIVILEGES ON *.* TO 'dbuser'@'localhost'

FLUSH PRIVILEGES;

Adding Mysql Monitoring User

GRANT SELECT, REPLICATION CLIENT, SHOW DATABASES, SUPER, PROCESS ON *.* TO 'monitor'@'10.0.0.1' IDENTIFIED BY 'password';

 

 

Replication

Is the replication working

SHOW SLAVE STATUS \G

Fixing replication stop

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

START SLAVE;

Verify so its working

SHOW SLAVE STATUS \G

Set GLOBAL variabel MYSQL

Show variabels

show variables;

To search for variables

show variables like '%slow%';

set global max_connect_errors = 20;