16

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 ...


8

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, ...


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

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 ...


5

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 ...


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

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;). ...


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

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

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

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 ...


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

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

If you don't want REDO the complete procedure, a good fix would be to use STOP SLAVE; SET GLOBAL sql_slave_skip_counter=1; START SLAVE; If there are too many such errors, a good idea would be to automate it using a bash script. Ref: Fixing Duplicate Entry Error


3

rsync is a great tool for keeping files in sync. I would use it in combination with SSH (and public keys), like this: rsync -az -e ssh --delete /var/www otherserver:/var/www For multiple servers multiple uses of rsync might be the best option. Another option is pdcp -r, but that requires copying all the files every time instead of doing delta-transfers. In ...


3

You don't mention, but I assume that you are after a multi-master setup. This rules out master-slave database setups. I believe which technology to use for security will be the least of your worries. 1. Datababase Keeping a multi-master database setup is sync is tricky. You can go with multi-master (active/active) database setups like MariaDB w/Galera ...


3

We had this problem too, and it took me a while to figure out what was going on because I managed to get Galera working at one point, then it failed again when I tried to repeat it all from scratch. The not-very-descriptive unknown error -141 appears to occur when spawned processes (such as SST backup runs) fail on startup, when they try to write to the ...


3

There's no discrepancy. RDS, being a managed service, does not enable every possible configurable value for every configuration parameter. The architects of RDS chose not to make STATEMENT available as an option for BINLOG_FORMAT, presumably, because they recognize the fact that it has many limitations in a replication environment. Not all statements ...


3

I already fix the replication by run the mysql_upgrade that I forgot to run after updating MariaDB from 5 to 10. Thanks


3

Solution Thanks to the folks that investigated with me, I was able to find the solution to my problem and I do now have a working setup. If you feel brave enough, you can read the comments on the original question, but here is a summary of the steps that helped me solve my issue. Read the source First thing to do when setting up HA resources, will sound ...


3

AWS offers the Database Migration Service (DMS) for exactly this. DMS can migrate from on-premises, EC2 or RDS instances. It offers progress, statistics and more. AWS blog: AWS Database Migration Service


3

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 ...


3

Define your problem properly. There are several options and constraints on how you design a MySQL cluster. What is it that you are trying to achieve? (availability, performance, offline backups, something else) What are your medium and long term objectives here? What is your budget? What is your hosting model? how could i set multiple master Do you ...


3

So you're talking about several databases, that are already replicated between clients servers. And you need to add one more replication layer, and replicate them to your server, correct? What's the purpose of this configuration? You can for example build your own datacenter with a couple of servers, and replicate this databases between your servers, just ...


3

Agree with the previous answer, mysql replication can be a possible solution. Also, you can create shared storage between some of your servers, and place the VM with the database on it, thus it will migrate between the servers if something will happen. For this scenario use something like HPE VSA or StarWind. They have free versions as well. It will allow ...


3

I've checked MySQL official documentation and haven't found answer on your question, then I googled for more information and found this article. So according this article it not possible to read from database, when there is no connection to master. When you start a new server, it will try to get all the data it is missing from the other group members. ...


3

I know it is late reply to this question. But just in case any one looking for the exact answer about how to safely shutdown and restart the mariadb galera cluster. For example we have three mariadb galera nodes(1,2,3) running on ubuntu servers. To stop/shutdown the cluster in safe way without destroying the cluster: Make sure no active transactions or ...


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