We want to set up MySQL server replication. We will use VMWare Workstation to set up two virtual machines – master and slave.

On Distributing Operating Systems Course we will use RS1 (as master) and RS2 (as slave).

We will use Linux Debian Jessie distribution.

We need some preparation:

• Make sure both server have IP communication. With VMWare Workstation standard IP setup should be fine.
• We need to find out what’s master IP (DB-MIP) and slave (DB-SIP). We can use “ip a” commands, as in previous tasks. On Distributed Operating Systems Course we can use 192.168.100.10 and 192.168.100.20.
• On both machines it’s good to update and upgrade, before installing  mysql.

We’re now ready to install mysql.

Install mysql-server package on both machines DB1 and DB2 (in the box below we present installation on DB1).

Note:

• you will be prompted for a password for root mysql user.  Choose “qwerty” and remember password – it will be needed in the future.

We can now set up master server on DB1.

Default configuration of mysql needs some modifications to become a master.

What we will do and why:

1. Add server-id, enable binary log, set up the listening on ip all ip addresses (0.0.0.0) and restart the mysql service.
2. Create mysql user for replication.
3. Get the information about the binary log position.
4. Dump the databases to a file.
5. Copy the dump file to mysql slave server.

Edit /etc/mysql/my.cnf and add the following lines in [mysqld] section:

Note:

• server-id is a unique server identification value in a replication cluster, it’s a good idea to pick 1 for master and higher values for slaves
• server-id should be an integer value
• log-bin states
• if there’s a line with “skip-networking” option enabled, comment it out, it disables IP access to the database
• bind-address should be set to 0.0.0.0

Finally we need to restart the mysql service for the changes to be applied:

You need to login to mysql and create user, grant privileges and apply the config:

It’s good to have a test database  and test table on which we will test our replication cluster:

We’ll also need a username and password for mysql connections (we could use root, but the GRANT query is necessary).

Note:

• if you have other than testdb database name, you need to modify GRANT query,
• if you want to use different username you need to modify create query,
• if you have other than testuser username, you need to modify GRANT query.

We need to run commands listed above on both RS1 and RS2.

We need to set the binary log position and name of the binary log file

In our case:

• binary log position value is equal to 107
• binary log file name is equal to mysql-bin.000004 
• don’t exit after FLUSH TABLES WITH READ LOCK; this command is in effect until you exit mysql cliet.

We need to get databases dump, which will be later inserted into slaves before slave will sync up with master.

We will use mysqldump command:

We will use scp file transfer protocol:

Edit /etc/mysql/my.cnf and add the following lines in [mysqld] section:

Note:

• server-id is different from master for the slave server
• we don’t set any binlog
• we don’t have to bind any address other than default, since slave is connecting over IP to master, but for our purposes, it’s good to make sure we’re binding all interfaces, because we want to connect to our database from the frontend:

Finally, we restart the service for the configuration to be applied:

We need to login to mysql slave server and set the master location and position.

We gathered from master in the previous steps that location in master is  mysql-bin.000004 and positions is 107.

Change this values to the ones you got from your master (otherwise it won’t work).

We’re now almost ready to start slave, but first we need to restore the dump of our database.

We use the mysql command line client, which can read standard input (the file location depends on the destination we used with scp command above, for Distributed Operating Systems it’s usually /home/kalkos/dbdump.db):

We can now start slave on DB2:

Let’s see if slave  works fine (\G set the more human friendly output):

Finally we can select from testtable in testdb:

First, on slave we’ll check what’s the content of testtable (DB2):

On master, we’ll insert the first value to testtable in testdb (DB1:):

Finally we’ll check if the same result was applied to slave server (DB2):

Which completes the configuration.

Check:

1. If show slave status cannot connect to master:
   1. check whether you properly changed the bind-address (that there are no two lines with bind-address) and removed skip-networking and restarted mysql service afterwars.
   2. check if netstat -tlnp shows open 3306 port on 0.0.0.0 address by mysql process
   3. check if you can telnet 192.168.100.10 3306 from slave to master
   4. check whether you properly set the address of master
2. If show slave stats shows it the user is not authorized to access master:
   1. check whether you properly added user (select * from user where user = ‘repl’;),
   2. run grant query and run flush privileges again.
3. If mysql fails to restart – check your config for misspellings.
4. If mysql doesn’s sync:
   1. test what’s position log and location file name on master with SHOW MASTER SLAVE STATUS \G; and compare it to setting you can find on slave with SHOW SLAVE STATUS \G;
5. If you get “no such file” while importing the dump – locate the dump file you uploaded and user proper path.