Backup MySQL database with MyISAM & InnoDB tables

Question

I have a MySQL database with mixed up tables (MyISAM, InnoDB).

How can I create a full backup of the database with mysqldump via Linux command line, what option should I use?

Answer 1

There is no convenient solution currently:

• MyISAM tables need the option --lock-tables
• InnoDB tables need the option --single-transaction --skip-lock-tables

Both options are mutually exclusive. You must select databases first and then dump them separately.

Or...

If you have databases with both types of tables, just run mysqldump twice...

Answer 2

use below-

Generic command for all db's is-

mysqldump -uroot -proot123 -A > /path/mydbbackup.sql

If you want to take few or all db's as per your choice then-

mysqldump -uroot -proot123 -B mydb1 mydb2 mydb3 > /path/mydbbackup.sql

If you want to avoid locking then use single transaction option-

mysqldump --single-transaction -uroot -proot123 -A > /path/mydbbackup.sql

If you want to take specific db backup then-

mysqldump -uroot -proot123 mydb > /path/mydbbackup.sql

If you want to take a table backup then-

mysqldump -uroot -proot123 mydb mytable > /path/mydbbackup.sql

where username is root and password is root123, you can change as per your's

Note: mysqldump utility takes innodb and myisam both backups.