Migrate a TXP database using the Terminal and FTP

Jonathan’s tip on how to Export and import a TXP database is indeed useful but has its limitations regarding speed as well as the size of databases which can be imported as phpMyAdmin has a 8,192KB maximum size limitation on shared hosts.

A more efficient way is to do it via SSH on the Terminal or any other tool which provides a command line interface and SSH access.1

Prepare

Preparation is easy with an FTP client. All you need is to create a folder called backups somewhere before2 the root of your old site and a folder called backup somewhere before the root of your new site.

Backing up in your current host

step1. Connect via ftp and create a directory below the root of your site and call it backups
step2. In the command line (if you use a mac launch the Terminal app) type: ssh accountname@servername.host.tld after replacing the information on that line with that of your account at your host.
step3. you will be asked for the password which you will need to type up.
step4. when the server loads type: mysqldump -u accountname -pmypassword db_name > /path/to/the/backups/dbb.sql after correcting the path to the backups folder and replacing accountname with the login name of your database, mypassword with your database’s password and db_name with your database name.
step5. Compress your database as it will speed up the download. In the command line type: gzip -c /path/to/the/backups/dbb.sql > /path/to/the/backups/dbb.sql.gz after correcting the path to the backups folder and replacing mypassword with your password and db_name with your database name.

Note: Steps 4 and 5 could be combined by typing: mysqldump -u accountname -pmypassword db_name | gzip -c > /path/to/the/backups/dbb.sql.gz. I included them separately above to cover a remote chance of a server not supporting gzip compression.

step6. Backup and compress your site’s directories and files: tar -czf /path/to/the/backups/site_backup.tar.gz /path/to/the/root/of/your/site. This may result in a large file which will nevertheless be simpler to download with your FTP client.
step7. Go back to your ftp and download the compressed files from the backups folder

Migrating to your new host

step1. Connect via ftp and create a folder called backup below the root of your site
step2. Upload your databases in .gz compression in the backup folder
step3. Connect to your host by typing ssh newaccountname@servername.newhost.tld after replacing the information with that of your account at your host and type in your password when prompted.
step4. Un Zip your database online by typing: gunzip -c /path/to/the/new/backup/dbb.sql.gz > /path/to/the/new/backup/dbb.sql
step5. Type: mysql -u username -pmypassword db_name < /path/to/the/new/backup/dbb.sql after correcting the path to the backup folder and replacing username with your database’s username, mypassword with your database’s password and db_name with your database name.

If all went well you should have all your database records in place. All that is left to do now is edit your config.php file so as to reflect the new path of your site.

For questions or a more targeted method of how one could migrate to Kaizen Garden please visit the Textpattern Forum.

Footnotes

1 SSH is not offered freely by all hosts whereas some of them only offer it on request.

2 Remember that databases contain some sensitive information such as emails, usernames and passwords and their backups should – whenever possible – not be accessible from the public side of your site.

comments powered by Disqus