MySQL-Replication could be a kind of load balancing for querieng applications or active failover/backup node which has the same data.
Also for common heavy loaded scenarios, where the primary write-queries are going to the master node and all read-queries are running against the slave node.
As first we have to modify our MySQL-configurations(my.cnf) to make them ready for replication setup:
On master:
1 2 3 | server-id = 1auto-increment-offset = 1auto-increment-increment = 10 |
On slave:
1 2 3 | server-id = 2auto-increment-offset = 2auto-increment-increment = 10 |
If replication has stopped and a query inserts into db-01 while a second query inserts into db-02 then the value of any auto_increment columns will be the same. When you get replication running again this will cause a problem.
To avoid this situation we can use auto-increment-increment and auto-increment-offset. These variables affect the way that MySQL generates the next value in an auto-incrementing series.
1 2 3 | CREATE USER 'repl'@'%' IDENTIFIED BY 'replsPassword';GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';FLUSH PRIVILEGES; |
On master:
1 2 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'replsPassword';FLUSH PRIVILEGES; |
On slave:
1 | CHANGE MASTER TO MASTER_HOST='192.168.3.50',MASTER_USER='repl', MASTER_PASSWORD='replsPassword'; |
Check the replication status:
1 | show slave status\G |
For better understanding set a MySQL pager to less.
Add a slave to existing MySQL-instance
On master:
1 2 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'replsPassword';FLUSH PRIVILEGES; |
Test connection to master:
1 | root@slave:~# mysql -h 192.168.3.50 -u repl -p |
On slave:
1 2 | CHANGE MASTER TO MASTER_HOST='192.168.3.50',MASTER_USER='repl', MASTER_PASSWORD='replsPassword';START SLAVE; |
If you just want slave to ignore some database, you can set replicate-ignore-db:
1 | replicate-ignore-db=typo3 |
Monitoring
For example the Icinga Monoring, create a separate user with minimal permissions:
1 2 3 | CREATE USER 'mon'@'%' IDENTIFIED BY 'pass4mon';GRANT REPLICATION CLIENT ON *.* TO 'mon'@'%';FLUSH PRIVILEGES; |
Troubleshooting
If a system user was only created on master server and after deletion of it, occur this error on slave.
Last_SQL_Error: Error ‘Operation DROP USER failed for ”@’%” on query. Default database: ‘mysql’. Query: ‘DROP USER ”@’%”
Skip a single replication error and start slave again:
1 | SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; |
Leave a Reply