Database | Spirit of Change

Archive for the Category » Database «

Sep

BigDump : Tips for Dumping Large MySQL Database

One main problem while moving my blog or my web application to another web hosting are moving and dumping the MySQL database. It should be no problem for small database or if an ISP has strong backup and restore feature but it will get you into headache for large database.

Hosting CPanel usually provided the backup and restore features but it will only restore your data without dumping the MySQL database. Dumping SQL from PHPMyAdmin or with MySQL command line limited for a few MB of file size. I get into big trouble while dumping my blog database with about 50 MB of SQL dump script while my ISP only limited the dump script for 2 MB.

Luckily, after a few Googling, I found bigdump, a simple and small PHP script with the capability to dump large MySQL database script. In my case, a 50 M MySQL database script has successfully dumped into database within 2 minutes process.

The usage is fairly easy. Just modify database connection script, upload the MySQL dump script and the modified bigdump script, open the web browser and then navigate to the relative path of Apache according to your upload folder.

Below is a step-by-step tutorial regarding the database dump script :

Download the bigdump.php from this link
Unzip (untar) and edit file bigdump.php, modify the database configuration according to your environment.
Changes the default setting for database encoding, I would like to recommend to use the encoding utf8 (replace $db_connection_charset = ”; with $db_connection_charset = ‘utf8′;) except you used another encoding.
```
// Database configuration
$db_server   = localhost
$db_name    = 'your database name';
$db_username = 'your user name';
$db_password = 'your password';
```
Upload the modified script
Click Start Import.
If you have a backup of MySQL dump and found and error while inserting data, try to investigating the SQL script. The SQL script created by CPanel has incorrect delimiter at the end of content a shown on the image below.

DELIMITER ;;
DELIMITER ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2009-05-11  0:57:57

Solve the problem by removing these lines.

Click here for more information regarding BigDump script.

Category: Database, OpenSUSE Tags: OpenSUSE, Zimbra 4 Comments

Oct

Amazing Tips for Improve, Tuning and Increase MySQL Database Performance

Vavai

Do you used MySQL Database as your database server and found that the performance is less powerfull than SQL Server, PostgreSQL, Oracle or another database server ? Let’s try to increase the performance by tuning up your database server with the following tips :

Checked your DNS setting and make sure that the setting are in the proper configuration
Add the IP address of host client into /etc/hosts entry on MySQL database server
Tips#1 & 2 will increase the speed of connection between host client and MySQL server. Without proper DNS setting and /etc/hosts entry, MySQL will be checked the address of host client for security reason.
Improve your MySQL Configuration and used improved setting
To prevent any problem while running on the production state, MySQL will be activating moderate setting by default. Moderate setting means that MySQL will not activating highly performance setting. Below is an example :
# This is for a system with little memory (32M – 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)MySQL usually included improved configuration called my-large.cnf. Checked this file out and applied the setting into my.cnf configuration. Please note that we must backup the my.cnf before trying to change any configuration. Also, don’t forget to making MySQL on the off mode before changing any setting.If we have powerful server with huge memory (ex : more than 2 GB), we could increase the performance by increasing the key_buffer_Size (for table with MYISAM engine) or innodb_buffer_pool_size (for table with inno-db engine) and increasing the table_cache setting.
Increase Server Memory (RAM)
It’s hardware improvement. High memory will ensure that server could serve data traffic without problem. Server will not used the swap memory if we have enough physical memory. Swap memory is okay while our memory is not enough but the swap memory usually more slower than physical memory
Do not used catch-all query, e.g : select *
Used specified column on the select clause. Select * will trying to get all of result, although it was not needed and doesn’t fit with all of situation. If we want to get item_name on the items table, I strongly recommended to use “Select item_name from items” rather than “select * from items”
Create an index for such column/fields. Used an index for searching field or any field whichh often be usage