Archive for the 'Mysql' Category

Mysql Tip

Dec 21 2012 Published by under Mysql

Ever start write a big mysql query in the mysql command line client and realize that you forgot to do something, and now you are stuck with pressing the backspace a million and copy/pasting the query etc etc,  no more.

\c

“\c” is your friend, just type it in and you can start back from scratch :) ..

Example below

mysql> select * from users\c  mysql>

Posted via email from Put to flight

No responses yet

Dumping/Backing up Mysql Databases (Innodb) without locking tables.

Jan 27 2012 Published by under Mysql

When not using hosted backup solutions or gems, I am make use good old “mysqldump” a lot to take a dump of my databases and move the sql around. I think its good practice generally to avoid locking tables for read and write atleast in a production like environment. I am yet to go deeper into this issue, but as a practice I have started using  the options almost all the time.

So in case of Transactional Tables ( Innodb ) , you can use the 

–single-transaction

option. What this option does is that it opens a new transaction which in turn avoids locking. The database is available to read and write in that time, though you may see a considerable loss in performance. 

Another useful option is 

–quick

This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

Checkout more details about mysqldump here

I have also heard good things about Percona XtraBackup for Mysql. I am yet to explore that option. 

Posted via email from Put to flight

No responses yet