Contents 1 Database Importing 1.1 Synopsis 1.2 MySQL 4.X to 5.X 1.2.1 Notes 1.3 PostgreSQL 7.x to 8.x

Database Importing

Synopsis

There are times when a database is developed for one version of a particular database software and version, and then needs to be migrated to another version or server type even, below there are some pointers to make this easier. This article assumes SSH Access is available on both servers.

MySQL 4.X to 5.X

So the first thing is we're going to need to get that export of the database in question, so on the MySQL 4.X Server, well open up a command prompt and enter:

  mysqldump -udbuser -p databasename > databasename-`date +%Y-%m-%d-%H.%M.%S`.sql

Be sure to enter in the password when prompted and remember, it will not echo any characters so watch the fat fingers, then we'll go ahead and let it do it's thing and once it completes, we're going to use Secure Copy, scp to copy it up to the destination server:

  scp databasename-2008-07-19-02.18.21.sql username@maindomain.com:/home/username/      # The database filename is an example, use the actual filename generated when dumped.

You'll likely be presented with a message like the following:

  The authenticity of host 'maindomain.com (12.34.56.78)' can't be established.
  DSA key fingerprint is 1z:2x:3c:4f:5b:6n:7m:8k:9l
  Are you sure you want to continue connecting (yes/no)? 

Be sure to type the entire word 'yes' then once again, enter the password for the remote machine when prompted. So now the database file is on the remote machine so SSH on in to the destination server and once logged in you'll be in your home directory, where we copied the .sql file, but before we can just go running the mysql command, we have to prepare the file for import on MySQL 5.X so open the file using your favorite text editor:

  vim ~/databasename-2008-07-19-02.18.21.sql                                            # The database filename is an example, use the actual filename generated when dumped.

Once open at the very top of the file put verbatim:

  SET SQL_MODE='MYSQL40'; 

Then save and close the file, now we can proceed to import the file by using the following command on the remote server:

  mysql -uusername_dbuser -p username_dbname <  databasename-2008-07-19-02.18.21.sql    # The database filename is an example, use the actual filename generated when dumped.

Let it do it's thing and once you get back to the command prompt, barring any errors, it's imported and ready for use, you can naviagate to PhpMyAdmin in cPanel to verify the data, if you so choose.

Notes

This is actually the preferred method for databases over a MB or 2 in size and PhpMyAdmin tends to choke on export files much larger, and it's alot slower, plus the command line won't fail if the file is huge and triggers a CPU Quota Exceeded Error.

PostgreSQL 7.x to 8.x

The Following may not work without SuperUser privileges.
The following procedure will export data from Postgres 7 to 8.

1. Use pg_dump to export the database:

     pg_dump database > database.sql

2. Fix the invalid UTF-8 sequences with iconv:

     iconv -c -f UTF-8 -t UTF-8 -o cleanfile.sql database.sql

where cleanfile.sql is the fixed database, database.sql is the database with the invalid UTF-8 sequences 3. Import the data into Postgres 8:

     psql -d database -f cleanfile.sql

where database is the name of the database where the data is being imported to, cleanfile.sql is the data with the correct UTF-8 sequencing

This procedure will import data from a Postgres 7 database to a Postgres 8 database.