Database Research & Development

  • Home
  • NoSQL
    • NoSQL
    • Cassandra
  • Databases
    • Database Theory
    • Database Designing
    • SQL Server Coding Standards
    • SQL Server
    • PostgreSQL
    • MySQL
    • Greenplum
    • Linux
  • Interviews
    • SQL Server Interviews
    • MySQL Interviews
    • SQL Puzzles
  • DBA Scripts
    • SQL Server DBA Scripts
    • PostgreSQL DBA Scripts
    • MySQL DBA Scripts
    • Greenplum DBA Scripts
  • Home
  • Blog Archives !
  • (: Laugh@dbrnd :)
  • Contact Me !
sqlserverinterviews
Home 2016 January MySQL: Backup and Restore Database using mysqldump

MySQL: Backup and Restore Database using mysqldump

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing a script for performing Backup and restoring of MySQL Database using Mysqldump.

Mysqldump is an important tool for MySQL Server, and it creates a *.sql file with database structure and data. It also includes all possible table lock and objects existence script.

I am sharing different syntax and scripts for performing the Backup and Restore of a single database or multiple databases.

Syntax and Command to take the backup of Single Database:

1
2
3
4
5
-- Syntax
mysqldump -u root -p[root_password] [database_name] > dumpMyFileName.sql
 
-- Example
mysqldump -u root -pTestPass MyDatabase > MyDBBackup.sql

Syntax and Command for restoring of Single Database:
The new blank database requires being created before any restore operation.

1
2
3
4
5
-- Syntax
mysqldump -u root -p[root_password] [database_name] < dumpMyFileName.sql
 
-- Example
mysqldump -u root -pTestPass NewDatabase < MyDBBackup.sql

Command to take backup of multiple databases:

1
mysqldump -u root -pTestPass --databases MyDB1 MyDB2 > MultipleDB.sql

Command to take backup of all databases:

1
mysqldump -u root -pTestPass --all-databases > All.sql

Jan 5, 2016Anvesh Patel
PostgreSQL: How to Allow Remote Connection to Connect DatabaseSQL Server: Script to Remove Extra Spaces from a String
Comments: 2
  1. Harikrishna Guggilla
    January 6, 2016 at 3:19 am

    To take Backup using mysqldump is right but for restoring is not fair using mysqldump So i think we have to go with
    Backup:
    mysqldump -u root -p –single_transaction –routines > “path.sql”

    Restore:
    To restore the data mysqldump is not fair for restore.
    mysql -u root -p

    We have another way to to take Backup and restore using utilities. Utilities is better than mysqldump . It is much faster than mysqldump

    syntax:

    step 1;

    select * into outfile ‘D:\mysql\backup\sakila.csv’
    fileds terminated by ‘,’
    enclosed by ‘”‘
    lines terminated by ‘\r\n’ from sakila;

    step2 :
    create table sakila_tmp like sakila;

    step 3:

    load data infile ‘D:\mysql\backup\sakila.csv’
    into table sakila_tmp
    fields terminated by ‘,’
    enclosed by ‘”‘
    lines terminated by ‘\r\n’;

    Is there any wrong Sorry

    Thank you

    From
    Harikrishna Guggilla
    harimysqldba@gmail.com

    • Anvesh Patel
      Anvesh Patel
      January 6, 2016 at 4:41 am

      Thanks, this way also we can do. Very soon, I will prepare video help for this.

Anvesh Patel
Anvesh Patel

Database Engineer

January 5, 2016 MySQL, MySQL DBA ScriptAnvesh Patel, backup, database, database research and development, dbrnd, MySQL, MySQL Command, MySQL Database Administrator, MySQL Database Designing, MySQL Database Programming, MySQL Error, MySQL Performance Tunning, MySQL Query, MySQL Tips and Tricks, mysqldump, restore
About Me!

I'm Anvesh Patel, a Database Engineer certified by Oracle and IBM. I'm working as a Database Architect, Database Optimizer, Database Administrator, Database Developer. Providing the best articles and solutions for different problems in the best manner through my blogs is my passion. I have more than six years of experience with various RDBMS products like MSSQL Server, PostgreSQL, MySQL, Greenplum and currently learning and doing research on BIGData and NoSQL technology. -- Hyderabad, India.

About DBRND !

dbrnd

This is a personal blog (www.dbrnd.com).

Any views or opinions represented in this blog are personal and belong solely to the blog owner and do not represent those of people, institutions or organizations that the owner may or may not be associated with in professional or personal capacity, unless explicitly stated.

Feel free to challenge me, disagree with me, or tell me I’m completely nuts in the comments section of each blog entry, but I reserve the right to delete any comment for any reason whatsoever (abusive, profane, rude, or anonymous comments) - so keep it polite.

The content of this website is protected by copyright. No portion of this website may be copied or replicated in any form without the written consent of the website owner.

Recent Comments !
  • Anvesh Patel { Sure will do... } – May 27, 12:43 PM
  • Anvesh Patel { Great... } – May 27, 12:41 PM
  • Anvesh Patel { Great... } – May 27, 12:39 PM
  • Anvesh Patel { Great... } – May 27, 12:36 PM
  • Anvesh Patel { Great... } – May 27, 12:28 PM
  • Anvesh Patel { Great... } – May 27, 12:27 PM
  • Anvesh Patel { Great... } – May 27, 12:16 PM
  • Older »
Follow Me !
  • facebook
  • linkedin
  • twitter
  • youtube
  • google
  • flickr
© 2015 – 2019 All rights reserved. Database Research & Development (dbrnd.com)
Posting....