Postgres: Copy a database

simple

November 17, 2012

Databases, Postgres

No Comment

For backup and development reasons a copy of a postgres database on the same system is often used. This is not a big problem and can be done directly as a SQL statement: [crayon-5a301984d9b0c098051450/] in your favorite console (psql, pgadmin, etc). There is one thing to remember, if you do a template copy this can […]

Read More

Oracle: Timeout idle or dead sessions

Lately in a project we had a lot of problems with idle database sessions. It turns out that the DBA did not configure any timeout for the oracle database for idle or dead sessions. This is done with the PMON process, and via SQL*Net, by the sqlnet_expire_time parameter. SQL*Net In your sqlnet.ora file add the […]

Read More

Postgres: Remote copy a database between two machines using ssh

Sometimes it might be useful to copy a postgres database between two systems. This is not a big hazel and can be done with a simple one-liner (Port 22): [crayon-5a301984da955585214403/] and even if your ssh connection uses a different port (1234) you are good to go: [crayon-5a301984da95b388771676/]

Read More

Database: Migration from SQLite to MySQL

simple

September 6, 2011

Databases

No Comment

After working with some sqlite databases for one of my iOS projects lately, I have now decided to also add an online service for the project. But how can I migrate from sqlite to MySQL? Here are the basic three steps, I figured out that are needed to extract and modify the DDL to be […]

Read More

How to drop multiple tables in MySQL

simple

February 9, 2011

Databases

No Comment

If you want to drop multiple tables in MySQL (current version 5.05 or before) you will find no command for that. But there is a simple way using some console commands, just hit your shell and simply paste this snippet: [crayon-5a301984dab98351610224/] Do not forget to replace [USER], [PASSWORD] and [DATABASE] with your settings. As always […]

Read More

Scalable second-generation distributed database

simple

March 28, 2010

Databases

No Comment

A good friend of mine just showed me two links to scalable second-generation distributed database, I want to share with you. This approach is my favorite one – as it is the underlying database of Digg, Facebook, Twitter and many others. It is written in Java and released under the Apache License: http://cassandra.apache.org/ The other […]

Read More

MySQL Replication Setup

On the Master we first flush the tables in the database setting a read lock: USE phpMyWebcam;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS; The last command will show something like this +——————+———-+————–+——————+ | File | Position | Binlog_do_db | Binlog_ignore_db | +——————+———-+————–+——————+ | mysql-bin.015586 | 364167 | phpMyWebcam | | +——————+———-+————–+——————+ 1 row in set […]

Read More

Purge MySQL Database

simple

February 23, 2010

Databases

No Comment

If you run out of disk space on your server, it might be a good time to check your mysql binary logs. If there is really to much space used than just issue some SQL commands and you can delete them. To delete all binary logs older than 7 days: mysql> PURGE BINARY LOGS BEFORE […]

Read More

MySQL Problems

* MYSQL WON’T START OR STOP?: ============================= You may never ever delete the special mysql user “debian-sys-maint”. This user together with the credentials in /etc/mysql/debian.cnf are used by the init scripts to stop the server as they would require knowledge of the mysql root users password else. So in most of the times you can […]

Read More