Tag Archives: MySQL

H2 Database: Missing UNIX_TIMESTAMP

So maybe you are running tests using an H2 database with your MySQL-based application, and you just got the message that the MySQL function UNIX_TIMESTAMP(value) is missing from the H2 database? No worries. With an H2 database, you can build your own UNIX_TIMESTAMP (or any other function you might need). I’m here going to show you one way to do it:

First, we need to create a class on the classpath of the application that connects to the H2 database, this is normally the application you are testing:

package h2;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class H2UserDefined {

    public static Long UNIX_TIMESTAMP(String d) throws ParseException {
        DateFormat dateFormat 
                          = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
        Date dateresult = dateFormat.parse(d);
        return dateresult.getTime()/1000;
    }
}

Now we need to tell H2 that we have a new function to use. To do this we need some SQL to run against the H2 database:

CREATE ALIAS UNIX_TIMESTAMP FOR "h2.H2UserDefined.UNIX_TIMESTAMP";

Here we tell the H2 database that there is a new alias called UNIX_TIMESTAMP that can be used and that it is located in the package ‘h2’ with the path ‘H2UserDefines’ and a function name UNIX_TIMESTAMP. Quite simple 🙂

Tested on Play Framework 2.3.6, H2 v1.4

A small MySQL backup script using mysqldump and creates one file per database

This is the script I use for all my Amazon RDS instances local backups (called from an EC2). It gives me one db backup file for every database in the RDS instance, and at the end it makes sure that I don’t have any files older then 30 days. I put it here now so I don’t loose it again 🙂

#!/bin/bash
 
DATE=$(date +"%Y%m%d_%H%M")
DATABASES=`mysql -hsomedbname-someregion.rds.amazonaws.com -udbadmin -pmypassword -e "show databases;"`
 
for db in $DATABASES; do
        echo $db
        mysqldump --databases $db --single-transaction -hsomedbname-someregion.rds.amazonaws.com -udbadmin -pmypassword | gzip > /opt/backup/mysql.backup.$db.$DATE.sql.gz
done
find /opt/backup/* -mtime +30 -exec rm {} \;

I set this on a two hour cron to make sure that I have fresh backups of my production databases

0 */2 * * * /opt/scripts/mysql_db_backup.sh > /var/log/mysql_db_backup.log

Backup and restore a MySQL database

For this task I normally use the mysqldump program bundled with the mysql database package. It is a niffty little program that lets you backup multiple databases, a selection of tables or just one database to a SQL file. Here is the syntax:

Backup one database:

mysqldump --user=username --password=password --databases dbname > filename.sql

This will backup alla tables of the database dbname to the file filename.sql

Backup multiple databases:

mysqldump --user=username --password=password --databases dbname1 dbname2 dbname3 > filename.sql

This will dump database dbname1, dbname2 and dbname3 to the file filename.sql

Backup all databases:

mysqldump --user=username --password=password -A > filename.sql

The -A option tells the program to dump all databases

Backup tables:

mysqldump --user=username --password=password --databases dbname --tables tablename1 tablename2 > filename.sql

This will backup tables tablename1 and tablename2 from database dbname to file filename.sql

Restore a database:

mysql --user=username --password=password dbname < filename.sql

Yes, when restoring a dump we use the normal mysql program and not the mysqldump program. This will restore the database/tables in the file filename.sql to the dbname database