Questions tagged [master-slave-replication]
The master-slave-replication tag has no usage guidance.
270
questions
15
votes
3
answers
38k
views
Trying to understand SHOW SLAVE STATUS in MySQL
I have a Master-Slave replication setup and it looks like that it is running fine. Below is a result of SHOW SLAVE STATUS command:
show slave STATUS\G;
*************************** 1. row *************...
- 959
6
votes
1
answer
3k
views
Queries running very slowly in Amazon RDS PostgreSQL read-replica
We have been maintaining a project which has both web and mobile application platform. The backend of the project is developed in Django 1.10 and deployed in AWS.
At the beginning, when there were ...
5
votes
3
answers
5k
views
MySQL Master/Slave replication on same VM instance?
I'm fairly new to databases so I hope this isn't a silly question!
Is it possible to set up master/slave replication on the SAME server?
I understand there are not any advantages to this in terms of ...
- 113
4
votes
3
answers
2k
views
In replication A->B->C, can A be binlog_format=STATEMENT and B be binlog_format=ROW?
I have replication setup between an on-prem MySQL 5.6 server (A) to another on-prem MySQL 5.6 server (B). I was setting up an AWS DMS instance (C) that I wanted to migrate and then replicate. It ...
- 143
4
votes
2
answers
6k
views
MySQL slave not able to execute long running update query
I am running a Master-slave replication servers of MySQL. As part of clean up of old data, i ran a delete query which deletes huge number of records from the database. It ran fine on Master server but ...
- 241
4
votes
3
answers
30k
views
PostgreSQL service start failed on CentOS 7
Installed PostgreSQL using:
sudo yum install postgresql-server postgresql-contrib
PostgreSQL version: 9.2.18
When start service:
sudo service postgresql start
Got error:
Redirecting to /bin/...
- 303
4
votes
1
answer
850
views
Why does mysqldump stop the slave SQL thread for the entire backup?
I realized recently that I was using --master-data to dump a backup from a replication slave when what I really wanted was --dump-slave. After switching to --dump-slave, I see that mysqldump will stop ...
4
votes
0
answers
233
views
MySQL replication - Follower stuck and behind
I understand there are lots of similar problems and threads but non of those solution helped me. Please read through the thread I will try to give as much information as possible:
I have setup a ...
- 141
4
votes
0
answers
624
views
How to get the Slave commit timestamp using MySql replication?
I've been looking for ways to collect information about the replication delays on MySql 8. I found an alternative solution, but I imagine there's a more straightforward way to do that.
By reading its ...
- 41
4
votes
1
answer
716
views
Can PostgreSQL slave server act as master for a new database?
My question is somewhat related to:
https://stackoverflow.com/questions/19331577/does-postgres-replication-native-support-per-database-level-replication
We have master-slave replication set up on ...
- 145
4
votes
0
answers
7k
views
Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave
I am trying to setup multi source replication. In my slave I have defined following two properties in its configuration file.
master-info-repository=table
relay-log-info-repository=table
These ...
- 441
3
votes
2
answers
2k
views
How to avoid replication lag in case all writes on master and reads on replica?
I have stuck with the replication lag problem. I am trying to refactor my DB infrastructure.
The first step was read-write split. I used ProxySQL то implement it. Now I have masterDb where all INSERT ...
3
votes
2
answers
7k
views
Change host IP of master in mysql replication
I have one master and one slave. Suppose IP of master is changed but the physical server is same. Now I want to update the new IP (of the master) at the slave server. At the slave server ...
- 81
3
votes
1
answer
2k
views
MySQL 8.016 master with MariaDB 10.2 slave on AWS RDS, Character set '#255' is not a compiled character set
I have a MySQL 8.016 master and a MariaDB 10.2 slave, both running on AWS RDS. This was working perfectly until today, when I figured I wanted to do chain replication instead, like this
MySQL 8.016 ->...
- 135
3
votes
2
answers
6k
views
org.postgresql.util.PSQLException: ERROR: canceling statement due to conflict with recovery
I currently have a PostgreSQL slave node that is presenting problems, that don't arise in the master node. Seems to be related with the node sync process.
Complete stacktrace:
org.postgresql.util....
- 949
3
votes
1
answer
6k
views
Service mongod fails after adding security.keyFile in config file
My mongo.conf:
# where to write logging data.
systemLog:
destination: file
logAppend: true
path: /var/log/mongodb/mongod.log
# Where and how to store data.
storage:
dbPath: /var/lib/mongo
...
- 213
3
votes
2
answers
274
views
MySQL chain replication timing of writes on first slave
With a MySQL chain replication setup, e.g. M1->S1->S2, what exactly is the order of operations for writing to the first slave? Is the binary log from the master simply streamed directly through ...
- 133
3
votes
1
answer
3k
views
MySQL Replication binary relay logs transfer too slowly
Master server
MySQL 5.6.28
Ubuntu 14.04
8 core CPU
Slave server
Slave: MySQL 5.7.22
Ubuntu 18.04
16 core CPU
Both of them have 64GB of RAM and plenty of disk space.
What I did up to this ...
- 171
3
votes
1
answer
3k
views
mariadb Unknown system variable 'server_uuid'
Im trying to setup replication between mariadb(10.1.31-MariaDB - MASTER) and mysql(5.7.12-log - SLAVE). After executed the start slave, I got the below error.
Fatal error: The slave I/O thread ...
- 1,860
3
votes
1
answer
2k
views
How does one scale out with master-slave replication?
To my understanding, a master-slave replication might be set up where clients can read from multiple databases. However, they can only write to the master. I believe this helps guarantee ACIDity.
I ...
- 261
3
votes
0
answers
1k
views
Got fatal error 1236 from master when reading data from binary log
I am trying to do a MySQL failover, in some situations I endup with broken replication. Any idea what could be the cause of it ?
Background
Needed to perform a host updates on master host. Hence ...
- 49
3
votes
0
answers
780
views
RESET MASTER; does not reset GTID_EXECUTED
I'm trying to get replication up and running between two kubernetes pods, and am getting the dreaded @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. error.
root@central-vm:~...
- 569
3
votes
1
answer
414
views
Can I stop replication of create/drop index statement on one slave of Mysql 5.6?
I have this cluster where master and slave uses same indexes but one of the slaves has some extra indexes (large machine which runs some analytics queries).
So I have to maintain two sets of indexes. ...
3
votes
0
answers
539
views
Replication doesn't resume once it fails to connect
We have MySQL replication running for our application. Right now the slave fails to resume if it fails to connect to the server for some time.
We get the error below:
[ERROR] Slave I/O: error ...
- 89
3
votes
2
answers
64
views
How do I create a MySQL slave server of a bunch of MyISAM databases on a master
I have a MySQL master server and slave became corrupt because of space issues on the master. Binary logs are potentially corrupt and I don't trust them. The databases are using MyISAM tables.
I want ...
- 253
2
votes
2
answers
3k
views
Post Mortem Analysis: PostgreSQL replication failed
We have a PostgreSQL 9.4.9 production server which was replicating to a slave instance, but today I discovered that the instance is out of sync!
The obvious actions would be to recreate the slave, ...
- 949
2
votes
1
answer
1k
views
Can we replicate or restrict specific databases in MongoDB
In MySQL, we are having replicate-do-db/replicate-ignore-db during replication, where we can replicate or restrict specific databases from Master to Slave.
In the similar way, is there any specific ...
- 997
2
votes
3
answers
3k
views
SELECT from slave and INSERT into master in MySQL
I'm using PHP/Laravel and MySQL databases.
Briefly, I want to use slave databases for reads and master database for writes.
What Laravel config, Laravel package, or database tool do you suggest to ...
- 141
2
votes
1
answer
1k
views
Postgresql - increase WAL retention to avoid slave go out of sync with master
I've setup a master / slave Postgresql (13.4) group, 1 master and 1 slave, using WAL warm standby.
(refer: https://postgresql.org/docs/current/warm-standby.html#STANDBY-SERVER-SETUP)
The slave gone ...
- 223
2
votes
1
answer
5k
views
MySQL Replication Slave Status OK but Data inconsistent
We are using MySQL 5.7, and have a Master and 3 Slave servers replication setup. All the 4 servers are dedicated for MySQL use only, and belong to the same data-center, connected over Private Network. ...
- 412
2
votes
1
answer
346
views
Fix mysql replication crashed due to underlying storage corruption
we have critical database server running on two virtual machines. one virtual machine crashed due to underlying storage problem and we had to move it to different storage medium and recover the xfs ...
- 123
2
votes
1
answer
4k
views
Is it possible to configure pgbouncer to distribute queries among master and slave?
We have master (write-read) and slave (readonly) Postgres 10 databases. Slave is online replication from master.
Now our application (c# webapi) connects to master only directly. We want to execute ...
- 121
2
votes
1
answer
87
views
Options for having same tables in 2 different data bases
I need to know what options I have for my next problem:
I have 2 databases (sql server 2012)
first for an ERP
second for an website
For the website's database, I want to use a table (customer) ...
- 41
2
votes
1
answer
203
views
Restart master in physical replication
I have 2 servers involving master-slave replication (physical replication).
I see the replication using select * from pg_replication_slots; there is a record saying :
user : rep_user`
application : ...
- 147
2
votes
3
answers
2k
views
What does this error mean "A slave with the same server_uuid/server_id as this slave has connected to the master;"?
Upon searching for this, the main cause of this error is usually when using snapshots or cloning the machine. But I did not do this. I perform mysqldump --master-data=2 on the master then restore the ...
- 109
2
votes
2
answers
790
views
What is the maximum latency / distance mysql replication can tolerate without having errors or wrong data?
I have two machines in a master - slave replication. I want to know if they are in two seperate places e.g. London, England and Tokyo, Japan are there going to be any latency issues.
What exactly is ...
- 21
2
votes
1
answer
5k
views
Can I use MariaDB as a replication slave for MySQL?
I've been a MySQL user/administrator for many years and I've been considering a switch to MariaDB. We have a statement-replicated setup with a MySQL slave and I've been thinking that one way to ease-...
2
votes
3
answers
1k
views
Seconds_Behind_Master high even though Read_Master_Log_Pos and Exec_Master_Log_Pos are very close
From what I understand from https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html, seconds_behind_master is the difference in the timestamp of where the IO thread is currently writing in the ...
- 31
2
votes
3
answers
245
views
Why is it not a good idea to use MariaDB's extra_port for replication?
MariaDB has an extra_port for administrative access that helps in cases the thread pool or main port is locked up.
MariaDB's documentation says it's use case is primarily administrative access and it ...
- 121
2
votes
1
answer
1k
views
Can a MySQL 5.6 master replicate to a 5.7 slave, which in turn replicates to a MySQL 8 slave?
According to the docs, "MySQL supports replication from one release series to the next higher release series. For example, you can replicate from a master running MySQL 5.6 to a slave running MySQL 5....
- 203
2
votes
1
answer
459
views
Master mysq8 slave Mariadb 10.3 Error 22
Master (mysql Ver 8.0.16)
Slave (mysql Ver 15.1 Distrib 10.3.15-MariaDB)
I have this error
Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/share/...
- 135
2
votes
2
answers
790
views
Issue with Time Zone while migrating MySQL on-premises DB to AWS RDS
I am having MySQL 5.6 database with PST time format in on-premises server. Now we are willing to migrate the database to RDS.
For this, we have taken backup from physical server and restored in RDS ...
- 997
2
votes
1
answer
1k
views
Master-slave replication doesn't work on PostgreSQL 9.6
I tried to create a master/slave configuration with two servers and Postgres 9.6
I modified master and slave postgresql.conf in /etc/postgresql/9.6/main
Same for master and slave:
data_directory = '...
- 23
2
votes
1
answer
841
views
Why has deadlock behaviour changed between MariaDB 10.1.22 and 10.2.14?
Since upgrading MariaDB 10.1.22 to 10.2.14 our MariaDB slaves are encountering deadlocks that are not handled in less than 600 seconds thus the classic semaphore decision to crash the server. The ...
- 301
2
votes
1
answer
4k
views
Mysql replication broken due to 'Duplicate entry' with error code 1062 on slave server
Mysql Slave replication was working fine, but suddenly it has crashed due to duplicate entries error
The slave SQL thread stops due to duplicate entries in MySQL DB, which I have kept in ...
- 21
2
votes
1
answer
2k
views
how can be changed hot_standby parameter on postgres 9.6 aws/rds?
I have a postgres solution on aws/rds with a master and a read replica.
master slave
hot_standby on on
transaction_read_only on on
...
- 21
2
votes
1
answer
743
views
Fetch just inserted data in master/slave model?
Say I have MySQL master DB(used for updates) and corresponding Slave DB(used for reads). Now user inserts the data into MASTER through application(java), just after insertion he tries to retrieve the ...
- 165
2
votes
1
answer
1k
views
PostgreSQL replication not working after base backup
Last week we had an incident with our master PostgreSQL server instance which got totally thrashed so, we had to switch to our slave as our one and only DB instance. After the initial chaos was ...
- 949
2
votes
1
answer
94
views
Setup proxysql read-write split with both galera cluster and master slave replication
I have the following setup:
Three node galera cluster (galera-1, galera-2, galera-3)
galera-2 is master to slave-1 (master-slave replication)
slave-1 is master to both slave-2 and slave-3
slave-1, ...
- 121
2
votes
0
answers
93
views
Issues with failback for logical replication using postgres
I am using PostgreSQL 13 to set up logical replication. When the original active node (A) becomes the secondary, the prior secondary(B) which is now active node needs to sync to the (A) node.
To ...
- 121