MySQL dump

Posted under » MySQL on 8 October 2009

There may be MySQL workbench, phpmyAdmin etc. but this method works, reliable and is most secure.

if windows

cd c:/mysql/bin

Basic dump

~$ mysqldump -u root -p database_name > database.sql

Specific table

~$ mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

Specific records

~$ mysqldump -u... -p... mydb t1 --where="student_id=4" > mydb_tables_stud4.sql

It's not advisable to put the password. Another user might check the cmd history.

If somehow the MySQL give out memory error, you can increase it without changing the MySQL config or the my.ini file by

~$ mysqldump -u root -p database_name > database.sql --max_allowed_packet=9999M

Restore

~$ mysql -u #username# -p #database# < #dump_file#

If cannot try this first

~$ mysql -u root -p databasename < #dump_file# 

Sometimes you cannot restore because of indexes. To bypass indexes,

~$ mysql -u root -p databasename --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;" < #dump_file#

» Mysql docu