Backup and restore in my sql mysqldump specific tables
- 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 :
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