Regularly backing up your databases is essential to maintaining up to date data, recover from a data loss event, or recover specific data from an earlier time.
Importing and exporting using SSH (Secure Shell) provides the most stable and secure way for backing up large databases over 50mb.
Prerequisites
- SSH access to server
Exporting a mySQL database
- Access the server via SSH e.g. ssh 111.111.111.111 (enter the server's IP address)
Execute the following command:
mysqldump -p -u username database_name > DBbackup.sql
Required details as per above:
username = mySQL username
database_name = mySQL database name
DBbackup.sql = output file - Enter password when prompted
3. The database is now backed up to your current directory and is namedDBbackup.sql
Importing a mySQL database
- Upload the .sql backup file to yourserver and navigate to this folder using SSH. The file must be in this format, please unzip if packed up in a .zip or .tar.gz file
- Create a new mySQL database in cPanel
- Execute the following command:
mysqldump -u username -p -D database_name > DBbackup.sql
Required details as per above:
username = mySQL username
database_name = newly created database on server
DBbackup.sql = backup file