Blog

Do you know how to take the MySQL database and table Backup? Today we will learn about this.
Generally, in MySQL, Backup can be taken in three ways:
i) mysqldump Utility
ii)Percona xtrabackup
iii)mysqlexport

Today we will discuss the mysqldump utility. So let’s go through the steps.

1. Single Database Backup :

We can take database backup through the below syntax :

Syntax :
mysqldump -u root -p db_name > db_name.sql

Here to take a backup, we have first logged in and see there are 4 databases. So we want to take the mysql database dump. For that, we have created a directory named mysqlbackup and then took a backup like the above. After that, a SQL file will be generated. If we cat the sql file, we can see all the information that resides in the mysql database.

2. All Databases Backup :

To take all the databases backup, we have to use –all-databases :

Syntax :
mysqldump -u root -p --all-databases > alldb.sql

3. Multiple Databases Backup :

To take multiple databases backup, use -databases database1 database2 as below :

mysqldump -u root -p --databases database1 daabase2 database3 > multipledbs.sql

Mysql Table Backup :

1. Single table Backup :

Suppose we want to take a backup of the accounts table under the performance_schema database. For this, we need to login to the performance_scehma database and then we can see there are 3 rows in the accounts table.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
+——————–+
4 rows in set (0.03 sec)

mysql> use performance_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+——————————————————+
| Tables_in_performance_schema |
+——————————————————+
| accounts |
| binary_log_transaction_compression_stats |
| cond_instances |
| data_lock_waits |
| data_locks |
| error_log |
| events_errors_summary_by_account_by_error |

mysql> select * from accounts;
+—————–+———–+———————+——————-+——————————-+————————–+
| USER | HOST | CURRENT_CONNECTIONS | TOTAL_CONNECTIONS | MAX_SESSION_CONTROLLED_MEMORY | MAX_SESSION_TOTAL_MEMORY |
+—————–+———–+———————+——————-+——————————-+————————–+
| NULL | NULL | 36 | 42 | 272128 | 66832624 |
| event_scheduler | localhost | 1 | 1 | 0 | 16665 |
| root | localhost | 1 | 1 | 647280 | 2702841 |
+—————–+———–+———————+——————-+——————————-+————————–+
3 rows in set (0.02 sec)

Now use the below syntax to take the backup of a single table :

Syntax :
mysqldump -u root -p database_name table_name > table_name.sql

We can cat the accounts.sql file and see the DDL and other details of the accounts table.

2. Multiple Tables Backup :

For multiple tables, use below :

mysqldump -u root -p database_name table_name1 table_name2 table_name3> table_names.sql

Here we are taking the backup of accounts and error_log table and storing the backup in the table2.sql file. So here are the details of the mysqldump utility and how we can take the database and table backup. Hope this helps !! In Next Article, we will see how to retore database and tables in MySQL. Also you can follow Percona documentation for extended mysqldump.