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,
- get the Replication Master Binary Log Coordinates
- create a data snapshot using mysqldump
- transfer the data on Slave
- On Slave,
- Restore the data snapshot
- 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
- Open a MySQL shell and give:
mysql> FLUSH TABLES WITH READ LOCK;
- IMPORTANT. Leave this shell open and create another mysql client shell, i.e. another session:
- Hence, on another session, type in the following mysql command:
mysql> SHOW MASTER STATUS;
- Open a MySQL shell and give:
- 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
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 filedebian.cnf
” in your MySQL installation directory. You can bring thedebian.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 so that it will not ask for password. Also, make sure it does not start replication:
- 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 withId 2
) and the SQL thread that executes the statements on Slave (in the output is the thread withId 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 toSlave_running
being with valueON
.
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 nameexpire_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.Frequently Asked Questions (FAQs) on MySQL Master-Slave Replication
What is the significance of MySQL Master-Slave Replication?
MySQL Master-Slave Replication is a process that allows data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). This is crucial for several reasons. Firstly, it provides a method of backing up data in real-time, which is essential for data recovery. Secondly, it allows for the distribution of reads, which can significantly improve the performance of your application. Lastly, it provides a way to analyze data without affecting the performance of the master server.
How do I set up MySQL Master-Slave Replication?
Setting up MySQL Master-Slave Replication involves several steps. First, you need to configure the master server by editing the MySQL configuration file and setting the server ID and the binary log file location. Next, you need to restart the MySQL service and create a user for replication. After that, you need to configure the slave server by setting the server ID and specifying the master server details. Finally, you need to start the slave server and test the replication.
What are the common issues in MySQL Master-Slave Replication and how can I troubleshoot them?
Some common issues in MySQL Master-Slave Replication include data inconsistency, replication lag, and connection issues. To troubleshoot these issues, you can use several methods. For data inconsistency, you can use tools like pt-table-checksum and pt-table-sync. For replication lag, you can monitor the Seconds_Behind_Master status variable. For connection issues, you can check the network connectivity and firewall settings.
How can I monitor MySQL Master-Slave Replication?
Monitoring MySQL Master-Slave Replication is crucial to ensure its smooth operation. You can use the SHOW SLAVE STATUS command to check the status of the slave server. This command provides information about the slave SQL thread and the slave I/O thread, the master log file position that the slave is reading from, and the number of seconds the slave is behind the master.
How can I improve the performance of MySQL Master-Slave Replication?
There are several ways to improve the performance of MySQL Master-Slave Replication. You can increase the network speed between the master and slave servers, use a dedicated network for replication traffic, increase the size of the relay log and binary log cache, and use row-based replication instead of statement-based replication.
What is the role of the ‘skip-slave-start’ command in MySQL replication?
The ‘skip-slave-start’ command in MySQL replication is used to prevent the slave from starting automatically when the MySQL server starts. This can be useful in situations where you want to manually control the start of the slave, such as during maintenance or troubleshooting.
How can I handle failures in MySQL Master-Slave Replication?
Handling failures in MySQL Master-Slave Replication involves identifying the cause of the failure, fixing the issue, and then resuming the replication. You can use the SHOW SLAVE STATUS command to identify the error number and error message. Once you have fixed the issue, you can use the START SLAVE command to resume the replication.
Can I set up multiple slaves for a single master in MySQL replication?
Yes, you can set up multiple slaves for a single master in MySQL replication. This can be useful for distributing reads across multiple servers to improve performance, or for setting up additional backups of your data.
What is the difference between synchronous and asynchronous replication in MySQL?
In synchronous replication, the master waits for the slave to acknowledge that it has received and applied the changes before proceeding. This ensures data consistency but can impact performance. In asynchronous replication, the master does not wait for the slave’s acknowledgment, which can improve performance but may lead to data inconsistency if the slave fails.
How can I switch from master-slave to master-master replication in MySQL?
Switching from master-slave to master-master replication in MySQL involves configuring the original master as a slave of the original slave. This creates a circular replication setup where each server is both a master and a slave. However, this setup can be more complex to manage and may lead to conflicts if not handled properly.
![Panayotis Matsinopoulos](https://uploads.sitepoint.com/wp-content/uploads/2014/11/14159024301184842_10203434991496179_1139973165_n-96x96.jpg)
Currently, spending most of the day as CTO for Book&Table. Otherwise, full stack engineer for Fraudpointer and Wyngle. Also, blogging here and teaching computer programming.