Christopher Juckins

SysAdmin Tips, Tricks and other Software Tools

User Tools

Site Tools


Use phpMyAdmin to export databases.

On new machine, make sure database does not exist.

On original machine, select database which you wish to export.

1. First export just the “Structure” of the database. Choose all options except “Add DROP TABLE”. Save as export file.
2. Then export just the “Data” as an SQL statement. Make sure all tables are selected. Don't choose complete inserts or extended inserts. Save as export file.
3. On target machine, import Structure file first. Then go to database, and import Data file.
4. You might need to restart the mysql database server with this command (note max_allowed_packet setting):

su - mysql
/usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/ --skip-external-locking --max_allowed_packet=32M --socket=/var/lib/mysql/mysql.sock

SQL statement to show all users on the system:

mysql> SELECT * from mysql.user;
exporting_importing_database_tips.txt · Last modified: 2008/08/09 18:10 by juckins