Reset the auto increment value for a MySQL table

To reset the auto increment value of a MySQL auto incremental primary key to a new value, either higher or lower than what it would otherwise next be, use the following command :

ALTER TABLE mytable AUTO_INCREMENT = xx

Its even more easy to change it in phpMyAdmin. Select mytable, click the “Operations” tab, change the displayed current next auto increment value and then click the “Execute” button.

Backing Up a MySQL database

It is strongly recommended to backup databases at regular intervals and always before an upgrade. Restoring the database from backup is then possible if anything goes wrong.

phpMyAdmin is the usual program to manipulate a MySQL database.

To export tables from a database, login to phpMyAdmin, select the name of the database, choose those tables that corresponds to the related application and check the following options :

  • SQL
  • Structure
  • Add drop table
  • Add auto-increment
  • Enclose table and field names with backquotes
  • Data heading (without ticking the inside boxes)
  • Same as file
  • Zipped compression
  • Export type “insert”

Click GO and save the file to the computer. If you have separate files and folders for images, downloads, media or other attachments, save them with FTP on the computer.

A general rule of thumb is to keep at least three backups of databases and data files and to keep them in different places.

To restore the database, login to phpMyAdmin, select the database to restore, click the import tab, locate the .sql file on the computer, check the SQL radio button and click GO. The import may take a while, at the end a success screen is displayed. In case of separate image and media files, restore them with FTP.

WordPress has an integrated export/import function which is very userfriendly and efficient. It’s possible to change the author of the posts before saving and to import also automatically the attachments.

Today I tried the import and export of blogs with phpMyAdmin and with WordPress with success.