Let’s assume you have two scenarios.
- Full replication which allows you to switch from slave to master.
- 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.