MySQL Replication

Let’s assume you have two scenarios.

  1. Full replication which allows you to switch from slave to master.
  2. Partial replication which includes only selected database, having different users, etc.

Put these configuration settings in my.cnf configuration file or include this from another directory. MySQL can read additional settings. Look if it is not already set for you like in Debian/Ubuntu example:

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

In this case you can create new file /etc/mysql/conf.d/replication.cnf and put settings there.

[mysqld]

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log

## binlog-format set to row allows to use undelete records
#binlog_format            = mixed
binlog-format             = row

max_binlog_size           = 50M

log_slow_slave_statements = on
slave_compressed_protocol = on

replicate-do-db           = galaxy
replicate-ignore-db       = mysql.%

binlog_do_db              = galaxy
binlog_ignore_db          = mysql

For example you may have additional file to specify default character settings if you have different settings than UTF-8.

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

Create user for replication

CREATE USER 'replication'@'%' IDENTIFIED BY 'password';

Give it REPLICATION SLAVE permission for all or specified databases.

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'

Dump data for all databases you want to replicate.

mysqldump -u root -p --database wordpress --master-data > wordpress.sql
mysqldump -u root -p --database galaxy --master-data > galaxy.sql

You may want to use –all-databases option if you are creating full slave replication.

Set up slave server. To do this you need to change server-id. In my case slave was installed on Windows platform so modifications where done in file C:\ProgramData\MySQL\MySQL Server 5.6\my.ini.

# Server Id.
server-id=2

You have to restart MySQL service after change. You can check id of your slave server by checking @@server_id variable.

SELECT @@server_id

Set up master by executing following commands.

CHANGE MASTER TO
    MASTER_HOST='galaxy.alyx.pl',
    MASTER_USER='replication',
    MASTER_PASSWORD='password';

However after that you can see warning about not using secure protocol.

1759 Sending passwords in plain text without SSL/TLS is extremely insecure.

1760 Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

Import database to slave server either by running mysql command or by selecting Run script in MySQL Workbench which is a very nice tool for it using previously generated backup files.

Finally, start replication by running following query.

START SLAVE;
SHOW SLAVE STATUS;

On master you can see connections from slaves.

SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000019 | 2293 | galaxy | mysql |
+------------------+----------+--------------+------------------+
SHOW PROCESSLIST;
+-----+-------------+--------------------------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+--------------------------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| 66 | replication | 89-66-197-81.dynamic.chello.pl:13346 | NULL | Binlog Dump | 138 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 144 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+-----+-------------+--------------------------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+

Different database on slave

You may have different database name although it is not recommended. To do this, you may need to import previously dumped data to new name on slave and then use my.cnf / my.ini option.

replicate-rewrite-db = "galaxy->galaxy_backup"

After that, MySQL needs to be restarted. It should automatically bring slave back to work so you will see new updates in your database almost immediately.