MYSQL DB dump from Web Hosting Sites
My_SQL Hosting
Most of the web sites are being hosted prefer MYSQL when compared to other DB's due to its ease of operation and robust infrastructure. Many standard and third party tools are available for connecting to MYSQL db. Having said that , one of the main safety requirement for database is the Backup. MYSQL provides a simple SQL command for backing up and restoring the database. The ease of usage depends on how your DB is hosted. For most of my developments i use Django with MYSQL which has proved to be user friendly. I have hosted my applications on GoDaddy , Pythonanywhere and AWS.
MYSQL Standard Backup Commands
Refer to this link for step by step procedure : https://home.erprealm.com/doc/My_SQL/Messages/backup-and-restore-mysql-db/
Backup data from Pythonanywhere hosting site
If your MYSQL is hosted in python anywhere then the above mentioned commands will not work because of the firewall setup.
https://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/
You can easily generate a dump using the following command , but the dump file will be downloaded on to the hosting site , which would occupy a lot of space and you may have to buy additional space.
On the other hand if your requirement is such that you need to generate a mysql dump file on your local system then you have to follow the following steps.
1. Download Putty
2. Configure Python Anywhere setup
3. Create new Tunnel Setup
Source Port : 3306 ( If local SQL is being used then use a difffernt port as per availability)
Destination : <username>.mysql.pythonanywhere-services.com
and click on Add.
This will create a tunnel setup for your PA database account.
How to connect to tunnel:
1. Click on Putty and select the destination and click Load. This will load the session for connection
2. Then click on Tunnel option on the Menu tree. This will display the tunnel if already added and saved. Click on open to open the tunnel.
Enter the PA site user name and password to login.
Generate MYSQL Dump (This will generate the dump file to local folder)
1. Open PA tunnel
2. Open command prompt and change directory to : C:\Program Files\MySQL\MySQL Server 8.0\bin (assuming default MYSQL installation)
3. Enter the following command
mysqldump.exe –e --column-statistics=0 –u<<dbusername>> -p -P 3307 -h 127.0.0.1 <<PAusername$PAdbname>> > C:\Users\XXXX\Desktop\PA_WeeklyBackup.sql
Note :
<< >> : Replace with actual dataEnter port number after -P if the port is different in the tunnel. If 3306 is used -P 3307 can be removed and default port 3306 will be used.
PAusername$PAdbname : Refer to Python Anywhere Database web page for details.
dbusername = Database username for PA
4. When the command is executed , DB username and Password to be entered.
5. Once connection is established successfully backupfile will be downloaded to desktop.
MYSQL Restore
1. Open PA tunnel
2. Open command prompt and change directory to : C:\Program Files\MySQL\MySQL Server 8.0\bin (assuming default MYSQL installation)
3. Enter the following command
mysql -u<<PAUserName>> -p -P 3307 -h 127.0.0.1 <<PAusername$Database>> < C:\XXX\sqldump.sql
Note :
<< >> : Replace with actual dataEnter port number after -P if the port is different in the tunnel. If 3306 is used -P 3307 can be removed and default port 3306 will be used.
PAusername$PAdbname : Refer to Python Anywhere Database web page for details.
dbusername = Database username for PA
4. When the command is executed , Password to be entered.
5. Once connection is established successfully backupfile will be restored to database from desktop.