1

I am using percona mysql 8.

I do not use any type of replication, but I read that binlogs are useful for data recovery. I would like to turn of binlogging and flush them while I run pt-online-schema-change to do non-impacting OPTIMIZE table.

After it is done, I want to turn binlogging back on (and then make efforts to move to a server with more space).

Is this safe and recommended? I need to optimize a table and can't go offline and making a copy of the table will make me run out of space unless I remove the 50GB of binlogs

3
  • Why do you need to use OPTIMIZE TABLE? Even if this does not help you today, it may help you tomorrow.
    – Rick James
    Dec 10 '20 at 20:10
  • if you din't make repication, please do backups, if it gets to big, you will switch ti replication
    – nbk
    Dec 10 '20 at 20:15
  • @RickJames because I deleted over 80% of the records of a TEXT field and effectively cleared many GBs of data, but the free space won't be realized until the table is optmized
    – ParoX
    Dec 19 '20 at 1:36
2

What you need to do is set sql_log_bin to 0 as you start pt-online-schema-change using set-vars

pt-online-schema-change --set-vars sql_log_bin=0

Your binary logs will never know the difference.

Please make sure you have enough space for the temp table (_tablename_new).

UPDATE 2020-12-10 14:45 EST

If you are not doing backups on this server, then you could run

PURGE BINARY LOGS BEFORE CURDATE() - INTERVAL 5 DAY;

to keep the last 5 days worth of binary logs or if you want all the binary logs gone, then run

RESET MASTER;

WARNING !!!

Before doing that, find out if the server is running replication as a Master.

UPDATE 2020-12-10 16:05 EST

Since you are running pt-online-schema-change, you should run it like this

pt-online-schema-change --set-vars sql_log_bin=0 --alter "ENGINE=InnoDB"

This will just create a temp table defragmented. It just so happens that --analyze-before-swap is the default value. That means the temp table has ANALYZE TABLE executed against it before swapping old and new tables. This has the exact same effect as OPTIMIZE TABLE on an InnoDB table.

As mentioned in nbk's comment, you should make sure you have backups. You would also do yourself a world of good by setting up replication and doing the backups from the slave.

2
  • Great to know that pt-online-schema-change can not write bin logs, but the issue is that I need to clear out 50GB of binlogs first too. Is that OK? PS: Yeah I ran pt-online-schema-change and run out of disk space mid-process. It couldnt drop the new table, was a bit of a scare for a moment but I purged old binlogs...now I need to know if I can just get rid of them for the time being
    – ParoX
    Dec 10 '20 at 19:09
  • Please keep in mind that I am not disabling the binary logs for the MySQL Instance. I am only disabling it for the session that is executing the pt-online-schema-change. You never want to globally disable binary logs. Dec 10 '20 at 21:24
0

The short answer is, NO, it is not safe, and not recommended.

I understand these logs are not being used for replication, but for data recover. These are useful to do "point in time" recovery. However, you have to have a valid previous full backup in order for the binlogs to be useful.

My recommendations:

  1. Make a full backup [Using mysqldump or any other way] to your database(s)
  2. Flush logs [and mark the time]
  3. Copy the binlogs files to a safe place [back them up]
  4. Purge the binary logs before the marked time
  5. Run your optimize statements. During this time, binlogs will still be written into, but the size will be minimal

HTH

0

When deleting a lot of rows of a large table, it is much more efficient to do

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT ... (the rows to keep);  -- the slow part
RENAME TABLE real TO old,
             new TO real;    -- fast, atomic
DROP TABLE old;

If the original table were built with innodb_file_per_table=ON, then the free space will be given back to the OS by this process; no need for OPTIMIZE.

The INSERT..SELECT is much faster (per row) than DELETE.

If you are deleting "old" data and will need to do the purge again, consider partitioning.

More details on these and other deletion techniques: http://mysql.rjweb.org/doc.php/deletebig

The space in the binlog --

  • DELETE with binlog_format = ROW: one entry per row deleted.
  • DELETE with STATEMENT: one entry for the entire statement.
  • INSERT: one entry per row.
  • OPTIMIZE: (I think) one entry for the entire statement.

That is, the DELETE is what is bloating the binlog. The OPTIMIZE is locking the table for a long time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.