Category Archives: Database

MySQL – Drop all tables without dropping the database

You could use the following command to drop all the tables of a MySQL DB.

  • mysqldump -u<user> -p<pwd> --add-drop-table --no-data <db> | grep ^DROP | mysql -u<user> -p<pwd> <db>

Just replace the <user>, <pwd> and <db> with your MySQL username, password and database name. That’s all.

Done =)

Reference: MySQL drop all tables command

MariaDB – Installation on Ubuntu Precise

Next: Run phpMyAdmin on Nginx in Ubuntu Precise

It’s time to give up MySQL and move on to MariaDB. Here is the steps for MariaDB installation on Ubuntu Precise (Ubuntu 12.04.1 LTS) with root privilege.

1. Create the following file.
/etc/apt/sources.list.d/MariaDB.list

# http://downloads.mariadb.org/mariadb/repositories/
deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main
deb-src http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu precise main

Continue reading MariaDB – Installation on Ubuntu Precise

PostgreSQL – Check Running SQLs

Recently, there is a performance issue on the application which i am supporting and i find that the bottleneck is related to the SQL query speed. Therefore, my SA suggests me to add an index on the table to increase the query speed.

But before i could add the index, i have to find out the SQL which runs slow. In PostgreSQL, you can query the recent SQLs by the pg_stat_get_backend_activity() function. Here shows you an example. Continue reading PostgreSQL – Check Running SQLs