Dual Master Mysql Replication ( Circular Replication )


We have 2 servers that will be configured as Circular Replication.
It means both servers act as master and slave to another server.

After we install mysql-server on both servers, check /etc/hosts should be same as both servers.
# more /etc/hosts
127.0.0.1 localhost
192.168.0.21 servera
192.168.0.22 serverb
192.168.0.23 dbhost

After that try to look at /etc/my.cnf
At Server A :
# more /etc/my.cnf
[MYSQLD]
basedir = /usr/local/mysql
datadir = /data/mysql/data
max_connections=10000
default-character-set=utf8
default-collation=utf8_bin

# InnoDB parameters
set-variable = innodb_buffer_pool_size=1500M
set-variable = innodb_additional_mem_pool_size=500M
set-variable = innodb_log_buffer_size=10M

log-bin
binlog-do-db=test
binlog-ignore-db=mysql

server-id=1
replicate-same-server-id = 0
auto-increment_increment = 2
auto-increment_offset = 1
master-host = serverb
master-user = replica
master-password = slave123
master-port = 3306

expire_logs_days = 10
max_binlog_size = 500M

[mysql.server]
user=mysql
basedir=/usr/local/mysql

[mysqld_safe]
err-log=/usr/local/mysql/log/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid

[mysql]
default-character-set=utf8

At Server B :
# more /etc/my.cnf
[MYSQLD]
basedir = /usr/local/mysql
datadir = /data/mysql/data
max_connections=10000
default-character-set=utf8
default-collation=utf8_bin

# InnoDB parameters
set-variable = innodb_buffer_pool_size=1500M
set-variable = innodb_additional_mem_pool_size=500M
set-variable = innodb_log_buffer_size=10M

log-bin=serverb-bin
binlog-do-db=test
binlog-ignore-db=mysql

server-id=2
replicate-same-server-id = 0
auto-increment_increment = 2
auto-increment_offset = 2
master-host = servera
master-user = replica
master-password = slave123
master-port = 3306

expire_logs_days = 10
max_binlog_size = 500M

[mysql.server]
user=mysql
basedir=/usr/local/mysql

[mysqld_safe]
err-log=/usr/local/mysql/log/mysqld.log
pid-file=/usr/local/mysql/mysqld.pid

[mysql]
default-character-set=utf8

If you want to add a database ( ex: dbex )that will be replicate, add this line at MYSQLD section.
-------------
binlog-do-db=dbex
-------------

start your sql server on both servers:
# /etc/init.d/mysql.server start

login to sql and add grant to user "replica" on both servers.
# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.33-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica'@'%' IDENTIFIED BY 'slave123';

restart your sql server on both servers:
# /etc/init.d/mysql.server restart

setting slave on server A :
- on server A, stop and reset slave first
mysql> stop slave;
mysql> reset slave;
- on server B, show master log file and position, before this lock the tables first.
mysql> flush tables with read lock;
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
File: serverb-bin.000001
Position: 98
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

ERROR:
No query specified

- on server A, set this master value, and then you can start slave.
mysql> CHANGE MASTER TO MASTER_LOG_FILE='serverb-bin.000001', MASTER_LOG_POS=98;
mysql> start slave;

- on server B, releasing read lock with
mysql> UNLOCK TABLES;

setting slave on server B :
- on server B, stop and reset slave first
mysql> stop slave;
mysql> reset slave;
- on server A, show master log file and position, before this lock the tables first.
mysql> flush tables with read lock;
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
File: servera-bin.000001
Position: 98
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

ERROR:
No query specified

- on server B, set this master value, and then you can start slave.
mysql> CHANGE MASTER TO MASTER_LOG_FILE='servera-bin.000001', MASTER_LOG_POS=98;
mysql> start slave;

- on server A, releasing read lock with
mysql> UNLOCK TABLES;


Successful replication can be check with :

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: servera
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: servera-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: serverb-relay-bin.000001
Relay_Log_Pos: 237
Relay_Master_Log_File: servera-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 237
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

ERROR:
No query specified

Popular posts from this blog

Howto configure boot device order on ILOM

To ensure the system can be installed using the DVD drive, the boot device order in the BIOS has to be configured correctly so that the DVD drive takes precedence over the internal hard drives. 1 Log into the ILOM. 2 Start the system if it is not already running: > start /SYS else reset the system: > reset /SYS 3 Log onto the system’s console: > start /SP/console 4 When prompted, press Esc 2 to enter setup. 5 Once the setup menu has loaded, navigate across to the Boot screen by using the right arrow key. 6 Using the down arrow key, select the Boot Device Priority item and press Enter. 7 Confirm that the item [CD/DVD] is higher in the list than the item [Hard Drive]. The order can be changed by selecting the relevant item and using the +/- keys to move the item up or down in the list. 8 Once the order is correct, save any changes made by pressing Esc and then navigate right to the Exit screen. Select the item Save Changes and Exit and press Enter, then confirm
Read more

SAN Switch Config Command

Login via console Fabric OS (swd77) swd77 console login: admin Password: Note: password default is "password" - set hostname swd77:admin> switchName swcdr1 Committing configuration... Done. swcdr1:admin> - set IP Address swcdr1:admin> ipaddrset Ethernet IP Address [192.168.0.25]: 192.168.0.11 Ethernet Subnetmask [255.255.255.0]: Fibre Channel IP Address [0.0.0.0]: Fibre Channel Subnetmask [0.0.0.0]: Gateway IP Address [192.168.0.25]: 0.0.0.0 Issuing gratuitous ARP...Done. 2008/07/14-18:46:43, [WEBD-1007], 494,, INFO, swcdr1, HTTP server will be restarted due to change of IP Address IP address is being changed...Done. Committing configuration...Done. - Configure basic parameter swcdr1:admin> switchDisable swcdr1:admin> configure Configure... Fabric parameters (yes, y, no, n): [no] y Domain: (1..239) [1] 22008/07/14-18:50:40, [FW-1424], 24,, WARNING, swcdr1, Switch status changed from HEALTHY to MARGINAL. 2008/07/14-18:50:40, [FW-1439], 25,, WARNING, swcdr
Read more

Howto cstm on HP-UX

If we want to show all machine information, just use cstm. # cstm Running Command File (/usr/sbin/stm/ui/config/.stmrc). -- Information -- Support Tools Manager Version C.46.05 (C) Copyright Hewlett Packard Co. 1995-2002 All Rights Reserved Use of this program is subject to the licensing restrictions described in "Help-->On Version". HP shall not be liable for any damages resulting from misuse or unauthorized use of this program. cstm> Map all device in this system with : cstm> map Dev Last Last Op Num Path Product Active Tool Status === ==================== ========================= =========== ============= 1 system system (1014) Information Successful 2 memory IPF_MEMORY (1014) Information Successful ------------ We want to know info about memory information cstm>sel d 2 cstm>
Read more