Forums

EDIT: never mind, I should have searched the forums first.Problem is solved here

I ran into a bit of trouble with my web2py/mySQL db today (couldn't insert a new record because a foreign key constraint kept failing). I couldn't figure out the problem, so I just started a fresh database and pointed web2py to it, which created the correct (empty) tables.

But, I couldn't figure out how to get my data from the old database into the new one. (Eventually I pointed web2py temporarily back to the old db, downloaded all the tables individually as .csv, pointed web2py back to the new db, and imported all the tables as .csv).

Is there an automated way to backup the database? I've seen info online for mysqldump but when I tried it, it said

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

I also tried SELECT * INTO OUTFILE but it said I didn't have sufficient privileges. Thanks.

Thanks for taking the time to make the edit and pointing back to a solution Matt.

Cheers

Just a quick note for anybody coming across this. In MySQL the SELECT...INTO OUTFILE <file> syntax is generally not as useful as it might first appear. The main issue is that the file created is on the MySQL server machine - this might be fine for small-scale little websites where the database and webserver are running on the same host, but in general MySQL runs on an external server machine. Even for websites that start small, it's generally good practice to plan for scale so you wouldn't want to assume any two components running on the same host (or even running on a single host).

In the case of hosting environments like PA then users don't have any access to that machine except via SQL, so creating a file on that machine is essentially pointless since the user has no way of retrieving the file. There are also various security implications which also make it troublesome from the point of view of those hosting the services. As a result, users aren't generally allowed the FILE privilege on such hosts - this explains the permissions error mentioned above.

In general, mysqldump is the most widely-used method of performing backups in small- and medium-scale setups. Larger deployments tend not to be able to afford any downtime, so they'll generally have multiple servers and use replication to keep them in sync, although that's firmly in the area of things that only administrators, and not users, need to care about.

Thanks, Cartroo!