Hot answers tagged

12

It will be handled immediately on mysql startup. You do not have to wait for a mysql restart. First, set expire_logs_days to be 10 in /etc/my.cnf [mysqld] log-bin server-id=1 binlog-do-db=foodb1 binlog-do-db=foodb2 expire_logs_days=10 Next, log in to mysql and run this PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 10 day); Note ...


8

The PURGE BINARY LOGS statement deletes all the binary log files listed in the log index file prior to the specified log file name or timestamp. Deleted log files also are removed from the list recorded in the index file, so that the given log file becomes the first in the list. I hope you have purged binary logs upto mysql-bin.000019 using command PURGE ...


7

There is no mysql option to ignore a table in binary log. If you could move the table to a separate database you might use binlog-ignore-db option, but this has its gotchas. You should understand how MySQL evaluates replication rules. If you can write a wrapper script to load the data instead of mysqlimport (the manpage says that it's a command line ...


6

Binary logs have different start positions upon the creation of a new binary log. In fact, a binary log position actual represents the numbers of bytes written in the binary at the end of the most recent SQL statement recorded. The following is the list of binary log starting positions for each major release of MySQL 5 For MySQL 5.5 : 120 For MySQL 5.5 : ...


5

This is an old question, but it came up in a search while I was trying to jog my memory about the correct option name and so now that I've figured it out I'm adding the details here. The key part of the question is: I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf You can do this from an included option file using the option skip-log-...


4

I'm not sure if this is what happened to you but in my case MySQL had stopped "cycling" logs and the mysql-bin.index file had become "corrupted" with invalid binlog file entries. Specifically, the index file had started at mysql-bin.000001 and got to mysql-bin.000220 but then had somehow started again from 001. When I compared this to the files on my server ...


3

The reason for this is pretty straightforward: if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes the whole transaction to the binary log and then commits this transaction into InnoDB http://dev.mysql.com/doc/refman/5.5/en/binary-log.html In periods of moderate to low traffic, it's easy for the "last thing that ...


2

DISCLAIMER : Not a user of mha4mysql. SUGGESTION #1 Hang the Lag Slave off from the Standby Server. How? Step 01: Stop all Writes to the Master Step 02: Run SHOW MASTER STATUS; on the StandByMaster Step 03: Run mysqlbinlog against the binlog from Step 02 Step 04: Acquire timestamp from given position Step 05: Subtract 43200 sec (12 hrs) from the ...


2

you can use --replicate-ignore-table=db_name.tbl_name See http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replicate-ignore-table for details.


2

Since the binary log on the Master is corrupt, there is nothing else you can do. My condolences. Just skip to the next binlog: STOP SLAVE; CHANGE MASTER TO master_log_file='ibm-pr-slglcd-01.000076',master_log_pos=107; START SLAVE; Of course, 107 is the start position for MySQL 5.5 binlogs. If the Master is 5.1 (or 5.0.95), use 106. If the Master is prior ...


2

4. I have no CRON which do some task auto magically I'll speculate that you actually do. Linux systems often use the logrotate utility to manage rotation of log files from numerous disparate subsystems. Distro builders love to coerce software installed from packages to Do Things The One True Way™ so it should not surprise you if they have "extended" MySQL ...


2

You can see it like this, log_bin should not accept parameters, either you put: log_bin or nothing at all, as it is a boolean parameter. However, if a value is set, logs are enabled and the value interpreted as the binlog basename. Log options have a history of not being too intuitive, and changing its format from version to version (I am looking at ...


2

I do not understand why an application requires the MIXED replication format, as MIXED is equal to STATEMENT-based, but changing to ROW format when executing "unsafe" queries. So, a MIXED-compatible application should be compatible with both STATEMENT and ROW. As you can see here, if the the format detected (server-side) is ROW or MIXED, it tries to change ...


2

If you want to erase all binary logs older than 30 minutes, do this mysql> PURGE BINARY LOGS BEFORE NOW() - INTERVAL 30 MINUTE; If you want to erase all binary logs except the last one, do this mysql> PURGE BINARY LOGS BEFORE NOW(); If you want to erase all binary logs before midnight, do this mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) + ...


2

Rotate logs: mysql> FLUSH LOGS; [Re]move the old binary logs, all except the latest it's safe to remove.


2

I am glad you found your own answer. I am sure you found out you can add this to my.cnf [mysqld] expire_logs_days = 14 You do not have to restart mysql. You login as root@localhost and run this: mysql> SET GLOBAL expire_logs_days = 14; To trigger the auto rotate immediately, run mysql> FLUSH LOGS; If you want to zap binary logs from the mysql ...


2

This works for me: (MySQL server version: 5.6.14) PURGE BINARY LOGS BEFORE NOW(); Removes all binary logs on my system.


2

Remove this clause: --database=securities


2

For splitting a single physical drive into two partitions, I see only the big downside (as you pointed out): You could run out of space on one partition, while having space left on the other. (Many times I have seen this.) For multiple drives, I recommend striping as being better than manually segregating this file/log/table/etc from that file/etc. In ...


1

Please look at the MySQL Documentation again A record of metadata operations such as those just described are written to the file ddl_log.log, in the MySQL data directory. This is a binary file; it is not intended to be human-readable, and you should not attempt to modify it in any way. ddl_log.log is not created until it is actually needed for ...


1

You may need to try something a little unorthodox service mysql stop Open mysql-bin.index in vi Change the contents to this ./mysql-bin.000313 ./mysql-bin.000314 ./mysql-bin.000315 Save mysql-bin.index service mysql start Login to MySQL and run SHOW BINARY LOGS; SHOW MASTER STATUS; This stunt worked a few times for me. Give it a Try !!!


1

PROBLEM MySQL needs to be very explicit when flushing to disk in Windows. You have caught on to this when you did STOP SLAVE; and START SLAVE; You said you are using sync-binlog = 1. That's great. That only addresses half the log file issues. SOLUTION (or at least SUGGESTION) You need to synchronize all other replication components [mysqld] sync-binlog =...


1

I think you should re-initialize your replication. If the replication worked good, the rows should not be missing. That the rows are missing means there is some inconsistency between your master und your slave. I would not act on the assumption that this is the only error and if I'd fix it by hand everything is back fine again. Instead I would set up the ...


1

To set mysql selinux on a directory you can: semanage fcontext -a -t mysqld_db_t "/yourdirectory(/.*)?" then restorecon -Rv /yourdirectory Also check the mysql user has permissions on your directory: ls -l Set permissions if needed: chown -R mysql:mysql /yourdirectory


1

Don't use --short-form. You are suppressing much of what you want to see. --short-form Display only the statements contained in the log, without any extra information or row-based events. This is for testing only, and should not be used in production systems. — http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#...


1

These are interesting questions with answers that are not too easy to come by. The internal structures are sometimes not very well documented or the documentation is difficult to find and more than just a little bit frustrating. Part 1: table_id The table_id in a row-based replication TABLE_MAP_EVENT is not a value ordinarily exposed to the user, though ...


1

--base64-output=DECODE-ROWS --verbose This is the format you need to be using if you are reviewing the binlog manually... it extracts the maximum amount of useful data from the log... but it will help if you understand how binary logging works in MySQL, first. RDS (at least for MySQL 5.5 and 5.6) uses binlog_format = MIXED. This means the server may ...


1

The only possible work around is mentioned in the MySQL Documentation You can tell mysqlbinlog to suppress the BINLOG statements for row events by using the --base64-output=DECODE-ROWS option. This is similar to --base64-output=NEVER but does not exit with an error if a row event is found. The combination of --base64-output=DECODE-ROWS and --verbose ...


1

You have chain replication as Master--> SLAVE A--> SLAVE B (Slave A is Slave of Master and Slave B is slave of Slave A) As you told On Slave Server(A) binary log is enabled. You additionally need to enable log_slave_updates because a slave does not log to its own binary log any updates that are received from a master server. This option tells the ...


1

There are four(4) things to look for to see if a binlog is corrupt on not: Binlog Magic Number Binlog Size mysqlbinlog MySQL Replication BINLOG MAGIC NUMBER Back on Dec 26, 2011, I wrote about a based-64 number that sits at the top of any binlog (hex value 0xfe 0x62 0x69 0x6e) regardless of MySQL version and regardless of binlog format (STATEMENT,ROW,...



Only top voted, non community-wiki answers of a minimum length are eligible