Mysql Tutorials and Examples

Enable query log without restarting mysql on Linux

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

How to find mysql query rate on Linux

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 – display row count and size of tables

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 – get size of all databases

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

Mysql – get total queries since beginning

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

Mysql – how to copy a table

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

Mysql – how to enable query log

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

Mysql 5.7 root password after installation

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 difference between CURDATE() and NOW()

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

Mysql find current timezone offset

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

Mysql find slave lag

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

Mysql how to dump schema of all databases

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

Mysql multi column Index

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

mysql – how to enable query logs

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

mysql find recently created-tables

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