From: Wm Mussatto
Date: December 23 2011 9:42pm
Subject: Re: FULL mysqldump
List-Archive: http://lists.mysql.com/mysql/226504
Message-Id: <7af636d3c5169675859682b4584966f9.squirrel@secure2.csz.com>
MIME-Version: 1.0
Content-Type: text/plain;charset=UTF-8
Content-Transfer-Encoding: 8bit

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