Mysql Backup Database
Shara Mchale
In this article, I am going to explain different ways to generate the backup in the MySQL database server. As we know, data is a valuable asset to the organization. As database administrators, it is our primary and crucial job to keep the data available and safe. If the system or data center fails, database corruption, and data loss, we must be able to recover it within the defined SLA.
Different database platforms provide various methods to generate the backup and restore the database. Many vendors provide state-of-the-art software and hardware solutions that can help to back up the database, and it can restore the database within the defined RTO and RPO.
Mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data. The command can also be used to generate the output in the XML, delimited text, or CSV format.
This command is easy to use, but the only problem that occurs while restoring the database. As I mentioned, when we generate a backup of the MySQL database, it creates a backup file that contains SQL commands that are necessary to rebuild or restore the database. Now, when we restore the database, the command executes all the SQL Statements to create tables and insert the data. If you have a large database, then the restoration process takes a long time to complete.
Note: By default, mysqldump command does not dump the information_schema database, performance_schema, and MySQL Cluster ndbinfo database.
There are lots of options and features that can be used with mysqldump. You can view the complete list of options here. I am going to some of the basic features. Following is the syntax of the mysqldump utility.
If you want to generate the backup of a specific table, then you must specify the name of the tables after the name of the database. The following command generates the backup of the actor table of the sakila database.
If you want to generate the backup of more than one tables, than you must separate the names of the tables with space, the following command generates the backup of actor and payment table of sakila database.
Restoring a MySQL database using mysqldump is simple. To restore the database, you must create an empty database. First, let us drop and recreate the sakila database by executing the following command.
When you restore the database, instead of using mysqldump, you must use mysql; otherwise, the mysqldump will not generate the schema and the data. Execute the following command to restore the sakila database:
For instance, someone dropped a table from the database. Instead of restoring the entire database, we can restore the dropped table from the available backup. To demonstrate, drop the actor table from the sakila database by executing the following command on the MySQL command-line tool.
Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargup...@outlook.com
The article provides an overview of the backup types available in MySQL and describes how-to examples of using the mysqldump command-line utility to take a backup of the database, tables, data, or schema and to restore the MySQL database.
In addition, you can view how to generate a database backup with MySQL Workbench and how simple and quick it is to perform the same task with the MySQL dump tool available in dbForge Studio for MySQL.
In MySQL, there are different types of backup. Which backup strategy to choose depends on several factors, for example, data size, the hardware you use, performance you want to achieve, storage capacity of your database, etc. In addition, you should take into consideration how much time it will take to restore the backup.
mysqldump is a command-line utility used to generate a MySQL logical database backup. It creates a single .sql file that contains a set of SQL statements. This file can be used to create tables, objects, and/or insert data that were copied from the database. With the utility, you can dump tables, one or several databases, or the entire server with all databases and tables along with their objects or migrate them to another server.
Note: As a rule, mysqldump is located in the MySQL Server installation directory. For example, for MySQL 8.0, the path to the directory is C:\Program Files\MySQL\MySQL Server 8.0\bin. Thus, we recommend adding this directory to the mysqldump command.
The mysqldump command can generate a backup of all or specific tables in the database by adding the selected table names to the command. Keep in mind that the names of the tables should be separated by a space. For example, dump the category, city, and country tables by running the following command:
3. Under Export Options, select Export to Dump Project Folder if you want database tables to be stored to separate .sql files or Export to Self-Contained File to store the database dump in a single .sql file.
To create a backup for the database, open the tool and connect to the MySQL server. In Database Explorer, right-click the database you want to back up and select Backup and Restore > Backup Database.
If you want to configure error processing behavior and logging options, switch to the Errors handling tab and set up the options. To launch the database backup, click Backup. After the process is complete, you will see the corresponding notification and can close the wizard.
In the article, we have covered the backup types in MySQL, provided examples of how to take a backup of all databases, data or database structure, and tables, as well as how to restore the database using the mysqldump utility. In addition, we examined how to generate a database backup using MySQL Workbench and dbForge Studio for MySQL.
Download a 30-day free trial version to evaluate the features of dbForge Studio for MySQL. After it expires, consider the purchase of the full license of the tool to double your productivity during the MySQL database backup/restore and other database development and management operations. Also, you can watch this video tutorial:
I am moving away from Linode because I don't have the Linux sysadmin skills necessary; before I complete the transition to a more noob-friendly service, I need to export the contents of a MySQL database. Is there a way I can do this from the command line?
mysqlsh is a new universal shell for database administration. Unlike the classic mysql client, which allows you to simply execute SQL queries, mysqlsh can work in SQL \ Python \ JS mode and, most importantly, it has access to specialized database objects: dba, utils, shell. Through these objects you can access specialized administration tools.
For example, using -B(--databases), you can export the schema and data of apple and orange databases to backup.sql with as shown below. *backup.sql is created if it doesn't exist and -B(--databases) can export one or more databases and generates the schema CREATE DATABASE ; and USE ; and my answer explains how to import the schema and data of multiple databases and my answer, my answer and my answer explain how to export the schema and data of all databases, a database and the tables of a database respectively and the doc explains how to export databases and my answer explains how to perfectly export databases:
Or, using -t(--no-create-info) and -c(--complete-insert), you can export only the data of apple and orange databases to backup.sql with INSERT statement which has column names as shown below. *By default, INSERT statement doesn't have column names and my answer explains how to export only data more:
Be careful, using -t(--no-create-info) and -c(--complete-insert), you cannot export only the data of the specific tables person and animal of apple and orange databases to backup.sql with INSERT statement which has column names as shown below:
But, using -t(--no-create-info), -c(--complete-insert) and --ignore-table, you can export only the data of apple and orange databases except apple database's person table and orange database's animal table to backup.sql with INSERT statement which has column names as shown below. *My answer explains how to export databases except some tables more:
For example, using -B(--databases), you can export the schema and data of apple database to backup.sql as shown below. *backup.sql is created if it doesn't exist and -B(--databases) can export one or more databases and generates the schema CREATE DATABASE apple; and USE apple; and my answer explains how to import the schema and data of a database and my answer, my answer and my answer explain how to export the schema and data of multiple databases, all databases and the tables of a database respectively and the doc explains how to export databases and my answer explains how to perfectly export databases:
Or, using -t(--no-create-info) and -c(--complete-insert), you can export only the data of apple database to backup.sql with INSERT statement which has column names as shown below. *By default, INSERT statement doesn't have column names and my answer explains how to export only data more:
Be careful, using -t(--no-create-info) and -c(--complete-insert), you cannot properly export only the data of the specific tables person and animal of apple database to backup.sql with INSERT statement which has column names as shown below:
760c119bf3