Scheduled backup of MySQL databases

Discussion in 'PHP/MySQL Development' started by RedRocky, Oct 9, 2009.

  1. RedRocky Member

    What is the best way to go about doing a scheduled backup of MySQL databases? I can use phpMyAdmin to do a manual backup, but that isn't practical if I am wanting to do backups of multiple databases twice a week, as I do at present for Access databases (using an FTP program to download the databases to my PC).

    Also, what do you reckon is better.. one big MySQL database for a site or multiple smaller MySQL databases serving this site? The site could have a few hundred users each with their own set of data for this site (the site displays that user's data). Which is quicker, more efficient, better for the server usage etc?

    Thanks.
  2. nedsbeds New Member

    A few hundred users won't tax mysql at all if the data is properly normalised.
    On massive sites (many thousands of users!) where users are creating lots of content then a table for each user is a good optimisation. you can then span your data over lots of servers without having to worry too much about synchronising data across them. Wordpress.com is a good example of that sort of arrangement.
    As long as you set up proper indexing for the tables you create, you should be reet!

    As far as backing up the database goes, in windows you can use the windows scheduler to run mysqldump periodically (create a batch file with all the details of your server in and then call the batch file)
    http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
    http://www.iopus.com/guides/winscheduler.htm

    Nick
  3. RedRocky Member

    What about in terms of web site visitors? The way my system works is that I have multiple domains mapped to a single web site (to a single set of web site files). Each domain has its own database at present, and so the data from each database is used to customise each web site. Users can update their web site / database from the content management system.

    Since I use one set of files to display the web site, I could also use one MySQL database (when I convert to MySQL), instead of multiple databases at present.

    I am wondering what would be more efficient however... lots of MySQL databases each being used by a few visitors at a time, or one MySQL database being used by lots of web site visitors at once.

    I am leaning towards having separate databases due to flexibility it gives me, with each domain having a separate set of data that I can get at separately without having to extract it from one big database.

    Any thoughts?

Share This Page