MySQL Forums
Forum List  »  Replication

Replication setup (for the first time)
Posted by: Ekrem Onsoy
Date: May 23, 2014 02:36AM

Hello!

Although I am a senior SQL Server DBA and have a good experience with SQL Server Replication and Oracle Golden Gate, now I have a mission to set up MySQL replication between two MySQL Servers 5.1.67 running on CENT OS 6.3.

After reading related sections from two MySQL books and numerous articles on web, I prepared my action plan as follows.

Here, I am sharing it with you and asking for your opinions for the plan. As this will be the first time for me to set up replication with these products, I thought you may be generous to share me your experiences and thoughts.

First, please let me tell you why I did not prefer mysqldump but copying database files, because I do not have enough disk space for an extra operation. Also, downtime is not that important for this environment. I've got a 10 hours of maintenance window for this operation.

Please keep in mind that I have had absolutely no experience with Linux and MySQL before this week =)

In advance, thank you for your time!

I.Configure Master

1. edit /etc/mysql/my.cnf. Enable networking for MySQL,

#skip-networking

2. Add the following lines to the my.cnf

server-id = 1
log-bin=binlog
expire-logs-days=5
sync_binlog = 1

3. restart mysql
$ /etc/init.d/mysqld restart

4. log into the MySQL database as root and create a user with

replication privileges:

mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user IDENTIFIED BY 'xxx';
mysql>FLUSH PRIVILEGES;

5. At this point, for the sake of consistency, I am locking tables.

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

NOTE: Keep this prompt running in order for lock to be ACTIVE

e.g.

mysql> SHOW MASTER STATUS;
+——————–+———–+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB

|
+——————–+———–+————–+——————+
| FLTVM01-bin.000051 | 444132144 | |

|
+——————–+———–+————–+——————+
1 row in set (0.00 sec)

6. Shut down mysqld to provide consistency during copy operation

$ /etc/init.d/mysqld stop

7. Copy Database files from Master to Slave

-- I am not sure if I better use z parameter for compression or not?
$ rsync -hrtplz --rsh='ssh -p22579' --progress /var/lib/mysql root@SLAVE.domain.com:/var/lib

8. Rename the copied "mysql" system database in the SLAVE as "mysql_master" and then copy the original mysql. -- I am not sure if I better preserving the old one too? Do you think so? With this configuration, is mysql db replicated?

$ cd /var/lib/mysql
-- This is the mysql db came from MASTER
$ mv mysql mysql_master
$ cd ..
-- This mysql db belongs to the original installation on SLAVE
$ scp -r /var/lib/mysql_OLD/mysql /var/lib/mysql


II. Configure Slave

1. Stop mysqld

$ /etc/init.d/mysqld stop

2. Configure Slave configuration

server-id=2
master-host=MASTER.domain.com
master-user=repl_user
master-password=xxx
master-connect-retry=60

3. Start mysqld

$ /etc/init.d/mysqld start

4. Issue command Slave Stop and Change master

mysql> SLAVE STOP;
mysql> CHANGE MASTER TO MASTER_HOST='MASTER.domain.com', MASTER_USER='repl_user', MASTER_PASSWORD=’xxx’, MASTER_LOG_FILE='<LOG FILE>', MASTER_LOG_POS=<POS>;

6. Start Slave

mysql> START SLAVE;

7. Check for errors
#tail -f /var/log/mysqld.log

III. CHECKING & TESTING

status of the master can be known from mysql prompt:

mysql> SHOW MASTER STATUS;

and slave can be known by:

mysql> SHOW SLAVE STATUS;

Options: ReplyQuote


Subject
Views
Written By
Posted
Replication setup (for the first time)
1901
May 23, 2014 02:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.