How to get size of Mysql tables

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.

[via Mysql Forum]

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 records from the table products will be dumped into file /tmp/products.csv. All fields of that dump will be delimited by comma, enclosed by double quotes and escaped by two slashes. Each row will be ended by end of the line character.

Sure you can change delimiter, enclose, escape symbols as well as add any others SQL statements such JOIN, WHERE, LIMIT ORDER etc. Enjoy!

UPDATED: Don’t forget to drop the export file before re-export data. Otherwise you’ll get the error:
ERROR 1086 (HY000): File '/tmp/products.csv' already exists

[via Electrictoolbox]

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 each time the Mysql session is started with logging the log file will be replaced. In my case it isn’t. As I expected the new messages just are appended to the log file.

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 code to your .vimrc:
to select database:
map <C-d> :call SwitchDB()<CR>
:function SwitchDB()
: let g:current_db = input("Database > ")
:endfunction

to run query:

map <C-m> :call Doquery()<CR>
:function Doquery()
: if !exists("g:current_db")
: call SwitchDB()
: endif
: let query_string = input(g:current_db . " > " )
: if query_string != ""
: exe "!mysql " . g:current_db . " -e \"" . escape(query_string, '"') . "\""
: endif
:endfunction

So, when you press Ctrl-d the VIM gives you a prompt to type database name, Ctrl-m will run query. Sure you can use the your preferable combinations of keys. Also you may extend the login functionality by adding prompt for database host and user or hardcoded that information in the SwitchDB() function.

Using VIM to construct queries in the Mysql shell even much easier. Just specify VIM as default editor:
export EDITOR=vim
After that type \e in the Mysql shell. It’ll bring you VIM window where you’ll able to type your query. To finish with editing just type ZZ as usual. To run built query type ; and press Enter.

As result we’ll have possibility to run Mysql queries directly from VIM and using VIM to create queries in the Mysql shell.

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;'
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake

The option init_connect replaces setting ‘SET NAMES utf8’ from the client and skip-character-set-client-handshake tells to server to ignore charset sent by client and use it default one instead.

I tested this approach with LinuxMCE and it works. The Russian text is displayed on the Orbiter correctly. I tested it with Perl as well and found that Perl script still should set option mysql_enable_utf8 to true.

[via Saiweb]

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 /etc/mysql/my.cnf:
    [mysqld]
    default-character-set=utf8
  • default charset for client should be set to utf8 in the /etc/mysql/my.cnf:
    [client]
    default-character-set=utf8

    After that the charset for mysql shell will be UTF8 but not latin1 as in the stock version.
  • default charset for desired database should be set to utf8:
    alter database pluto_main charset=utf8;
  • default charset for desire table should be set to utf8:
    alter table Text_LS charset=utf8;
  • default charset for all text fields in the table should be also set to utf8:
    alter table Text_LS modify column `Description` longtext CHARACTER SET utf8 COLLATE utf8_general_ci
  • the client application should pass UTF8 flag to tell mysql about charset for connection. Here is a Perl example:
    my $dbh = DBI->connect("dbi:mysql:pluto_main;host=localhost", "root") or die "Cannot connect to database: $DBI::err!";
    $dbh->{'mysql_enable_utf8'} = 1;
    $dbh->do('SET NAMES utf8');

To display UTF8 text in the Perl script just set UTF8 charset for STDOUT:
binmode STDOUT, ":utf8";

But with LinuxMCE the situation is more complicate. Its UI is developed on C++. So, After a googling I found the way to set UTF8 for connection. There is a function mysql_options() in the Mysql C API. It should be called after mysql_init() but before mysql_connect() or mysql_real_connect() and allows to set desire charset:
mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, 'utf8');
and run some SQL statement when connecting to the MySQL server:
mysql_options(&mysql, MYSQL_INIT_COMMAND, 'SET NAMES utf8');
But it didn’t help. The Russian text is still displayed as question mark. So, have to dig LinuxMCE code to solve that. Otherwise the Russian translation won’t be added.

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(). Perl geeks can get the ID from DBI connection property – mysql_insertid:
my $id = $dbh->{mysql_insertid};

3. Update summary counters.
To update some counters in a table you can do following:

  • do UPDATE everytime but catch a case if the record is not existing and do INSERT in this case:
    my $ret = $dbh->do('UPDATE counters SET hits=hits+1 WHERE IP="192.168.1.1"');
    $ret = $dbh->do('INSERT INTO counters (hits, IP) VALUES(1, "192.168.1.1")) unless $ret;
  • use INSERT ON DUPLICATE KEY UPDATE (via MySQL Performance Blog):
    my $ret = $dbh->do("INSERT INTO ipstat VALUES(inet_aton('192.168.0.1'),1,now()) ON duplicate KEY UPDATE hits=hits+1");
    Note: IP should be a primary key in this case