10

TL;DR STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; START SLAVE; Now the slightly longer version: if you have manually created the user on your slave first and on your master second, the CREATE USER command executed on the master has been replicated to the slave. Attempted subsequent execution of the statement failed, because the user was already ...


7

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done, so I imagine that placing your logs on a slower disk can have an impact as other transactions will be delayed until the current transaction is logged. I would keep your binary logs on your fastest storage, but reduce the ...


6

MySQL 5.5 has semi-synchronous replication. Basically it guarantees that at least one slave has received the transaction before it is commited. http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html


6

I was on the right track here with the large query transactions from yesterday. After I migrated the data, I executed a DELETE statement on the original table, to get rid of the rows that I had migrated away. These tables are just full of tracking data, and therefore, don't have any primary or unique keys on them. Due to how ROW based replication works, ...


5

The expire_logs_days setting controls binary logging, not relay logging. Normally, the relay logs are purged when the slave has finished applying the data from them. This can be changed with relay-log-purge, but it's default is 1. If your slave is simply behind, it will continue to accumulate relay logs by design. You might want to consider setting relay-...


5

From MySQL manual: By default, the binary log is synchronized to disk at each write (sync_binlog=1). If sync_binlog was not enabled, and the operating system or machine (not only the MySQL server) crashed, there is a chance that the last statements of the binary log could be lost. To prevent this, enable the sync_binlog system variable to ...


5

You should use internal IP addresses for your instances to talk to each other. If you use their public or elastic IP addresses, you are charged for in-region network data transfer. There is no charge for using the private IP addresses.


4

I have an interesting method but you will have to stop replicaton to do For DB server M1, S1, and S2 STEP01) On S2, install same version of MySQL that S1 has STEP02) On S2, make sure server_id in /etc/my.cnf is different from server_id in S1 STEP03) On S1, STOP SLAVE; STEP04) On S1, SHOW SLAVE STATUS\G STEP05) Write down the following two values from ...


4

Inspect the relevant traffic with tcpdump/wireshark.


4

There is one flaw with mysql's seconds_behind_master value: it only takes into account the position relative to one upstream hop away. Easiest demonstrated with a slightly simpler replication topology: server1 -> server2 -> server3 If server2 falls behind, and is processing some long-running queries, the following will happen, assuming 00:00 as start point:...


4

You are looking at your slave's own binary logs. Your slave has clearly the log_bin variable set, and it's filling its disks with it's own binary logs (not the master's logs, their names probably contain a relay word, since you are using defaults in binary and relay log names). You can view them with show master status. To solve this problem (if you don't ...


4

I won't worry about it if the IO and SQL are running, as well as the Relay_Master_Log_File is catching up with the Master_Log_File. I believe the delay is in the fact that your total relay log file is huge, approx. 105G - Relay_Log_Space: 112995681998 and considering that the Slave is at 000774 position and master is at 000990 position, there are a total of ...


4

This depends entirely on the architecture and availability options of the specific applications in use. Does your database support a clustered deployment? A mirrored/replicated deployment? Both? What are the caveats of each? What's the failover/recovery process? The resource allocation requirements? What about for your web app? Can that be clustered? Load ...


4

The problem is that the AWS RDS MySQL read-replica mechanism requires that the two schemas be exact. Replication, in general, requires this. But, if your definition of "close enough" is indeed close enough, this can be done in RDS for MySQL the same way you'd always do it. create a DB instance ... and SET it to be the replica. Well, that won't be the ...


4

FYI—this is typically called a replication relay chain. In order to turn this on, you will have to make sure the following settings are set on server B: log_bin log_slave_updates This will tell server B to update its log position when updates from A are received. The documentation for this can be found here (emphasis added): Normally, a slave does not ...


4

You can locate the sql clause code like /usr/bin/mysqlbinlog -v --start-position=142743807 --stop-position=147399325 /data/mysql/data/master-bin.000010 > temp.log Then compare slave and master database difference according to temp.log on specific pos. Then update slave database. Then skip that line with mysql -e "stop slave; SET GLOBAL ...


4

MySQL and MariaDB have compatible API, which means the clients and programs should work with either MySQL or MariaDB without any changes. However on the server side they are more and more diverging and not all MySQL config options are available in MariaDB and vice versa. In particular MariaDB and MySQL have different GTID implementations that are not ...


3

As of MySQL 5.7.8, there is now a super_read_only option, which prevents even SUPER users from performing client updates. It does not disrupt the replication process. As with other settings, it can be set: in command line format (--super_read_only=ON), as a variable in my.cnf (super_read_only=1), or from the client prompt (SET GLOBAL super_read_only = 1;). ...


3

Attempted this on a K8 cluster. MySQL running inside of a container, GlusterFS installed directly on the node (non-containerized). MySQL container had an affinity set to always run on the same node as GFS, mounting volume using K8's native GFS volume driver. Experienced random high latency for WordPress sites, even with NGINX FastCGI (PHP-FPM) caching ...


3

The problem is caused by setting up the master on a running production server BEFORE doing the dump (as far as I can tell). So, there are queries written in the master_log that have already been executed on the data residing on the slave. I never actually saw a solution on the mysql website or mailing list. So, I came up with the following solution that ...


3

Sounds like only thing you'd need to change when you got the IP from the co-lo provider would be network config and /etc/hosts, and perhaps some apache config lines if you were using specific IPs in there rather than NameBasedVirtualHost entries. I have done these install and bulk setup in advance, then before I haul it to the co-lo, fire it up, edit those ...


3

Use an elastic IP address. Make sure to refer to the server by the elastic IP address's public hostname, not the actual IP address. Elastic IP addresses will resolve to the internal IP address when the resolution request comes from the availability zone's internal network. Otherwise, it will resolve to the public IP address. So when one EC2 instance ...


3

It's not really that simple to do a DNS Failover for a database. There are many reasons, but here are a few that might cause problems. A lot of applications use connection pooled libraries, so they might create persistent connections to the database, so the assumption that a DNS failover might actually cause all application traffic (reads and writes) to go ...


3

We've been doing multi-master replication on MySQL for a while, and it works great as long as you understand and can plan around/be prepared for the potential pitfalls. Be diligent about the auto-increment-offset, and auto-increment-increment replication settings (not as important if your app uses only GUIDs or some other manual keyfield assignment), and ...


3

Thats fine. The slave tables can be locked, and it will catch up the master later on (as long as the bin log is not expired, normally its set for couple of days)


3

Thanks for your help, guys, but it turns out the issue was SELinux not allowing MySQL on a non-standard port.


3

The autossh utility may do what you want autossh is a program to start a copy of ssh and monitor it, restarting it as necessary should it die or stop passing traffic. You should really do the job correctly and create a VPN between the two systems using openvpn (or similar).


3

When a MySQL Slave connects to the master, it requests a stream of the binary log, and the master autonomously sends binlog events as often as they occur, with no acknowledgement required from the slave unless you're using semisynchronous replication. The slave doesn't originate any traffic, other than low level acknowledgements handled by the TCP stack. ...


3

Documentation says "Native replication is the default, unless other operation is specifically configured." ( http://doc.powerdns.com/html/replication.html ) By Native it is referring to a replication thats native to the backend you use where it does not need to take an active part in the process itself but just uses the data it is presented with. For most ...


Only top voted, non community-wiki answers of a minimum length are eligible