List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:December 23 2011 9:42pm
Subject:Re: FULL mysqldump
View as plain text  
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

FULL mysqldumpJim McNeely23 Dec
  • Re: FULL mysqldumpReindl Harald23 Dec
    • Re: FULL mysqldumpWm Mussatto23 Dec
      • Re: FULL mysqldumpReindl Harald23 Dec
    • Re: FULL mysqldumpIgor Shevtsov24 Dec
      • Re: FULL mysqldumpReindl Harald24 Dec
  • Re: FULL mysqldumpClaudio Nanni23 Dec