MySQL/MariaDB replication

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:

server-id = 1
auto-increment-offset = 1
auto-increment-increment = 10

On slave:

server-id = 2
auto-increment-offset = 2
auto-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.

CREATE USER 'repl'@'%' IDENTIFIED BY 'replsPassword';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

On master:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'replsPassword';
FLUSH PRIVILEGES;

On slave:

CHANGE MASTER TO MASTER_HOST='192.168.3.50',MASTER_USER='repl', MASTER_PASSWORD='replsPassword';

Check the replication status:

show slave status\G

For better understanding set a MySQL pager to less.

Add a slave to existing MySQL-instance

On master:

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'replsPassword';
FLUSH PRIVILEGES;

Test connection to master:

root@slave:~# mysql -h 192.168.3.50 -u repl -p

On slave:

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:

replicate-ignore-db=typo3

Monitoring

For example the Icinga Monoring, create a separate user with minimal permissions:

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:

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;