MySQL Replication - Master and Slave


We have 2 servers that will be configured as Master and Slave.
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 Master :
# 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=servera-bin
binlog-do-db=dsc_fleet
binlog-do-db=dsc_friend
binlog-do-db=dsc_info
binlog-do-db=dsc_marketing
binlog-ignore-db=mysql

server-id=1
#information for becoming slave.
master-host = serverb
master-user = replica
master-password = slave123
master-port = 3306
relay-log=servera-relay-bin
# remember to add line above

[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 Slave :
# 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=dsc_fleet
binlog-do-db=dsc_friend
binlog-do-db=dsc_info
binlog-do-db=dsc_marketing
binlog-ignore-db=mysql

server-id=2
#information for becoming slave.
master-host = servera
master-user = replica
master-password = slave123
master-port = 3306
relay-log=serverb-relay-bin
# remember to add line above

[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

if you still found problem that Slave can't be started with "start slave" at mysql prompt for slave server, like this :

mysql> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

try to reset slave
mysql> reset slave;

Succesfull replication can be check with :
on Master server :
mysql> SHOW MASTER STATUS \G;
*************************** 1. row ***************************
File: servera-bin.000013
Position: 98
Binlog_Do_DB: dsc_fleet,dsc_friend,dsc_info,dsc_marketing
Binlog_Ignore_DB: mysql
1 row in set (0.00 sec)

ERROR:
No query specified

on Slave server :
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.000013
Read_Master_Log_Pos: 98
Relay_Log_File: serverb-relay-bin.000039
Relay_Log_Pos: 237
Relay_Master_Log_File: servera-bin.000013
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

Thanks to Mas Yudy for help me at this issue.

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

Installing Depot files on HP-UX 11.23 ia64

Download your depot files from http://hpux.connect.org.uk/ based on your machine type. Download it, for example : bash-3.2-ia64-11.23.depot.gz, put it on /tmp. Install it with 2 option : 1. It will display SAM # swinstall -s /tmp/bash-3.2-ia64-11.23.depot.gz 2. It don't display SAM, so you get quick way # swinstall -s /tmp/bash-3.2-ia64-11.23.depot.gz bash Don't forget to install dependencies for this depot file, it's different from Redhat, who will reject rpm that missed dependencies. HP-UX accept all depots without any warning, but you must install dependencies yourself. You can know dependencies on http://hpux.connect.org.uk/, when you download, look at any dependencies for this depot file. Check depot files that already installed on your system # swlist Somehow any error happened when installing from directory another than /tmp, for example : /data. You should register that directory to depot. # cd /data # swreg -l depot `pwd` If it still fails, look at my post before a...
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