Hot answers tagged

11

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 ...


6

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 ...


5

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 : ...


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

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 ...


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 ...


3

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 ...


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 ...


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

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


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

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

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


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

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

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

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 ...


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

Guess what ? You have a nasty little bug (Replication stall with multi-threaded replication) This bug was first reported in MySQL 5.6.17 on 20 Jun 2014 15:45 SUGGESTION #1 You need to look over all the release notes from MySQL 5.6.18 up to MySQL 5.6.22 to see if it was resolved. If any of those release notes claim the problem was fixed, then someone ...


1

Please keep in mind what MySQL Documentation says on sync_binlog: If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after sync_binlog commit groups are written to the binary log. The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server ...


1

You must understand what the binary logs are: they store all the queries (in STATEMENT format) or row changes (in ROW format) that happened since they were recorded. So, your command: mysqlbinlog -d dbname --start-position=107 mysql-bin.000{727..755} | mysql dbname basically executes all insert, updates & deletes that occurred to the server during ...


1

The semantics of those isolation modes cannot be guaranteed in SBR since the Slave is unlikely to execute the statements of multiple threads in the same order. More specifically, from the Change Log: ----- 2008-11-14 5.1.30 General Availability -- Bugs Fixed -- ----- With statement-based binary logging format and a transaction isolation level of READ ...


1

I have developed software with similar functionality, the ability to use the MySQL replication stream (binary log, binlog) to capture events in near-real-time in response to inserts/updates/deletes in the database. Here are some observations I've made regarding performance. Luckily, the potential hot spots are largely independent of each other. I will ...


1

I have seen your problem before Problem #1 You have /etc/mysql/conf.d/mycustom.cnf and the log-bin line is not being read Problem #2 Chances are /etc/mysql/conf.d/my.cnf exists and has the following line [mysqld] log-bin=OFF That's why the binlogs start with a filename of OFF Some feel log-bin=OFF disables binlogging. The MySQL Documentation on ...


1

When it comes to an empty binary log, it is version specific: 120 in MySQL 5.6 107 in MySQL 5.5 106 in MySQL 5.1 98 in MySQL 4.1/5.0 I have discussed this many times in the DBA StackExchange Nov 19, 2014 : Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB Jun 26, 2013 : Is it possible to convert mysql binlog from statement format to row ...


1

The Error Message you received Last_SQL_Error: Could not execute Delete_rows event on table test.pruning; Can’t find record in ‘pruning’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000002, end_log_pos 1448 This message can come up when doing a DELETE query with row-based replication. Unfortunately, it is one ...


1

When you have binlog_format=MIXED some statements will appear as actual SQL. Other statements may get obfuscated like this: ### INSERT INTO `sanma`.`response` ### SET ### @1=743096223 ### @2=0 ### @3='IR' ### @4='UnderProcess' ### @5=2015-05-29 20:51:34 ### @6=743053329 ### @7=NULL ### @8=NULL ### @9='758484829' ### @10=1432912893 ### ...


1

When binary logs do not reach the limit set by max_binlog_size, there are two(2) major reasons why mysqld closes the currently open binlog and opens a new binlog Running service mysql restart Running service mysql top followed by service mysql start mysqld crashes and mysqld_safe restarts it if the mysqld crash was not severe Running FLUSH LOGS will do ...



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