BACKUP DATABASES 

mysqldump --all-databases -u root -p > all_databases.sql.gz 

RESTORE DATABASES 

mysql --all-databases -u root -p < all_databases.sql.gz

How to select all tables

SELECT 'ALTER TABLE '|| schemaname || '.' || tablename ||' DISABLE TRIGGER ALL;'FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema') ORDER BY schemaname, tablename;

Install MYSQL

yum install mysql-server

/usr/bin/mysqladmin -u root password 'new-password'

Monitor MYSQL Queries

top mytop -S /var/lib/mysql/mysql.sock -u root -p webmin321 -d mysql

MySQL Optimization

Only effective if the database has run for a full work day

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

MySQL Tuner

User has no password set

DELETE FROM mysql.user WHERE user= or password=;

MySQL: Useful Commands

SELECT GRANTEE, PRIVILEGE_TYPE FROM information_schema.user_privileges;

REVOKE ALL PRIVILEGES ON *.* FROM ''@'localhost'

DROP USER 'phpmyadmin'@'localhost';

GRANT ALL PRIVILEGES ON *.* TO 'yyy'@'127.0.0.1' identified by 'xxx' WITH GRANT OPTION;

How to reset the root password?

mysqladmin -u root password NEWPASSWORD

UPD ATE mysql.user SE T Password=PASSWORD('MyNewPass') WHERE User='root';

FLUSH PRIVILEGES;

Configuring MySQL for remote access

nano /etc/mysql/my.cnf

add: bind-address = 0.0.0.0

Repair and check for corruption - InnoDB

mysqlcheck --all-databases --auto-repair -u root -p

Dump all tables

mysqldump --single-transaction --skip-lock-tables --force -AER > /all.dmp -p

Dumping Blobs from mysql database

SELECT certificate

INTO DUMPFILE '/tm