• Previous
• 437 Likes
• Next

• mysqldump is one of the effective tool to take a backup of MySQL database.

• The output provides you a *.sql file with 

1. DROP table,

2. CREATE table and 

3. INSERT into sql-statements of the source database. 

• To restore the database,  execute the *.sql file on destination database.
  

Backup a single database:

• This example takes a backup of wikitechy database and dumps the output to wikitechy.sql

# mysqldump -u root -pvenkat_password wikitechy > wikitechy.sql
# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Backup all the databases:

• Below command enables you to take a backup of  all the database of the MySQL instance.

# mysqldump -u root -pvenkat_password --all-databases > /tmp/all-database.sql

Dumping only specific table:

• If you are dumping tables t1, t2, and t3 from wikitechydb

mysqldump -u root -pvenkat_password wikitechydb t1 t2 t3 > wikitechydb_tables.sql

Example :

 mysqldump -u root -pvenkat_password wikitechydb accountsTbl \ > /dbbackup.sql

Dumping all the table leaving specific table:

• If you have a ton of tables in wikitechydb and you want to dump everything except t1, t2, and t3, do this:
  

DBTODUMP= wikitechydb
SQL="SET group_concat_max_len = 10240;"
SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')"
TBLIST=`mysql -u... -p... -AN -e"${SQL}"`
mysqldump -u root -pvenkat_password ${DBTODUMP} ${TBLIST} > wikitechydb_tables.sql

• Alternatively, we can write this command too. Considering I want to exclude Table1 and Table2
  

mysqldump -u root -pvenkat_password wikitechydb --ignore-table= wikitechydb.Table1 --ignore-table= wikitechydb.Table2 > dump_file.sql

Exact query :

mysqldump -u root -pvenkat_password wikitechydb \
  --ignore-table=schema.tablename1    \
  --ignore-table=schema.tablename2    \
  --ignore-table=schema.tablename3 > mysqldump.sql

Restore a database

• Once the database is backed we need to restore the data in another location. In the above example, we got the dump file. We need to restore to get back the database, tables and other db objects from this backed up sql file. 

# mysql -u root -pvenkat_password
mysql> create database wikitechydb;
Query OK, 1 row affected (0.02 sec)
# mysql -u root -pvenkat_password wikitechydb < /tmp/all-database.sql

---

---