This may be awkward to set up, but will work once you're done.
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
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-idneeds to be unique among all connected master and slave servers.
replicate-do-dbis 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 = mysqlis generally recommended if you are not 100% sure you want to replicate the master's
mysqldatabase 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
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
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):
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).
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-ignore-db (see master configuration).
Restart your (slave) MySQL server
Load the dumped database
SOURCE the_dump.sqlin 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 5122defines 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_PORTare set to the slave's side of the SSH tunnel (localhost:3307).
MASTER_PASSWORDare the credentials of the previously created replication user.
MASTER_LOG_POSare 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.
You can check the status of your slave with
SHOW SLAVE STATUS.
If you don't get
Slave_IO_Running the replication is not working -- you need to check
/var/log/mysql.err and similar files.