Posted by "Albert" on 01/12/06 14:40

David Grant wrote:
> Ross wrote:
> > Can you just export the mysql? Will the pdfs be exported as binary data?

> > Will I have to re-upload them? (there are hundereds).
>
> Try using "mysqldump" to pull the data into a flat file full of SQL
> statements. You can then use the "mysql" command to reinsert the data.

An alternate method, which I found to be more reliable, would be to put the
database files in a tarball, transfer it to the new server and then extract
the tarball into the database directory.

I am running MySQL on a Linux server. The databases are in /var/lib/mysql.

If your database is called db then there will be a db directory in
/var/lib/mysql.

Before making the backup you either need to stop MySQL (preferred) or flush
all data to tables and make sure nothing is writing/reading to/from the
database.

To put it in a tarball:
origin# cd /var/lib/mysql
origin# tar cvf db_backup_20060112.tar db

You then copy that db_backup_20060112.tar file to the MySQL data directory
of your destination server and once it's there extract it again:
origin# scp db_backup_20060112.tar user@your.destination.host
destination# cd /var/lib/mysql
destination# tar xvf db_backup_20060112.tar

You then might need to set permissions if the UID of your origin server
MySQL user and destination server MySQL user is not the same.

After starting MySQL server on your destination server you should have the
database exactly like it was on the original.

BTW you can transfer MySQL data files from a Windows machine to a Linux
machine and vice versa without any issues. There is a note in the MySQL
documentation of the formatting your Windows server should use (to do with
case sensitivity)

HTH

Albert

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.17/227 - Release Date: 2006/01/11

[Back to original message]