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.
#!/bin/bash
# Database Configurations
BUCKET=backup.javachap.com
DB_USER=backup
DB_PWD=readonly
NOW=$(date +_%m_%d_%y)
S3_BACKUP=/opt/s3backup
CVSROOT=/usr/local/cvsroot
CVS_BACKUP_NAME=jc_cvs_backup$NOW.tar.gz
MYSQL_BACKUP_NAME=jc_mysql_backup$NOW.sql.gz
cd $S3_BACKUP
# tar the cvsroot folder
tar czf $CVS_BACKUP_NAME $CVSROOT
# list MySQL databases and dump each
DB_LIST=`mysql -u $DB_USER -p"$DB_PWD" -e'show databases;'`
DB_LIST=${DB_LIST##Database}
for DB in $DB_LIST;
do
FILENAME=mysql_${DB}${NOW}.sql.gz
mysqldump -u $DB_USER -p$DB_PWD --opt --flush-logs $DB | gzip > $FILENAME
done
tar czvf $MYSQL_BACKUP_NAME mysql*
rm -f mysql*
cd /opt/s3sync
ruby s3sync.rb -r -v -s --exclude="archieves$|s3backup.sh" /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:
- 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.
- Employ Master-Master Replication. Immediately have a read-only slave for your application to hit as well.
- Use Your Memory. Set innodb_buffer_pool_size, key_buffer_size and other key options.
- 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.
- 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
SELECT * INTO OUTFILE 'data.txt' FIELDS TERMINATED BY ',' FROM mytable;
- To import the csv file into table execute the following SQL Query.
LOAD DATA INFILE 'data.txt' INTO TABLE mytable FIELDS TERMINATED BY ',';
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 leadapp.zip (5.66 Mb)
Building and deploying the application
- Create a schema/user using the schema.sql file in src/database
- Open build.properties 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 user@javachap.com/javachap
I hosted this application on stax.net, access it from here http://hw4999.dvkvarma.staxapps.net, please write in the comments section if you have any problems running the application.
Application Screenshots
