Tag Archives: MySQL

MySQL Docker helper script

Posted by on January 7, 2022 No comments

Here is a little script I made to help me start/stop and remove a MySQL docker instance. The script also points to an init file (docker-entrypoint-initdb.d) for the database where you can put all the creations of init objects needed, eg. CREATE TABLE…, INSERT INTO…, and so on

Usage:

>my-docker-db-script start # Starts MySQL docker instance. Creates it if it does not exist
>my-docker-db-script stop # Stops the MySQL docker instance
>my-docker-db-script remove # Removes the MySQL instance

Bash script:

#!/bin/bash

name="my-mysql-db"
command="$1"

function start() {
 state=$(2>/dev/null docker inspect $name --format '{{ .State.Status }}' || echo "")

 if [[ "$state" == "running" ]]; then
  echo "Databse already running!"
  exit 1
 fi
 
 if [[ "$state" == "exited" ]]; then
  echo "Database already exists. Starting..."
  docker start $name && echo "  ... database started"
 else
  echo "No database exists. Creating..."
  docker run -d -p 3306:3306 --name $name \
   -e MYSQL_ROOT_PASSWORD=root \
   -e MYSQL_DATABASE=my_db \
   -e MYSQL_USERNAME=myusername \
   -e MYSQL_PASSWORD=myuserpassword \
   -v $(pwd)/my-mysql-db/mysql-docker-init:/docker-entrypoint-initdb.d \
   -d mysql:5.6.51 && echo "  ... database created"
 fi

}

function stop() {
 state=$(2>/dev/null docker inspect $name --format '{{ .State.Status }}' || echo "")

 if [[ "$state" == "" ]]; then
  echo "Database does not exist!"
  exit 1
 fi

 echo "Stopping database ..."
 docker stop $name && echo "  ... database stopped"
}

function remove() {
 state=$(2>/dev/null docker inspect $name --format '{{ .State.Status }}' || echo "")

 if [[ "$state" == "" ]]; then
  echo "Database does not exist!"
  exit 1
 fi

 echo "Removing database"
 if [[ "$state" == "running" ]]; then
  docker stop $name && echo "  ...database stopped"
 fi

 docker rm $name && echo "   ...database removed"
}

case "$command" in
 start)
  start
  ;;
 stop)
  stop
  ;;
 remove)
  remove
  ;;
 *)
  echo "Invalid command: ${command}. Usage $0 (start|stop|remove)"
  exit 1
esac

Tested on MySQL v5.6.51, OSX 10.15.7 and Docker Desktop 4.3.2

H2 Database: Missing UNIX_TIMESTAMP

Posted by on April 4, 2021 No comments

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

Posted by on January 8, 2018 No comments

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