MySQL Hacks

[1] get database storage engine

mysql> SHOW TABLE STATUS\G;


*************************** 57. row ***************************
           Name: User
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1
 Avg_row_length: 16384
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 402653184
 Auto_increment: NULL
    Create_time: 2013-06-16 12:56:03
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
57 rows in set (0.01 sec)


mysql> SHOW TABLE STATUS where name='User'\G;
*************************** 1. row ***************************
           Name: User
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 188
 Avg_row_length: 261
    Data_length: 49152
Max_data_length: 0
   Index_length: 49152
      Data_free: 0
 Auto_increment: 33000000000003
    Create_time: 2018-03-22 10:57:28
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.18 sec)
ERROR: 
No query specified

[2] export a database to *.SQL

prayagupd@prayagupd:~$ mysqldump -h 192.168.2.18 -u root -p bloodoverflow > /home/prayagupd/bloodoverflow_24_mar_2012.sql

Enter password: 

Note : Don't forget to turn off pager ay /etc/mysql/my.cnf while using mysqldump.

[3] Import a database from *.SQL
3.1 create a database
mysql> create database bloodoverflow;

3.2 import into database

prayag@prayag:~$ mysql bloodoverflow -u root -p < /home/prayag/bloodoverflow_24_mar_2012.sql

[4] list constraints

SELECT * 

FROM information_schema.table_constraints

WHERE table_schema = 'eccount'

AND table_name = 'User'

AND constraint_type='FOREIGN KEY';



[5] INSERT INTO ... SELECT ... FROM
INSERT INTO HeartBeat(created, code, message) 
SELECT created, code, message from HeartBeatResp;


[6] disable/enable foreign keys
##disable foreign keys
mysql> SET FOREIGN_KEY_CHECKS=0;


##enable foreign keys
mysql> SET FOREIGN_KEY_CHECKS=1;

[7] delete a column 

mysql> ALTER TABLE student_class DROP year;
Query OK, 2 rows affected (0.33 sec)
Records: 2  Duplicates: 0  Warnings: 0

CLIENT TOOL HACKS
[1] pager to adjust query results

Add following pager content to /etc/mysql/my.cnf

[client]

port = 3306 socket = /var/run/mysqld/mysqld.sock pager = less -n -i -S

If colour-mysql-console is being , set following line to ~/.my.cnf
pager = grcat ~/.grcat | less -RSFXin

If you hate to vi, fire the following pager option in mysql CLI

mysql> pager less -n -i -S

Check pager

mysql> \P PAGER set to 'less -n -i -S'

[2] disable UPDATE queries without WHERE clause
Add safe-updates to [client] block of /etc/mysql/my.cnf

[client]

safe-updates


[3] update wait_timeout (loose connection in ... secs)
mysql> SET GLOBAL wait_timeout = 28800;


mysql> show global variables like '%time%' ;

+---------------------------------------+-------------------+
| Variable_name                         | Value             |
+---------------------------------------+-------------------+
| connect_timeout                       | 10                |
| datetime_format                       | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout                | 300               |
| flush_time                            | 0                 |
| have_response_time_distribution       | YES               |
| innodb_lock_wait_timeout              | 50                |
| innodb_old_blocks_time                | 0                 |
| innodb_rollback_on_timeout            | OFF               |
| innodb_thread_concurrency_timer_based | OFF               |
| interactive_timeout                   | 60                |
| lc_time_names                         | en_US             |
| lock_wait_timeout                     | 31536000          |
| long_query_time                       | 10.000000         |
| net_read_timeout                      | 30                |
| net_write_timeout                     | 60                |
| query_response_time_range_base        | 10                |
| query_response_time_stats             | OFF               |
| slave_net_timeout                     | 3600              |
| slow_launch_time                      | 2                 |
| slow_query_log_timestamp_always       | OFF               |
| slow_query_log_timestamp_precision    | second            |
| system_time_zone                      | NPT               |
| thread_pool_idle_timeout              | 60                |
| time_format                           | %H:%i:%s          |
| time_zone                             | SYSTEM            |
| timed_mutexes                         | OFF               |
| wait_timeout                          | 60                |
+---------------------------------------+-------------------+

[4] check max concurrent connections (max_connections > processlist)

mysql> SHOW VARIABLES like '%max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+


mysql> show status where `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 258   |
+-------------------+-------+
1 row in set (0.10 sec)


mysql> show session status\G;
*************************** 1. row ***************************
Variable_name: Aborted_clients
        Value: 38158
*************************** 2. row ***************************
Variable_name: Aborted_connects
        Value: 12
*************************** 3. row ***************************
Variable_name: Binlog_cache_disk_use
        Value: 5442
*************************** 4. row ***************************
Variable_name: Binlog_cache_use
        Value: 4055146
*************************** 5. row ***************************
Variable_name: Binlog_stmt_cache_disk_use
        Value: 0
*************************** 6. row ***************************
Variable_name: Binlog_stmt_cache_use
        Value: 20212
*************************** 7. row ***************************
Variable_name: Bytes_received
        Value: 7095
*************************** 8. row ***************************
Variable_name: Bytes_sent
        Value: 444347

References

How can i monitor and improve performance of my Apache/Tomcat/MySQL application?, http://qnatech.wordpress.com/2008/10/30/how-can-i-monitor-and-improve-performance-of-tomcat/

Should you move from MyISAM to Innodb ?, http://www.mysqlperformanceblog.com/2009/01/12/should-you-move-from-myisam-to-innodb/

How do I kill all the processes in Mysql “show processlist”?, http://stackoverflow.com/a/9938302/432903

MySQL ::   MySQL 5.0 Reference Manual :: 3.5 Using mysql in Batch Mode, http://dev.mysql.com/doc/refman/5.0/en/batch-mode.html