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.

Unlikely, but when you want to migrate, you need to dump all dbs.

~$ mysqldump -u root -p --all-databases > migrate.sql

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