From: siranee.ja@tpc.co.th
To: "Chris Murphy" <lists@colorremedies.com>
Cc: "Chris Murphy" <lists@colorremedies.com>,
"Btrfs BTRFS" <linux-btrfs@vger.kernel.org>,
voravat@tpcorp.co.th
Subject: Re: btrfs issue with mariadb incremental backup
Date: Sat, 12 Aug 2017 12:08:31 +0700 (ICT) [thread overview]
Message-ID: <1420.49.228.123.163.1502514511.squirrel@mail> (raw)
In-Reply-To: <CAJCQCtQ2exGHj3zq0221aXDqMAok+_G6vwpaiQVfy8_1ybJKYQ@mail.gmail.com>
Hi Chris,
Sorry for the misunderstanding and inappropriate attached the file (I thought that
too long text in the mail message so I used the attached file).
The backup script has the btrfs sync command since Aug 3
I sent the following rsync command as you said (sorry for the misunderstanding with
the rsync command that I'd never used before)
The snapshot has been different since snapshot mysql_201708090830 but the mariadb
can recovery and start until mysql_201708110830 seems too much differences that can
not start.
[root@backuplogC7 ~]# more /root/script/backup/backupsnap.sh
#Backup
#
user=$1
password=$2
basepath=$3
datet=$(date +%Y%m%d%H%M)
snappath=${basepath}_${datet}
echo "Locking databases ${datet}"
mysql -u$user -p$password << EOF
FLUSH TABLES WITH READ LOCK;
system btrfs sub snap -r $basepath $snappath
system btrfs sub sync $basepath
UNLOCK TABLES;
quit
EOF
echo "Databases unlocked ${datet}"
[root@backuplogC7 ~]# ls -l /root/script/backup/backupsnap.sh
-rwxr--r-- 1 root root 333 Aug 3 09:11 /root/script/backup/backupsnap.sh
[root@backuplogC7 ~]# rsync -avnc /var/lib/mariadb/mysql_201708070830/
root@192.168.45.166:/var/lib/mariadb/mysql_201708070830/
sending incremental file list
./
sent 3773 bytes received 19 bytes 1083.43 bytes/sec
total size is 718361496 speedup is 189441.32 (DRY RUN)
[root@backuplogC7 ~]# rsync -avnc /var/lib/mariadb/mysql_201708080830/
root@192.168.45.166:/var/lib/mariadb/mysql_201708080830/
sending incremental file list
./
sent 3769 bytes received 19 bytes 841.78 bytes/sec
total size is 718361496 speedup is 189641.37 (DRY RUN)
[root@backuplogC7 ~]# rsync -avnc /var/lib/mariadb/mysql_201708090830/
root@192.168.45.166:/var/lib/mariadb/mysql_201708090830/
sending incremental file list
./
ib_logfile1
ibdata1
sent 3779 bytes received 25 bytes 1086.86 bytes/sec
total size is 718361496 speedup is 188843.72 (DRY RUN)
[root@backuplogC7 ~]# rsync -avnc /var/lib/mariadb/mysql_201708100830/
root@192.168.45.166:/var/lib/mariadb/mysql_201708100830/
sending incremental file list
./
ib_logfile1
ibdata1
sent 3779 bytes received 25 bytes 1086.86 bytes/sec
total size is 718361496 speedup is 188843.72 (DRY RUN)
[root@backuplogC7 ~]# rsync -avnc /var/lib/mariadb/mysql_201708110830/
root@192.168.45.166:/var/lib/mariadb/mysql_20170811830/
sending incremental file list
created directory /var/lib/mariadb/mysql_20170811830
./
aria_log.00000001
aria_log_control
ib_logfile0
ib_logfile1
ibdata1
mysql-bin.000001
mysql-bin.index
backups_db/
backups_db/db.opt
backups_db/ints.frm
backups_db/tpc_backup_detail.frm
backups_db/tpc_backup_header.frm
backups_db/tpc_backup_history.frm
backups_db/tpc_backup_schedule_level_master.frm
backups_db/tpc_backup_schedule_master.frm
backups_db/tpc_backup_schedule_master_extra.frm
backups_db/tpc_backup_schedule_old.frm
backups_db/tpc_backup_server.frm
backups_db/tpc_backup_step.frm
backups_db/tpc_backup_user.frm
backups_db/v_backup_header_status.frm
backups_db/v_calendar.frm
backups_db/v_dup_backup.frm
mysql/
mysql/columns_priv.MYD
mysql/columns_priv.MYI
mysql/columns_priv.frm
mysql/db.MYD
mysql/db.MYI
mysql/db.frm
mysql/event.MYD
mysql/event.MYI
mysql/event.frm
mysql/func.MYD
mysql/func.MYI
mysql/func.frm
mysql/general_log.CSM
mysql/general_log.CSV
mysql/general_log.frm
mysql/help_category.MYD
mysql/help_category.MYI
mysql/help_category.frm
mysql/help_keyword.MYD
mysql/help_keyword.MYI
mysql/help_keyword.frm
mysql/help_relation.MYD
mysql/help_relation.MYI
mysql/help_relation.frm
mysql/help_topic.MYD
mysql/help_topic.MYI
mysql/help_topic.frm
mysql/host.MYD
mysql/host.MYI
mysql/host.frm
mysql/ndb_binlog_index.MYD
mysql/ndb_binlog_index.MYI
mysql/ndb_binlog_index.frm
mysql/plugin.MYD
mysql/plugin.MYI
mysql/plugin.frm
mysql/proc.MYD
mysql/proc.MYI
mysql/proc.frm
mysql/procs_priv.MYD
mysql/procs_priv.MYI
mysql/procs_priv.frm
mysql/proxies_priv.MYD
mysql/proxies_priv.MYI
mysql/proxies_priv.frm
mysql/servers.MYD
mysql/servers.MYI
mysql/servers.frm
mysql/slow_log.CSM
mysql/slow_log.CSV
mysql/slow_log.frm
mysql/tables_priv.MYD
mysql/tables_priv.MYI
mysql/tables_priv.frm
mysql/time_zone.MYD
mysql/time_zone.MYI
mysql/time_zone.frm
mysql/time_zone_leap_second.MYD
mysql/time_zone_leap_second.MYI
mysql/time_zone_leap_second.frm
mysql/time_zone_name.MYD
mysql/time_zone_name.MYI
mysql/time_zone_name.frm
mysql/time_zone_transition.MYD
mysql/time_zone_transition.MYI
mysql/time_zone_transition.frm
mysql/time_zone_transition_type.MYD
mysql/time_zone_transition_type.MYI
mysql/time_zone_transition_type.frm
mysql/user.MYD
mysql/user.MYI
mysql/user.frm
performance_schema/
performance_schema/cond_instances.frm
performance_schema/db.opt
performance_schema/events_waits_current.frm
performance_schema/events_waits_history.frm
performance_schema/events_waits_history_long.frm
performance_schema/events_waits_summary_by_instance.frm
performance_schema/events_waits_summary_by_thread_by_event_name.frm
performance_schema/events_waits_summary_global_by_event_name.frm
performance_schema/file_instances.frm
performance_schema/file_summary_by_event_name.frm
performance_schema/file_summary_by_instance.frm
performance_schema/mutex_instances.frm
performance_schema/performance_timers.frm
performance_schema/rwlock_instances.frm
performance_schema/setup_consumers.frm
performance_schema/setup_instruments.frm
performance_schema/setup_timers.frm
performance_schema/threads.frm
test/
test/db.opt
sent 4124 bytes received 370 bytes 1797.60 bytes/sec
total size is 718361496 speedup is 159849.02 (DRY RUN)
[root@backuplogC7 ~]#
[root@backuplogC7 ~]# rsync -avnc /var/lib/mariadb/mysql_201708120830/
root@192.168.45.166:/var/lib/mariadb/mysql_20170812830/
sending incremental file list
created directory /var/lib/mariadb/mysql_20170812830
./
aria_log.00000001
aria_log_control
ib_logfile0
ib_logfile1
ibdata1
mysql-bin.000001
mysql-bin.index
backups_db/
backups_db/db.opt
backups_db/ints.frm
backups_db/tpc_backup_detail.frm
backups_db/tpc_backup_header.frm
backups_db/tpc_backup_history.frm
backups_db/tpc_backup_schedule_level_master.frm
backups_db/tpc_backup_schedule_master.frm
backups_db/tpc_backup_schedule_master_extra.frm
backups_db/tpc_backup_schedule_old.frm
backups_db/tpc_backup_server.frm
backups_db/tpc_backup_step.frm
backups_db/tpc_backup_user.frm
backups_db/v_backup_header_status.frm
backups_db/v_calendar.frm
backups_db/v_dup_backup.frm
mysql/
mysql/columns_priv.MYD
mysql/columns_priv.MYI
mysql/columns_priv.frm
mysql/db.MYD
mysql/db.MYI
mysql/db.frm
mysql/event.MYD
mysql/event.MYI
mysql/event.frm
mysql/func.MYD
mysql/func.MYI
mysql/func.frm
mysql/general_log.CSM
mysql/general_log.CSV
mysql/general_log.frm
mysql/help_category.MYD
mysql/help_category.MYI
mysql/help_category.frm
mysql/help_keyword.MYD
mysql/help_keyword.MYI
mysql/help_keyword.frm
mysql/help_relation.MYD
mysql/help_relation.MYI
mysql/help_relation.frm
mysql/help_topic.MYD
mysql/help_topic.MYI
mysql/help_topic.frm
mysql/host.MYD
mysql/host.MYI
mysql/host.frm
mysql/ndb_binlog_index.MYD
mysql/ndb_binlog_index.MYI
mysql/ndb_binlog_index.frm
mysql/plugin.MYD
mysql/plugin.MYI
mysql/plugin.frm
mysql/proc.MYD
mysql/proc.MYI
mysql/proc.frm
mysql/procs_priv.MYD
mysql/procs_priv.MYI
mysql/procs_priv.frm
mysql/proxies_priv.MYD
mysql/proxies_priv.MYI
mysql/proxies_priv.frm
mysql/servers.MYD
mysql/servers.MYI
mysql/servers.frm
mysql/slow_log.CSM
mysql/slow_log.CSV
mysql/slow_log.frm
mysql/tables_priv.MYD
mysql/tables_priv.MYI
mysql/tables_priv.frm
mysql/time_zone.MYD
mysql/time_zone.MYI
mysql/time_zone.frm
mysql/time_zone_leap_second.MYD
mysql/time_zone_leap_second.MYI
mysql/time_zone_leap_second.frm
mysql/time_zone_name.MYD
mysql/time_zone_name.MYI
mysql/time_zone_name.frm
mysql/time_zone_transition.MYD
mysql/time_zone_transition.MYI
mysql/time_zone_transition.frm
mysql/time_zone_transition_type.MYD
mysql/time_zone_transition_type.MYI
mysql/time_zone_transition_type.frm
mysql/user.MYD
mysql/user.MYI
mysql/user.frm
performance_schema/
performance_schema/cond_instances.frm
performance_schema/db.opt
performance_schema/events_waits_current.frm
performance_schema/events_waits_history.frm
performance_schema/events_waits_history_long.frm
performance_schema/events_waits_summary_by_instance.frm
performance_schema/events_waits_summary_by_thread_by_event_name.frm
performance_schema/events_waits_summary_global_by_event_name.frm
performance_schema/file_instances.frm
performance_schema/file_summary_by_event_name.frm
performance_schema/file_summary_by_instance.frm
performance_schema/mutex_instances.frm
performance_schema/performance_timers.frm
performance_schema/rwlock_instances.frm
performance_schema/setup_consumers.frm
performance_schema/setup_instruments.frm
performance_schema/setup_timers.frm
performance_schema/threads.frm
test/
test/db.opt
sent 4124 bytes received 370 bytes 1797.60 bytes/sec
total size is 718361496 speedup is 159849.02 (DRY RUN)
Best Regards,
Siranee Jaraswachirakul
> On Fri, Aug 11, 2017 at 8:38 PM, <siranee.ja@tpc.co.th> wrote:
>> Hi Chris,
>>
>> I explain what I have done in the attached file.
>
> That is way too long. And please don't attach files directed at me,
> that's not appropriate. Just message the list normally, so it can be
> searched by others down the road.
>
> There is a problem with your rsync command, you really don't
> understand what I was asking. The whole point of rsync is to compare
> the origin and destination subvolumes to see if they are different
> from each other. You ran the command on a single subvolume on a single
> machine which is pointless.
>
> On machine A:
>
> $ rsync -avnc /mnt/snapshot6/ chris@192.168.1.116:/mnt/snapshot6/
> chris@192.168.1.116's password:
> sending incremental file list
>
> sent 125 bytes received 12 bytes 39.14 bytes/sec
> total size is 30,686 speedup is 223.99 (DRY RUN)
> [chris@f26h ~]$
>
> Because no files are listed, all are confirmed to be identical. If
> files are listed, there's a difference.
>
>
>
>> Please suggest me what I should do next?
>
> Check this. I wonder if you're running into this obscure bug where
> maybe your read only snapshot is not yet synced before it's being
> sent. Hence it *is* changing on origin machine, but not because of
> other snapshots being deleted. I have no idea if your scripting
> handles errors like the bogus stale NFS handle error, maybe it's
> possible it happens but you're not seeing it? Anyway, you might try
> just adding a single sync right after taking the snapshot, change
> nothing else and see if the problem reproduces itself.
>
> https://btrfs.wiki.kernel.org/index.php/Incremental_Backup#Initial_Bootstrapping
>
> I'm pretty sure this is fixed in newer kernels, I haven't run into it
> in a long time myself. But I don't know when it was fixed.
>
>
>
> --
> Chris Murphy
>
next prev parent reply other threads:[~2017-08-12 5:08 UTC|newest]
Thread overview: 31+ messages / expand[flat|nested] mbox.gz Atom feed top
2017-08-09 4:32 btrfs issue with mariadb incremental backup siranee.ja
2017-08-09 5:46 ` Chris Murphy
2017-08-09 6:36 ` siranee.ja
2017-08-09 17:59 ` Chris Murphy
2017-08-10 2:03 ` siranee.ja
2017-08-11 4:40 ` siranee.ja
2017-08-11 6:00 ` siranee.ja
2017-08-11 15:35 ` Chris Murphy
2017-08-12 2:38 ` siranee.ja
2017-08-12 4:31 ` Chris Murphy
2017-08-12 5:08 ` siranee.ja [this message]
2017-08-12 21:34 ` Chris Murphy
2017-08-12 22:41 ` Janos Toth F.
2017-08-12 23:07 ` Chris Murphy
2017-08-12 22:49 ` Hugo Mills
2017-08-12 23:14 ` Chris Murphy
2017-08-13 2:20 ` siranee.ja
2017-08-13 2:59 ` Chris Murphy
2017-08-13 3:40 ` siranee.ja
2017-08-13 4:34 ` Chris Murphy
2017-08-13 10:49 ` siranee.ja
2017-08-13 19:31 ` Chris Murphy
2017-08-13 6:20 ` A L
2017-08-13 10:52 ` siranee.ja
2017-08-13 12:51 ` A L
2017-08-13 14:00 ` siranee.ja
2017-08-13 21:31 ` A L
2017-08-14 1:57 ` siranee.ja
2017-08-13 19:50 ` Chris Murphy
2017-08-14 2:04 ` siranee.ja
2017-08-11 15:06 ` Chris Murphy
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=1420.49.228.123.163.1502514511.squirrel@mail \
--to=siranee.ja@tpc.co.th \
--cc=linux-btrfs@vger.kernel.org \
--cc=lists@colorremedies.com \
--cc=voravat@tpcorp.co.th \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for NNTP newsgroup(s).