{"id":13414,"date":"2019-04-02T02:07:57","date_gmt":"2019-04-02T02:07:57","guid":{"rendered":"http:\/\/www.appservgrid.com\/paw92\/?p=13414"},"modified":"2019-04-02T02:07:57","modified_gmt":"2019-04-02T02:07:57","slug":"mysql-backup-and-restore-commands-for-database-administration","status":"publish","type":"post","link":"https:\/\/www.appservgrid.com\/paw92\/index.php\/2019\/04\/02\/mysql-backup-and-restore-commands-for-database-administration\/","title":{"rendered":"MySQL Backup and Restore Commands for Database Administration"},"content":{"rendered":"

This article shows you several practical examples on how to perform various backup operations of\u00a0MySQL<\/strong>databases using\u00a0mysqldump<\/strong>\u00a0command and also we will see how to restore them with the help of\u00a0mysql<\/strong>\u00a0and\u00a0mysqlimport<\/strong>\u00a0command in\u00a0Linux<\/strong>.<\/p>\n

mysqldump<\/strong>\u00a0is a command-line client program, it is used to dump local or remote\u00a0MySQL<\/strong>\u00a0database or collection of databases for backup into a single flat file.<\/p>\n

\n

\"How<\/a><\/p>\n

How to Backup and Restore MySQL Database<\/p>\n<\/div>\n

We assume that you already have\u00a0MySQL<\/strong>\u00a0installed on\u00a0Linux<\/strong>\u00a0system with administrative privileges and we assume that you already have a small amount of knowledge on\u00a0MySQL<\/strong>. If you don\u2019t have MySQL installed or don\u2019t have any exposure to\u00a0MySQL<\/strong>\u00a0then read our articles below.<\/p>\n

    \n
  1. Install MySQL Server on RHEL\/CentOS 6-5, Fedora 17-12<\/a><\/li>\n
  2. 20 MySQL Commands for Database Administration<\/a><\/li>\n<\/ol>\n

    How to Backup MySQL Database?<\/h3>\n

    To take a backup of\u00a0MySQL<\/strong>\u00a0database or databases, the database must exist in the database server and you must have access to it. The format of the command would be.<\/p>\n

    # mysqldump -u [username] \u2013p[password] [database_name] > [dump_file.sql]<\/pre>\n
    The parameters of the said command as follows.<\/p>\n
      \n
    1. [username]<\/strong>\u00a0: A valid MySQL username.<\/li>\n
    2. [password]<\/strong>\u00a0: A valid MySQL password for the user.<\/li>\n
    3. [database_name]<\/strong>\u00a0: A valid Database name you want to take backup.<\/li>\n
    4. [dump_file.sql]<\/strong>\u00a0: The name of backup dump file you want to generate.<\/li>\n<\/ol>\n
      How to Backup a Single MySQL Database?<\/h4>\n
      To take a backup of single database, use the command as follows. The command will dump database [rsyslog<\/strong>] structure with data on to a single dump file called\u00a0rsyslog.sql<\/strong>.<\/p>\n
      # mysqldump -u root -ptecmint rsyslog > rsyslog.sql<\/pre>\n
      How to Backup Multiple MySQL Databases?<\/h4>\n
      If you want to take backup of multiple databases, run the following command. The following example command takes a backup of databases [rsyslog<\/strong>,\u00a0syslog<\/strong>] structure and data in to a single file called\u00a0rsyslog_syslog.sql<\/strong>.<\/p>\n
      # mysqldump -u root -ptecmint --databases rsyslog syslog > rsyslog_syslog.sql<\/pre>\n
      <\/a>How to Backup All MySQL Databases?<\/h4>\n
      If you want to take backup of all databases, then use the following command with option\u00a0\u2013all-database<\/strong>. The following command takes the backup of all databases with their structure and data into a file called\u00a0all-databases.sql<\/strong>.<\/p>\n
      # mysqldump -u root -ptecmint --all-databases > all-databases.sql<\/pre>\n
      How to Backup MySQL Database Structure Only?<\/h4>\n
      If you only want the backup of database structure without data, then use the option\u00a0\u2013no-data<\/strong>\u00a0in the command. The below command exports database [rsyslog<\/strong>]\u00a0Structure<\/strong>\u00a0into a file\u00a0rsyslog_structure.sql<\/strong>.<\/p>\n
      # mysqldump -u root -ptecmint -\u2013no-data rsyslog > rsyslog_structure.sql<\/pre>\n
      How to Backup MySQL Database Data Only?<\/h4>\n
      To backup database\u00a0Data<\/strong>\u00a0only without structure, then use the option\u00a0\u2013no-create-info<\/strong>\u00a0with the command. This command takes the database [rsyslog<\/strong>]\u00a0Data<\/strong>\u00a0 into a file\u00a0rsyslog_data.sql<\/strong>.<\/p>\n
      # mysqldump -u root -ptecmint --no-create-db --no-create-info rsyslog > rsyslog_data.sql<\/pre>\n
      How to Backup Single Table of Database?<\/h4>\n
      With the below command you can take backup of single table or certain tables of your database. For example, the following command only take backup of\u00a0wp_posts<\/strong>\u00a0table from the database\u00a0wordpress<\/strong>.<\/p>\n
      # mysqldump -u root -ptecmint wordpress wp_posts > wordpress_posts.sql<\/pre>\n
      How to Backup Multiple Tables of Database?<\/h4>\n
      If you want to take backup of multiple or certain tables from the database, then separate each table with space.<\/p>\n
      # mysqldump -u root -ptecmint wordpress wp_posts wp_comments > wordpress_posts_comments.sql<\/pre>\n
      How to Backup Remote MySQL Database<\/h4>\n
      The below command takes the backup of remote server [172.16.25.126<\/strong>] database [gallery<\/strong>] into a local server.<\/p>\n
      # mysqldump -h 172.16.25.126 -u root -ptecmint gallery > gallery.sql<\/pre>\n
      How to Restore MySQL Database?<\/h3>\n
      In the above tutorial we have seen the how to take the backup of databases, tables, structures and data only, now we will see how to restore them using following format.<\/p>\n
      # # mysql -u [username] \u2013p[password] [database_name] < [dump_file.sql]<\/pre>\n
      How to Restore Single MySQL Database<\/h4>\n
      To restore a database, you must create an empty database on the target machine and restore the database using\u00a0msyql<\/strong>\u00a0command. For example the following command will restore the\u00a0rsyslog.sql<\/strong>\u00a0file to the\u00a0rsyslog<\/strong>database.<\/p>\n
      # mysql -u root -ptecmint rsyslog < rsyslog.sql<\/pre>\n
      If you want to restore a database that already exist on targeted machine, then you will need to use the\u00a0mysqlimport<\/strong>\u00a0command.<\/p>\n
      # mysqlimport -u root -ptecmint rsyslog < rsyslog.sql<\/pre>\n
      In the same way you can also restore database tables, structures and data. If you liked this article, then do share it with your friends.<\/p>\n
      Source<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
      This article shows you several practical examples on how to perform various backup operations of\u00a0MySQLdatabases using\u00a0mysqldump\u00a0command and also we will see how to restore them with the help of\u00a0mysql\u00a0and\u00a0mysqlimport\u00a0command in\u00a0Linux. mysqldump\u00a0is a command-line client program, it is used to dump local or remote\u00a0MySQL\u00a0database or collection of databases for backup into a single flat file. How … <\/p>\n
      Continue reading “MySQL Backup and Restore Commands for Database Administration”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-13414","post","type-post","status-publish","format-standard","hentry","category-linux"],"_links":{"self":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/13414","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/comments?post=13414"}],"version-history":[{"count":1,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/13414\/revisions"}],"predecessor-version":[{"id":13415,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/posts\/13414\/revisions\/13415"}],"wp:attachment":[{"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/media?parent=13414"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/categories?post=13414"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appservgrid.com\/paw92\/index.php\/wp-json\/wp\/v2\/tags?post=13414"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}