Setting up a MySQL 5.7 read replica on the same machine (MacOS) involves a series of steps to ensure both the master and the replica servers are configured correctly. Using Homebrew simplifies the installation process but configuring the servers will require manual setup. Here’s a guide on how to achieve this:
Install MySQL 5.7 using Homebrew
If you haven’t installed MySQL yet, you can install it using Homebrew. If it’s already installed, skip to the configuration part.
brew install mysql@5.7
brew services start mysql@5.7Locate MySQL config from our installation
When MySQL is installed via Homebrew on macOS, the default location for the MySQL configuration file (my.cnf) can vary based on the specific MySQL version and how Homebrew manages its cellar and symlinked paths. However, you can generally find MySQL configuration files in one or several of the following locations on macOS:
mysqld --verbose --help
% Usage: mysqld [OPTIONS]
% Default options are read from the following files in the given order:
% /etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
% The following groups are read: mysqld server mysqld-5.7
% The following options may be given as the first argument:
# a more convenient way
mysql --verbose --help | grep -A 1 "Default options"Look for the section that starts with “Default options are read from the following files in the given order”. This section lists the configuration files MySQL reads from, if they exist.
Now, we need to find the data directory where our database storage resides using the following command:
show variables like '%datadir';
% +---------------+-----------------------+
% | Variable_name | Value |
% +---------------+-----------------------+
% | datadir | /usr/local/var/mysql/ |
% +---------------+-----------------------+- config: /etc/my.cnf
- datadir: /usr/local/var/mysql
Prepare MySQL master
Run the following command:
which mysqlThe output should be /usr/local/opt/mysql-client@5.7/bin/mysql for mysql5.7 homebrew, otherwise to ensure your system’s PATH is set up to use the Homebrew’s MySQL version by adding the following to your .bash_profile or .zshrc file:
export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"let’s modify the /etc/my.cnf
#/etc/my.cnf
[mysqld]
bind-address=0.0.0.0
server-id=1
log_bin=mysql-bin
binlog_format=row
binlog_do_db=ossowhere the osso is the name of our database. Let’s reload the server to make sure the config does not harm.
brew services restart mysql@5.7
# or by using native mysql command
# mysqld_safe --defaults-file=/etc/my.cnf &then login to the master server and create a user with replication privileges
mysql -h 127.0.0.1 -uroot -p -P 3306
CREATE USER 'replica'@'%' IDENTIFIED BY 'replica-pwd';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;
# reset password to 123456
ALTER USER 'replica'@'%' IDENTIFIED BY '123456';Export the data for the replica server
mysqldump -u root -p --opt osso > osso-replica.sqland finally get the log status of the master
mysql -h 127.0.0.1 -uroot -p -P 3306
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000019 | 154 | osso | | |We will need the log file: mysql-bin.000019 and the position of the log 154.
Set up the MySQL replica server
To simplify things, we will run the replica on the same machine but with a different port, in our case 3307.
Create the following file: /etc/my-replica.cnf
# /etc/my-replica.cnf
[mysqld]
bind-address = 0.0.0.0
server-id = 2
port = 3307
datadir = /usr/local/var/mysql-replica
socket = /tmp/mysql-replica.sock
pid-file = /usr/local/var/mysql-replica/mysql-replica.pid
log_error = /usr/local/var/log/mysql-replica_error.log
relay-log = /usr/local/var/log/mysql-relay-bin.log
relay-log-index = /usr/local/var/log/mysql-relay-bin.index
read_only = 1Where
- datadir=/usr/local/var/mysql-replica is the data directory. You can use any directory you want.
- binlog_do_db=osso is the database name to be replicated.
Initializing the replica server
Use the following command
mysqld --initialize --datadir=/usr/local/var/mysql-replica --user=$(whoami) --explicit_defaults_for_timestamp
# 2024-03-29T08:29:40.484587Z 0 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/var/mysql-replica/ is case insensitive
# ...
# 2024-03-29T08:29:41.047077Z 1 [Note] A temporary password is generated for root@localhost: 6YGh5d9e49/zWhen you initialize MySQL using mysqld — initialize, MySQL 5.7 and later versions generate a random password for the root@localhost user account. This password is marked as expired, requiring the password to be changed the first time you connect to MySQL. The temporary password is written to the MySQL error log file, typically located in the data directory under the hostname.err file, or as specified in the MySQL configuration file.
However, if you want to initialize MySQL without generating a temporary root password, you can use the — initialize-insecure option with mysqld. This option initializes the MySQL data directory without setting a password for the root@localhost account, leaving it empty, which means you can connect to the MySQL server as root without a password. After connecting, you should immediately set a new password for security reasons.
mysqld_safe --defaults-file=/etc/my-replica.cnf &
# [1] 7380
# 2024-03-28T10:13:03.6NZ mysqld_safe Logging to '/usr/local/var/log/mysql-replica_error.log'.
# 2024-03-28T10:13:03.6NZ mysqld_safe Starting mysqld daemon with databases from /usr/local/var/mysql-replicaConnect the MySQL replica server on port 3307
mysql -h 127.0.0.1 -P 3307 -u root -p # --socket=/usr/local/var/mysql-replica.sockA frequent mistake is that the command line overlooks the -P (port) variable, leading to an unintentional connection to the master database instead. To ensure you’re connected to the correct database server, execute a few SQL statements for verification:
# there should not be any databases
show databases;
# +--------------------+
# | Database |
# +--------------------+
# | information_schema |
# | mysql |
# | performance_schema |
# | sys |
# +--------------------+
# the datadir should properly be set as specified in the /etc/my-replica.cnf
show variables like '%datadir%';
# +---------------+-------------------------------+
# | Variable_name | Value |
# +---------------+-------------------------------+
# | datadir | /usr/local/var/mysql-replica/ |
# +---------------+-------------------------------+
# n order to replica you need to have the datase created on the replica server.
create database osso;Now let’s import the database
mysql -h 127.0.0.1 -u root -p --port=3307 osso --socket=/tmp/mysql-replica.sock < ./osso-replica.sqlNow start registering the replica.
# register slave
CHANGE MASTER TO
MASTER_HOST='127.0.0.1',
MASTER_USER='replica',
MASTER_PASSWORD='123456',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000019',
MASTER_LOG_POS=154;
# > Query OK, 0 rows affected, 2 warnings (0.09 sec)
# start slave
START SLAVE;
# > Query OK, 0 rows affected (0.01 sec)
# show the status of the slave
SHOW SLAVE STATUS\G
# *************************** 1. row ***************************
# Slave_IO_State: Waiting for master to send event
# Master_Host: 127.0.0.1
# Master_User: replica
# Master_Port: 3306
# Connect_Retry: 60
# Master_Log_File: mysql-bin.000019
# Read_Master_Log_Pos: 154
# Relay_Log_File: mysql-relay-bin.000002
# Relay_Log_Pos: 320
# Relay_Master_Log_File: mysql-bin.000019
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
# Exec_Master_Log_Pos: 154
# Relay_Log_Space: 527
# Master_Server_Id: 1
# Master_UUID: ac65f4fa-2f88-11ea-882e-a44054e684e1
# Master_Info_File: /usr/local/var/mysql-replica/master.info
# SQL_Delay: 0
# SQL_Remaining_Delay: NULL
# Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
# Master_Retry_Count: 86400
# 1 row in set (0.00 sec)Noted that mysql-bin.000019 and the 154 are the log file and position from the master status.
Test our setup
Master server
Perform and validate an update on one of the tables in the database. For this example, let’s work with the assumption that we have a ‘users’ table.use osso;
# Database changed
select id, sign_in_count from users order by id desc limit 1;
# +----+---------------+
# | id | sign_in_count |
# +----+---------------+
# | 75 | 200 |
# +----+---------------+
# 1 row in set (0.00 sec)update users set sign_in_count = 300 where id=75;
# Query OK, 1 row affected (0.01 sec)
# Rows matched: 1 Changed: 1 Warnings: 0select id, sign_in_count from users order by id desc limit 1;
# +----+---------------+
# | id | sign_in_count |
# +----+---------------+
# | 75 | 300 |
# +----+---------------+
# 1 row in set (0.00 sec)show master status;
# +------------------+----------+--------------+------------------+-------------------+
# | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
# +------------------+----------+--------------+------------------+-------------------+
# | mysql-bin.000019 | 1463 | osso | | |
# +------------------+----------+--------------+------------------+-------------------+
Replica server
Check the propagation on the replica server
show slave status\G
# *************************** 1. row ***************************
# Slave_IO_State: Waiting for master to send event
# Master_Host: 127.0.0.1
# Master_User: replica
# Master_Port: 3306
# Connect_Retry: 60
# Master_Log_File: mysql-bin.000019
# Read_Master_Log_Pos: 1463
# Relay_Log_File: mysql-relay-bin.000002
# Relay_Log_Pos: 1629
# Relay_Master_Log_File: mysql-bin.000019
# Exec_Master_Log_Pos: 1463
# Relay_Log_Space: 1836
# Master_Server_Id: 1
# Master_UUID: ac65f4fa-2f88-11ea-882e-a44054e684e1
# Master_Info_File: /usr/local/var/mysql-replica/master.info
# SQL_Delay: 0
# SQL_Remaining_Delay: NULL
# Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
# Master_Retry_Count: 86400
# 1 row in set (0.00 sec)
# Read_Master_Log_Pos: 1463 & Exec_Master_Log_Pos: 1463 got the replication correctly
# Let's check the data
show databases;
use osso;
select id, sign_in_count from users order by id desc limit 1;
+----+---------------+
# | id | sign_in_count |
# +----+---------------+
# | 75 | 300 |
# +----+---------------+
# 1 row in set (0.00 sec)
# and if you do an update
update users set sign_in_count = 1000 where id=75;
# Query OK, 1 row affected (0.01 sec)
# Rows matched: 1 Changed: 1 Warnings: 0Let’s work around to prevent users from updating the replicated database.
Configuring the Read Replica as Read-Only
To ensure a MySQL read replica does not write updates to the database, you must configure it with the appropriate settings that enforce its role as a read-only server. This configuration helps maintain the integrity of your replication setup by preventing any direct modifications on the replica that could cause data inconsistencies with the master.
1. Set read_only Option
On the read replica, you can set the read_only system variable to prevent users with write permissions from making changes. Only users with the SUPER privilege can perform write operations when read_only is enabled in the MySQL configuration file /etc/my-replica.cnf.
[mysqld]
read_only=1
...2. Limit User Privileges
Ensure that applications and users connecting to the read replica have limited privileges and cannot execute write operations. You can explicitly grant only the necessary permissions (like SELECT, EXECUTE, and so on) to each user for the databases they should access
CREATE USER 'rep'@'%' IDENTIFIED BY '123456';
GRANT SELECT ON osso.* TO 'rep'@'%' IDENTIFIED BY '123456';
FLUSH PRIVILEGES;Now let’s try to log in with the rep and run the update command
# login to mysql with user: rep
mysql -u rep -p --port=3307
# select the database and perform an update
use osso;
# > Database changed
update users set sign_in_count = 1000 where id=75;
# ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statemenCaveats
While the read_only option prevents most write operations, users with the SUPER privilege can still perform writes. Be cautious when granting SUPER privileges. Some operations, like system table maintenance or data definition language (DDL) operations, might still be possible depending on the user’s privileges. Always ensure that only trusted users have elevated privileges. By following these practices, you can maintain the integrity of your read replica setup, ensuring it remains synchronized with the master database without inadvertently accepting write operations.
Useful commands
The following commands are quite helpful for debugging:
Start and stop MySQL manually
# 1. find the process by port 3307
sudo lsof -i :3307
# > process_id
# 2. terminate the daemon
sudo kill -TERM process_id # sudo kill -KILL process_id
# 3. start the mysql
mysqld_safe --defaults-file=/etc/my-replica.cnf &Stop replication
# stop the slave from replicating data from the master
STOP SLAVE;
# stop the replication
RESET SLAVE ALL;
# show slave status;
SHOW SLAVE STATUS;