Solved (without restart) Mysql slow query log is on but not logging anything

1.First cross check /etc/my.cnf to make it permanent

slow_query_log = 1
slow_query_log_file = /var/log/mysql_slow_query.log

2.Permission of

mysql mysql  763482 Oct  6 11:43 /var/log/mysql_slow_query.log

3.Slow query logging without restart mysql

login to  mysql as root
mysql> set global slow_query_log = 1;
mysql> set global slow_query_log_file = '/var/log/mysql_slow_query.log';
mysql> flush privileges;

Cheers!!!!!!!

Solved Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work

1. check and correct server-id under /etc/my.cnf
2. remove auto.cnf file under data directory then restart mysql 

Dumping MySQL Stored Procedures, Functions and Triggers

Mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this behavior:

Assume we want to backup only the stored procedures and triggers
 the mysql tables and data (this can be useful to import these in another db/server that has already the data but not the stored procedures and/or triggers),
 then we should run something like:

mysqldump -u root -p'password' --routines  --triggers --no-create-info --no-data  --no-create-db --skip-opt db_name > dbfunction.sql

Mysql slow query log not working

Add this in my.cnf and then restart MySQL server:

general_log = 1
log=/var/log/mysql_slowquery.log

You can check whether the general_log is 'ON' or 'OFF' first using query:

mysql>show variables like '%log%';

You can enable it by below commoand without restart

SET GLOBAL slow_query_log = 'ON';

Mysql change table engine MyISAM to InnoDB

 The easiest way is

ALTER TABLE table_name ENGINE = InnoDB;

InnoDB uses row level locks

Command to optimize all MySQL tables one by one automatically

 use mysqlcheck with --optimize and --databases combination. mysqlcheck is a command-line interface for administrators


 mysqlcheck -uroot -p --optimize --databases myDatabase

Optimizing a Table

It's simple and straightforward to optimize a table in MySQL

Optimize table TABLE_NAME

Speedly MySQL InnoDB Shutdown

Depending on the size of the databases you have in mysql innodb the time it takes mysql to restart can be very slow.Some tricks that can speed this up


mysql> set global innodb_max_dirty_pages_pct = 0;

You can check the number of dirty pages with the command

mysqladmin ext -i10 | grep dirty

SELECT without Table

You can also issue SELECT without a table. For example, you can SELECT an expression or evaluate a built-in function.

mysql> select 1+1;

+-----+

| 1+1 |

+-----+

|   2 |

+-----+

1 row in set (0.00 sec)

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2016-05-03 12:40:41 |

+---------------------+

1 row in set (0.00 sec)

mysql> select now(), 1+2;

+---------------------+-----+

| now()               | 1+2 |

+---------------------+-----+

| 2016-05-03 12:40:55 |   3 |

+---------------------+-----+

1 row in set (0.00 sec)

Recover a MySQL root password

Simple and smart reset mysql root password

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Save the Select Output to a File

Using SELECT INTO, we can save the output of a select command into a file.Instead of displaying the output on the screen

mysql> SELECT * INTO OUTFILE '/tmp/employee.txt' FROM employee;

We can also store the output into a comma delimited file by specifying the “FIELDS TERMINATED BY”

mysql> SELECT * INTO OUTFILE '/tmp/employee1.txt'   FIELDS TERMINATED BY ',' FROM employee;