This may be awkward to set up, but will work once you're done.
Fun facts:
STOP SLAVE; RESET SLAVE;
and reset your my.cnf) and restart the MySQL daemon.Create replication user
: In the MySQL shell:
CREATE USER 'replicator'@'%' IDENTIFIED BY 'some_password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
Adjust MySQL configuration
: Edit /etc/mysql/my.cnf
:
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
replicate-do-db = some_project_production
replicate-do-db = other_project_production
replicate-ignore-db = mysql
server-id
needs to be unique among all connected master and slave servers.replicate-do-db
is provided only the chosen databases will be replicated; this can be set inside both the master and the slave configuration -- setting it on the master means a smaller binlog.replicate-ignore-db = mysql
is generally recommended if you are not 100% sure you want to replicate the master's mysql
database onto slaves; on Debian systems (and derivates like Ubuntu) there is a maintenance user which uses a different password on every system. Unless you change it to be the same among masters and slaves you most likely want to skip the mysql
database.Restart your MySQL server
You can skip the following and use LOAD DATA FROM MASTER
on the slave. Although, this is deprecated and may result in data loss!
Stop your database via the MySQL shell:
FLUSH TABLES WITH READ LOCK;
Do not close the shell as this would remove the read lock we just applied!
Print and write down the current replication status. You need the File
and Position
values for the slave:
SHOW MASTER STATUS;
The result might look like this:
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 1219 | | |
+------------------+----------+--------------+------------------+
Dump the database (in a new terminal):
mysqldump -uroot -p -r the_dump.sql some_project_production
Remove the read lock (from the MySQL shell):
UNLOCK TABLES;
The master server will now resume operations. New queries will be appended to the log file and can be processed by the slave later on (which knows at which state the database was dumped).
Edit /etc/mysql/my.cnf
:
server-id = 2
replicate-do-db = some_project_production
replicate-ignore-db = mysql
Take care you pick a new unique identifier for server-id
. You may also set replicate-do-db
and replicate-ignore-db
(see master configuration).
Restart your (slave) MySQL server
Load the dumped database
scp
)SOURCE the_dump.sql
in the MySQL shell)Manually, for testing purposes:
ssh -L 3307:localhost:3306 10.50.0.101
This maps the local port 3307 to port 3306 on 10.50.0.101 (you can check the connection via telnet localhost 3307
)
Is the ssh tunnel working you should use a tool like autossh
to reconnect the ssh tunnel in case of a connection loss:
/usr/local/bin/autossh -f -M 5122 -g -N -C -L 3307:localhost:3306 10.50.0.101 -i /root/ssh_keys/keyfile -l username
-M 5122
defines a port for monitoring if the connection is up.In the MySQL shell:
CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3307, MASTER_USER='replicator', MASTER_PASSWORD='some_password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1219;
MASTER_HOST
and MASTER_PORT
are set to the slave's side of the SSH tunnel (localhost:3307).MASTER_USER
and MASTER_PASSWORD
are the credentials of the previously created replication user.MASTER_LOG_FILE
and MASTER_LOG_POS
are those you wrote down above (take care to provide the log position as a number, not a string).Those data will reside in /var/lib/mysql/master.info
and be loaded upon the next start of the MySQL slave server.
SLAVE START;
You can check the status of your slave with SHOW SLAVE STATUS
.
If you don't get Yes
for Slave_IO_Running
the replication is not working -- you need to check /var/log/mysql.err
and similar files.