Script to Backup MySQL and CVS to Amazon S3

Here is a simple script that i wrote to backup mysql and cvs root folder to Amazon S3. For the script to work you need to have S3Sync to be in path.


# Database Configurations
NOW=$(date +_%m_%d_%y)



# tar the cvsroot folder

# list MySQL databases and dump each
DB_LIST=`mysql -u $DB_USER -p"$DB_PWD" -e'show databases;'`
for DB in $DB_LIST;
  mysqldump -u $DB_USER -p$DB_PWD --opt --flush-logs $DB | gzip > $FILENAME

tar czvf $MYSQL_BACKUP_NAME mysql*
rm -f mysql*

cd /opt/s3sync
ruby s3sync.rb -r -v -s --exclude="archieves$|" /opt/s3backup/ $BUCKET:

mv /opt/s3backup/*.gz /opt/s3backup/archieves/

The 5 Biggest Ways to Boost MySQL Scalability

Sean Hall with some really good MySQL advice:

  1. Tune those queries. Biggest bang for your buck. Enable the slow query log and watch it. Once you’ve found a heavy resource intensive query, optimize it! Tune what receives real-world traffic.
  2. Employ Master-Master Replication. Immediately have a read-only slave for your application to hit as well.
  3. Use Your Memory. Set innodb_buffer_pool_size, key_buffer_size and other key options.
  4. RAID Your Disk I/O. Use RAID 10 mirroring and striping. On EC2 striping across a number of EBS volumes using the Linux md software raid.
  5. Tune Key Parameters. speeds up inserts & updates with innodb_flush_log_at_trx_commit=2; create a tablespace and underlying datafile for each table with innodb_file_per_table.

Full details at the original article.

MySQL Import and Export

Here are few MySQL Queries/commands that i regularly use for exporting and importing MySQL data.  This is for my reference, but just thought this might be useful for newbies.

MySQL Table Data Export and Import
  • To export the just table data please use the following query
  • To import the csv file into table execute the following SQL Query.
Copy the contents of one database to another
  • First, create the new database
    create database db2;
  • Then, from the command-line, do the following to copy the contents from db1 to db2.
    $ mysqldump -u root --password=pass db1 | mysql -u root --password=pass db2
MySQL Dump
  • To export the MySQL database into a dump file, please execute the following command
    $ mysqldump -u root --password=pass db1 > dbdump.sql
Create MySQL User
  • Creating MySQL user and granting access
    CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser';
    GRANT ALL ON *.* TO 'myuser'@'localhost' IDENTIFIED BY 'myuser';

CRUD Application : Struts 1, Hibernate and MySQL

Here is my Sample Struts, Hibernate CRUD application. This can act as starting point for those who are starting to build applications on hibernate and struts. I know struts 1 is sort of out dated, but i still think there are lot of legacy applications running on Struts 1.

This application has the following features.

  • Basic Create, Update and Delete Operations
  • Security (Login Support)
  • Validation (Struts Validations)
  • Internationalization and Localization (Look at the Links on the top of page, you can change the language of the application).
  • Ant build script to build and deploy the application.

Download the source code (5.66 Mb)

Building and deploying the application

  • Create a schema/user using the schema.sql  file in src/database
  • Open and modify “tomcat.home” property to point your tomcat home directory
  • Database username/password can be configured in the hibernate config file \WebRoot\WEB-INF\hibernate.cfg.xml
  • Issue “ant clean deploy” command to build and deploy the application to tomcat.
  • Access the application http://localhost:8080/leadapp  (assuming tomcat is running on 8080 port)
  • username/password is

I hosted this application on, access it from here, please write in the comments section if you have any problems running the application.

Application Screenshots

Login Page

Lead Listing Page

Lead Create Page