MySQL full and differential backup and restore

https://dev.mysql.com/doc/refman/5.7/en/log-file-maintenance.html provides coverage of solutions.

Normally, if this is an issue, you can simply run mysqldump with the --flush-logs option, which will flush logs before the dump begins.

Caveat: Using the --flush-logs option only does a single flush before your backup begins. This means any data written to logs during your backup may or may not be captured.

hi,

from that link and I change to version 8.0:

"To force MySQL to start using new log files, flush the logs. Log flushing occurs when you execute a FLUSH LOGS statement"

why we need to use a new log file? why we don't simply keep using the existing one.

actually I am focusing on how to backup transcatoin log, so is that mean transcation log backup for PITR is to move and change the existing log name as a backup, then do a flush log to generate a new log and use that new one, while we backup the old (which is the moved one)?

so whenever we flush log the existing log file we need to stop the MySQL before processing sth like this from the link:

cd mysql-data-directory
mv mysql.log mysql.log.old
mv mysql-slow.log mysql-slow.log.old
mv err.log err.log.old
mysqladmin flush-logs

Select allOpen in new window

?

in this case we just copy/backup the *.old files ?

"This means any data written to logs during your backup may or may not be captured."

but those data are in current log but not *.old log, right?

1) why we need to use a new log file? why we don't simply keep using the existing one.

--flush-logs says to flush/commit log transactions into /var/lib/mysql. Nothing to do with creating a new log file.

2) actually I am focusing on how to backup transaction log

This won't really do you any good.

You could do this, for a backup which will restore.

a) service mysql stop

b) backup /var/lib/mysql (data files) + /var/log/mysql (log files)

c) service mysql start

If you do anything else with log files, you'll likely end up with some sort of corruption when trying to restore.

Note: Better to just use mysql/mysqldump as intended, which means using --flush-logs.

3) Reading your question carefully, I'm talking about binlogs + looks like you're talking about informational logs.

If you're talking about informational logs, just back them up somewhere. Nothing special, as they're just log files.

4) Be aware log flushing relates only to binlogs, nothing to do with informational logs (error.log, general.log, slow.log) as the only way to completely flush informational logs is to stop your database instance, otherwise last block of log files will many times be corrupt, as informational log filehandles are always open in memory, so last write/EOF marker lives in memory till next OS sync runs, to flush i/o buffers to disk.

Suggestion: After you close out this question, open a new question describing what you're trying to accomplish by keeping informational logs + likely someone can provide details about how you can accomplish your goal.

"--flush-logs says to flush/commit log transactions into /var/lib/mysql"

you mean write all commit log to the datafile in the path /var/lib/mysql ?

"This won't really do you any good.
"

why not? for PITR operation a full and transaction log backup is necessary, agree?

"If you're talking about informational logs, just back them up somewhere. Nothing special, as they're just log files.'

I want to PITR, what should I do? full backup plus transaction log backup, right?

" --flush-logs option, which will flush logs before the dump begins."

I look back at your answer , it seems we have definition of flush logs, what is your flush log means ?

" by keeping informational logs + likely someone can"

no I am concerning full backup + transaction log backup so that I can do PITR for MySQL.

how about this:

https://dev.mysql.com/doc/mysql-backup-excerpt/8.0/en/backup-methods.html

and it say:

Making Incremental Backups by Enabling the Binary Log

MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see The Binary Log. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 1.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS or mysqldump --flush-logs. See mysqldump — A Database Backup Program.

this is what I meant! what the flush log do actually behind the scene and what is the full command to do the incremental/differential backup for MySQL?

so once we do flush log it will generate a new bin log which we DO NOT backup at all as MySQL is using it, we just backup the OLD *.log ?

and here:

https://dev.mysql.com/doc/mysql-backup-excerpt/8.0/en/point-in-time-recovery.html

when we restore log we do:

shell> mysqlbinlog binlog_files | mysql -u root -p

binlog_files is the name of all bin log files, one bye one and the mysqlbinlog will convert it to readable and appliable format,

so how can I backup all <binlog_files> while the MySQL is online ? As bin log keep generating one by one so we only have to flush log, then copy all INACTIVE log to another location so THAT IS OUR LOG BACKUP ?

and here:https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

for fast backup and restore physical backup is needed and it is ONLY from MySQL enterprise backup ?

so do you think this should be the answer: https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-backup.html

Example: mysqldump + mysqlbinlog for Backup and Restore
The following example describes a simple scenario that shows how to use mysqldump and mysqlbinlog together to back up a server's data and binary log, and how to use the backup to restore the server if data loss occurs. The example assumes that the server is running on host host_name and its first binary log file is named binlog.000999. Enter each command on a single line.

Use mysqlbinlog to make a continuous backup of the binary log:

mysqlbinlog --read-from-remote-server --host=host_name --raw
  --stop-never binlog.000999
Use mysqldump to create a dump file as a snapshot of the server's data. Use --all-databases, --events, and --routines to back up all data, and --master-data=2 to include the current binary log coordinates in the dump file.

mysqldump --host=host_name --all-databases --events --routines --master-data=2> dump_file
Execute the mysqldump command periodically to create newer snapshots as desired.

If data loss occurs (for example, if the server crashes), use the most recent dump file to restore the data:

mysql --host=host_name -u root -p < dump_file
Then use the binary log backup to re-execute events that were written after the coordinates listed in the dump file. Suppose that the coordinates in the file look like this:

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.001002', MASTER_LOG_POS=27284;
If the most recent backed-up log file is named binlog.001004, re-execute the log events like this:

mysqlbinlog --start-position=27284 binlog.001002 binlog.001003 binlog.001004
  | mysql --host=host_name -u root -p
You might find it easier to copy the backup files (dump file and binary log files) to the server host to make it easier to perform the restore operation, or if MySQL does not allow remote root access.

?

before use mysqlbinlog to backup log we should do:

mysql> SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000130 |     27459 | No        |
| binlog.000131 |     13719 | No        |
| binlog.000132 |     43268 | No        |
+---------------+-----------+-----------+

to find out earliest log files name and then use this command to keep backing up log?

mysqlbinlog --read-from-remote-server --host=host_name --raw
  --stop-never binlog.000130

as .000130 is the FIRST log file
?

so question now is , how can we keep the bin log backup retention period ? bin log should be big I think.