Installation and Configuration of MySQL Database Master - Master Replication Centos 6.5
========================================================================================
https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication
https://www.howtoforge.com/mysql_master_master_replication
http://tecadmin.net/how-to-set-up-mysql-master-master-replication/
https://www.linode.com/docs/databases/mysql/mysql-master-master-replication
===============================================
MySQL Database Master - Master Replication Centos 6.5:
Master-1 IP: 192.168.100.119
Master-2 IP: 192.168.100.120
===========================================
# On Master-1 IP: 192.168.100.119:
=================================
1. CentOS with yum updated
2. MySQL installed with service up
3. Static IP
4. For safer side we can take all mysql database backup
5. First Take master-1 db backup and restore it on second master server
========================================================================
1. Configure On Master Server - 1:
Add Below Line in /etc/my.cnf
server_id= 2154 # Enter Unique Server ID
auto_increment_increment=2
auto_increment_offset=1
innodb_buffer_pool_size=10G <-- check server physical ram and give atleast 20-40 % ram for mysql.... now physical memory is 30 G
log-bin=mysql-bin
log_bin_index=log-bin-index
sync_binlog=1
expire_logs_days=10
log_bin_trust_function_creators=1
binlog_format=MIXED
relay_log=mysql-relay
relay_log_index=relay-log-index
skip-slave-start
=====================================
[root@test-master /]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id= 2154 # Enter Unique Server ID
auto_increment_increment=2
auto_increment_offset=1
log-bin=mysql-bin
log_bin_index=log-bin-index
sync_binlog=1
expire_logs_days=10
log_bin_trust_function_creators=1
binlog_format=MIXED
relay_log=mysql-relay
relay_log_index=relay-log-index
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
========================================
2. Verify Once Again:
# cat /etc/my.cnf
4. Now, Restart MySQL Service
# service mysqld stop
# service mysqld start
3. Login to MySQL Server Prompt and Create Replication User: We need to create this user on both server.
# mysql -u root -p
mysql> Create user replicant@'%' identified by 'passwddd';
mysql> grant replication slave on *.* to 'replicant'@'%';
mysql> flush privileges;
mysql> select version();
mysql> show variables like 'server%';
mysql> show databases;
mysql> quit
3. Login to mysql and change master host:
mysql> select user, host from mysql.user;
mysql> show variables like '%uuid%';
mysql> select version();
mysql> show variables like 'server%';
mysql> show databases;
mysql> show slave status \G
mysql> stop slave;
mysql> CHANGE MASTER TO
> MASTER_HOST='192.168.100.120', # master server ip
> MASTER_USER='replicant', # master server replicatation user
> MASTER_PASSWORD='passwd', # master server replicatation user password
> MASTER_PORT=3306, # mysql port number
> MASTER_LOG_FILE='mysql-bin.000014', # fire command on master server # show master status; & enter output here
> MASTER_LOG_POS=106, # fire command on master server # show master status; & enter output here
> MASTER_CONNECT_RETRY=10;
Note: Fire above command on second master server.
OR Direct fire commands:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.83', MASTER_USER='replicant', MASTER_PASSWORD='password', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000014', MASTER_LOG_POS=79299, MASTER_CONNECT_RETRY=10;
mysql> start slave;
mysql> show slave status \G
mysql> show databases;
mysql> \q
4. Now, Restart MySQL Service
# service mysqld stop
# service mysqld start
5. Login to MySQL & Check Master Databaser Server Status:
# mysql -u root -p
mysql> show master status;
mysql> SELECT table_schema "Database Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES WHERE table_schema = 'db_name'GROUP BY table_schema;
mysql> SELECT table_schema "Database Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES -- WHERE table_schema = 'db_name'GROUP BY table_schema;
mysql> SELECT table_schema "Database Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES GROUP BY table_schema;
mysql> \q
6. Login MySQL database and check database size:
# mysql -u root -p
mysql> SELECT table_schema "Database Name",sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB" FROM information_schema.TABLES WHERE table_schema like '%wom_%'
GROUP BY table_schema;
[Note: '%test_%' :- test is database starting name ]
7. Finaly Check Master Status:
mysql> show master status;
mysql> stop slave;
mysql> start slave;
mysql> show slave status\G
=====================================
#####################################
=====================================
On Master-2 IP: 192.168.100.120
=================================
1. CentOS with yum updated
2. MySQL installed with service up
3. Static IP
4. For safer side we can take all mysql database backup
=====================================
1. Configure On Master Server - 2:
Add Below Line in /etc/my.cnf
server_id=9453
auto_increment_increment=2
auto_increment_offset=2
relay_log=mysql-relay
relay_log_index=relay-log-index
innodb_buffer_pool_size=10G <-- check server physical ram and give atleast 20-40 % ram for mysql.... now physical memory is 30 G
log-bin=mysql-bin
log_bin_index=log-bin-index
sync_binlog=1
expire_logs_days=10
log_bin_trust_function_creators=1
binlog_format=MIXED
=========================================
[root@test-slave /]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id=9453
auto_increment_increment=2
auto_increment_offset=2
relay_log=mysql-relay
relay_log_index=relay-log-index
skip-slave-start
log-bin=mysql-bin
log_bin_index=log-bin-index
sync_binlog=1
expire_logs_days=10
log_bin_trust_function_creators=1
binlog_format=MIXED
#replicate only below DBs
#replicate_do_db = database1
#replicate_do_db = testnd
#replicate_do_db = rathod
#replicate_ignore_db=mysql
#replicate_ignore_db=information_schema
#replicate_ignore_db=performance_schema
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
:wq
==========================================
3. Now, Restart MySQL Service
# service mysqld stop
# service mysqld start
2. Login to MySQL Server Prompt and Create Replication User:
# mysql -u root -p
mysql> Create user replicant@'%' identified by 'r3pl!c@n7';
mysql> grant replication slave on *.* to 'replicant'@'%';
mysql> flush privileges;
mysql> select version();
mysql> show variables like 'server%';
mysql> show databases;
mysql> quit
3. Login to mysql and change master host:
mysql> select user, host from mysql.user;
mysql> show variables like '%uuid%';
mysql> select version();
mysql> show variables like 'server%';
mysql> show databases;
mysql> show slave status \G
mysql> stop slave;
mysql> CHANGE MASTER TO
> MASTER_HOST='192.168.100.119', # master server ip
> MASTER_USER='replicant', # master server replicatation user
> MASTER_PASSWORD='r3pl!c@n7', # master server replicatation user password
> MASTER_PORT=3306, # mysql port number
> MASTER_LOG_FILE='mysql-bin.000002', # fire command on master server # show master status; & enter output here
> MASTER_LOG_POS=120, # fire command on master server # show master status; & enter output here
> MASTER_CONNECT_RETRY=10;
OR Direct fire commands:
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.251', MASTER_USER='replicant', MASTER_PASSWORD='r3pl!c@n7', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=103111, MASTER_CONNECT_RETRY=10;
mysql> start slave;
mysql> show slave status \G
mysql> show databases;
mysql> \q
========================================
========================================
Testing:
Now, Finally Once MySQL Databse Replication Completed then Just test with below commands, is it working or now:
On Master Server:
mysql> show databases;
mysql> use testdb;
mysql> show tables;
mysql> create table test_test(id int(10));
mysql> show tables;
On Slave Server Check replication done or not:
# mysql -u root -p
mysql> use test;
mysql> show tables;
If created table showing on slave server that means your replication is successfully configured.
Try to create database on both server and test it.
========================================================================================================================
STOP SLAVE;
set global sql_slave_skip_counter=1;
START SLAVE;
show slave status \G
No comments:
Post a Comment