what happens with the auto increment primary field when data is restored from a backup

Question

My MySQL table is something like:

userid(AI,PRIMARY)    name    password
1                     aa       dfsdfsdfds
2                     ioi      kjkjkjkjk
(3) user deleted his account
4                     ghghj    jhjhkhj

and there is another table with there info

userid( will be userid from above table)    address    phone
1                                            sfdsfds   9999999
2                                            dfsdfdsf   333333
3 (deleted)
4                                            sdfdsf     999999

When I will backup and restore the data, what will happen what will be my table data of table 1?

Answer 1

When you export your data (i.e. using mysqldump), all your keys (and their value) are exported as they are. The backup basically looks like this:

CREATE TABLE yourtable (
    userid INT(10) unsigned NOT NULL auto_increment,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    PRIMARY KEY (userid)
) AUTO_INCREMENT=5;
INSERT INTO TABLE yourtable ( userid, name, password )
VALUES 
(1, 'aa', 'dfsdfsdfds'),
(2, 'ioi', 'kjkjkjkjk'),
(4, 'ghghj', 'jhjhkhj')

As you see in the example:

• your keys are being fully backuped and restored
• MySQL remembers the last auto-increment value and returns to that value on the next INSERT. (the next user will get id 5)