MySQL DBA Tutorial - How to restore single table from full database backup in MySQL Server

Scenario:

You have used mysqldump to take the full backup of a database. The database has multiple tables. Now you need to restore only single table from full backup in MySQL Server, how would you restore single table in MySQL Server?

Solution:

First of all you will be separating the data for deleted table from full backup. You can set program to extract the data for the table you want to restore. The below command will extract the data for table and create new file. Once we have the data for single table, then we will restore.

shell > sed -n -e '/DROP TABLE.*`YourTableName`/,/UNLOCK TABLES/p' /SourceDirection/FullbackupName.sql > Extracted_YourTableData.sql

Let's say our table name is employee, then we will modify above as shown below.

shell > sed -n -e '/DROP TABLE.*`employee`/,/UNLOCK TABLES/p' /mysqlbackup/testdb.sql > Employee.sql 

Now you are ready to restore employee table to testdb.
shell > sudo mysql --user=root --password=DBA123 testdb< /mysqlbackup/Employee.sql
MysQL DBA Tutorial - Restore single table from full database backup in MySQL

4 comments:

  1. Different sports betting systems can be used to predict the future results of a match. Many people will use a system that combines past performance with the current performance of each team สูตรบาคาร่าใช้ได้จริง. This makes it possible to figure out a trend or a pattern of past performances that can be used to predict the future performances of the team. There are many sports betting systems out there that have been proven successful for this purpose, but many people will choose to go this route if they feel that there are factors that are too complex to be explained by a simple average.

    ReplyDelete
  2. Perfecting a best burger in portland is nowhere near as easy as it seems. Any teenage employee or backyard barbecuer can serve up a patty on a bun. To fine-tune each element to perfection, to create a chorus of flavors, to find and release our inner burger endorphins, is no easy task. But when done right, few things in life are more satisfying. So we had to know. Where are they? Which ones would Josh eat?

    ReplyDelete
  3. Generally, the tire pressure on a dirt bike should be between 6 and 18 psi with a traditional tire. The most common tire pressure that avid dirt bike riders recommend is 12psi. If you’re an average rider on an average bike riding on average terrain, set your tire pressure to 12psi and forget about the rest of this article. However, if you want to customize your tire pressure perfectly to suit you and your riding style, then read on the for the full story visit.

    ReplyDelete