Cara menggunakan does mysqldump create database?

Data is valuable to people and organizations. Imagine losing a company database with customer or payroll information. Backing up databases is vital as a safety net from possible data loss—and that’s not up for debate.

Table of Contents

• Prerequisites
• Creating the Test Database
• Backing Up Entire Databases using the mysqldump Command
• Specific Database Backup
• Specific Database Backup
• All Database Backup
• Inspecting the Database Backup Files
• Backing Up Tables using the mysqldump Command
• Restoring from Backup
• Restoring Entire Database
• Restoring Database Table
• How can I get Mysqldump from all databases?
• How use Mysqldump command
• How do I view all MySQL databases?
• Does Mysqldump create database?

An excellent way to backup MySQL databases and tables is the mysqldump command. Don’t let the dump in the name fool you; mysqldump is a versatile and powerful tool that comes with your MySQL installation.

Ready? Read on and become a MySQL database backup pro!

• Prerequisites
• Creating the Test Database
• Backing Up Entire Databases using the mysqldump Command
  • Specific Database Backup
  • Specific Database Backup
  • All Database Backup
  • Inspecting the Database Backup Files
• Backing Up Tables using the mysqldump Command
• Restoring from Backup
  • Restoring Entire Database
  • Restoring Database Table
• Conclusion

Prerequisites

This tutorial will be a hands-on demonstration. If you’d like to follow along, you must have a Linux computer with MySQL already installed. This tutorial uses an Ubuntu 20.04 computer with MySQL 8.0.29. Follow the How to Install MySQL on Ubuntu tutorial first if you haven’t installed MySQL yet.

This tutorial also assumes that you have already opened a terminal session to the Linux computer.

Creating the Test Database

Before you can back up a database, you need to have at least one! In this section, you’ll create two sample databases with tables and data for testing.

This tutorial does not cover in detail the creation of MySQL databases, tables, and inserting data. The instructions or commands in this section are for demonstration purposes only.

1. First, connect to the mysql command shell as root. Type the password when prompted, and press Enter.

2. Next, copy the below code, paste them to the mysql prompt, and press Enter. Refer to the inline comments to understand what each statement does.

This code will create two databases named menagerie1 and menagerie2, create a pets table inside each database, and insert a sample record inside each table.

/* Create two databases called menagerie1 and menagerie2 */
CREATE DATABASE menagerie1;
CREATE DATABASE menagerie2;

/* Create a new table called pets on the menagerie1 database */
CREATE TABLE menagerie1.pets (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

/* Create a new table called pets on the menagerie2 database */
CREATE TABLE menagerie2.pets (
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

/* Insert a new sample record into the pets table on menagerie1*/
INSERT INTO
    menagerie1.pets
VALUES
    (
        'Puffball',
        'Diane',
        'hamster',
        'f',
        '1999-03-30',
        Null
    );

/* Insert a new sample record into the pets table on menagerie2*/
INSERT INTO
    menagerie2.pets
VALUES
    (
        'Good Boy',
        'Karen',
        'dog',
        'm',
        '2000-02-18',
        NULL
    );

3. Now, confirm that the sample data exists inside the tables in your databases by running the below statements.

SELECT * FROM menagerie1.pets;
SELECT * FROM menagerie2.pets;

You should see a result similar to the screenshot below. As you can see, the table in both databases has one record each.

4. Lastly, exit the mysql shell by typing quit or \q and press Enter.

Backing Up Entire Databases using the mysqldump Command

You now have MySQL databases, and it’s time to see the mysqldump command in action!

mysqldump is a versatile command-line tool because it lets you back up one or more databases or specific tables only. Also, this tool can generate SQL files that you can use to recreate databases or tables.

There are two ways to back up entire MySQL databases; specific databases and all databases.

IMPORTANT! Each backup method you’ll learn in the succeeding sub-sections overwrites existing backup files with the same filename. Be cautious not to overwrite existing backups in production.

Specific Database Backup

This method creates a backup of one database at a time, which is useful when you want to dump each database into separate backup files. The basic syntax for this method is shown below.

mysqldump [OPTIONS] database_name > backup_file.sql

For example, run the below commands to back up the menagerie1 and menagerie2 databases individually.

# OPTIONS
## * -u root = connect to the MySQL server as the root account.
## * -p = prompt for the account password.
mysqldump -u root -p menagerie1 > menagerie1_db_backup.sql
mysqldump -u root -p menagerie2 > menagerie2_db_backup.sql

Specific Database Backup

The mysqldump command lets you specify one or more databases to back up. As you can see from the syntax below, you must specify the --databases or -B argument followed by the list of databases in the same line.

Note: This method backs up every specified database into one backup file.

mysqldump [OPTIONS] --databases database1 [database2] > backup_file.sql
# OR
mysqldump [OPTIONS] -B database1 [database2] > backup_file.sql

For example, run the below command to create a single backup for the menagerie1 and menagerie2 databases.

mysqldump -u root -p -B menagerie1 menagerie2 > menagerie1_menagerie2_db_backup.sql

All Database Backup

When you need to backup all databases at once without listing each database name, you must specify the --all-databases or -A argument, as shown below.

mysqldump [OPTIONS] --all-databases > backup_file.sql
# OR
mysqldump [OPTIONS] -A > backup_file.sql

To back up all databases into a single backup file, run the below command in your terminal.

mysqldump -u root -p --all-databases > all_db_backup.sql

Note: This method will backup ALL existing MySQL databases, including the default ones, such as sys, mysql, information_schema, and performance_schema. Be mindful when using this backup method.

Inspecting the Database Backup Files

If you followed all instructions, you now have two backup files with the *.sql extension in your current directory. To verify, run the below command in the terminal.

As a result, you should see the four backup files on the list.

Now, open one of the backup files in your preferred text editor, such as nano or Visual Studio Code. Choose the file with the smallest size, which, in this case, is the menagerie1_menagerie2_db_backup.sql.

Can you recognize the formatting in the below screenshot? Yes, those are SQL statements. The mysqldump command generates an SQL script that creates the databases, tables, and records.

Backing Up Tables using the mysqldump Command

Apart from taking backups of entire databases, the mysqldump command allows you to backup specific tables. Taking specific table backups can be more manageable and uses less storage space.

Other benefits of table backups are portability and granularity of data restoration. A smaller backup file size is more portable, and you can restore table backups into a new or existing database.

The syntax for backing up tables is as follows. After specifying the database name, you must append the list of tables to back up. If you do not provide the table names, the mysqldump command will dump all tables in the database.

mysqldump [OPTIONS] database [table1 table2...] > table_backup.sql

Now that you know this syntax follow these steps to back up a database table.

1. Run the below command to back up only the pets table in the menagerie1 database. This command creates a file named menagerie1_pets_tb_backup.sql.

mysqldump -u root -p menagerie1 pets > menagerie1_pets_tb_backup.sql

2. Verify that the backup file creation is successful by listing the *.sql files.

3. Optionally, open the backup file in a text editor or display the contents on the screen.

# Open in the text editor
nano menagerie1_pets_tb_backup.sql
# OR display on the screen
cat nano menagerie1_pets_tb_backup.sql

Restoring from Backup

Having backups of your database or tables gives you peace of mind. Knowing that if your database becomes corrupted or someone accidentally deleted it, you have a point in time of the data you can restore.

At this point, the mysqldump command has already done its job to back up the database and tables. Restoring those backups will now be through the mysql command shell.

Restoring Entire Database

Earlier in this tutorial, you created a backup of the menagerie1 and menagerie2 databases to a file named menagerie1_menagerie2_db_backup.sql. In this section, you’ll simulate multiple database losses and restore them from your backup file.

1. First, connect to the MySQL shell.

2. Next, drop the menagerie1 and menagerie2 databases to simulate losing multiple databases.

# Delete the databases
DROP DATABASE menagerie1;
DROP DATABASE menagerie2;

# List remaining databases
SHOW DATABASES;

As you can see below, the database deletion was successful. Both databases no longer exist on the MySQL server.

3. Now, restore the databases from the menagerie1_menagerie2_db_backup.sql file. To do so, execute the source or \. command followed by the backup filename (full path or relative path), and watch the magic happen!

source menagerie1_menagerie2_db_backup.sql

You’ll see a similar output on the screen to the screenshot below.

4. Finally, confirm that the databases and their data were successfully restored.

SELECT * FROM menagerie1.pets;
SELECT * FROM menagerie2.pets;

You’ve now successfully restored your databases from backup.

Restoring Database Table

In this example, you’ll be restoring the pets table to the menagerie1 database. You previously created a backup of this table to a file named menagerie1_pets_tb_backup.sql.

1. First, to simulate the loss of the table, run the below commands to delete the pets table from menagerie1.

# Switch to the menagerie1 database
USE menagerie1;
# Delete the pets table from menagerie1
DROP TABLE pets;
# List the tables (if any) from menagerie1
SHOW TABLES;

The output below shows that no tables exist in the menagerie1 database after dropping the pets table.

2. Now, execute the source or \. command followed by the table backup file. In this example, the backup file is menagerie1_pets_tb_backup.sql.

\. menagerie1_pets_tb_backup.sql

3. Finally, check that the pet table restoration was successful by running the below commands in the mysql shell.

SHOW TABLES;
SELECT * FROM pets;

As you can see below, the restoration recreated the pets table and data.

Conclusion

Backing up your MySQL databases is an essential task that an administrator should ensure to happen regularly. The mysqldump command makes creating backups of entire databases or individual tables effortless and uncomplicated.

This guide has only covered the basic options of using the mysqldump commands. You can further customize them to suit your needs. Perhaps you should set up a cron job to run the backups on schedule.

In real-life usage, your database is hundred if not thousands of times bigger, and you don’t want your disk filled with SQL files. In that case, you would want to compress your dump file and save it on a network drive or, even better, to cloud storage like the Amazon S3.

How can I get Mysqldump from all databases?

To create a backup of all MySQL server databases, run the following command:.

mysqldump --user root --password --all-databases > all-databases.sql. ... .

mysql --user root --password mysql < all-databases.sql. ... .

mysql --user root --password [db_name] < [db_name].sql. ... .

select @@datadir;.

How use Mysqldump command

To dump/export a MySQL database, execute the following command in the Windows command prompt: mysqldump -u username -p dbname > filename. sql . After entering that command you will be prompted for your password.

How do I view all MySQL databases?

SHOW DATABASES lists the databases on the MySQL server host. SHOW SCHEMAS is a synonym for SHOW DATABASES . The LIKE clause, if present, indicates which database names to match. The WHERE clause can be given to select rows using more general conditions, as discussed in Section 26.8, “Extensions to SHOW Statements”.

Does Mysqldump create database?

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.