Questions tagged [replication]
Replication is the process of sharing any level of information so as to ensure consistency between redundant hardware/software resources to improve reliability, fault-tolerance, and accessibility
3,521
questions
150
votes
4
answers
252k
views
Is it safe to delete mysql-bin files?
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 ...
user18530
83
votes
7
answers
194k
views
How do I completely disable MySQL replication
I am running dual master MySQL replication and now want to move to a single database with no replication. How do I completely disable replication on both databases?
David Collie
39
votes
4
answers
97k
views
MySQL relay log corrupted, how do I fix it? Tried but failed
A MySQL v5.1.61 relay got corrupted when the machine suddenly shut down. I tried to fix it but it didn't work.
— How do I fix it? Did I do something wrong?
As far as I've read, corrupted MySQL relay ...
- 1,239
39
votes
3
answers
15k
views
MySQL 5.1.66 SSL connection error ERROR 2026 (HY000)
UPDATE2
Using WireShark I found out the problem string (I hope I did):
28 | 9.582638 | 192.168.18.128 | 192.168.18.129 | MySQL Response Error 1043
And the error is (according to docs):
Error: 1043 ...
- 440
30
votes
2
answers
53k
views
What is the safest way to switch the binlog format at runtime?
Because of the following warning in mysqld.log:
[Warning] Unsafe statement written to the binary log using statement
format since BINLOG_FORMAT = STATEMENT. The statement is unsafe
because it ...
- 1,006
28
votes
3
answers
67k
views
Should a MySQL replication slave be set to read only?
I've got replication running on Percona Server 5.5 by following this guide and wondered if I should add read-only=1 to my slave's my.cnf to make it read only?
The guide sets up replication for the ...
- 759
28
votes
5
answers
56k
views
Add article to transactional publication without generating new snapshot
Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I'd like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours).
From this ...
- 381
27
votes
1
answer
2k
views
Splitting Snapshot files with MaxBCPThreads for Transactional Replication
I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm ...
- 271
25
votes
3
answers
19k
views
Running pg_dump on a hot standby server?
Disclaimer: I admittedly haven't tried this yet, but I'm not sure I would know if it wasn't working correctly, so I wanted to ask.
I would like to run a nightly backup job (via pg_dumpall) from a hot ...
- 471
23
votes
3
answers
50k
views
Postgresql doesn't accept replication connection
Plain old streaming replication.
PostgreSQL: 9.2.7
Windows 8.1 64 bit
My primary and secondary clusters are on same windows machine. I have already done pg_start_backup() and everything, so both ...
- 809
23
votes
3
answers
49k
views
How to determine master in mysql master-slave
I am setting up MySQL Master-slave replication and I am trying to figure out how to handle the failover situation where I promote the slave to master (in the event that the master goes down).
My ...
- 365
23
votes
1
answer
43k
views
How do I query the running pg_hba configuration?
I want to test if a replication connection is authorized by pg_hba.conf on the provider before issuing the replication-starting command, and don't know how. (I have access to both unix and postgresql ...
- 333
22
votes
1
answer
41k
views
How soon after updating expire_logs_days param and restarting sql will old binlogs get deleted?
MySQL 5.1.x | InnoDB | Windows
My mysql data directory is starting to fill up with bin logs.
I currently have the following settings configured in my windows mysql server:
[mysqld]
log-bin
server-...
- 617
21
votes
5
answers
53k
views
Using Postgresql logical replication, how do you know that the subscriber is caught up?
Postgresql has some interesting monitoring tools for monitoring the new logical replication system's progress, but I don't really understand them. The two tools I'm aware of are:
pg_stat_replication
...
- 603
21
votes
3
answers
66k
views
What max_allowed_packet is big enough, and why do I need to change it?
I have MySQL (5.5) in master-slave setup and created another slave server.
I stopped the original slave, dumped the data, copied and reimported and it worked fine. I noted the master_log pos of the ...
- 471
21
votes
2
answers
25k
views
How to backup large MongoDB database
What is the recommended way to back up large data sets in MongoDB? Let's say we have a data size in the order of 10TB - how would you back that up?
We're considering a hidden, possibly delayed, ...
- 313
21
votes
3
answers
12k
views
PostgreSQL: Can I do pg_start_backup() on a live, running db under load?
Our established replication has broken ("requested WAL segment has already been removed" during downtime)
We cannot easily stop the master again.
Can we do
pg_start_backup(),
rsync ${PGDATA}/ ...
- 313
21
votes
1
answer
24k
views
In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Several Questions
Would the Slave entry remain until there is an insert/update/delete to the table on the Master that could affect the Slave?
If not, how do I ensure that the Slave is in-sync with ...
- 849
21
votes
3
answers
9k
views
SQL Server database synchronization
Problem definition
Our users need the ability to query a database that is mostly up to date. The data can be stale up to 24 hours and that is acceptable. What would be the lowest cost approach to ...
- 16k
20
votes
4
answers
52k
views
MySql - Changing expire_logs_days without restarting the server
I'm using MySql 5.5.
Is it possible to change expire_logs_days and have the changes take effect without restarting the server?
- 1,563
20
votes
5
answers
8k
views
Infrastructure for Highly Concurrent, High Write DB
My requirements are:
3000 Connections
70-85% Write vs Read
Currently, we are maxing out a High-CPU, Extra Large Instance at 700 connections. All 8 cores are maxed. We think it's the number of ...
- 301
19
votes
2
answers
25k
views
Difference between row-based and statement-based replication in MySQL?
What is the actual difference between row-based and statement-based replication. I am actually looking in terms of replication's effect on the slave.
If I am using row-based replication then what is ...
- 9,717
18
votes
7
answers
28k
views
Transaction Log won't shrink, DB thinks it is replicating
I've got a SQL Server 2008 R2 Express database running Kaspersky Security Center, and I have no idea under what circumstances the install happened, but the database appears to think that it's being ...
- 306
18
votes
2
answers
3k
views
What can we do in MySQL 5.0 Replication to address bandwidth concerns?
I am developing an application to run on the client PC (Win) which is configured with a MySQL server 5.1 instance that will act as read-only slave to the remote master. The remote master has dozens of ...
- 283
17
votes
3
answers
19k
views
ALTER TABLE on a large table with an indexed column
I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about ...
- 1,342
17
votes
3
answers
5k
views
Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving?
Context:
Let's say that, while using Streaming Replication/Hot Standby on a Postgres 9.1 cluster, a standby node goes down. It stays down for an day, during which time a lot of DML occurs on the ...
- 526
16
votes
1
answer
4k
views
Is PostgreSQL replication production ready?
How does PostgreSQL native replication compare to MySQL?
I know asynchronous replication has been supported for longer than sync, which is recent. Is synchronous reliable to be used in real projects?
- 263
16
votes
2
answers
33k
views
Mongo DB Replica set Stuck at RECOVERING state
We have created a replica set and now the problem is 2 members of replica set [3 members set] are in recovering mode from 48 hours. Initially the size of recovering nodes was increasing and now even ...
- 161
16
votes
3
answers
9k
views
Replicate RDS-MySQL to a non-amazon host?
I have a fairly large MySQL (5.1) database running in Amazon as an RDS instance. I'm working on migrating it out of Amazon. To do that smoothly I'd like to set up the new hardware as a read-only ...
- 825
15
votes
3
answers
43k
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 *************...
- 979
15
votes
2
answers
10k
views
What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?
What are the drawbacks of using Galera Cluster instead of regular Master/Slave Replication? Galera's 0 slave lag time, Synchronous replication and no Single point of failure seem very appealing then ...
- 334
15
votes
1
answer
13k
views
Replicate tables to different database name
Our QA environment has all it's database names suffixed with "test". For example dbname1 in production would have a counterpart dbname1test in QA. (This is largely to help protect against prod/qa ...
- 5,273
15
votes
1
answer
9k
views
SQL Server Many-to-One replication
I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data.
I would like to establish a reporting ...
- 245
15
votes
4
answers
8k
views
Migrate heroku database to Amazon RDS with minimum downtime
I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then ...
Byron Singh
15
votes
1
answer
6k
views
PostgreSQL failover and replication
I'm evaluating PostgreSQL 9.1 and have few questions related to failover and replication details.
I have few test scenarios. First one with a Master server and few Slaves. In case Master crashes I ...
- 253
14
votes
4
answers
130k
views
Error 1236 - "Could not find first log file name in binary log index file"
Our setup:
Master: MariaDB 10.0.21
Slave: MariaDB 10.0.17
Replication was working fine until recently at which point the slave's DBs had to be restored from a dump. I performed all of the necessary ...
- 437
14
votes
2
answers
27k
views
What is the Best Way to create MySQL Master-Slave Replication Setup and Troubleshoot it?
I am very new to Database Administration.
I face a lot of problems while setting up mysql master-slave replication.
I also face regular mysql replication troubleshooting issues.
Can anybody helps ...
- 9,717
14
votes
2
answers
5k
views
Streaming replication and failover on PostgreSQL
I am doing a proof of concept on PostgreSQL replication. After the discussion on forum we decided to go with streaming replication as the performance is good compared to other solutions. PostgreSQL is ...
- 141
13
votes
4
answers
51k
views
How to change a MySQL previous slave to be a master and remove slave status information?
I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine.
What I can't seem to do is to remove ...
- 232
13
votes
3
answers
26k
views
Replication Monitor Information using T-SQL
The image below shows a transaction replication problem that I am currently investigating.
The image comes from the Replication Monitor.
How can I get this information using T-SQL?
- 16.4k
13
votes
5
answers
5k
views
Is it unwise to run replication on same physical server?
I'm contemplating setting up a Master-Slave replication for my database. The slave server will be used for redundancy and possibly a reports server. However, one of the biggest issues I'm running into ...
- 23.5k
13
votes
2
answers
13k
views
Is binary logs required on the slave in order to do replication?
I have a MySQL database setup with a master and a slave. As far as I understand, the master writes transaction events to a binary log, which is read and executed by the slave, thus making the slave a ...
- 253
13
votes
1
answer
22k
views
Deleting bin logs in replication environment
I have a question about deleting binary logs in the replication environment:
We have an environment with 1 master and 2 slaves (running mysql 5.5). Sometimes, we run into a space issues during heavy ...
- 151
13
votes
3
answers
5k
views
Snapshot Replication Retention
I've got Snapshot replication set up on my SQL Server 2008 production server, and I just noticed that there are snapshots ranging back to a year ago in the snapshot folder. How can I change the ...
- 3,058
13
votes
1
answer
5k
views
Does SQL Server Snapshot replication completely copy the data every time or does it issue deltas?
I am looking at snapshot replication between two servers. Here is what I have:
500GB database
~500MB nightly bcp loads
~50MB daily transactions
I was asking other DBAs in the company about which ...
- 316
13
votes
2
answers
16k
views
Rebooting a MySQL Replication Slave
For the first time since it was set up I need to reboot a read-only MySQL replication slave.
I found this article about downing a slave for maintenance (albeit he's just describing stopping the mysql ...
- 1,336
12
votes
3
answers
34k
views
MySQL replication - slave is continuously lagging behind master
I am using MySQL-5.1.50 with a Master-slave replication setup.
Most of the time the slave is lagging behind the master.
When I run show processlist;, there is no query that's taking a long time. ...
- 121
12
votes
2
answers
36k
views
MySQL replication: 'Duplicated entry for PRIMARY key'
Could you please help me to understand why I'm receiving 'Duplicated entry for PRIMARY key' on a slave server after a full re-sync.
Basically 'mysqldump' was running almost whole night and then the ...
- 459
12
votes
1
answer
30k
views
Mysql replication is enabled but stuck at "waiting for binlog to be updated"
I have set up replication between an existing database (server-id=23), and a new one (server-id=51).
I have followed the steps in this stackoverflow question, but replication is not happening.
On ...
- 1,222
12
votes
1
answer
7k
views
Upgrade SQL Server 2005 to SQL Server 2012
I have a test environment set up with the following:
Virtual Machine (Hyper-V)
Windows Server 2008 R2 SP1(x64-bit)
Windows SQL Server 2005 Developer Edition SP4 (x64-bit)(default instance name)
1 ...
- 1,126