Backup and restore in my sql mysqldump specific tables

Wikitechy | 2362 Views | mysql | 07 Jun 2016

 

  • 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

Applies to :

  • MySQL 3.23
  • MySQL 4.0
  • MySQL 4.1
  • MySQL 5.0

Related Tags :

  • backup and restore mysql database using mysqldump
  • backup and restore mysql database windows
  • backup and restore mysql database using mysql administrator
  • backup - Can I restore a single table from a full mysql mysqldump file
  • mysqldump - how to take backup of a single table in the mysql
  • Restoring a MySQL table back to the database
  • mysql - How do you mysqldump specific table
  • How to backup (dump) and restore a specific table in MySQL
  • Dumping Data in SQL Format with mysqldump
  • MySQL Database Backup & Restore using mysqldump



Workshop

Bug Bounty
Webinar

Join our Community

Advertise
<