Mysql Tutorials and Examples

Steps to enable mysql query log on mysql server without restarting mysql process on Ubuntu and other Linux flavours. Set general_log_file on SQL prompt: SET read more

Sometimes we want to know the current queries rate being sent to mysql server. This can be useful to get some idea on query load read more

Mysql SHOW TABLE STATUS command can be used to display information about all or specified tables in mysql db. Here are some examples: Show status read more

Mysql contains size of all tables in information_schema.TABLES table. The following query can be used to get size of all databases on a mysql server: read more

Total queries on mysql prompt: mysql> SHOW STATUS like “Queries”; +—————+——–+ | Variable_name | Value | +—————+——–+ | Queries | 278583 | +—————+——–+ 1 row read more

Cloning table with indexes/triggers and data First create new table with same structure. We’ll use wordpress table wp_posts for the purpose of this tutorial. mysql> read more

It may be useful to enable mysql query log on development of even production server for a short duration for debugging or code/flow analysis purpose. read more

A random root password is generated and logged in log /var/log/mysqld.log sudo cat /var/log/mysqld.log | grep -i password2020-06-02T17:39:23.434297Z 1 [Note] A temporary password is generated read more

Mysql CURDATE CURDATE() returns only date portion of date in current timezone. select CURDATE(); Outcome 2015-12-02 Mysql NOW NOW() returns full time in current timezone. read more

Print current local and global timezone SELECT @@global.time_zone, @@session.time_zone; Here is the outcome @@global.time_zone @@session.time_zone +05:30 +05:30 Find timezone offset using convert_tz select TIMEDIFF(now(), convert_tz(now(), read more

Command to find slave lag (on mysql slave): $ mysql -u root –password=PASSWORD -e “show slave status\G” | grep Seconds_Behind_Master Seconds_Behind_Master: 0

It is a good idea to frequently take schema dump of all your databases. This may be useful in case there is some accidental change. read more

This article covers when to user mysql multi column index and how does the order of column impact the query and index usage. Sample table read more

The steps to enable mysql query logs on Mysql 5.7 (should work on other versions also) on Linux Edit /etc/my.cnf and add the followinggeneral_log=1general_log_file=/var/log/mysqlquery.log Ensure read more

The query to find recently created tables in mysql select table_schema, table_name, create_timefrom information_schema.TABLESwhere table_schema not in (‘information_schema’, ‘mysql’)order by CREATE_TIME desc