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 space - Purge is not making more space. Any idea what to do to clear relay logs and Bin file .

I have set up a master/slave with mysql-5.1.73 The master's binlog format is "statement". The master and slave seemed running very well with slave status: Slave_IO_Running: Yes Slave_SQL_Running: Yes And when I modified the content on the master manually, whether it was select or update or insert, or alter table, the modification will be synchronized to the slave instantly. However, after running for several days, I found **the slave missed a lot of insert statements**, these insertion statements didn't violate any PRIMARY key rule. More ever, when I tried to redo the binlog on another slave with: mysqlbinlog mysql-binlog.00000X | mysql Those missed statement were missed again with no warning or error. **Have you ever met such a situation, what should I do to restore all the changes to the slave?** (There are quite a lot of missed changes, so I could not restore them one by one). I dug into this matter to find that, the relay log on the slave contains all the insertion statement, which means the binlog is transmitted to the slave correctly. However, the binlog on the slave missed some of the insertion statement, so this issue **appeared to occurred during the redo process of the slave**. Any suggestions to diagnose into this issue or work around it?

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 at slave (show slave status \G;): > Last_Error: Table definition on master and slave does not match: Column 18 type mismatch - received type 19, rtmariadb10.empdetails > has type 11 Here Master: Mariadb 10.1,binlog_format: row ; Slave : Mysql 5.1, binlog_format=statement/row/mixed(any one of these) Can someone please help to solve this issue?

Is there any way to do this? Specify a different directory for currently active binary log. (It would nice if possible, because then we could store past binary logs in a compressed file system without compromising write performance of the current log.)

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 that? I have in my new server already a database but is kinda old, could this be possible. Thanks in advance

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.

This is a question that I've not been able to find any good documentation for on the internet. I think it's a common industry standard that for tables with heavy writes, you add an index on the read replica and not on the master. This way, you're ensuring the writes on master don't slow down, while also ensuring the reads on read replica are faster. But doesn't the read replica also have writes, in the form of binlog replication? If I'm not wrong, the replication is simply a form of copy-pasting the commands from the binlog as-is. But the index on the read replica would still have to be updated, right? Since the binlog file won't have any "index updates" commands (because the master doesn't even have the index), I'm assuming that the "index update" on the read replica happens when an INSERT or an UPDATE commands comes from the binlog. So, is it still not a "WRITE" operation that happens? Will it still not slow down the write latency? I understand that not all DBs have this capability of indexing a read replica separately, so this question is restricted to only those DBs that do, such as MySQL.

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 disable binary logging on such installation, comment out the line in my.cnf works of course, but I wonder if there is a way to disable binary logging by setting explicitely log_bin to OFF, in a debian style, I mean in an included file like /etc/mysql/conf.d/myCustomFile.cnf, so default my.cnf is not changed and can be easily updated by apt, if necessary. I tried "log_bin = 0", "log_bin = OFF" or "log_bin =" but none works...

I am using Kafka and Debezium to connect to a MariaDB I have a problem where in the event the MariaDB loses power and shutsdown abruptly, on restart Debezium is no longer able to connect I have this error in particular

[2023-12-06 13:26:05,800] ERROR [192.168.108.1|task-0] Error during binlog processing. Last offset stored = null, binlog reader near position = mariadb-bin.000006/53938 (io.debezium.connector.mysql.MySqlStreamingChangeEventSource:1161)

io.debezium.DebeziumException: Client requested master to start replication from impossible position; the first event 'mariadb-bin.000006' at 53938, the last event read from 'mariadb-bin.000006' at 4, the last byte read from 'mariadb-bin.000006' at 4. Error code: 1236; SQLSTATE: HY000.

And on the mariadb side i have this

+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       330 |
| mariadb-bin.000002 |       330 |
| mariadb-bin.000003 |     67494 |
| mariadb-bin.000004 |     69007 |
| mariadb-bin.000005 |    126922 |
| mariadb-bin.000006 |     45056 |
| mariadb-bin.000007 |    103304 |
+--------------------+-----------+
7 rows in set (0.001 sec)

MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000007 |   103304 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

I have tried resetting offsets on Kafka side of things but I get the exact same error without fail, does anyone know why exactly there is a problem and how to resolve things? All help appreciated thanks so much!

I want to stop replicating system database like mysql using binlog-ignore-db and replicate-ignore-db, the settings are applied: **master:**

mysql> SHOW BINARY LOG STATUS\G;
*************************** 1. row ***************************
             File: binlog.000001
         Position: 1251
     Binlog_Do_DB: 
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 990ca1d0-beab-11ef-aa36-ba9eeabae344:1-4
1 row in set (0.01 sec)

**slave:**

mysql> SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 127.0.0.1
                  Source_User: replica
                  Source_Port: 3301
                Connect_Retry: 60
              Source_Log_File: binlog.000001
          Read_Source_Log_Pos: 1251
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 1462
        Relay_Source_Log_File: binlog.000001
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 1251
              Relay_Log_Space: 1679
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 1
                  Source_UUID: 990ca1d0-beab-11ef-aa36-ba9eeabae344
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 990ca1d0-beab-11ef-aa36-ba9eeabae344:1-4
            Executed_Gtid_Set: 990ca1d0-beab-11ef-aa36-ba9eeabae344:1-4,
99293d0e-beab-11ef-9b97-00b58ea694f2:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 1
            Network_Namespace: 
1 row in set (0.00 sec)

Meanwhile,

-wild-ignore-table=mysql.%

works, why?

I'm using Debezium mySQL connector to get CDC data from a 3-node xtraDB database. Its worked for other databases in the past, but for some reason, the connector to the current database is unable to acquire global read locks. The fall back option of using TABLE LOCKS if acquiring GLOBAL READ LOCK fails does not work given the way xtraDB is set up, so if the connector fails to acquire GLOBAL READ LOCK, it fails entirely. I want to re-produce this error in a dev environment but I can't figure out how to force a FLUSH TABLES WITH READ LOCK to fail. Anybody got any ideas on what cause FLUSH TABLES WITH READ LOCK to fail (locks not getting acquired) in a mySQL database and how I may reproduce it? I tried starting a transaction on one node to UPDATE a particular column value of a row in a table but not committing the transaction so it remains open with a row lock. I then started another session using the same DB user connecting to the same DB node and tried FLUSH TABLES WITH READ LOCK. I was hoping that would not succeed as I thought the UPDATE would still be having a lock which would block the FLUSH TABLES WITH READ LOCK statement, but it did not.

Some of the MySQL clusters I manage have the following my.cnf settings:

log_bin   = mysql-bin.log
relay_log = mysql-relay-bin.log

For the log_bin setting the .log extension is not needed because MySQL ignores and removes the extension from the file name. According to the binary-log documentation: > If you supply an extension in the log name (for example, --log-bin=base_name.extension), the extension is silently removed and ignored. I noticed that the relay_log setting behaves the same with extensions. MySQL also ignores and removes the extension but it seems this behaviour is (currently) not present in the replication-options documentation. When showing these variables in MySQL there is also a small difference. The log_bin value shows ON (instead of the file name) and the relay_log value shows the actual file name:

mysql> SHOW GLOBAL VARIABLES LIKE 'log_bin%';

| log_bin             | ON
| log_bin_basename    | /var/lib/mysql/mysql-bin

mysql> SHOW GLOBAL VARIABLES LIKE 'relay_log%';

| relay_log           | mysql-relay-bin.log
| relay_log_basename  | /var/lib/mysql/mysql-relay-bin

My questions are: 1. Why are the log_bin and relay_log values different (ON vs File Name)? 2. Will relay_log always behave the same as log_bin with extensions (is this by design)? If this is by design then the .log extensions could be removed in my.cnf for both settings:

log_bin   = mysql-bin
relay_log = mysql-relay-bin

I'm learning about MariaDB GTID replication and one thing I am completely stuck on is why GTIDs have both a domain ID and a server ID, which make up the (domain_id)-(server_id)-(sequence_number) in the GTID, such as 0-1-12345. The docs are very thorough, but don't address this question directly. I've learned as a result of this wonderful video that the sequence number increments for each transaction, but *is tied to the domain_id*. This doesn't seem to be stated anywhere in the GTID docs. So, if you have two servers with server_id 1 and 2, but the same domain_id (say, 1), the GTIDs would indeed be delineated due to the unique server_id, but they would share the same sequence number. e.g. Alternating transactions on server 1 followed by server 2 would result in the following: 1-1-1 1-2-2 1-1-3 1-2-4 They're still unique, so I have to figure they work just fine for replication. I'd love to be corrected if that is wrong though. Anyway, if you set the domain IDs to 1 and 2, this would be the result: 1-1-1 2-2-1 1-1-2 2-2-2 But it is still not clear to me *why* or *how* you'd want to have a unique sequence per server. If two master servers had the same server ID and domain ID, then there would be a conflict. But the question is why would you ever want to have two servers with the same server ID, such that you need to have a unique domain id per server? Or perhaps there is there a tool and use-case for extracting a specific domain stream out of the shared binlog, which perhaps can't be done just by specifying the server_id?

I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those files like mysql-bin.000123, mysql-bin.000223 etc. I have checked the mysql replication by doing show master status and show slave status they are using some mysql-bin files at certain positions, but I guess all the other bin files are **leftovers** which will not be used anymore. In this case is it safe to delete all those mysql-bin files except the ones that replication is currently pointing at ? If it is safe to delete, then is there anything I could do to **automatically** delete those files once they are not in use ?

## MariaDB Config ## version:

> mysql -V
mysql  Ver 15.1 Distrib 10.4.27-MariaDB, for Win64 (AMD64), source revision 0946c99e7d6f7ac9dfcf3e60dae6ae85161d5ef2

my.ini:

[mysqld]
log-bin=bin.log
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=row
socket=mysql.sock

binlog_row_image=full (as default)
## MariaDB Tables ## I have tested more tables, but I will only include 2 of them.

CREATE TABLE use_all_in_one_btree (
  primary_key_1 int(11) NOT NULL,
  primary_key_2 varchar(128) NOT NULL,
  unique_key_1 int(11) NOT NULL,
  unique_key_2 varchar(128) NOT NULL,
  index_key_1 int(11) NOT NULL,
  index_key_2 varchar(128) NOT NULL,
  fulltext_key_1 text NOT NULL,
  fulltext_key_2 varchar(128) NOT NULL,
  PRIMARY KEY (primary_key_1,primary_key_2),
  UNIQUE KEY unique_key_1 (unique_key_1,unique_key_2),
  KEY index_key_1 (index_key_1,index_key_2),
  FULLTEXT KEY fulltext_key_1 (fulltext_key_1,fulltext_key_2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

CREATE TABLE use_all_in_one_hash (
  primary_key_1 int(11) NOT NULL,
  primary_key_2 varchar(128) NOT NULL,
  unique_key_1 text NOT NULL,
  unique_key_2 varchar(128) NOT NULL,
  unique_key_3 varchar(255) NOT NULL,
  fulltext_key_1 text NOT NULL,
  fulltext_key_2 varchar(128) NOT NULL,
  PRIMARY KEY (primary_key_1,primary_key_2),
  UNIQUE KEY unique_key_1 (unique_key_1,unique_key_2) USING HASH,
  UNIQUE KEY unique_key_3 (unique_key_3) USING HASH,
  FULLTEXT KEY fulltext_key_1 (fulltext_key_1,fulltext_key_2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

## Problem ## In reality I'm reading "TABLE_MAP" event that takes place before any of row modification events, but the columns are same for both. Link to MariaDB TABLE_MAP_EVENT docs **I have edited question 16.11.2023** When I "read" changes to these tables (insert/write_row) in binlog, for example using _"mysqlbinlog"_ tool. **Table "use_all_in_one_btree" will have 8 columns**, which corresponds to the number of its physical columns.

But when it comes to **table "use_all_in_one_hash" it has 9 columns**, while it only has 7 physical columns. I presume it's caused by HASH index, both of those columns has a type 8 (LONGLONG) in binlog. tried googling and reading many pages of mysql/mariadb docs but wasn't able to find any information about it. ## Question ## How do you tell if INDEX will be part of binlog, is it every HASH index or can it happen for any other index types too? (Wasn't able to test spatial).

I read recently about a setting in Percona Server called max_binlog_files which restricts the total number of binlog files. This is exactly what I need. My binlog_expire_logs_seconds is currently set to 3 days, which works great 99% of the time. However, I'm currently engaged in project which runs a massive amount of queries and the binlogs grew to consume over 70GB in just a few hours. In this case, I really need to limit the number of files. Is there any native setting with MySQL to achieve this? What is the best way to restrict total disk space consumed by binlogs?

I'm using Mariabackup and I'd like to know if there is an option to allow me to rotate the binary log when running mariabackup. I can't seem to find anything in the documentation.

I was reading about how to create a primary-replica system and I want to do it in production. But based on this link , I realized that binlog thread could fail and data would be lost. I didn't find anything useful about the topic, but is there anyway to find out if any data is lost? What can I do to prevent this?

I keep getting this error whenever I start MySQL. 121028 1:38:55 [Note] Plugin 'FEDERATED' is disabled. 121028 1:38:55 InnoDB: The InnoDB memory heap is disabled 121028 1:38:55 InnoDB: Mutexes and rw_locks use Windows interlocked functions 121028 1:38:56 InnoDB: Compressed tables use zlib 1.2.3 121028 1:38:56 InnoDB: Initializing buffer pool, size = 16.0M 121028 1:38:56 InnoDB: Completed initialization of buffer pool 121028 1:38:56 InnoDB: highest supported file format is Barracuda. 121028 1:38:57 InnoDB: Waiting for the background threads to start 121028 1:38:58 InnoDB: 1.1.8 started; log sequence number 3137114 121028 1:38:58 [ERROR] Binlog has bad magic number; It's not a binary log file that can be used by this version of MySQL 121028 1:38:58 [ERROR] Can't init tc log 121028 1:38:58 [ERROR] Aborting 121028 1:38:58 InnoDB: Starting shutdown... 121028 1:38:58 InnoDB: Shutdown completed; log sequence number 3137114 121028 1:38:58 [Note] C:\PROGRA~2\EASYPH~1.1\MySql\bin\mysqld.exe: Shutdown complete I have already tried [this](https://stackoverflow.com/questions/10848622/mysql-not-starting-in-wmp) . I have an EasyPHP 12.1 setup on Windows 7x64 PC.

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 **max_binlog_size = 1073741824** which is equivalent to 1GB. This means, once a binary log reaches the size of 1GB, a new file should be created. I observed in my server that all the binary log files are of different sizes with lot of contrast like 200GB, 165GB, 4GB etc... Why does this happen when the maximum size is 1GB for a binary log file? Is it necessary to mention even the default maximum size explicitly in the configuration file?