New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Comments
Personally, I'd set up a cron job to run mysqldump.
mysqldump via cron. Or mysqlhotcopy if your tables are all MyISAM. If you need your backup to have no impact on your database (i.e. not locking it), you might consider setting up mysql replication, and doing your backup from the slave.
mysqldump, cron, scp
mysql replication and backup from slave mysqldump and automysqlbackup
How big are the databases anyone is using these approaches with?
~350 MB in our case and yes mysqldump via cron works fine.
Small there @BlueVM. We have terabytes here in MySQL and looking for other ideas too. Bulky as all heck and PITA to manage backups and handle restores.
http://www.zmanda.com/backup-mysql.html
I use this with some big db
http://www.percona.com/doc/percona-xtrabackup/
Running a replica and getting backups from that is usually another option.
+1 Percona xTraBackup
Thanks @bamn and @prometeus. Interesting products.
Got slapped this week with a drive corruption that started who knows when, dropped a bunch of tables due to disk errors. Backups on server were eating up space on other disks. Been playing shuffle board.
Very weary of the Master-Slave typical install since in theory, bad data will be replicated. Who knows in this sort of failure.
In my current experience I am about to keep backups - full ones for a year. Dump the table layouts to a file on a daily basis since it's rather small to do such. The incrementals, well that's fussing with software recommended.
Fairly perturbed with MySQL now, especially InnoDB which happens to be the table types that ended up busted/corrupted.
You folks all running Percona version of MySQL?
does the replica run on another machine also? I'm lost heh.
@Juan,
As proposed and typically done, you have 2 MySQL instances running on different servers.
From 2nd "copy" or the slave, you then cut your more traditional backups, to a third server.
@pubcrawler - yop, Percona XtraDB 5.5
I use an ssh tunnel and mysql replication. then use mysqldump and rsync over ssh to an off-site location.
mydumper + pigz + custom written scripts
One of the reasons MariaDB 5.2/5.5 MySQL are my favourite mysql versions is I get to use multi threaded backup tool, mydumper (written in C by MySQL engineers who worked for MySQL and later moved to Facebook, SkySQL) which is 3x to 10x times faster than mysqldump for backups and restores of databases. InnoDB benchmarks http://vbtechsupport.com/1716/ & MyISAM benchmarks http://vbtechsupport.com/1695/
Mydumper combined in a custom written script and add multi threaded compression, pigz where it is up 33x times faster than gzip http://vbtechsupport.com/2094/ and you have a pretty fast mysql backup system
. But ultimately depends on how many cpu threads your server has http://vbtechsupport.com/1614/ and to an extent, the speed of your source/destination server disks and network.
Unfortunately, mydumper isn't compatible with Oracle MySQL 5.5 or Percona 5.5. But is compatible with Remi repository version of MySQL 5.5
1>2>3>4>5
@eva2000, good tools there. Giving them a try right now as working on an ongoing large migration between two remote sites.
Thanks for the posts on your site @eva2000 about mydumper. Huge difference compared to mysqldump. Ditto for pigz!
These should save me hours overall
How safe is mysqldump? Will it compatible when restoring via cPanel / phpmyadmin?
http://sourceforge.net/projects/automysqlbackup/
@Fritz, Mysqldump is a standard tool included in the mysql install. It entirely safe.
Might need to tweak settings to make sure you get all indexes, triggers, stored procedures, etc. included with the dump though.
@Fritz, mysqldump used correctly, is the safest way to backup a mysql database it's what i've been using for 12+ yrs now of which 10+ yrs with cPanel/WHM. But you want to restore database via mysql command line as phpmyadmin for large databases may timeout etc.
Glad I could help, on optimally configured system you can save a good couple of hours
Works well with forum databases in 30-105GB range so far
@eva2000 , @pubcrawler please enlightened me with your working settings so that the database can be restored safely without errors. :-)
ATM, I think the safest way is using PhpMyadmin which is totally wrong.
Glad to see better option. :-)
Vouch @eva2000, importing with phpmyadmin with large databases even with editing your webserver's config to allow higher uploads/etc, always poses problems, use http://www.ozerov.de/bigdump/ or the command line.
Although, anything less than 500MB-1GB, posed no problems with me so far..... :P (Via phpmyadmin)
I don't use PHP to deal with MySQL. Realize some folks are in shared hosting and that's the means. If you have a real site and real data, that won't probably cut it.
At minimum PHP timesouts need made longer and possible same needed in web server settings to allow extended open socket time as well as perhaps larger file upload support.
Historically I used mysqldump and mysql to import and export data. It's rather simple and once you do it once works every other time.
@Fritz, here's a doc someone created with examples. What you need starts under "Using mysqldump tool"
http://zetcode.com/databases/mysqltutorial/exportimport/