Re: FULL mysqldump
Am 23.12.2011 22:42, schrieb Wm Mussatto: >> so you have a REAL consistent backup with minimal downtime you can restore >> on any machine and pull dumps of whatever you really need instead of >> breaindead hughe dumps with long locking time while they are done or >> withut locking inconsistent state >> >> the first rsync runs while the server is online and the second one >> after mysqld is stopped takes a few moemnts because only changed data >> in the meantime have to be synced again >> >> this way you can backup many GB of mysql-data with minimal downtime >> and 100% consistence >> > This is true if the problem is many relatively small tables. Not sure how > well it would work if the problem was one or more very large tables. does not matter if you look how rsync works i am using rsync ober the WAN each day for backups or some TB real data with only 3-6 GB traffic each day to give an specifiy example how good this works a part of this backups is a mysql-server for dbmail with one table-file (innodb) > 10 GB, the whole backup over a 22Mbit wire limited to 800KB/Sec. takes ususally around 30 minutes signature.asc Description: OpenPGP digital signature
Re: FULL mysqldump
On Fri, December 23, 2011 12:27, Reindl Harald wrote: > > > Am 23.12.2011 21:14, schrieb Jim McNeely: >> Hello all, happy holidays! >> >> What is the best way to run a mysqldump to get the tables, the data, the >> triggers, the views, the procedures, the privileges and users, >> everything? It seems confusing in the online documentation, or is that >> just me? > > echo "Prepare im laufenden Betrieb" > rsync --times --perms --owner --group --recursive --delete-after > /mysql_data/ /mysql_backup/ > echo "Offline_sync" > /sbin/service mysqld stop > cd /Volumes/dune/mysql_data/ > rm -f /Volumes/dune/mysql_data/bin* > rsync --progress --times --perms --owner --group --recursive > --delete-after /mysql_data/ //mysql_backup/ > /sbin/service mysqld start > > so you have a REAL consistent backup with minimal downtime you can restore > on any machine and pull dumps of whatever you really need instead of > breaindead hughe dumps with long locking time while they are done or > withut locking inconsistent state > > the first rsync runs while the server is online and the second one > after mysqld is stopped takes a few moemnts because only changed data > in the meantime have to be synced again > > this way you can backup many GB of mysql-data with minimal downtime > and 100% consistence > This is true if the problem is many relatively small tables. Not sure how well it would work if the problem was one or more very large tables. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: FULL mysqldump
Hi Jim happy holidays to you! actually you just need to add the --routines trigger mysqldump --all-databases --*routines* > fulldump.sql with this you get all databases including the system one with privileges (mysql), triggers is on by default, you enable routines with the flag --* routines* * * Keep in mind that this method needs any application to be stopped from writing either by shutting it down, blocking it at network level or locking the database with something like FLUSH TABLES WITH READ LOCK; Depending on your application, your SLA, etc. Keep also in mind that for database larger than a few GB it is not recommended to use mysqldump (text dump) but any binary method, among which Percona XtraBackup in my opinion is the golden tool, derived from InnoBackup allows hot backups. Cheers Claudio 2011/12/23 Jim McNeely > Hello all, happy holidays! > > What is the best way to run a mysqldump to get the tables, the data, the > triggers, the views, the procedures, the privileges and users, everything? > It seems confusing in the online documentation, or is that just me? > > Thanks, > > Jim McNeely > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > > -- Claudio
Re: FULL mysqldump
Am 23.12.2011 21:14, schrieb Jim McNeely: > Hello all, happy holidays! > > What is the best way to run a mysqldump to get the tables, the data, the > triggers, the views, the procedures, the privileges and users, everything? It > seems confusing in the online documentation, or is that just me? echo "Prepare im laufenden Betrieb" rsync --times --perms --owner --group --recursive --delete-after /mysql_data/ /mysql_backup/ echo "Offline_sync" /sbin/service mysqld stop cd /Volumes/dune/mysql_data/ rm -f /Volumes/dune/mysql_data/bin* rsync --progress --times --perms --owner --group --recursive --delete-after /mysql_data/ //mysql_backup/ /sbin/service mysqld start so you have a REAL consistent backup with minimal downtime you can restore on any machine and pull dumps of whatever you really need instead of breaindead hughe dumps with long locking time while they are done or withut locking inconsistent state the first rsync runs while the server is online and the second one after mysqld is stopped takes a few moemnts because only changed data in the meantime have to be synced again this way you can backup many GB of mysql-data with minimal downtime and 100% consistence signature.asc Description: OpenPGP digital signature
