Cara menggunakan mysql restore specific table

Playing with data in databases is sometimes tricky but when you get down to it it's just couple of lines on the command line. Sometime ago we switched from Piwik PRO to Matomo and of course we wanted to migrate logs. We couldn't just use the full MySQL / MariaDB database dump and go with it as table names and the schema was different (Piwik PRO 3.1.1. -> Matomo 3.5.1). In short we needed to export couple of tables and rename them to match new instance similarly as discussed in Stack Overflow.

There's a VisitExport plugin for Piwik/Matomo which lets you export and import log tables with PHP and JSON files but it didn't seem usable approach for our use case with tables being 500 MB or so.

The more practical solution was to simply create a dump of the tables we wished to restore separately.

The sugarcrm.sql will contain drop table, create table and insert command for all the tables in the sugarcrm database. Following is a partial output of sugarcrm.sql, showing the dump information of accounts_contacts table:

“Backups are a very critical operation in modern applications. For example, when working as a database administrator, you must ensure the latest backup copies of your databases. This will allows you to perform restorations in case of failure or reverting to a specific timeline in the database changes.

Although backups are essential, they can take up large amounts of space, limiting the frequency of backups. The best way to overcome this is by backing up specific features of the database instead of the complete database.”

That’s what we will look into using this tutorial. You will learn how to back up and restore specific tables from a given database.

Let’s jump in.

How to Backup a Single Table Using MySQLDump

To backup a specific table from a database, we can use the command syntax as shown below:

mysqldump database_name table_name > filename.sql

 
The command above should export the specific table from the target database to the filename.sql file.

The command will default save the file in the current working directory. However, you can specify a path to the file, provided you have to write permissions to the target path.

You can also specify database credentials using the -u and -p parameters for username and password, respectively.

The command syntax is as shown:

mysqldump -u -p database_name table > /path/to/file.sql

Example

The following example illustrates how to back up a specific table from a given database.

NOTE: If you already have an existing database, feel free to skip the database and table creation statements.

create database users;
use users;
drop table if exists employees;
create table employees(
    id int auto_increment primary key,
    name varchar(100),
    salary int,
    active bool
) character set utf8mb4 collate utf8mb4_general_ci;
insert
    into
    employees(name,
    salary,
    active)
values ('Beatriz',
67000,
true),
       ('Ali',
87000,
true),
       ('Gabriel',
76800,
false),
       ('Hanna',
87000,
true),
       ('Eric',
100000,
true);

 
To back up the “employees” table, we can run the command:

mysqldump -u root -p users employees > employees.sql

 
The command above will prompt you for the password of the root user. Once authenticated, the command will export the employees’ table into the employees.sql file under the current working directory.

mysqldump database_name table_name | gzip > filename.sql.gz

How to Restore a Single Table From .SQL File

Once you have backed up your table into a specific file, you can restore it using the mysqldump utility and the less than the operator.

The command syntax is as shown below:

mysqldump database_name < path/to/filename.sql

 
The command will restore the table in the .sql file to the target database specified in the command.

If you need to specify the username and password, run the command:

mysqldump -u username -p < /path/to/filename.sql

 
If you are using a compressed gzip file, you can restore the table with the command:

gunzip < filename.sql.gz | mysql -u username -p database_name

Conclusion

In this post, you learned how to use the MySQLDump utility to back up and restore a specific table from a given database.