MySQL master to master replication database servers

[embleton]

We assume that the mysql package has been set up on the primary master and secondary master server. The configuration file we deal with first is located at /etc/mysql with its name being mysql.cnf and this needs to be modified first after exporting the database with phpMyAdmin package. Information on the package is at https://www.phpmyadmin.net but it is an easy package to install on ubuntu.

Server A: 192.168.10.1 = Server B: 192.168.10.2 # master to master MySQL replication servers. Ubuntu 17.x.x and 18.x.x

Export the data from the current system or the primary server with the phpMyAdmin package. Ensure that none of the databases is read by any package or internet access for that'll destroy the pointers into the database. The MySQL database will be offline until the secondary is in operation.

Modify the mysql.cnf file as follows the primary master server A.

# start of the configuration file.
[mysqld]
server-id = 67345215 # this number needs to be unique across the primary and secondary masters.
sync_binlog=1 # even though this causes greater disk I/O for it flushes the data immediately to disk.
auto_increment_increment=2
auto_increment_offset=1
binlog_do_db=database # This should be the same name as the database in the schemas.
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_ignore_db=mysql
# end of the configuration file but all statements in the standard file
# should remain after this statements that have been added.
-----
The above statements are the only ones necessary and there should be no binding addresses with bind-address=127.0.0.1 in the configuration which many specify in their method of setting up the master to master replication.

Modify the mysql.cnf file as follows on the secondary master server B.

[mysqld]
server_id = 1231654587
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
# bind-address = 0.0.0.0
binlog_do_db=database
log_bin=mysql-bin
log_error=mysql-bin.err
binlog_ignore_db=mysql


Now we should create users for the replication process with and at the command terminal on the primary and secondary masters (and slaves).

mysql -u root -p

create user 'replicator'@'%' identified by 'password';

grant replication slave on *.* to 'replicator'@'%';

flush privileges;

The rest of the process can be done in the phpMyAdmin package on the access to that package on the primary and secondary master websites. By entering the primary master the replication page is executed once one has logged on to the primary MySQL database with its root and password for the root on the website for primary and secondary masters. The route for creating the primary master on each server can be run once the MySQL server is restarted on the primary and secondary master with.

sudo service mysql restart

The scary bit is if the database belongs on the secondary master then the database must be dropped. This can be done in the phpMyAdmin website on the secondary master. The export of the database should now be imported into the secondary primary master replicator. Select the option 'change or reconfigure master'. The replicator username can be used at this stage with the IP address of the secondary master replication. Move to the secondary and follow the same process of not forgetting the IP address is that of the primary master replication server. The slave can now be started on each primary master servers by selecting the option 'Control slave' and then perform a full start on each server.

And that is it both servers should be up and running and one has a master to master replication MySQL databases. This is known to work for this IPv4 & IPv6 site https://embleton.me.uk and IPv6 only https://mental.me.uk are in a live master to master replication MySQL database for the written content.

[LeoFreskos]

For database administratation over the web obviously phpMyAdmin has become a defacto standard. But for local databases this tool is quite slow and inconvenient. After some research I ended up with HeidiSQL which I think is very good.

I would be interested in hearing what other people are using.