How to export SQLite3 database to *.sql file

Here’s a quick way to export SQLite3 database to *.sql text file.

sqlite3 this.db .dump > this-db.sql

The command is useful for exporting SQLite database to standard ANSI .sql which can be imported into other database management system such as Oracle, MariaDB/MySQL, etc A place to look for cheap-ish VPS and Dedicated Server – Less than USD7 / month! is a website that lists bare minimum VPS or Dedicated Server offerings with the lowest price.

For VPS, the category is divided by the virtualization technology and the operating system, as well as the geographical location of the data centers.

low end box VPS

Additionally provides discussion forum for optimizing web server and databases for the bare minimum servers!

How to optimize MySQL tables automatically using cron

Busy websites which has a lot of insert/delete transactions may introduce fragmentation in MySQL tables. Fortunately, users and optimize mysql tables with ‘OPTIMIZE TABLE’ command, but how to execute it automatically?

Here’s how:
The mysql-client package in Ubuntu installation comes with a tool called mysqlcheck which is handy for optimizing table in mysql. This command can be executed from bash and can be executed using cron.

to do that, just run this command.

cron -e

#in the crontab file– add this line
59 23 * * * /usr/bin/mysqlcheck -o -v -u <mysql username> -h localhost <database_name> -p <password>

This will tell cron to execute mysqlcheck and optimize mysql table of the specified database exactly on 11:59pm, every day. You can change the setting to suit your need.

How to limit MySQL port access to specific network

MySQL service port are not meant to be accessible to the outside world as it would become a security concern to the administrator.

Although MySQL server (mysqld) by itself has a built-in mechanism to deny access from unauthorized ip-address, it still does not protect it from being overwhelmed by multiple malicious requests or buffer overflow attack directed to the server.

One of the solution is to limit the MySQL port access to trusted network using iptables

This assume your trusted network has the address within the range of

iptables -A INPUT -i eth0 -p tcp -s --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT
iptables -A OUTPUT -o eth0 -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT

MySQL server (mysqld) uses port 3306.

Note: Always assume the internet as the untrusted network!

How to Import export MySQL database from command line

This tip would be useful for those who are either making backup of their remote mysql database or moving their web hosting to their provider.

Here’s how to export mysql DB to SQL file using command-line utility :
mysqldump –user=username –password=1234 –databases your_database –opt –quote-names –complete-insert > example.sql

Here’s how to import the SQL back into mysql database :
mysql –user=username –password=1234 –databases your_database –host=mysql_server host

Don’t forget to add your ip address to the list of allowable host on the remote MySQL server!

Grab Dreamhost Promo Codes and Crazy Offerings!

Usually I don’t post things such as promo codes and offerings on my blog, but I’ll make an exception for this case.

Dreamhost has made available 5 promo codes that gives you special offer on their already generous hosting plans, which includes :

  • Disk Storage up to 500GB
  • Monthly Bandwidth at 5TB
  • Secured Shell access (SSH)
  • Unlimited Domain host
  • PHP5, Python, Perl, Ruby On Rail support
  • Custom DNS configuration
  • CVS, SVN Repository and WebDav support
  • User management control, you can add user in your own hosting accout, each user can accesss their own shell!
  • and other exciting features…!

Oh they have a Dreamhost Wiki page too
The Promo codes gives you :

  • Four (4) times the normal disk and bandwidth!
  • If you choose their five-year plan, you’ll get $150 off!
  • If you choose their ten-year plan, you’ll get $200 off!

Here’s the list of Promo Codes (to be entered in “Promo Code” field when you sign up) :


Alternatively, you may choose to enter “KIROSTUDIO” promo code, to get USD 70.40 discount on their web host offerring!

All promo codes except “KIROSTUDIO” expires on 15 October 2008, so hurry up!