Questions tagged [mysql-replication]

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves).

Filter by
Sorted by
Tagged with
99 votes
11 answers
152k views

How can I export the privileges from MySQL and then import to a new server?

I know how to export/import the databases using mysqldump & that's fine but how do I get the privileges into the new server. For extra points, there are a couple of existing databases on the new ...
Gareth's user avatar
  • 8,583
20 votes
5 answers
31k views

Can a MySQL slave be a master at the same time?

I am in the process of migrating 2 DB servers (Master & Slave) to two new DB Servers (Master and Slave) DB1 - Master (production) DB2 - Slave (production) DB3 - New Master DB4 - New Slave ...
mmattax's user avatar
  • 1,314
17 votes
4 answers
23k views

MySQL Master-Master Replication of ALL databases. How?

Background: I have two MySQL 5.1 servers setup in Master-Master row-based-replication (RBR) following this excellent guide. I want all databases replicated, and will be adding new databases regularly....
Andrew Ensley's user avatar
15 votes
5 answers
21k views

Can I run mysqld on top of glusterfs?

I have been playing with glusterfs recently. What I want to try is to run mysqld on top of the glusterfs in a similar way as it is possible to run MySQL on top of DRBD. I am familiar with MySQL ...
Richard Holloway's user avatar
15 votes
6 answers
64k views

What causes the MySQL error 1062 - duplicate entry when starting slave?

MySQL Master version: 5.5.16-1 MySQL Slave version: 5.5.18-1 The master's snapshot is created by: mysql> FLUSH TABLES WITH READ LOCK; shell> mysqldump --all-databases --master-data > ...
quanta's user avatar
  • 51.8k
15 votes
2 answers
6k views

Is it ok to replicate the `mysql` database?

When I originally set up my master-to-master replication, I used: binlog-ignore-db=mysql and have been syncing the user accounts and grants manually. This is simply how it was done in the how-to I ...
dlo's user avatar
  • 471
14 votes
5 answers
11k views

Prevent non-replication writes to MySQL slave?

We have some MySQL database servers set up with row-based replication, for performance. The software writes to the master, and reads from either the master or the slave. Everything's working great, ...
user avatar
14 votes
1 answer
35k views

Slave_SQL_Running: No: MySQL replication stopped working

Replication on my slave database (MySQL 5.7.12) stopped working suddenly. When I run SHOW SLAVE STATUS\G I see the following error: Last_Errno: 1396 Last_Error: Error 'Operation CREATE USER failed ...
tptcat's user avatar
  • 257
14 votes
3 answers
7k views

How to setup MySQL replication with minimal downtime

Basically we have a large MySQL database and we're looking to do replication to a slave (master slave setup). Do you guys have any step-by-step guide on how to do this with MINIMAL downtime on the ...
Patrick's user avatar
  • 455
12 votes
2 answers
30k views

Restart mysql replication after sql_error

I have two mysql servers, one master and one slave. Someone went on to the slave and created a table, then subsequently went on to the master and created the same table. Of course this DDL statement ...
ʞɔıu's user avatar
  • 279
12 votes
4 answers
48k views

Slave replication stops with Last_SQL_Errno: 1032

I have added an extra Slave server to an existing MySQL Replication. The Master server and the old Slave server are working fine without any issue, but the newly added server is stoping with the ...
adminz's user avatar
  • 397
11 votes
3 answers
2k views

MySQL replication across geographically separate servers

My organisation has been looking into how to spread our servers around geographically while keeping backups very up to date, and ideally spreading the load. The initial thing I have in mind is Rails ...
Hamish Downer's user avatar
11 votes
1 answer
23k views

max_binlog_size & log-bin size

I have a problem with full disk -rw-rw---- 1 mysql mysql 1073741982 2012-07-03 18:14 mysql-bin.000034 -rw-rw---- 1 mysql mysql 1073741890 2012-07-04 14:39 mysql-bin.000035 -rw-rw---- 1 mysql mysql ...
jmp's user avatar
  • 207
9 votes
1 answer
21k views

Which binlog format to use for MySQL Replication?

Just about to start using MySQL replication and I'm wondering what kind of binlog format is the best to use? There's Row, Statement and Mixed. I'm going to be using 5.1.49. Row is winning at the ...
delenda's user avatar
  • 91
8 votes
3 answers
38k views

How to sync two MySQL tables (on demand or via cron)

I have seen Cron sync mysql tables . But I cannot use replication. I used to use percona-toolkit and it worked perfectly. I could run the sync command on demand or just run it via cron. It would ...
Anirudh's user avatar
  • 233
8 votes
1 answer
4k views

Recovery strategy for Master-Master replication

I have implemented a HA solution for mysql based on master-master replication. There is a mechanism on the front end part which guarantees that only one db will be read/written to at a given time (i.e....
David Cournapeau's user avatar
7 votes
9 answers
2k views

Monitor MySQL Replication

What is the best practice for monitoring a slave to make sure that it is a) Still running b) Not too far being the master I would like to alert by email if it is behind, happy to write a script or ...
johnwards's user avatar
  • 785
7 votes
1 answer
5k views

MySQL Slave stuck on a single bin log + bin log position for 17+ hours

tl;dr: Replication is stalled on a specific binlog and position, and I'm not sure why I have a MySQL Replication setup with MySQL 5.5. This replication setup has no history of ever falling behind ...
Jim Rubenstein's user avatar
7 votes
3 answers
16k views

Improving MySql replication speed

i'm looking for pointers on how to improve the replication speed of a mysql slave. It is fast enough for normal production but needs a long time to catch up if it falls behind for some reason. (If the ...
edorian's user avatar
  • 143
7 votes
1 answer
14k views

MySQL: Pacemaker cannot start the failed master as a new slave?

pacemaker-1.0.12-1 corosync-1.2.7-1.1 I'm going to setup failover for MySQL replication (1 master and 1 slave) follow this guide: https://github.com/jayjanssen/Percona-Pacemaker-Resource-Agents/blob/...
quanta's user avatar
  • 51.8k
6 votes
4 answers
4k views

Testing MySQL Database Replication / Synchronization

I'm setting up a MySQL Master database which replicates to a few Slave databases. My question is what are the best practices to monitor and / or test that the slave databases are up-to-date, and that ...
pjama's user avatar
  • 163
6 votes
2 answers
1k views

Can I put the MySQL binlogs on a slow disk?

We have only 450 GB nvme space on our master server and the binlogs are using a lot of space, even if they are kept for only two days. Does writing the MySQL binlogs to a slower disk (like a remote ...
the_nuts's user avatar
  • 450
6 votes
2 answers
8k views

MySQL slave replication reset with no Master Downtime (using MyISAM)

Root of the issue: In all instructions for creating a slave on a running master requires flush tables with read lock. We use MyISAM, so we can't just use --single transaction to get consistent table ...
Jonathan Hendler's user avatar
6 votes
2 answers
7k views

How do I add additional databases to my mysql replication slave (read-only)?

I have a mysql read-only replication slave setup and currently working for about 10 databases and I need to add 3 new databases. I cannot find any information on how to add additional information. ...
Greg Arcara's user avatar
6 votes
3 answers
2k views

Creating a *Consistent* *Online* MySQL Backup with InnoDB AND MyISAM tables

I just spent hours building a new database server to replace 2 failed servers from a mysqldump file created a month ago, then used my server's bin-logs to bring that mysqldump up to date. Now that I ...
Jim Rubenstein's user avatar
6 votes
1 answer
5k views

Recover a crashed MySQL master server from the slave

we are building a simple master/slave MySQL configuration using asynchronous replication, with MySQL enterprise 5.5.17 on both servers and innoDB based tables. In case of a crash of the master server ...
LeonardoC's user avatar
6 votes
6 answers
1k views

How can I centralise MySQL data between 3 or more geographically separate servers?

To explain the background to the question: We have a home-grown PHP application (for running online language-learning courses) running on a Linux server and using MySQL on localhost for saving user ...
Andy Castles's user avatar
6 votes
2 answers
3k views

MySQL replication issues after a power outage

After a power outage at our data centre, the slave MySQL databases are struggling. This is in the logs for one of the slaves: 100118 10:05:56 [Note] Slave I/O thread: connected to master 'repl@db1:...
jabley's user avatar
  • 335
6 votes
1 answer
7k views

MySQL - Unable to enable binary logging

I'm trying to enable binary logging so that I can set up master-slave replication between two servers. I'm working with an existing server with lots of data and I'm trying to dump the data with the ...
Justin's user avatar
  • 161
6 votes
3 answers
4k views

MySQL to PostgreSQL replication

Is there way to replicate in near-realtime MySQL changes to equal PostgreSQL database?
datacompboy's user avatar
5 votes
5 answers
10k views

Diagnosing Mysql Replication Issues

We have a mysql replication client running on our backup server. Since a power failure last week it's stopped replicating. Before this it was running uninterrupted for several months. I've tried ...
theotherreceive's user avatar
5 votes
3 answers
2k views

Replicating a linux web server

I'm planning on replicating a web server for high availability purposes. The server is running as a Ubuntu 15.04 virtual machine in Hyper-V and has MariaDB 10.0, Apache 2.4 and PHP-FPM 5.6 installed. ...
gijs007's user avatar
  • 117
5 votes
1 answer
8k views

Where can I find documentation on my.cnf directives?

I'm skimming through how to set up a basic master/slave replication in mysql. http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/ However ...
user784637's user avatar
  • 1,592
5 votes
5 answers
9k views

How can I back up and replicate a large MySQL database?

How can I backup a large database in mysql? And what is the best way to replicate the database? what i'm looking for is backing up large database with large amount of data in a small time limit then ...
hkshambesh's user avatar
5 votes
2 answers
4k views

Mysql replicate-rewrite-db not working

Been "slaving" on this issue all day. I have a master/slave setup that works, but when I try to replicate to a different database name on the slave it fails. This is what my.cnf looks like: server-...
Ed Manet's user avatar
  • 532
5 votes
3 answers
24k views

Failed to connect from slave to master with error "error connecting to master (1045)"

I try to setup replication from slave to the master. CHANGE MASTER TO MASTER_HOST = 'master', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'xxx'; And I did grant privileges ...
Fang-Pen Lin's user avatar
5 votes
2 answers
23k views

MySQL slave server not removing old relay binlogs

I have a MySQL server with slave replication on another host. Today I stumbled across the high disk usage of the slave host and invastigated what takes up all the space. As it turns out this space is ...
Izzy's user avatar
  • 835
5 votes
2 answers
19k views

How to "safely" shutdown/restart a Galera cluster?

First time setting up Galera (Ubuntu 14.04) and I'm unable to restart the cluster after rebooting my nodes. I'm following the this guide and it doesn't exactly mention how to handle a shutdown or ...
Server Fault's user avatar
  • 3,774
5 votes
3 answers
581 views

MySQL package upgrades (Debian apt-get) always break master-master replication due to changes in mysql schema

I have master-master replication set up on 2 Debian servers, and they replicate everything, including the mysql database itself (so that new users and such also replicate). This generally works very ...
dlo's user avatar
  • 471
5 votes
1 answer
5k views

How to get rid of mysql binlogs?

I've been using mysql for development. I don't use replication but I guess I had binary-logging enabled for some time on one of my previous computers and I have unknowingly accumulated hundreds of ...
Stann's user avatar
  • 523
5 votes
3 answers
918 views

MySQL load balancing

I'm looking into ways to balance the load on our MySQL infrastructure, and can't seem to find an answer that works for me... :) So, I have one big and fat server, that handles everything. Many DBs, ...
Sergey's user avatar
  • 948
5 votes
1 answer
3k views

why use mixed-based replication for mysql

I am in the process of configuring MySQL replication and am intending to use row-based-replication but I was also reading up about mixed-based replication. This is where statement-based is the ...
Alistair Prestidge's user avatar
4 votes
3 answers
4k views

mysql master-slave setup with synchronous replication

I have a very trivial mysql master->slave setup going on between two servers. The problem is, replication is asynchronous, and this can cause issues (even on a low latency link), if the master server ...
imaginative's user avatar
  • 1,981
4 votes
3 answers
10k views

Database scalability with write-heavy application

I have a write-heavy application. The application is best compared to surveys - the customer creates custom questionares and this is saved to the database. Most of the requests are from their users ...
Luke's user avatar
  • 1,942
4 votes
2 answers
2k views

Are Master-Master database collisions a problem even with auto increment?

I've done quite a lot of reading into this and I'm still I'm not sure where I stand. Am I going to get database collisions (and then corruption) with two Masters both receiving writes ? Now that ...
Jonathan Ross's user avatar
4 votes
2 answers
3k views

MySQL - If slave is offline and comes back online, does it automatically catch data from master?

MySQL - If slave is offline for a few hours and comes back online, does it automatically catch data from master?
Mike's user avatar
  • 45
4 votes
1 answer
11k views

making slave server as master for another server

I have three servers, Server X, Y, and Z. I have the main mysql DB on X (innodb DB). now I have created Master-Slave from X to Y. everything is working fine here. and now i set Y as a master for Z. ...
Alaa Alomari's user avatar
4 votes
2 answers
2k views

Why does MySQL replication break on missing row, then continue after "START SLAVE"?

The title is a bit confusing, but I can't think of a better one. What I have is a simple vanilla MySQL replication, with the slave occasionally failing, with this error: Error 'Can't find record in '...
shlomoid's user avatar
  • 289
4 votes
2 answers
3k views

MySQL replication: Getting the master in sync with the slave again

I have two computers on the same network, in a master-slave replication setup. Simply to test failover, I inserted some data straight into the slave database. So now the slave database contains more ...
Deniz Dogan's user avatar
4 votes
2 answers
1k views

Percona MySQL 5.7 Replication error 1236 "unknown error reading log event on the master"

we are using two instances of 5.7.20-18-log (in the meantime updated to 5.7.21-21-lo) Percona Server in a master-master (dbm1 / dbm2) replication. The application only uses dbm1 for writes and reads ...
Adrian's user avatar
  • 41

1
2 3 4 5
11