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
# 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;'`
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*
ruby s3sync.rb -r -v -s --exclude="archieves$|s3backup.sh" /opt/s3backup/ $BUCKET:
mv /opt/s3backup/*.gz /opt/s3backup/archieves/
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.
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
Create MySQL User
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 email@example.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.