zeldor.biz

Linux, programming and more

Copyright © 2023
Log in

MySQL/MariaDB replication

December 24, 2020 by Igor Drobot Leave a Comment

mysql-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;

Filed Under: Linux, MySQL Tagged With: Debian, MySQL, MySQL replication, MySQL slave

Categories

Archives

Tags

apache2 Apple arduino ARM Automation backup bash Cisco Cluster Corosync Database Debian Debian squeeze DIY DNS Fedora FTP Fun Icinga Ipv6 KVM Linux LVM MAC OS X Monitoring MySQL Nagios Nginx openSUSE OpenVPN PHP Proxy Python python3 qemu RAID rsync Samba security ssh Ubuntu virtualization Windows Windows 7 Wordpress

Leave a Reply

Your email address will not be published. Required fields are marked *