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).
529
questions
99
votes
11
answers
153k
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 ...
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
...
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....
15
votes
5
answers
22k
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 ...
15
votes
6
answers
65k
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 > ...
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 ...
14
votes
5
answers
12k
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, ...
14
votes
1
answer
36k
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 ...
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 ...
12
votes
3
answers
31k
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 ...
12
votes
4
answers
51k
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 ...
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 ...
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 ...
9
votes
1
answer
22k
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 ...
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 ...
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....
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 ...
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 ...
7
votes
3
answers
17k
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 ...
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/...
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 ...
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 ...
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 ...
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. ...
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 ...
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 ...
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 ...
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:...
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 ...
6
votes
3
answers
4k
views
MySQL to PostgreSQL replication
Is there way to replicate in near-realtime MySQL changes to equal PostgreSQL database?
5
votes
5
answers
11k
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 ...
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.
...
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 ...
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 ...
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-...
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 ...
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 ...
5
votes
2
answers
20k
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 ...
5
votes
3
answers
593
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 ...
5
votes
1
answer
6k
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 ...
5
votes
3
answers
924
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, ...
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 ...
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 ...
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 ...
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 ...
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?
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.
...
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 '...
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 ...
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 ...