… | mysql -N | mysql -E *************************** 1. row *************************** test.Bar.ID: 42 DIY RI We can get information about foreign key constraints from the INFORMATION_SCHEMA, but… LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> INSERT INTO test.MY_KEY_COLUMN_USAGE SET CONSTRAINT_SCHEMA = ‘ecommerce’, CONSTRAINT_NAME = ‘fk_lineitems’, TABLE_SCHEMA = ‘ecommerce…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
….mysql.com/doc/refman/5.5/en/innodb-physical-record.html You can also get the table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries’; +————+ | ROW…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
… at information_schema to see how much memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema… in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my…
Post: INFORMATION_SCHEMA tables in the InnoDB pluggable storage engine
May 24, 2008 By Leave a Comment
… is INFORMATION_SCHEMA tables that show some status information about InnoDB. Here are the tables: mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE ‘INNODB%’; +—————————————-+ | Tables_in_INFORMATION_SCHEMA (INNODB… opinion about what would be useful to put in the INFORMATION_SCHEMA. I told them the single biggest thing I could not…
Post: Using INFORMATION_SCHEMA instead of shell scripting
November 24, 2008 By 7 Comments
INFORMATION_SCHEMA, in particular by favorite TABLES table is not only helpful … /var/lib/mysql/”,table_schema,”/”,table_name, “.frm”) | +———————————————————————+ | rm -f /var/lib/mysql/art73/article73.frm | | rm -f /var/lib/mysql/art73/author73.frm…
Post: Fun with the MySQL pager command
January 21, 2013 By 6 Comments
… solved by querying INFORMATION_SCHEMA. For instance, counting the number of sleeping connections can be done with: mysql> SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE… for each status can be done with: mysql> SELECT COMMAND,COUNT(*) TOTAL FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY COMMAND ORDER BY TOTAL DESC…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.processlist p on b.trx_mysql_thread…
Post: MySQL Wish for 2013 - Better Memory Accounting
December 31, 2012 By 6 Comments
… Schema does not accounts CPU usage directly but it is something which can be relatively easily derived from wait and stage information). Where we’re still walking blind with MySQL is resource usage – specifically Memory Usage… by various database operations is reasonable. For example if some INFORMATION_SCHEMA query were to take couple of GB of memory we…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…_BATCH_SIZE | 20 | 300 | | PERFORMANCE_SCHEMA_MAX_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER…_on_metadata is disabled by default in MySQL 5.6 Welcome to much faster information_schema queries! innodb_log_file_size – default has…
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
… an example: Enable the log: general_log_file = /var/log/mysql/mysql.log general_log = 1 User correctly authenticated: 121227 8:31… so on. Very valuable information. It is important to mention that these tables are stored in INFORMATION_SCHEMA and that means that after a mysqld restart all the information will be lost. So if you really need that information…
