August 22, 2018 MySQL-MariaDB Show In a Master/Slave replication data consistency is a biggest concern and is very easy to break your replication with issues such as duplicate keys, missing rows , accidental writes from application connected to the slave or even by a DBA connected to the slave 🙁 “yes things happen” . 1. read_only When the read_only system variable is enabled, the server permits no client updates except from users who have theSUPER privilege. This variable is disabled by default. It’s a good
practice then to set Unfortunately users with the SUPER privilege can override the setting and could still run DML queries. 2. super_read_only However, since Percona Server 5.6.21 and MySQL 5.7.8, you can use the super_read_only feature
to extend the read_only option and apply it to users with SUPER privileges. If the 3. temporary use of slave-skip-errors With slave-skip-errors you can make the replication slave ignore certain error codes (you can find the list of MySQL error codes here: Server Error Codes and Messages) Example : the purpose of the example below is to temporary skip error
Caution : Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of sync with the master, with you having no idea why this has occurred. Here are a couple bugs to consider on Percona Server 5.6:
For more information, please check following links:
Skip to content
Normally MySQL replication will stop whenever there is an error running a query on the slave. This happens in order for us to be able to identify the problem and fix it, and keep the data consistent with the mater that has sent the query. You can skip such errors, even if this is not recommended, as long as you know really well what are those queries and why they are failing, etc. For example you can skip just one query that is hanging the
slave using: “1062 | Error ‘Duplicate entry ‘xyz’ for key 1′ on query. Default database: ‘db’. Query: ‘INSERT INTO …”There might be cases where you will want to skip more queries. For example you might want to skip all duplicate errors you might be getting (output from show slave status;): If you are sure that skipping those errors will not bring your slave inconsistent and you want to skip them ALL, you would add to your my.cnf:
As shown above in my example 1062 is the error you would want to skip, and from here we have: _ Error: 1062 SQLSTATE: 23000 (ER_DUP_ENTRY) Message: Duplicate entry ‘%s’ for key %d_ You can skip also other type of errors, but again don’t do this unless you understand very well what those queries are and what impact they have on your data:
and for the error codes you can see them all here … (you will see them in your _show slave statu_s; also). Post navigationHow do I fix a duplicate entry in MySQL replication?Try to locate duplicate entry and delete that entry from slave DB. Once you have deleted the old entry then execute stop slave and then start slave on slave DB. Most probably replication will start again and come back to normal. If it gets stuck again for same error for some other record then repeat same steps.
How do you skip errors in replication?You should first try one of these: How To Repair MySQL Replication or use the replicate-ignore-db or replicate-ignore-table statements in the slave's my. cnf file to skip replication for databases/database tables that cause replication to fail (if you don't need replication for these databases/database tables).
How do I skip transactions on Gtid replication?We can skip a error in GTID based replication by following steps: STOP SLAVE; set GTID_NEXT='SERVER_UUID:LAST_TRANSACTION_NUMBER+1' ; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC"; START SLAVE; But if a replication is running with channel information, than how to skip the transaction for a particular channel ?
How do I fix MySQL error 1062?1062 - Duplicate Entry
To solve this, Set the primary key column as AUTO_INCREMENT . And when you are trying to insert a new row, ignore the primary key column or insert NULL value to primary key.
|