1. dump all database into backup file

mysqldump –opt  –all-databases > /tmp/databkup

(–opt includes lock table option)

2. dump several databases

mysqldump –opt –databases db1 db2 db3 > /tmp/databkup

3. dump single table

mysqldump –opt db1 table1 > /tmp/databkup

Touble shooting
Error: Access denied for user x@y to database z when using lock tables
mysqldump -u username -p database –single-transaction >dump.sql

User tar to back up db

mysql> FLUSH TABLES WITH READ LOCK;

(This will lock the db so it won’t be updated by other threads. Write threads by other process will wait till the lock is released)

#tar cvzf /var/tmp/datadump.tgz /var/lib/mysql/*

mysql> UNLOCK TABLES;