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
