Automate regular backups of SonarQube's DB using bash script

Guys, this post will be useful if you are using SonarQube for code analysis and quality control. In today's post we will look into how to automate taking regular backups of SonarQube's back-end database.

For illustration, we will use MySql DB to store SonarQube's metadata which includes analysis parameters like code smells, bugs, code coverage, unit test coverage etc. In this case we host Sonar on a linux machine, so we write a bash script to take regular backups of the MySql DB. Take a look at a simple sample script below :

#!/bin/bash
# script for taking database dump of sonar

user="root";
hostname="localhost";
db_name="sonar";
dateformat=`date +%d-%m-%y`;

mysqldump --defaults-extra-file="/usr/local/cron_scripts/.my.cnf" -u $user -h $hostname sonar | gzip > /usr/local/mysql_dumps/sonar_dump_$dateformat.sql.gz

chmod 755 /usr/local/mysql_dumps/sonar_dump_$dateformat.sql.gz

As you see, we have used mysqldump to take dump of the database, zip it, append it with the current date and store it at the said location.

Taking dumps of Sonar can be extremely useful for the purpose of importing and exporting of a Sonar instance from one machine to another without losing the analysis history.

MySQL JDBC Connector on Mac OSX

If you're working on any Java based program which uses MySQL as it's back-end database, you might find this article extremely helpful.

In order to pull data from the MySQL database, it is first important to establish a connection with it so that the script can talk with it.

The connection can be established by using the MySQL JDBC driver. Now, this driver needs to be separately downloaded. Here's the download link btw. If you're working on Mac OSX, please select the platform-independent version of the Connector from the drop down.

Once downloaded, untar the tar (or unzip the zip file). Copy the mysql-connector-x.x-bin.jar to /Library/Java/Extensions.

If you're using Eclipse IDE for editing class files, you need to make some changes in Eclipse's preferences. So go to Eclipse -> Preferences -> Java -> User Libraries. Click 'New' to create a new user library named as "MYSQL_CONNECTOR_LIBRARY" for instance. Now, click "Add External JARs..." button and browse to the downloaded jar : mysql-connector-x.x-bin.jar.

So if you're Java application is trying to connect to MySQL database, the following code snippet might come in handy for quick reference.

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;

try {

connection=DriverManager.getConnection("jdbc:mysql://localhost:3306", "username", "password");

}

List all users and passwords in MySql on OSX

If you want to list all the users and their passwords in MySql on OSX, type the following on Terminal :

In the mysql prompt:

mysql> select user, password from mysql.user;

The above command will display the entire list of Mysql users and their encrypted passwords in a tabulated format.

Note that, since the default encryption is not considered safe enough, it is often advised to use MD5 or SHA1 encryption. We will cover that topic in another post.

Login to Mysql on OSX

This post assumes that you already have installed MySql on your machine. If haven't done it yet, please download the relevant platform version from the Official Mysql Download site.

In-order to logon and invoke the MySql prompt, type the following in Terminal :

mysql -u <username> -p

Enter password : <Type your password here>

Eg:

mysql -u IroncladWriter -p
Enter Pasword: ***

On a successful login, you get the following prompt -