Periodic backups MySQL Database Server in Solaris 10

Indonesian Version

After several years struggling with the Solaris server, I am grateful much new knowledge that can digging during that period, and it makes me want to share with others. And now I would like to share how MySQL database backup periodically, into compressed *. tar.gz file, this can be done by using 'time scheduler' if used windows OS, but what would we use if it works in Solaris environment as a server, and now we going to discuss it.

The first thing to know is how the process of 'dump and restore' in MySQL database, as has been stated in the manual doc, process can be done by execution of command as follows:

mysqldump –u username --password=pass_user --databases schema_db > one_file.sql

The script above is showing database command for backup into *.sql file, for a glance we can describe this commands as follows: 'mysqldump' is one of few basic commands in MySQL database to save the database to a file, '-u' is one of property command in mysql to access database using username, which is an important component to access MySQL database, 'username' is used to authorize users to access and manipulate commands in the database, for this we will use 'root' as user, '- password = pass_user ', while MySQL passwords are properties that must be included when users want to access the database as an authentication key, while 'schema_db' is a name of database schema to be data loaded into SQL dump file.

After 'dump' the database schema into a file 'one_file.sql' finished, the next process is to change the file into more compact file by using '*. tar.gz' extension, in the following manner:

tar –cf one_file.sql | gzip –c > /destination_folder/one_file_tar.tar.gz

And now we get sql dump files that have been zipped into tar.gz file, then what else to do? Next is to remove sql file from temporary folder so that left behind is only tar.gz file using 'rm' command.

rm *.sql

And now we know some major orders in Autobackup process on server, now we're going to combine a collection of several commands into 'sh' file which can be executed. For example if we send a command to database server use Solaris platform with database schema named 'gienet' that will be saved to a folder '/export/home0/gienet_backup', backup file with naming format 'gienet-yyyy-MM-dd-HH-mm-ss.sql', which will be save in zip format with naming convention 'gienet-yyyy-MM-dd-HH-mm-ss.tar.gz', this backup process will execute when the clock system shows at 00:30.

Now lets create a file named backup-script.sh (names not required to use this name), use command 'touch backup-script.sh' in the shell, then do editing with this command 'vi backup-script.sh', begins with the first line contains '#! / bin / sh' with a few lines of comment followed by examples like show below:

#!/bin/sh
#
# This is example executable file for mysql database backup  
#

Continue with add a declaration variable for date format on the file name, and also folder name variable that will become a place to store backup files.

NEW_DATE=’date +%Y-%m-%d-%T’
NEW_DIR=’/export/home0/gienet_backup’

Then proceed with the database script backup, assuming we use a root as user to perform backups and 'admin' as password, then the next script which should be written like this:

mysqldump –u root –password=admin –-databases gienet > $NEW_DIR/gienet-$NEW_DATE.sql

Once the mysqldump process completed and produces 'gienet-yyyy-MM-dd-HH-mm-ss.sql' file continue with a compress the files to smaller sizes into a tar.gz file with the command like show below:

cd $NEW_DIR
tar –cf gienet-$NEW_DATE.sql | gzip –c > $NEW_DIR/gienet-$NEW_DATE.tar.gz

Perform delete sql files in a folder that was setting in variable '$ NEW_DIR' with the aim of remaining files in the folder only tar.gz file and maintain disk space not full by ambiguous file contents, with this script:

rm $NEW_DIR/*.sql

It's finished to make MySQL database automatic backups scripts in the file 'backup-script.sh', do the saving process on the active editor 'vi' with command ':wq!'. The process followed by move the file to the folder '/usr/bin' and give the file system permissions with this command 'chmod + x backup-script.sh', and then register the file to Solaris scheduler use 'crontab', you must first do some setting editor default in shell. With this command sequence:

#export DISPLAY=vi
#crontab -e

So the shell will produce output like shows below:

#ident  "@(#)root       1.21    04/03/23 SMI"
#
# The root crontab should be used to perform accounting data collection.
#
#
10 3 * * * /usr/sbin/logadm
15 3 * * 0 /usr/lib/fs/nfs/nfsfind
30 3 * * * [ -x /usr/lib/gss/gsscred_clean ] && /usr/lib/gss/gsscred_clean
#
# The rtc command is run to adjust the real time clock if and when
# daylight savings time changes.
#
1 2 * * * [ -x /usr/sbin/rtc ] && /usr/sbin/rtc -c > /dev/null 2>&1
#10 3 * * * /usr/lib/krb5/kprop_script ___slave_kdcs___

Point the cursor on last row and last column then press enter, do some input script to run the backup-script.sh file for every 0:30 AM every day in server with the script as follows:

#
# Script for autobackup database
#
30 0 * * * sh /usr/bin/backup-script.sh

To learn more about crontab script, it can be seen in http://adminschoice.com/crontab-quick-reference, to know the script running as desired, the next morning you can see to the folder '/export/home0/gienet_backup', if there any 'gienet-yyyy-MM-dd-HH-mm-ss.tar.gz' files, so the script is successfully without any error, if not meaning there still have errors in script. Do some check to that script and repeat the process from above.

Maybe for now, only this knowledge can be shared with readers, and hopefully may be useful, if there is a shortage or entries which can improve please feel free to add comments.

Read More...

Hibernate Criteria Query - MySQL Command Syntax Dictionary

Indonesian Version

For some viewer already know about Java Platform may already familiar with Hibernate, and so for viewer already familiar with Hibernate probably already know with “Criteria Query” term. In this time I’ll try to discuss equality function between Criteria Query and MySQL, with purpose to help other in order to learn about Hibernate and how the function similarity between Criteria Query and basic standard command in MySQL query syntax.

And now we can start to discuss those thing, first we take a table for example in MySQL and going to convert into a class model with java platform and follow by term of Hibernate. In this case we going to use Hibernate Annotation so we not need xml file for field class mapping to field table in a database. For simple case we try to create a table and called it with name “karyawan” and so we create java class model with a same name.

Nama Field		Type Field		Primary Key
id		Integer / Numeric		Yes
nama		Varchar(30)
tgl_masuk		Date
upah		Decimal(20,10)

With this command we can create this table on MySQL:

CREATE TABLE `karyawan` (                                  
  `id` int(11) NOT NULL auto_increment,                    
  `nama` varchar(30) default NULL,                         
  `tgl_masuk` date NOT NULL default '1970-01-01',          
  `upah` decimal(20,10) default NULL,                      
  PRIMARY KEY (`id`)                                      
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC  

Then create model class “Karyawan” made by these syntax show below:

@Entity
@Table(name="karyawan")
public class Karyawan implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    
    @Column(name="nama")
    private String nama;
    
    @Temporal(TemporalType.DATE)
    @Column(name="tgl_masuk",nullable=false,columnDefinition="date")
    private Date tglMasuk;
    
    @Column(name="upah")
    @Type(type="big_decimal")
    private BigDecimal upah;
    
    public Karyawan() {}

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getNama() {
        return nama;
    }

    public void setNama(String nama) {
        this.nama = nama;
    }

    public Date getTglMasuk() {
        return tglMasuk;
    }

    public void setTglMasuk(Date tglMasuk) {
        this.tglMasuk = tglMasuk;
    }

    public BigDecimal getUpah() {
        return upah;
    }

    public void setUpah(BigDecimal upah) {
        this.upah = upah;
    }
}  

By assuming that you already know about Hibernate by this reference article “Proses CRUD Dengan Hibernate Annotations Menggunakan Netbean 6.0”, then we can strait to discuss how to use Criteria Query, we can use these class “Criteria” from “org.hibernate.Criteria” or “DetachedCriteria” from “org.hibernate.criterion.DetachedCriteria” and followed by these class “Expression” from “org.hibernate.criterion.Expression” or “Restriction” from “org.hibernate.criterion.Restriction”, and than with assumption we going to call all data in the table “Karyawan” then we can use this SQL command like shown below:

select * from Karyawan;

whereas used Criteria Query even though “Criteria” or “DetachedCriteria” will produce command line syntax like shown below:

getSession().createCriteria(Karyawan.class).list();

The table shown below are equality command between Criteria Query and MySQL command:

MySQL	Hibernate Criteria Query
select * from Karyawan where id = 1;	getSession().createCriteria(Karyawan.class) .add(Expression.eq(“id”, Long.valueOf(1))) .list();
select * from Karyawan where id != 1;	getSession().createCriteria(Karyawan.class) .add(Expression.ne(“id”, Long.valueOf(1))) .list();
select * from Karyawan where id <> 1;	getSession().createCriteria(Karyawan.class) .add(Expression.ne(“id”, Long.valueOf(1))) .list();
select * from Karyawan where nama = “sesuatu”;	getSession().createCriteria(Karyawan.class) .add(Expression.eq(“nama”, “sesuatu”)) .list();
select * from Karyawan where tgl_masuk = “1900-01-01”;	getSession().createCriteria(Karyawan.class) .add(Expression.eq(“tglMasuk”, new java .util.Date(java.sql.Date .valueOf("1900-01-01")))) .list();
select * from Karyawan where upah = 10000;	getSession().createCriteria(Karyawan.class) .add(Expression.eq(“upah”, BigDecimal .valueOf(10000))) .list();
select * from Karyawan where id > 10;	getSession().createCriteria(Karyawan.class) .add(Expression.gt(“id”, Long.valueOf(10))) .list();
select * from Karyawan where id < 10 and id > 30;	getSession().createCriteria(Karyawan.class) .add(Expression.lt(“id”, Long.valueOf(10))) .add(Expression.gt(“id”, Long.valueOf(30))) .list();
select * from Karyawan where id < 10 or id > 30;	getSession().createCriteria(Karyawan.class) .add(Expression.or(Expression.lt(“id”, Long.valueOf(10)), Expression.gt(“id”, Long.valueOf(30)))) .list();
select * from Karyawan where id <= 10;	getSession().createCriteria(Karyawan.class) .add(Expression.le(“id”, Long.valueOf(10))) .list();
select * from Karyawan where id >= 10;	getSession().createCriteria(Karyawan.class) .add(Expression.ge(“id”, Long.valueOf(10))) .list();
select * from Karyawan where nama like ‘sesu%’;	getSession().createCriteria(Karyawan.class) .add(Expression.like(“nama”, “sesu”, MatchMode.END)) .list();
select * from Karyawan where nama like ‘%sua%’;	getSession().createCriteria(Karyawan.class) .add(Expression.like(“nama”, “sua”, MatchMode.ANYWHERE)) .list();
select * from Karyawan where nama like ‘%atu’;	getSession().createCriteria(Karyawan.class) .add(Expression.like(“nama”, “atu”, MatchMode.START)) .list();
select * from Karyawan where nama like ‘sesuatu’;	getSession().createCriteria(Karyawan.class) .add(Expression.like(“nama”, “sesuatu”, MatchMode.EXACT)) .list();
select * from Karyawan where id between 0 and 100;	getSession().createCriteria(Karyawan.class) .add(Expression.between(“id”, Long.valueOf(0), Long.valueOf(100))) .list();
select * from Karyawan where nama = ‘’;	getSession().createCriteria(Karyawan.class) .add(Expression.isEmpty(“nama”)) .list();
select * from Karyawan where nama <> ‘’;	getSession().createCriteria(Karyawan.class) .add(Expression.isNotEmpty(“nama”)) .list();
select * from Karyawan where nama is null;	getSession().createCriteria(Karyawan.class) .add(Expression.isNull(“nama”)) .list();
select * from Karyawan where nama is not null;	getSession().createCriteria(Karyawan.class) .add(Expression.isNotNull(“nama”)) .list();
select * from Karyawan where id in (1,3,5,7);	List<Long> idlist = new ArrayList<Long>(); idlist.add(1); idlist.add(3); idlist.add(5); idlist.add(7); getSession().createCriteria(Karyawan.class) .add(Expression.in(“id”, idlist)) .list();
select * from Karyawan where id not in (1,3,5,7);	List<Long> idlist = new ArrayList<Long>(); idlist.add(1); idlist.add(3); idlist.add(5); idlist.add(7); getSession().createCriteria(Karyawan.class) .add(Expression.not(Expression.in(“id”, idlist))) .list();
select * from Karyawan where id > 1 order by id ASC;	getSession().createCriteria(Karyawan.class) .addOrder(Order.asc(“id”)) .add(Expression.ge(“id”, Long.valueOf(1))) .list();
select * from Karyawan where id > 1 order by id DESC;	getSession().createCriteria(Karyawan.class) .addOrder(Order.desc(“id”)) .add(Expression.ge(“id”, Long.valueOf(1))) .list();

For additional note, these “Expression.not” or “Restriction.not” command cannot used for MySQL query command like this “Select * from Karyawan where id <> 1” or “Select * from Karyawan where id != 1” though in syntax show where condition “not equal” cause by this command in Criteria Query already represent by “Expression.ne” or “Restriction.ne”. As reference for correct syntax, if Hibernate command we created as part of web-app using Spring Framework, then all example show above must written in Class DAO Implementation, but if isn’t than it necessary to write in Object Class. Like sample below:

@SuppressWarnings("unchecked")
public List<Karyawan> loadContohSatu() {
    List<Long> idlist = new ArrayList<Long>();
    idlist.add(1);
    idlist.add(3);
    idlist.add(5);
    idlist.add(7);
      
    return getSession().createCriteria(Karyawan.class)
    .add(Expression.not(Expression.in(“id”, idlist))).list();
}

@SuppressWarnings("unchecked")
public List<Karyawan> loadContohDua() {
    return getSession getSession().createCriteria(Karyawan.class)
    .addOrder(Order.asc(“id”))
    .add(Expression.ge(“id”, Long.valueOf(1))).list();
}

Perhaps for this moment this knowledge that I can share to all reader, if in this article contain some mistake, please don’t mind to correct me if I done some wrong or give me some advise and comment.

Read More...