Replication in MySQL is keeping databases somewhat “in sync”, minus the latency at least.. So let’s pray to the computer gods (maybe Ava in Ex Machina?) 🙂 that we don’t run into any weird, complex, race conditions..
There are multiple ways to do data replication in MySQL and if your database isn’t very big, then true MySQL replication is probably overkill.
First, you could just write a bash script that runs as a cronjob on the host machine to mysqldump and save. Then on the client write and run a bash script as a cronjob to grab from the host and import the mysqldump into the database. But this obviously isn’t going to be ideal with anything other than small ‘toy’ databases.
So we need some awesome automated replication-like feature that only updates child databases with changes that are made in the parent database as it changes. Have no fear, MySQL has had such a feature built into its core pretty much since its inception!
Now, if you’re reading this, I’m sure you know the benefits of an SSH tunnel, but if you don’t, well…. SSH tunneling is a cheap (and ghetto) way to keep our data transfer done in a secure fashion while at the same time minimizing open ports and keeping our attack surface minimal.
Enough said.. So let’s get started!
**NOTE: Setting up an SSH tunnel is beyond the scope of this tutorial unfortunately, so search for one of the million tutorials on how to setup up a SSH Tunnel (with keys) if you need help and then come back..
**ALSO NOTE: Data replication is NOT a substitution for a proper data backup policy.. You’ve been warned…
First we need to configure our Master. The “master” MySQL server is the one that will be changing. The data that changes in the Master will then propagate and be replicated in the slave(s).
Edit /etc/my.cnf
# place this is under [mysqld] and NOT [mysqld_safe]
[mysqld]
server-id=1
log-bin=mysql-bin
# binary logs take massive disk space, this removes logs after 10 days.
# Change to an appropriate time frame for your needs.
expire_logs=10
# change database_name to the db name to be replicated
binlog-do-db=database_name
Next, let’s create a MySQL user to specifically do the replication.
Create replication user
# login to mysql
]$ mysql -u root -p
# create a user called replicator
# (he's the king of all replicators,
# in case you were wondering..) :D
mysql> CREATE USER 'replicator'@'127.0.0.1'
IDENTIFIED BY 'Aw3someSecurePassw0rd111!';
Next, grant permissions to the new replicator account.
NOTE: We only create this account on the master, this will be the account the slave uses to access the master via the slave machine.
mysql> GRANT REPLICATION SLAVE ON *.*
TO 'replicator'@'127.0.0.1' IDENTIFIED
BY 'Aw3someSecurePassw0rd111!';
mysql> quit;
Now restart the MySQL server
]$ sudo service mysqld restart
Next, we need to check our master’s status.
Check Master server status
]$ mysql -u root -p
mysql> USE database_name;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
You should see an output similar to:
+———————–+———-+—————+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+———————–+———-+—————+——————+
| mysql-bin.000001 | 107 | database_name | |
+———————–+———-+—————+——————+
Take note of what is under “File”, meaning write down mysql-bin.000001 along with the “Position” of 107. You need these values on the slave(s).
Next perform a mysqldump of the database that you’re replicating. If you need more help than this, google how to mysqldump a mysql database and pick one of the million tutorials. Umm, make sure you bring air freshener. 😛
**NOTE: Don’t exit from the mysql terminal, open a new terminal to do the dump. Trust me.
Mysqldump the database
]$ mysqldump -u root -p
--opt database_name > database_name.sql
Next copy the db dump to your slave machine and then create the same database on the slave and import the dump. If you need help on this, there’s also many tutorials available by asking the google god.. Errr I mean Ava. 🙂
Now we need to edit /etc/my.cnf on the slave machine
Add the following to /etc/my.cnf
# again make sure all of this is under [mysqld] and NOT [mysqld_safe]
[mysqld]
server-id=2
Next we need to restart the mysql daemon
Restart MySQL
]$ sudo service mysqld restart
Now on the slave let’s create an ssh tunnel (assuming you have ssh working properly)
SSH tunnel on the slave
# IP ADDRESS: ip address of the master
# PORT: any port you have free
]$ sudo ssh -f -L :127.0.0.1:3306 -N
# example: ssh -f 23.23.29.129 -L 4444:127.0.0.1:3306 -N
This will ssh to the master server
-f requests ssh to go to the background
-L specifies the local port (client/slave)
to forward to the remote host on 3306
-N don’t execute a remote command
Next let’s test the tunnel
]$ mysql -h 127.0.0.1 -P 4444 -u any_valid_user -p
If everything is valid and good to go with the tunnel, then we
need to login to mysql on the client (slave) and configure a few
things..
On a MySQL slave server type
# substitute your values that were output
# earlier when checking the master's status
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107,
MASTER_HOST='127.0.0.1', MASTER_PORT=3305,
MASTER_USER='replicator',
MASTER_PASSWORD='Aw3someSecurePassw0rd111!';
# start the slave
mysql> start slave;
# and then type
mysql> show slave status\G
If everything went according to plan, you should see:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
And that’s it! 😀
Everything written to the master db should propagate to the slave(s) almost instantly.
**Note: With this basic setup, do not write to a slave or replication will more than likely break, induce problems and ultimately a headache…
You can add read_only flags, replicate only specific tables, and just have a more detailed and complicated use case, but that’s out of the scope of this tutorial.
This is just a basic tutorial to help get your feet wet! But if you have a question or need help on a more complex setup, just ask! 😀
On a MySQL slave server type it says “MASTER_PORT=3305”.
If I’m not mistaking it should be “MASTER_PORT=4444”
Other than that, thanks a lot for the tutorial.