I had alot of problem when exporting database from one server to another. The large amount of data which is not needed for future purpose is log tables. These table contains much more data than we need. so first we have to TRUNCATE these tables:

TRUNCATE dataflow_batch_export;
TRUNCATE dataflow_batch_import;
TRUNCATE log_customer;
TRUNCATE log_quote;
TRUNCATE log_summary;
TRUNCATE log_summary_type;
TRUNCATE log_url;
TRUNCATE log_url_info;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;
TRUNCATE log_visitor_online;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
TRUNCATE report_event;
TRUNCATE index_event;

After that export database MY_DATABASE in file db_magento.sql with following command

mysqldump -u USERNAME -p --single-transaction MY_DATABASE > db_magento.sql

Here USERNAME should be given the mysql username or “root” and then it will ask for password. if your mysql server is without password then just remote “-p” from above command.

then upload this db_magento.sql to server by ftp or scp any command and import with following command

mysql -u USERBAME -p MY_DATABASE < db_magento.sql

Note: If you are using windows servers then you have to be in mysql’s bin directory something like : “C:\Program Files\MySQL\MySQL Server 5.4\bin” for running above command

After importing this database you have to change two place to run magento exactly same way as old server. run following sql in mysql with changing NEW_MAGENTO_URL with your magento url.

update `core_config_data` set `config_id`='7',`scope`='default',`scope_id`='0',`path`='web/secure/base_url',`value`='http://NEW_MAGENTO_URL/' where `config_id`='7'
update `core_config_data` set config_id`='6',`scope`='default',`scope_id`='0',`path`='web/unsecure/base_url',`value`='http://http://NEW_MAGENTO_URL/' where `config_id`='6'

Note: you can just update web/unsecure/base_url and web/secure/base_url of core_config_data table.