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.
Reference: MySQL drop all tables command
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.
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
If you want to modify the MySQL configuration in MAMP, just create the my.cnf file by the following command Continue reading MAMP – Where is the MySQL config file/my.cnf?
When i try to import a database, the following error is thrown and the import fails.
- Warning: MySQL server has gone away query: INSERT xxx…
Continue reading MySQL – MySQL server has gone away
Export a database which is called Test:
mysqldump -u <username> -p Test > Test.sql
The sql file will be generated after you input the password. Continue reading MySQL – Import and Export Database with mysqldump
Sometimes we need to convert the selected data into other content type. Here is an example.
Assume we got a table with a VARCHAR column which stores number only.
mysql> SELECT * FROM demo;
| a_string |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
13 rows in set (0.00 sec)
Continue reading MySQL – Data Type Conversion
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
I found a good article which the author shows you how to select random records from table among different kind of databases.
Here are the example for SQLite, MySQL and PostgreSQL.
Continue reading SQL – Select Random Records From Table
PostgreSQL version: 8.3
By default, the PostgreSQL database files is located at /var/lib/pgsql/data. Initialize the database can be done by the following command.
Continue reading PostgreSQL – Initialize Database At Different Location
The current available postgresql-server package in CentOS Yum Repository is 8.1.18. But my application needs postgresql-server 8.3. Luckily, PostgreSQL provides its own repository for upgrade. So Fedora, Redhat and CentOS users can upgrade by the following steps.
Continue reading PostgreSQL – Upgrade version from PostgreSQL Yum Repository