Part 1: Master-Master Replication
Setting up master-master replication is like setting up each master to be a slave of the other. These directions detail the steps on how to set up master-master replication between two nodes.
Step 1: Setup config file for each master master
Edit config files for first master server (/etc/mysql/my.cnf) to include the following settings. Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). You may already have some of these values in your my.cnf, especially the server-id)
Note that server-id needs to be unique. auto_increment_increment and auto_increment_offset are needed to avoid primary key conflicts on auto increment columns. One of the master will have auto_increment_increment = 2, auto_increment_offset = 1 while the second master will have auto_increment_increment = 2, auto_increment_offset = 2.
First Master:
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 1
max_binlog_size = 100M
binlog_format=row
Second Master:
[mysqld]
server-id=2
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 2
max_binlog_size = 100M
binlog_format=row
It may be necessary to restart the MySQL service on each master if this is the first time setting up replication on this master.
/etc/init.d/mysql restart
If MySQL fails to start, check the MySQL error log for details. Also, check to make sure that each of the log files, .info files and .index files in the above added parameters exists and have the correct file ownership of mysql.mysql.
You can fix the ownership like this:
chown -R mysql.mysql /var/log/mysql
Step 2: Check the status of the first master:
show master status;
Copy the values to a notepad window for possible use later.
Step 3: Create a replication user on both master servers
CREATE USER 'replication'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;
Step 4: Test that the replication user can connect to each server
mysql -u replication -p -h YOURMASTERNODE
Step 5: Create a dumpfile from the master
Run mysqldump on one of the Master servers (note the option of --master-data in the mysqldump command, this is needed to give you the point in time from which to start replication, this will do some locking so be careful when running it).
Also note that choosing --all-databases will dump all databases including the mysql schema, performance schema and information schema. The MySQL schema has the privileges and users which if restored on your slave it will over-write any existing users. To preserve your user permissions, it would be better to dump only the specific databases you want so as to not include the MySQL, performance_schema and information_schema using the --databases option.
mysqldump -u username -p --master-data --all-databases > dump.sql
OR
# Copy this into a bash script file on your server and execute it:
#################################################################################
MYSQL_USER=root
MYSQL_PASS=PASSWORD
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
MYSQLDUMP_OPTIONS="--master-data --routines --triggers --events --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > dump.sql
#################################################################################
Step 6: Restore dumpfile from the master on the slave
If you are only importing one database you would run the restore like this:
mysql -u username -p DATABASENAME < dump.sql
Step 7: Give yourself a motivational pep talk
Step 8: Configure Slave Process
Here is how you would get the first 25 lines from your dump file:
head -25 dump.sql
It should have something like this:
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Copy those values.
Log onto your second master server and use the above copied values to configure it as a slave of the first master server. This is the command you will run but replacing the appropriate values for MASTER_HOST, MASTER_USER and MASTER_PASSWORD (change the MASTER_LOG_FILE and MASTER_LOG_POS to match up with the values extracted from the dump file or from Step 2):
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE2',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Step 9: Start Slave Process on first master
mysql> start slave;
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
If you get errors, reset the slave and redo step 8:
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
Step 10: Slave Status
mysql> show slave status
Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If "Slave_SQL_Running" shows "No", then look in the "Last SQL Error" column. There might be one query causing problems with replication and you can tell MySQL to ignore it and keep going:
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> show slave status;
If you are setting up replication on a VM that was copied from another (vmware, vagrant, virtual box) system you may get this error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
In order to resolve you will need to remove the file at /var/lib/mysql/auto.cnf on one or more of your servers and restart MySQL. MySQL will automatically regenerate that file with a unique UUID when it restarts.
Stop and Start the slave again.
Step 11: Test the replication
CREATE DATABASE IF NOT EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test`. `test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`attribute` varchar(45) NOT NULL COMMENT 'The attribute',
`value` varchar(255) DEFAULT NULL COMMENT 'The value of the attribute',
`date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `IDX_attribute` (`attribute`)
) ENGINE=InnoDB CHARACTER SET='utf8' COLLATE='utf8_unicode_ci' COMMENT='This is a test table for testing replication';
SET NAMES utf8;
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ', @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'TEST DATA', 'English: test - Japanese: 試し - Chinese: 试验 - Arabic: اختبار', now());
SELECT * FROM `test`.`test_table`;
You should notice that the id column has all odd numbers because there were written while connected to the first master.
Step 12: Check the status of the second master
Copy the values to a notepad window.
Step 13: Repeat slave process on second master
Step 14: Test auto-increment offsets
SET NAMES utf8;
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ', @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
SELECT * FROM `test`.`test_table`;

You should notice that the values from the first master replicated over with the odd numbers and the values written while connected to the second master have all even numbers. This is because of the offset values provided in the my.cnf file.
Part 2: Master-Slave Replication
Setting up Master - Slave Replication
Step 1: Setup config file for master
[mysqld]
server-id=1
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
#auto_increment_increment = 2
#auto_increment_offset = 1
max_binlog_size = 100M
binlog_format=row
It may be necessary to restart the MySQL service on the master if this is the first time setting up replication on this master.
/etc/init.d/mysql restart
Step 2: Check that status of the master
Copy the values to a notepad window for possible use later.
Step 3: Create a replication user on the master server
CREATE USER 'replication'@'%' IDENTIFIED BY 'slavepass';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;
Step 4: Setup config file for slave(s)
Edit the my.cnf for the slave(s) (/etc/mysql/my.cnf). Some of the values may vary on your server, such as the log-bin location, the binlog format (STATEMENT/MIXED instead of ROW). Each slave server-id needs to be unique.
[mysqld]
server-id=3
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-index = /var/log/mysql/log-bin.index
log-error = /var/log/mysql/error.log
relay-log = /var/log/mysql/relay.log
relay-log-info-file = /var/log/mysql/relay-log.info
relay-log-index = /var/log/mysql/relay-log.index
expire_logs_days = 2
log_slave_updates = 1
max_binlog_size = 100M
binlog_format=row
read_only=1
Restart the MySQL service on the slave after making changes. Setting the slave to read_only is only effective against users that do not have SUPER access.
/etc/init.d/mysql restart
If MySQL fails to start, check the MySQL error log for details. Also, check to make sure that each of the log files, .info files and .index files in the above added parameters exists exist and have the correct file ownership of mysql.mysql.
Step 5: Create a dumpfile from the master
Run mysqldump on the Master server (note the option of --master-data in the mysqldump command, this is needed to give you the point in time from which to start replication, this will do some locking so be careful when running it).
Also note that choosing --all-databases will dump all databases including the mysql schema. The MySQL schema has the privileges and users which if restored on your slave it will over-write any existing users. It may be better to dump only the specific databases you want so as to not include the MySQL, performance_schema and information_schema using the --databases option.
mysqldump -u username -p --master-data --all-databases > dump.sql
OR
# Copy this into a bash script file on your server and execute it:
#################################################################################
MYSQL_USER=root
MYSQL_PASS=PASSWORD
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
DBLISTFILE=/tmp/DatabasesToDump.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
MYSQLDUMP_OPTIONS="--master-data --routines --triggers --events --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} > dump.sql
#################################################################################
Step 6: Restore dumpfile from the master on the slave
If you are only importing one database you would run the restore like this:
mysql -u username -p DATABASENAME < dump.sql
Step 7: Connection Testing (Slave to Master)
mysql -u replication -p -h YOURMASTERNODE
Step 8: Configure Slave Process
head -25 dump.sql
It should have something like this:
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Copy those values.
Log onto the slave server. Use the above copied values to configure the slave like this with the appropriate values for MASTER_HOST, MASTER_USER and MASTER_PASSWORD (change the MASTER_LOG_FILE and MASTER_LOG_POS to match up with the values extracted from the dump file):
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Step 9: Start Slave
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
If you get errors, reset the slave and redo step 8:
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
CHANGE MASTER TO MASTER_HOST='YOURMASTERNODE',MASTER_USER='replication', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysqld-bin.000117', MASTER_LOG_POS=824943477;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
Step 10: Slave Status
Make sure to check the below parameter status should be "YES" and the remaining values are appropriate.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
If "Slave_SQL_Running" shows "No", then look in the "Last SQL Error" column. There might be one query causing problems with replication and you can tell MySQL to ignore it and keep going:
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;
mysql> show slave status;
If you are setting up replication on a VM that was copied from another (vmware, vagrant, virtual box) system you may get this error:
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
In order to resolve you will need to remove the file at /var/lib/mysql/auto.cnf on one or more of your servers and restart MySQL. MySQL will automatically regenerate that file with a unique UUID when it restarts.
Stop and Start the slave again.
Step 11: Test the replication
-- SQL to run on the MASTER:
CREATE DATABASE IF NOT EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test`. `test_table` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`attribute` varchar(45) NOT NULL COMMENT 'The attribute',
`value` varchar(255) DEFAULT NULL COMMENT 'The value of the attribute',
`date_created` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `IDX_attribute` (`attribute`)
) ENGINE=InnoDB CHARACTER SET='utf8' COLLATE='utf8_unicode_ci' COMMENT='This is a test table for testing replication';
SET NAMES utf8;
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'host name ', @@hostname, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'server id ', @@server_id, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'version ', @@version, now());
INSERT INTO `test`. `test_table` (`id`, `attribute`, `value`,`date_created`) VALUES (NULL, 'TEST DATA', 'English: test - Japanese: 試し - Chinese: 试验 - Arabic: اختبار', now());
-- SQL to run on both the MASTER and SLAVE to verify replication worked:
SELECT * FROM `test`.`test_table`;
No comments:
Post a Comment