Monday, March 7, 2011

How to dump database perfectly?

Sometimes you're requested to repair some portal function. To do that, maybe you need to test is at localhost.
Then you're provided database. This database must be dumped into database in localhost.

The common things that happened are database cannot be dumped perfectly. Maybe you found error on generate Foreign Key, or table name not existed etc.

I will explain base on these criteria.
A. Application Server: XAMPP
B. OS: Windows (XP or 7)
C. My DB Name: myportal

I installed application server in the shortest path; i.e C:\xampp

1. Open my.ini file in C:\xampp\mysql\bin
2. Then under [mysqldump] add this line
3. Change max_allowed_packet to greater value
max_allowed_packet = 80M
Note: Make sure both values for max_allowed_packet under [mysqldump] and [mysqld] are same.
4. Restart mysql service in xampp Control Panel. [Click 'Stop' and then 'Start']

[Figure1: xampp Control Panel]

5. Open cmd windows. Point to bin.
cd \xampp\mysql\bin
6. Type command line to dump database
mysql -u root myportal < "C:\project\myportal\myportalbackup03072011.sql"
7. Press Enter
8. Restart tomcat server.