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
Last modified: 27/11/2019