When transferring MySQL databases between hosts or servers there are various methods available from the web based phpMyAdmin to remote GUI clients such as Toad.
One of our favourites is the classic mysqldump and mysql-client due to their simplicity, portability and originality and we’ll need this for the purposes of this tutorial.
Download and install mysql/mysqldump to get started:
Windows, install MySQL server to your local computer which also provides mysql.exe, mysqldump.exe and related.
Macintosh, download and install the relevant MySQL server .dmg.
Ubuntu, run ‘sudo apt-get install mysql-client’ which will provide mysql, mysqldump, mysqladmin, mysqlcheck and other tools.
Backup an existing database using the mysqldump executable.
mysqldump -h hostname -u username -ppassword dbname > localfile.sql
Replace the following text where applicable:
- hostname is the name of the server you wish to backup, for transferring databases within Pipe Ten this would be either mysql.pipeten.co.uk (Sheffield) or dmysql.pipeten.co.uk (Derby).
- username is the login username for the database and can be found in your hosting control panel.
- password is for the username/login entered above.
- dbname is the name of the database you wish to create a dump/backup of.
- localfile.sql is the local filename into which you want the MySQL structure and data to be placed.
Restore a database using the ‘mysql’-client tool:
mysql -h hostname -u username -ppassword dbname < localfile.sql
Backup specific tables with mysqldump:
mysqldump -h hostname -u username -ppassword dbname –tables tableone tabletwo > localfile.sql
Ignore specific tables with mysqldump:
mysqldump -h hostname -u username -ppassword dbname –ignore-table tableone > localfile.sql
MySQL direct server to server backup and restore (not recommended):
mysqldump –compact -h hostname -u username -ppassword dbname | mysql -h hostname -u username -ppassword dbname
Classification: Public
Last saved: 2019/11/27 at 16:03 by Jamie