MySQL Master-Slave Replication: Starting Replication

February 19, 2012

In my last article, I took you through Phase 1 of setting up the MySQL Master-Slave relationship.

Now, we have the Master SQL Server running and serving our clients, which connect using remote IPs. Also, the Master has a replication server id (e.g. “100”) and waits for the Slave connections with a user that is allowed replication (e.g. ‘repl’).

We also have Slave SQL Node almost ready to start.

What we are going to do now is the following:

On Master,
1. get the Replication Master Binary Log Coordinates
2. create a data snapshot using mysqldump
3. transfer the data on Slave
On Slave,
1. Restore the data snapshot
2. Set the Slave to start replication.

Detailed Actions

On Master

During steps 1 and 2 below, your Master database will be set to read-only mode.

Get Master Coordinates
1. Open a MySQL shell and give:
  
  mysql> FLUSH TABLES WITH READ LOCK;
2. IMPORTANT. Leave this shell open and create another mysql client shell, i.e. another session:
3. Hence, on another session, type in the following mysql command:
  
  mysql> SHOW MASTER STATUS;
Write down the values for the column FILE and POSITION. You will need them later when setting up the Slave to start replication
On a command prompt, get a data snapshot:

os-shell> mysqldump --all-databases --master-data > dbdump.db

If you have a big database, this might take quite some time. In our case, for a 25Gb database, it took something like 15 minutes.

Release lock and allow your Master to play

When your data dump finishes, just close the connection you opened on step 1 and your Master database server will resume serving transactions.

Now, you have a database file you can use on Slave to restore the database. You have to transfer this file to your Slave node.It might be a good idea to tar and gzip this file before transferring it and then untar and unzip it at the Slave node.

On Slave

Assuming that you have transferred your database dump file to Slave, you move on to work on this node as follows:

Start MySQL server with --skip-slave-start option so that replication does not start. Here is the suggested way:
On your operating system command prompt:

os-shell> mysqld_safe –-skip-slave-start
Import the dump file:

On another operating system command prompt:

os-shell> mysql –u root –p < dbdump.db

This will start the import. It will take quite some time if your dump file is big.

Important gotcha:: Before you start the import, make sure that you have enough space both on the datadir of MySQL and on the binary log directory too.
Stop MySQL Server

Assuming that import has finished successfully, and assuming that on step 1 above you started the MySQL Server skipping slave start, you need to stop this and make sure that MySQL server is not running.
Some Problems that you might have now on Slave

The problem that you have now is that you restored the system databases as well. These are coming from your Master server, which has a different IP. This will mean that ‘root’ user might not have any access to MySQL server now from the local Slave machine.

Also, ‘root’ might have different password. In Debian machine this is encrypted in the file debian.cnf” in your MySQL installation directory.

You can bring the debian.cnf file from your Master to your Slave machine. Or you can change/reset the ‘root’ password on your Slave machine.

Hint: You can change/reset the ‘root’ password on MySQL server as follows:
- Start MySQL so that it will not ask for password. Also, make sure it does not start replication:
  
  os-shell> mysqld_safe –-skip-slave-start –skip-grant-tables
- Then connect with mysql –u root and issue a command to update ‘root’ password as follows:
  
  mysql> use mysql;
  
  mysql> update user set password=PASSWORD(‘new-password’) WHERE User = ‘root’;
  
  mysql> flush privileges;
- Stop MySQL Server that you started with skipping slave start and grant tables.

Start MySQL Server with skipping Slave Start

os-shell> mysqld_safe –-skip-slave-start
Set Master configuration on the Slave

Execute the following command on a MySQL prompt:

mysql > CHANGE MASTER TO MASTER_HOST=’10.100.10.80’, MASTER_USER=’repl’, MASTER_PASSWORD=’slavepassword’, MASTER_LOG_FILE=’mysql-bin.000003’, MASTER_LOG_POS=106;

This is how you tell Slave how to connect to Master in order to replicate. Note the log coordinates. These are the coordinates you got from step 1 above.
Stop MySQL that you have started on step 4 above.
Start MySQL normally, e.g. on an OS shell:

os-shell> /etc/ini.d/mysql start

Checking out that everything is OK

Having started the slave MySQL node, you can log in and issue some commands to make sure that Slave is running OK.

On mysql prompt, give the following command:

mysql> show processlist;

The output should be something similar to the following:

+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------+------------------+

| Id | User        | Host      | db    | Command | Time | State                                                                 | Info             |

+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------+------------------+

|  1 | system user |           | NULL  | Connect |  232 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |

|  2 | system user |           | NULL  | Connect |  232 | Waiting for master to send event                                      | NULL             |

| 39 | root        | localhost | mysql | Query   |    0 | NULL                                                                  | show processlist |

+----+-------------+-----------+-------+---------+------+-----------------------------------------------------------------------+------------------+

3 rows in set (0.00 sec)

You can see the SQL thread that gets data from Master (in the above output is the thread with Id 2) and the SQL thread that executes the statements on Slave (in the output is the thread with Id 1).

On mysql prompt, give the following command

mysql> show slave status;

This will display the current status on slave. Pay attention to the *_Errno and *_Error columns. Normally, you shouldn’t see anything that indicates existence of errors there.

On mysql prompt, give the following command

mysql> show status like ‘Slave%’;

You should see an output like the following:

+----------------------------+-------+

| Variable_name              | Value |

+----------------------------+-------+

| Slave_open_temp_tables     | 0     |

| Slave_retried_transactions | 0     |

| Slave_running              | ON    |

+----------------------------+-------+

3 rows in set (0.00 sec)

Pay attention to Slave_running being with value ON.

Important note on binary log time to live

As we have said before, you can have Slave down and resynchronize as soon as you bring it up again. But do not put it out of service for quite long because, then it will be impossible to synchronize its content with Master. This is because the binary logs on Master do not leave forever.

There is the variable with name expire_logs_days that determines the number of days for automatic binary log file removal. Check this out. This should be 10, meaning that if you ever have your Slave down for 10 days or more, it will not be able to do replication as soon as you bring it up, and you will have to everything from the beginning.

Conclusion

That was our story on how, more or less, we have implemented MySQL replication for our Fraudpointer application. The steps may not apply exactly to your particular case, but they can still give you a kick start and show you the way to implement replication the way it should work on your configuration.

Your comments are more than welcome. We want them. We need to improve this process and your feedback is absolutely valuable to us.

Visual Studio Code: End-to-End Editing and Debugging Tools for Web Developers