26.2.1. How to restore a single table

Suppose we only need to restore the nuke_users table, leaving all other tables untouched. For this we use phpMyAdmin (Section 3.3) to selectively delete the nuke_users table. We then open our backup file with a decent text editor (see Chapter 11) and copy from it only those parts that are relevant to the

• structure and

• data

of the nuke_users table. The structure part is normally preceded by a three-line comment like:

#
# Table structure for table `nuke_users`
#

The data part, on the other hand, is preceded by a three-line comment like:

#
# Dumping data for table `nuke_users`
#

All in all, our structure and data parts for the nuke_users table will look like the following:

#
# Table structure for table `nuke_users`
#
CREATE TABLE nuke_users (
user_id int(11) NOT NULL auto_increment,
name varchar(60) NOT NULL default ",
username varchar(25) NOT NULL default ",
user_email varchar(255) NOT NULL default ",
femail varchar(255) NOT NULL default ",
 
...many other fields of nuke_users follow here 
 
#
# Dumping data for table `nuke_users`
#
INSERT INTO nuke_users VALUES (1, ", 'Anonymous', ", ", ", 
'blank.gif', 'Nov 10, 2000', ",", ", ", ", 0, 0, ", ", ", 
", 10, ", 0, 0, 0, ", 0, ", ", 4096, 0, 0, 0, 0, 0, 1, 0, 
0, 1, 0, 0, 0, 10,NULL, 'english', 'D M d, Y g:i a', 0, 0, 0, NULL, 
1, 1, 1, 1, 1, 1, 1, 1, 0, 3, NULL, NULL, NULL);
 
...many other user data follow here 
 

In the above example, only a small part of the structure of the nuke_users table is shown for brevity. You will also most probably have more than one user, so that the data part will also be much bigger (we only show the user data for user "Anonymous").

We can get a partial restore of the single nuke_users table in three ways:

1. We copy and paste the relevant structure and data parts from our backup file into a separate file that we will call partial_backup.sql. Then we use phpMyAdmin's "SQL query" function (see Section 3.4.2) to import the partial_backup.sql file, just as we do with any other file containing SQL queries. We enter the full path to partial_backup.sql in the "textfile" field of Figure 26-3 and click on "Go".

2. We copy and paste the the relevant structure and data parts from our backup file into the text area field above the "Browse" button, then click on "Go".

3. We don't use phpMyAdmin but use the shell command line instead. From the shell promt, we type:

   mysql -h dbhost -u dbuname -p dbname < partial_backup.sql

   where dbhost, dbuname and dbname are exactly the same as in your config.php file(Section 3.7).