Questions tagged [binlog]
The binlog tag has no usage guidance.
123
questions
0
votes
1
answer
5k
views
Target log not found in binlog index, from purge binary logs mysqld-relay-bin
I am trying to run the following.
mysql > purge binary logs to 'mysqld-relay-bin.000075';
ERROR 1373 (HY000): Target log not found in binlog index
# cat ./mysqld-relay-bin.index
/srv/mysql/logs/...
- 727
1
vote
2
answers
1k
views
Is it safe to copy the binlogs from a running mysql server
I want to backup a tokudb database.
I want to be able to restore it to the most recent state.
Which I am writing to a file at backup.
My guess would be to run something like this
mysql -e "SHOW ...
- 727
2
votes
1
answer
619
views
sync_binlog different for master and slave
Is it fine if sync_binlog is different for master and slave?
For my system I can see master has sync_binlog=1 and slave has sync_binlog=0.
- 121
6
votes
2
answers
23k
views
MySQL - Binary Log File size growing more than the maximum limit
I have a MySQL 5.7 running on a Centos 6. I enabled Binary Logging and didn't provide any custom value for the maximum size of Binary Log in my configuration file. By default, the parameter ...
- 163
1
vote
2
answers
3k
views
PURGE BINARY LOGS taking time
We are observing high latency for some queries, digging further we found that every 5 minutes the queries take long time by examining the slow query logs.
We observed that
PURGE BINARY LOGS TO '...
- 195
0
votes
1
answer
153
views
How could I use binary log as relay log?
I has a full backup of MySQL, and binary logs from the time of starting full backup.Can I restore the data in another instance use replication like only start slave sql_thread; rather than mysql < ...
- 71
0
votes
1
answer
449
views
Purge old binlogs in mysql 4
I need to purge binary logs from mysql version 4. I have checked the manual found that the syntax is like an advanced versions:
PURGE MASTER LOGS TO mysql-bin.xxx
However, when i use this command, i ...
- 353
0
votes
1
answer
231
views
recovery with binlogs?
I would like to know if there is a way that I can restore my databases, I don't have a replication and I would like to migrate my databases without downtime or losing data.
So with binlogs could I do ...
2
votes
2
answers
536
views
Race condition in mysql / percona innodb binlog for master -> slave
I'm having a really hard time diagnosing and working around a race condition that is causing problems on my slave and knocking it offline due to a foreign key relationship. We're using MIXED format ...
- 506
4
votes
2
answers
2k
views
Is it safe to disable sync_binlogs, innodb_flush_log_at_trx_commit and innodb_doublewrite on a site-redundant galera cluster?
On a site-redundant galera cluster, a commit should only return after a quorum of database nodes have accepted the transaction. If one database node goes down, all commits will be preserved by the ...
- 211
0
votes
2
answers
2k
views
Mysql binlog filename keeps changing, preventing replication
First off, some versions: Centos 6, mysql 5.1 are what I'm stuck with using for now.
So - The problem is that when I replicate using either of these methods:
A: From the manual, unavailable via ...
0
votes
2
answers
1k
views
Replication error from Mariadb 10.1 to Mysql 5.1/5.0/5/5 when master's logging format is set to row based
While replicating from Mariadb 10.1 to MySQL (5.0, 5.1, 5.5) or Mariadb (5.2, 5.5) lower versions, if master's binlog_format is set to row, the replication failure occurs with the following message ...
6
votes
3
answers
6k
views
MySQL replication slave hangs after encountering SET @@SESSION.GTID_NEXT= 'ANONYMOUS';
I recently installed two identical default installations of MySQL 5.7 under Ubuntu Server 16.04, and configured them to do a binary log replication. Until now this has been working fine, but suddenly ...
- 163
4
votes
1
answer
1k
views
MySQL replication: purge_logs giving error
On a MySQL replication instance I have this error message:
[ERROR] MYSQL_BIN_LOG :: purge_logs Was called Expired with file / db-space-no-connected-cpa / DBMS / dxnc6sla / arch / not listed in the ...
- 41
2
votes
1
answer
1k
views
Add query executed user information to Mysql binlog
We are using MYSQL as our production database and we have setup another salve for read requests. Time to time various database users can execute insert/update queries on master database. If we want ...
1
vote
1
answer
22k
views
replicate-ignore-table setup and usage for excluding table from binlog backup
I need to make an everyday backup of a mysql database that is very big on a remote server and I only want to get the difference since the last backup. I also need to exclude some tables from being ...
- 13
3
votes
1
answer
925
views
How can you close or flush a binlog file on a remote server
I am replicating my binlog files to a secondary server using the mysqlbinlog --stop-never --read-from-remote-server flags. If the primary database server is compromised I want to replay the binlogs ...
- 153
6
votes
3
answers
2k
views
In MySQL, how exactly does data flows from query to disk?
I would like to have clarity on how exactly data flows from the the time of firing a query till it goes to disk. (I have rough idea it will go to buffers, then to redo logs then disk files) but then ...
- 71
1
vote
1
answer
92
views
Contradictory reports from MySQL that a binlog file exists
Our replication recently stopped working. I submitted a question but was unsuccessful in fixing the problem (Error 1236 - "Could not find first log file name in binary log index file"). ...
- 437
1
vote
2
answers
1k
views
Partial mysqlbinlog on latest binlog file
I'm trying to do a partial mysqlbinlog on the latest binary log file at a certain position therein (trying to extract just one query).
mysqlbinlog --start-position=1234 --stop-position=1234 /var/lib/...
- 113
1
vote
1
answer
414
views
Changing tx_isolation and binlog format on master server - do i also need to adjust slave?
We are running a Drupal Commerce site with MariaDB 10.0 and master/slave replication. Because of the way our application works, we end up running in to a ton of deadlocks under heavy load. Apparently ...
- 121
4
votes
2
answers
19k
views
Mysql FLUSH LOGS in live server with replication
I need to run the slow query and unindexed query logging for some time on a live production db with replication. Changing GLOBAL const value sounds like a good solution. This article also provides ...
- 229
0
votes
1
answer
670
views
Mysql datadir and binlogs: same partition or not?
On a MySQL cluster, does it make sense to store MySQL database files and binlogs in different partitions?
(This is about partitions on the same physical disk. If they were on different physical disks,...
- 1,314
2
votes
1
answer
2k
views
mixing binlog formats between master and slave
I have a pretty much straightforward Master --> Slave replication setup in MySQL 5.6 / Innodb.
Both my Master and Slave are setup in MIXED binlog format.
Now I need my slave to act as a master to ...
- 25
2
votes
1
answer
1k
views
MySQL binlog does not show SQL statements
While checking the binlogs, it struck me that I cannot see any SQL statement. Here's an example:
$> sudo mysqlbinlog
--database=securities
--start-datetime='2015-11-26 13:00:00'
--...
- 236
2
votes
2
answers
1k
views
Suddenly a massive amount of binlog files
I've seen a sudden increase of binlog files, very large in number and size, in all three nodes of a Percona XtraDB cluster:
-rw-rw---- 1 mysql mysql 1,1G 19 oct. 16:11 binlog-32.000001
-rw-rw---- ...
- 1,314
0
votes
1
answer
751
views
mysqlbinlog: unknown variable ssl_key or ssl_cert or ssl_ca
I am trying to recover binlog from a remote server using mysqlbinlog client program, however it appears that mysqlbinlog doesn't recognize the SSL options that's required to establish SSL connect to ...
- 101
1
vote
1
answer
1k
views
mysql max_binlog_size is not working
I have a weird problem. I am currently using mysql and have defined 100M of max_binlog_size. However, I have hundreds of files created every day and each file is of no more then 2MB or 3MB each and ...
- 367
2
votes
2
answers
10k
views
How can I parse mysql bin log file for statements (Row/Mixed Format)
I have a mysql database running with binary log enabled. Unfortunately, a statement was executed by the client, which turned out erroneous. Since a number of records have been "affected", I want to ...
- 275
0
votes
1
answer
1k
views
Unable to show binary logs how to remove logs from mysql?
Server version: 5.1.50-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show binary logs ;
PROBLEM
Data directory is full
I want to remove ...
5
votes
1
answer
2k
views
MySQL v5.1.73 - can the binlog_format betweem Master & slave be different?
We are in the process of testing MySQL replication before using it in production. The MySQL is being setup by Puppet in our case and I noticed that the binlog_format setting is different between ...
- 51
2
votes
2
answers
2k
views
MySQL binary logging with just one log file?
I have a MySQL database running with binary logging turned on. Each time the database is restarted a new binary log file is created in the format :
mysql-bin.00001
mysql-bin.00002
...
mysql-bin.0000n
...
user68337
1
vote
1
answer
922
views
How to determine if a mysql binary log file is empty?
In order to determine whether a database backup is needed or not, I'm trying to see whether a mysql binlog file has any data in it. (During a backup, we make a fresh binlog file and record the ...
- 125
1
vote
1
answer
205
views
MySQL: Incorrect binary log format - OFF.000
I have a Percona MySQL 5.5.30 as a master(Debian 6.0.7). I got a weird issue that all my binary logs started saving in format OFF.00* instead of the specified format in .cnf file.
mysql> show ...
- 123
0
votes
1
answer
414
views
Why binlog capture only postions
Please help me out, why master server records only position not statement.
### INSERT INTO `sanma`.`response`
### SET
### @1=743096223
### @2=0
### @3='IR'
### @4='UnderProcess'
### @5=2015-...
- 29
1
vote
1
answer
1k
views
MySQL binlog limitations
I have a MySQL instance running on AWS, with around 5000 inserts per second. Any idea what the performance impact will be if I use binlog (row) and a binlog tailer?
Check this link
From my ...
- 13
2
votes
1
answer
818
views
MySQL: Why statement-based binlog format cannot work with Innodb READ UNCOMMITTED or READ COMMITTED Isolation levels?
In MySQL documentation it's written:
If you are using InnoDB tables and the transaction isolation level is READ COMMITTED or READ UNCOMMITTED, only row-based logging can be used.
Why statement-...
- 121
1
vote
2
answers
2k
views
Why is restoration from binary logs so slow?
We have a MySQL 5.5 server with binary logging enabled and a backup script that collects the binary logs and, once a day, generates a complete database dump with mysqldump. In testing database ...
- 141
0
votes
1
answer
887
views
MySQL Crash After Starting Binary Logs
Recently I have added binary logs to mysql to start replication but after that setting, mysql process failed to start and keep crashing constantly. In mysql error logs some messages are there but not ...
- 133
2
votes
1
answer
7k
views
Hi disk I/O and crash when turning on sync_binlog in MySQL
I have a MySQL master server (MariaDB 10.0.17) that have a very odd behavior. It has one slave fetching data from it.
When I set global sync_binlog = 1, the server I/O goes very high and after some ...
- 165
2
votes
2
answers
4k
views
MySQL 5.6 Slave Stops Processing Binlog on Master Restart
I have a pair of Debian 7 servers with MySQL 5.6.23. The slave server is a clone of the master (but the uuid has been regenerated on the slave).
For some reason, whenever MySQL is restarted on the ...
1
vote
1
answer
1k
views
what will happen to the binary log if it reaches its maximum value ? Does it reset to mysql-bin.00001 again?
what will happen to the binary log if it reaches its maximum value ? Does it reset to mysql-bin.00001 again ?
I know the below points exist means, there won't be any reset happen in binlogs.
I would ...
- 59
1
vote
1
answer
7k
views
How to track only DDL statements executed in MySQL
Can we track only executed DDL statements in MySQL but not DML statements. I dont want to use either binlog/general log to enable. I just wanted to track only DDL statements.
I had gone through the ...
- 1,007
3
votes
1
answer
7k
views
Purge mysql log bins older than two weeks automatically
I have to manually write
purge binary logs bin to "mysql-bin.somenumbers" ;
Once in a while, I need to reduce the spaces that binlogs are taking on my web server. Is it possible to purge binary ...
- 185
1
vote
2
answers
9k
views
Mysql binlogs exists, show binary logs is empty
I am working on a replication setup and have turned on bin-logs. I have moved my mysql datadir to a new location as well as the binlog path
From my.cnf
datadir = /vol/data/mysql
log-bin=/vol/data/...
- 113
3
votes
1
answer
2k
views
Binlog not consistent between slave and master
Initially I don't see binlog on slave is updating. Today I discovered and add log-slave-updates option to slave, now it is updating. However, the filename does not match between slave and master.
...
- 133
72
votes
6
answers
218k
views
Disable MySQL binary logging with log_bin variable
Default MySQL config file /etc/mysql/my.cnf installed by some debian package using APT often set log_bin variable, so binlog are enabled:
log_bin = /var/log/mysql/mysql-bin.log
When I want to ...
- 2,368
5
votes
2
answers
4k
views
pt-table-checksum and row-based replication
The set up I work with is MySQL 5.5 in a master-slave replication configuration, but we have to use "MIXED" replication (statement and row-based) because we use an application that requires it, and it ...
- 281
5
votes
2
answers
5k
views
MySQL : 30GB of binlogs in 6 Hours
I have a mysql master/master setup with tungsten replicator. My server is reaching out of space, occupied around 30G in 6 hours. I'm not sure how to handle this. Would be great if some one can help me ...
- 247
3
votes
2
answers
7k
views
MySQL Master-Master Replication not syncing
SETUP:
I have a WAMP MySQL Master-Master replication setup on 2 machines with sync_binlog=1 for safety.
Machine config: Windows Azure Standard 2: 2 core, 4GB RAM, Windows Server 2008, MySQL 5.6.12, ...
- 51