how can i take backup of mysql database tables

Question

i want to move mysql database tables which resides in one computer to another computer. how can i create dump file as we created in Oracle ? i m using exp command but not working.

Answer 1

mysqldump -u <username> -p<password> -h <hostname> <dbname> <tablename> > filename.sql

Now you may need to take the dump of just the schema. For example you use a command called like.

mysql> show create table tablename;

this will give you the query that created the table. Now for some reason you need to take schema dump of all the tables inside you database/databases you may use -d option like this

mysqldump -d -u <username> -p<password> -h <hostname> <dbname> > filename.sql

The -d option means "without data". Now you have full dump without the data and just the schema.

Answer 2

Use mysqldump.

mysqldump -u <username> -p<password> <db_name> > <filename>.sql

To import, create empty database named <db_name>, thena -

mysql -u <username> -p<password> <db_name> < <filename>.sql

To export all databases -

mysqldump -u <username> -p<password> --all-databases > <filename>.sql