Forums

You guys have done great jobs, I am gald to run my code on your site. Thanks very much.

I find a way to dumpdata, the command is ' python manager.py dumpdata '

Sorry for disturb.

Thanks for posting! You're right, that should work for all Django apps.

Recently, I find the django dumped data cannot import by django, it report duplicated key.

I try many options and database initial status to get over it, and fails at last.

Could you backup my database for me? You can run the cmd follow, and

mysqldump --databases "liquninfo$default" > /home/liquninfo/dump.liquninfo.sql

If you want some information, please let me know, thanks.

Is there a reason you can't run mysqldump yourself?

I have got it. thx.

I try the command but I have no privilege to run, I remember, when I post the first post.

I donot try again after that.

I think you're missing some parameters from mysqldump -- try:

mysqldump -uliquninfo -p --databases 'liquninfo$default' > /home/liquninfo/dump.liquninfo.sql

It's important to note that the quotes around the database name MUST be single quotes, otherwise bash mangles the database name and it doesn't work.

hi, i tried command: mysqldump -u xDrgh 'xDrgh$Polyglot' exercises_adjectives > adj.sql -p (in local machine works) but get this: mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

also tried: mysqldump -u xDrgh -p --databases "xDrgh$default" > /home/xDrgh/dump.sql
-the same error

Ah, looks like I missed something from the previous post -- add this to the command line:

-hmysql.server

Hi,

I am trying to use mysqldump as well with the following command:

mysqldump -u dreeves -p -h mysql.server --databases "dreeves$default" > /home/dreeves/dump.sql

After entering my mysql command, I get the following error message:

mysqldump: Got error: 1044: Access denied for user 'dreeves'@'%' to database 'dreeves' when selecting the database

I am sure that my password is correct because I can connect to mysql through the command line with the same password.

Can anybody tell what I might be doing incorrectly here?

Use single quotes around the database name otherwise bash interprets the $default as a shell variable and replaces it with nothing, so you end up connecting to the wrong database. I'll edit giles' post so that others don't make the same mistake.

Thanks! That did the trick.

Due to my very simple nature, I need more explicit instructions:

1. Open a BASH console, change to your home folder: cd ~. Ok I did not really need this step, but felt I had to include it.

2. Edit the following line, replacing UserName with your account user name - note the system is case sensitive. So if you foolishly used capitals when you registered your account, you must use them now.

   mysqldump -u UserName -p -h mysql.server --databases 'UserName$default' > /home/UserName/dump.sql

3. When you run the customized command, it will ask for your database password. If you are using Django this is the same password in your settings.py file.

4. You should now have a (possibly very large) dump.sql file!

Who has a nice example Scheduled Task job file to run this command and FTP the result to an offsite location?

Rich.

Thanks for the details! Hopefully that will help anyone else who's confused by our vaguer directions above...

I had to do this for my web2py app and thought I would share. This code can be ran from the schedule tab. This runs the command without the password prompt but you will have to save your db password in plain text in the script block.

#!/usr/bin/python
import os
import time

db_User_Name = 'UserName'
DB_User_Password = 'secret'
DB_Name = 'UserName$default'
backupDir = '/home/UserName/backup/dump'

datetime = time.strftime('%m%d%Y-%H%M%S')
datetimeBackupDir = backupDir + datetime

print "creating backup folder"
if not os.path.exists(datetimeBackupDir):
    os.makedirs(datetimeBackupDir)


mysqldump_cmd = "mysqldump -u " + db_User_Name + " --password='" + DB_User_Password + "' -h mysql.server --databases '" + DB_Name + "' > " + datetimeBackupDir + "/" + DB_Name + ".sql"
os.system(mysqldump_cmd)

That's really useful, thanks! Good point about the DB password being in plaintext, but I guess it's going to be in plaintext in your web2py app's code anyway, so at least it doesn't make things any less secure than they already are :-)

I made a help page about backing up and restoring mysql databases using mysqldump -- let me know if it helps (or any suggestions for improvement)

ok. So I finally got around to automating this on this client's Django site. PA might want to augment their help guide with this.

I'm using two files, the first is a BASH shell because it tends to work better with scheduling stuff - especially CRON. Also, I ALWAYS use a virtual enviroment to contain my Python setup so I can easily test new library versions (like new Django releases). And its usually safer to activate the virtual env from the BASH environment - this is something PA could choose to "certify" an "accepted" method to always ensure the env is active, until then, take no chances!

Both of these files are under source-code control using Subversion, in case you notice they are in a sub-folder for no apparent reason.

BASH shell file:

#! /bin/bash

source /home/YourUserName/.virtualenvs/py27dj18/bin/activate

/home/YourUserName/.virtualenvs/py27dj18/bin/python /home/YourUserName/svn/db_backup.py

Python file (building upon the posting above by indepthcorp):

#!/usr/bin/python
import os
import time
from ftplib import FTP

db_User_Name = 'YourUserName'
DB_User_Password = 'Brexit-TooSoon?'
DB_Name = 'YourUserName$default'
backupDir = '/home/YourUserName/backup'
datetime = time.strftime('%Y-%m-%d-%H-%M-%S')
db_save_file = db_User_Name + datetime + ".sql"

#print "creating backup folder"
if not os.path.exists(backupDir):
    os.makedirs(backupDir)

def send_ftp(fpath, fname):
    ''' Upload a file to the GNV web server for safe-keeping'''
    #print "opening FTP connection"
    myftp = FTP('ftp.server.easyweb.com', 'backupUserName@easydns.ca', 'BrexitAgain')
    #print "opening file to send"
    myfile = open(os.path.join(fpath, fname), 'rb')
    #print 'starting to send file'
    myftp.storbinary("STOR " + fname, myfile, 1024)
    #print "upload done!"
    myfile.close()
    myftp.quit()
    #print "closing connection and file"

mysqldump_cmd = "mysqldump -u " + db_User_Name + " --password='" +\
    DB_User_Password + "' -h YourUserName.mysql.pythonanywhere-services.com --databases '" +\
    DB_Name + "' > " + os.path.join(backupDir, db_save_file)
#print "Dump cmd:\n", mysqldump_cmd
os.system(mysqldump_cmd)

send_ftp(backupDir, db_save_file)

Let me know if you find this useful!

R. Cooke

GNV Technology (www.gnv.ca)

@harry
When tries to backup the database using command

mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname'  > db-backup.sql

given on help page http://help.pythonanywhere.com/pages/MySQLBackupRestore, I got the below error ->

mysqldump: Got error: 1044: Access denied for user 'mysqlusername'@'%' to database 'mysqlusername$db' when selecting the database

Any suggestions why this is happening?

Did you use the wrong datrabase name? What's the full error traceback?

I used correct DB name. There was only this error.

Your mysql username is not 'mysqlusername'. Your actual mysql username is shown on the Databases tab.

I replaced the my actual username with mysqlusername but then there are some other issue. Now it is working fine. Thanks

:)

when I schedule the following code, the files are created, but my .sql file is empty. where do you think i'm making a mistake?

 import os
import datetime
from zipfile import ZipFile


BACKUP_DIR_NAME = ...................db_yedek"
DAYS_TO_KEEP_BACKUP = 3
FILE_PREFIX = "my_db_........._"
FILE_SUFFIX_DATE_FORMAT = "%Y%m%d%H%M%S"
USERNAME = "............."
DBNAME = USERNAME+"$..............."

timestamp = datetime.datetime.now().strftime(FILE_SUFFIX_DATE_FORMAT)
backup_filename = BACKUP_DIR_NAME+"/"+FILE_PREFIX+timestamp+".sql"

os.system("mysqldump -u "+USERNAME+" -h "+USERNAME+".mysql.pythonanywhere-services.com '"+DBNAME+"'  > "+backup_filename)

zip_filename = BACKUP_DIR_NAME+"/"+FILE_PREFIX+timestamp+".zip"
with ZipFile(zip_filename, 'w') as zip:
    zip.write(backup_filename, os.path.basename(backup_filename))

os.remove(backup_filename)

list_files = os.listdir(BACKUP_DIR_NAME)

back_date = datetime.datetime.now() - datetime.timedelta(days=DAYS_TO_KEEP_BACKUP)
back_date = back_date.strftime(FILE_SUFFIX_DATE_FORMAT)

length = len(FILE_PREFIX)

for f in list_files:
    filename = f.split(".")[0]
    if "zip" == f.split(".")[1]:
        suffix = filename[length:]
        if suffix < back_date:
            print("Deleting file : "+f)
            os.remove(BACKUP_DIR_NAME + "/" + f)

os.system runs the command in a subshell, so it interprets the "$something" in your database as a variable. Put the database name in single quotes for the shell.

the backup file haveing the total data in the database or not?

If you use a command like the one on our MySQL backup help page then yes, it will contain all of the data from the database.

OSError: [Errno 21] Is a directory: '/home/pvrvl/backup/11-02-2020'

how can i send that .sql file via email at the time of genarateing the file

You can send .sql file as any other file. For a free account, you have to use https://help.pythonanywhere.com/pages/SMTPForFreeUsers/

I have a script in my Tasks that follows the instructions on the help page: Backing up (and restoring) MySQL databases:

mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com --set-gtid-purged=OFF --no-tablespaces 'yourusername$dbname' > db-backup.sql

However, a few days ago the backup it generated came without the data, only the structure of the database. How can I fix this problem? I have had an account for 2 years and this problem occurred a few days ago, after having changed the system image to "haggis"

Did the task produce any additional output? My guess would be that you need to add --column-statistics=0 to the command.