UnixServerAdmin

Server Administration & Management

How to configure MySQL Master-Slave Replica

Master IP :- 192.168.1.10
Slave IP :- 192.168.1.20
Slave Server Username :- replication
Password of Username :- redhat

#######################################################################
On MySQL Master Server

Edit my.cnf file

# vim /etc/my.cnf
———————————————————————————————-
log-bin     # is the binary log basename to generate binary log file names.
binlog-do-db=<database name>     # input the database which should be replicated
binlog-ignore-db=mysql     # input the database that should be ignored for replication
server-id = 199     # option is used in replication to enable master and slave servers to identify themselves uniquely. On the master and #each slave, you must use the server-id option to establish a unique replication ID in the range from 1 to 231
———————————————————————————————-

# grant replication slave on *.* to ‘replication’@192.168.1.20 identified by ‘redhat’;

# show master status;
+—————————+————+———————-+—————————+
| File                               | Position | Binlog_Do_DB  | Binlog_Ignore_DB  |
+—————————+————+———————-+—————————+
|mysql-bin.000027    |      265 |                                   |                                     |
+—————————+————+———————-+—————————+
1 row in set (0.00 sec)

#######################################################################
On MySQL Slave Server

Edit my.cnf file

# vim /etc/my.cnf
———————————————————————————————-
log-bin     # is the binary log basename to generate binary log file names.
binlog-do-db=<database name>     # input the database which should be replicated
binlog-ignore-db=mysql     # input the database that should be ignored for replication
log-slave-updates     # makes the slave log the replicated events to its binary log
server-id = 201     # option is used in replication to enable master and slave servers to identify themselves uniquely. On the master and #each slave, you must use the server-id option to establish a unique replication ID in the range from 1 to 231
———————————————————————————————-

# CHANGE MASTER TO MASTER_HOST=’192.168.1.20′, MASTER_USER=’replication’, MASTER_PASSWORD=’redhat’, MASTER_LOG_FILE=’mysql-bin.000027′, MASTER_LOG_POS=265;

mysql> start slave;
mysql> show slave status G;
————————————-
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
————————————-
mysql> reset slave;
mysql> mysqlstop slave;
#######################################################################

Error:- Slave_IO_Running: Connecting

Note:- Eventually, I find the reason. The password is too long. So please try to use a short password and it will be work. I think mysql command may truncate the password if it is too long, but it appears the replication didn’t do that for you.

November 11, 2011 - Posted by | MySQL | ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: