Halo, saya novri. Pada blog ini saya akan membahas seputar panduan dan tips hosting, email, domain, vps, server, website, linux dan cms. Jangan lupa untuk shared dan bookmark novri.web.id agar lebih mudah mendapatkan informasi tutorial maupun tips dari kami. 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. Show
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. ContentsTypes of Backup in MySQLIn 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. MySQL supports the following backup types:
If there may happen some interruptions during a copying operation, backups can be divided into the following types:
Whether data should be fully or partially copied, backups can be classified into:
Back up a MySQL database using the mysqldump commandTo back up a MySQL database, you can use either third-party tools or execute the mysqldump command from the command line. 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. In addition, the mysqldump utility can output the dump to the CSV or XML file formats. It should be noted that mysqldump cannot dump a database or data to separate .sql files. To restore the database, the utility executes all SQL statements to recreate the tables and populate them with data that, in turn, requires a lot of time. The basic syntax of the mysqldump command includes the following parameters:
Note: As a rule, 9 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 9 command.Next, let’s take a closer look at the examples describing how to take a backup of the MySQL database using the mysqldump command-line utility. For demo purposes, we are going to connect to a remote server dbfmylast. That’s why we will add some additional information to the 1command, such as 2 and 3 parameters.mysqldump to back up all databasesThe example shows how to use 1 to back up all MySQL databases on the server. The scripts of databases are exported to the .sql file, which you can later use, for example, to migrate databases to a new server. For more information, see Backing up all MySQL server databases or only the required ones.To create a backup of all databases, execute the following command by adding the 5 parameter:
This will generate a backup of all databases with their structure and data to a backup_all_databases.sql file. mysqldump to back up only dataIf you want to take a backup of data without the database structure, execute the command with the 6 parameter. For example, dump data for the sakila database.
The file will output the script containing only dumping table data: mysqldump to back up only structureThere may be cases when you need to export the database or table structure without data. To get that done, run the command with the 7 parameter.
The 1 command outputs only table structure to the file.mysqldump to back up all tables in the databaseThe 1command 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:
The output is as follows: mysqldump to back up a single tableTo take a backup of a single table in the database, indicate the name of the specific table in the 1command. For example, dump the inventory table from the sakila database.
The backup script outputs structure and data only for the inventory table: MySQL Workbench to back up a databaseIn this part, we are going to examine how to make a database backup using the MySQL Workbench tool. To create a database backup, do the following: 1. On the Administration panel, click Data Export. The Administration – Data Export document opens. 2. On the Object Select > Tables to Export tab, select the sakila schema. 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. 4. To create a backup file, click Start Export. On the progress bar, you can view that the data was exported to the specified file. Note: With MySQL Workbench, it is not possible to automate or schedule a database backup. How to back up a database using dbForge Studio for MySQLNow, let’s see how easy and fast it is to create a database backup using dbForge Studio for MySQL. It is a versatile MySQL GUI tool for developing, managing, and administering MariaDB and MySQL databases. This IDE can increase productivity, optimize query performance, efficiently compare and synchronize databases, automate and schedule database backups. With the MySQL backup tool, built into dbForge Studio for MySQL, you can back up and restore MySQL databases in the most efficient and quickest way. 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. In the Database Backup Wizard that opens, specify the connection, database, location to store the output file, and name for this file. On the Backup content tab, select whether you want to back up a database structure or data, or both, and select database objects. On the Options tab, set additional options that will handle the backup process. 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. Restore the database in MySQL from the fileNow, let’s restore the sakila database from the .sql output file using the 1utility.First, you need to create an empty database on the target server and restore the database using the 2command that will generate a schema with data.
where 3 is an empty database that will contain a database structure with data after the import from the 4 backup file.Note: If there is a database with the same name on the target server, then first, you need to drop it and then create an empty database. To view the list of tables located in the sakila database, execute the following 2 command:
ConclusionIn 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: |