In the previous post I wrote about a way to get a Mysql database size from Mysql shell. Now I’d like to share a way to get size of each Mysql table: SELECT table_name,`engine` ,ROUND(data_length/1024/1024/1024,2) total_size_gb, ROUND(index_length/1024/1024/1024,2) total_index_size_gb, table_rows FROM information_schema.TABLES WHERE table_schema = ‘rt3′; It’s useful if you need to know what you should clean in your huge database. [via Techie-Gyan]
How to check Mysql database size
Recently I needed to know the size of our RT3 database. So, I found this SQL to do that from Mysql shell: SELECT table_schema “rt3”, sum( data_length + index_length ) / 1024 / 1024 / 1024 “Data Base Size in GB” FROM information_schema.TABLES GROUP BY table_schema; The result is 360GB! We have to cleanup before upgrade to RT4. Otherwise the upgrade procedure will take ages. […]
Export result of query from mysql console to CSV file
Sure all GUI DB tools can do export of result of query into CSV file. But what if you have to do this from command line? There is a simple way perform that task. Here is an example: SELECT * INTO OUTFILE ‘/tmp/products.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n’ FROM products When you run that query all […]
Log for Mysql console session
To log Mysql console session use option –tee (two dashes!) with full path to the log file: mysql -uroot my_db –tee=/tmp/mysql_console.log As result the file /tmp/mysql_console.log will contains all commands and queries with result of their executions. That might be helpful to keep your queries for using next time or for troubleshooting. The author of the post, where I found that useful info, said that […]
VIM and Mysql integration
I use VIM with additional configuration instead of IDE. That configuration includes code explorer using ctag, syntax checking for PHP and Perl and run Perl scripts (syntax highlighting is not a subject of discussion ;)). Also I should run Mysql queries often. So, I decided to add integration with Mysql to VIM. I managed to do that thanks to that simple solution. Just add this […]
Additional mysql server UTF8 confutation
I spent a few day with patching LinuxMCE database connection to pass UTF8 option to the server. But without luck. However I found a way to configure mysql server to skip client’s request about charset and send all data in the defined one. To do that just add following lines in the my.cnf file under mysqld section: [mysqld] init_connect=’SET NAMES utf8; SET collation_connection = utf8_general_ci;’ […]
Perl, C++, mysql and UTF8
Recently I finished with Russian translation of LinuxMCE UI. But as I know now it was the easiest part. After addition Russian descriptions in the database I faced with problem of displaying them. I made a research and found following. To have UTF8 data in the mysql database a few steps should be done: default charset for server should be set to utf8 in the […]
Mysql useful tips
I decided to put in one post several Mysql commands and tips which I used during this week. 1. Get a table structure To get a table structure in a SQL it can be used a command show create table <table name> \G 2. Get ID of last inserted record. To get the ID of the last inserted record just use mysql built-in function LAST_INSERT_ID(). […]