Posted about 9 years ago. Visible to the public.

MySQL replication how-to

This may be awkward to set up, but will work once you're done.

Fun facts:

  • In case of a connection loss the slave will try to reconnect to the master server and resume replication for the next 24 hours
  • If you want to use your slave as a "real" MySQL server, you basically need to switch off replication (STOP SLAVE; RESET SLAVE; and reset your my.cnf) and restart the MySQL daemon.

Master server configuration

  • Create replication user
    In the MySQL shell:
    Copy
    CREATE USER 'replicator'@'%' IDENTIFIED BY 'some_password'; GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
  • Adjust MySQL configuration
    Edit /etc/mysql/my.cnf:
    Copy
    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.
    • When 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.
    • Setting 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

Dump the database for the slave

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:

    Copy
    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:

    Copy
    SHOW MASTER STATUS;

    The result might look like this:

    Copy
    +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1219 | | | +------------------+----------+--------------+------------------+
  • Dump the database (in a new terminal):

    Copy
    mysqldump -uroot -p -r the_dump.sql some_project_production
  • Remove the read lock (from the MySQL shell):

    Copy
    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).

Slave configuration

  • Edit /etc/mysql/my.cnf:

    Copy
    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

    • Copy the dump from the master to the slave (with scp)
    • Load it (for example SOURCE the_dump.sql in the MySQL shell)

Set up an SSH tunnel

  • Manually, for testing purposes:

    Copy
    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:

    Copy
    /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.
    • You should create an init script for this so the tunnel is set up upon the next server reboot.
    • Use keyfiles as you don't need to supply a password for connecting to the remote host.

Configure Replication

In the MySQL shell:

Copy
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.
  • Values for 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.

Start replicating

Copy
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.

Does your version of Ruby on Rails still receive security updates?
Rails LTS provides security patches for old versions of Ruby on Rails (3.2 and 2.3).

Owner of this card:

Avatar
Arne Hartherz
Last edit:
over 4 years ago
by Arne Hartherz
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more