set --server-id to enable either a master or a slave
Below issue can be faced, when we run CHANGE MASTER command to
setup replication.
mysql> change master to
master_host='xx.xx.xx.xx',master_user='rep_user',master_password="xxxxxxx",
master_log_file='db-01-test-bin.000064',master_log_pos=95854072,master_auto_position=0;
ERROR 1794 (HY000): Slave is not configured or failed to
initialize properly. You must at least set --server-id to enable
either a master or a slave. Additional error messages can be
found in the MySQL error log.
Solution :- Here It is assumed, We have already set server-id
parameter value different on master and slave node but still, we
have the above error.
It happens once, you performed the server packages upgrade(like
from 5.6 to 5.7) and just started MySQL service and going to run
CHANGE MASTER command.
So, to solve this issue, Run mysql_upgrade command to …
PORP LAB : ProxySQL/Orchestrator/Replication/PMM Summary PORP Lab will create 4 different nodes. Each node will have below packages/applications/db installed.
app -- Percona Server 5.7 -- Percona Toolkit -- Percona XtraBackup -- Sysbench -- ProxySQL -- Orchestrator -- PMM mysql1 / mysql2 / mysql3 -- Percona Server 5.7 -- Percona Toolkit -- pmm-client -- Replication
PORP LAB have ProxySQL,Orchestrator and PMM properly configured,
we can just create this lab and use it.
Install VirtualBox
Version 5.2.22 or Later
Install Vagrant
Version 2.2.2 or Later
Install Vagrant plugin hostmanager
vagrant plugin install vagrant-hostmanager
Update Vagrant Plugin
vagrant plugin update
Clone the repo
git clone …[Read more]
Master has purged binary logs containing GTIDs that the slave
requires Problem : Last_IO_Error: Got fatal error 1236 from
master when reading data from binary log: 'The slave is
connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but
the master has purged binary logs containing GTIDs that the slave
requires.'
Solution : stop slave;
Query OK, 0 rows affected (0.00 sec)
CHANGE MASTER TO MASTER_AUTO_POSITION = 0;
Query OK, 0 rows affected (0.01 sec)
start slave;
Query OK, 0 rows affected (0.00 sec)
At the end of the month, some engineers of the MySQL Team will be present in Austin, TX !
We will attend the first edition of Percona Live USA in Texas.
During that show, you will have the chance to meet key engineers, product managers, as well as Dave and myself.
Let me present you the Team that will be present during the conference:
The week will start with the MySQL InnoDB Cluster full day tutorial by Kenny and myself. This tutorial is a full hands-on tutorial where we will start by migrating a classical asynchronous master-replicas topology to a new MySQL InnoDB Cluster. We will then experience …
[Read more]Overview The Skinny
From time to time we are asked how to check whether or not there are data discrepancies between Master/Slave nodes within a MySQL (or MariaDB) cluster that’s managed with Tungsten Clustering. This is always a challenging task, not least because we hope and believe that our replication mechanism would avoid such occurrences, that said there can be factors outside of our control that can appear to “corrupt” data – such as inadvertent execution of DML against a slave using a root level user account.
Tungsten Replicator, the core replication component in our Tungsten Clustering solution for MySQL (& MariaDB), is just that, a replicator – it takes transactions from the binary logs and replicates them around. The replicator isn’t a data synchronisation tool in that respect, the …
[Read more]
GTID Replication (Skip Transaction using empty transaction)
To skip SQL thread's error in GTID
replication setup, Insert empty transaction. PROBLEM :
db-test (none)> show slave status\G
*************************** 1. row
***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xxxxx
Master_User: rep_user
Master_Port: 3306
…
MySQL 8.0.16 has been released last Thursday. In it, you can find some new replication features. Here is a quick summary. Follow-up blog posts will provide details about these features.
- Large Messages Fragmentation Layer for Group Replication. Tiago Vale’s work, introduces message fragmentation to the Group Communication Framework.
… Tweet Google Plus Share
So over the years, we all learn more and more about what we like
and use often in MySQL.
Currently, I step in and out of a robust about of different
systems. I love it being able to see how different companies use
MySQL. I also see several aspect and settings that often
get missed. So here are a few things I think should always be set
and they not impact your MySQL database.
At a high level:
- >Move the Slow log to a table
- Set report_host_name
- Set master & slaves to use tables
- Turn off log_queries_not_using_indexes until needed
- Side note -- USE ALGORITHM=INPLACE
- Side note -- USE mysql_config_editor
- Side note -- USE mysql_upgrade --upgrade-system-tables
Move the Slow log to a …
Just about a month ago, Pavel Ivanov released Ripple under the Apache-2.0 license. Ripple is a MySQL binlog server: software which receives binary logs from MySQL or MariaDB servers and delivers them to another MySQL or MariaDB server. Practically ,this is an intermediary master which does not store any data, except the binary logs themselves, and does not apply events. This solution allows saving of a lot of resources on the server, which acts only as a middle-man between the master and its actual slave(s).
The intermediary server, keeping binary logs only and not doing any other job, is a prevalent use case which allows us to remove IO (binlog read) and network (binlog retrieval via network) load from the actual …
[Read more]Recently, we had an edge case where a MySQL slave went out-of-sync but it couldn’t be rebuilt from scratch. The slave was acting as a master server to some applications and it had data was being written to it. It was a design error, and this is not recommended, but it happened. So how do you synchronize the data in this circumstance? This blog post describes the steps taken to recover from this situation. The tools used to recover the slave were pt-slave-restart, pt-table-checksum, pt-table-sync and mysqldiff.
Scenario
To illustrate this …
[Read more]