MySQL database replication

It was a long time since I last posted technical tips in my blog. I’ll post one this time for some reason. Topic? “MySQL database replication”

As I commence implementation and testing for my internal project in relation to High availability I am concurrently doing an internal project with regards to MYSQL database replication. I will be happy sharing this information; not only will this be very exciting but if given utmost attention, will shed aid for every database administrator that using mySQL server.

As you read along, the things and steps are scribbled to make this solution possible.

In my example below, I used the following information.

Database name to be on mirror/replicated: mericdb

My Network/Server Information:
Master: Primary Database
IP ADDRESS: 192.168.69.108
Server name: node01

Slave: Secondary Database
IP ADDRESS: 192.168.69.208
Server name: node02

My SLAVE USER NAME: mericuser

Another thing that we need to note is that I already installed my web and database server using “yum” and downloaded phpmyadmin to handle the administration of My MySQL over the Web.

wget http://jaist.dl.sourceforge.net/sourceforge/phpmyadmin/phpMyAdmin-2.11.6-all-languages-utf-8-only.tar.gz

On My MASTER SERVER


1. Configure Master /etc/my.cf:
[root@node01 ~]# vi /etc/my.cnf

Under [mysqld]
log-bin
binlog-do-db=mericdb
server-id=1
Then let’s restart MySQL:
[root@node01 ~]# service mysqld restart
2. Then, log into the MySQL database as root and create a user with replication privileges:
mysql -u root -p
Enter password:
Now we are on the MySQL shell.
GRANT REPLICATION SLAVE ON *.* TO ' mericuser'@'%' IDENTIFIED BY '123'; FLUSH PRIVILEGES;
Next (still on the MySQL shell), do this:
USE mericdb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
The last command will display something like this:
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| node01-bin.000002 | 282 | mericdb | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Write down this information specifically the “File” and the “Position”
In this case “node01-bin.000002” and “282”

3. Getting data undo SLAVE

[root@node01 ~]#mysqldump -u root -p --opt mericdb > mericdb.sql
[root@node01 ~]#scp mericdb.sql root@192.168.69.208:/tmp/
Enter password:
Okay, now let’s unlock the tables in mericdb:
mysql -u root -p
Enter password:
UNLOCK TABLES;
quit;
(magkape ka muna, tapos cr na rin… - for my international readership: get a cup of batangas coffee, then take a long pee)

On My SLAVE SERVER
1. On the slave server, we first need to create the database mericdb:
[root@node02 ~]# mysql -u root -p
Enter password:
CREATE DATABASE mericdb;
quit;
[root@node02 ~]# mysql -u root -p mericdb < /tmp/mericdb.sql
2. Configure server /etc/my.cf:
[root@node02 ~]# vi /etc/my.cnf

Under [mysqld]

server-id=2
master-host=192.168.69.108
master-connect-retry=60
master-user= mericuser
master-password=123
replicate-do-db=mericdb

Then, we restart MySQL:
[root@node02 ~]# service mysqld restart
[root@node02 ~]# mysql -u root -p
Enter password:
LOAD DATA FROM MASTER;
quit;

[root@node02 ~]# mysql -u root -p
Enter password:
SLAVE STOP;
In the next command (still on the MySQL shell), you have to replace the values appropriately:
CHANGE MASTER TO MASTER_HOST='192.168.69.108', MASTER_USER=' mericuser ', MASTER_PASSWORD='123', MASTER_LOG_FILE=' node01-bin.000002', MASTER_LOG_POS=282;
• MASTER_HOST is the IP address of the master database
• MASTER_USER is the user we granted replication privileges on the master.
• MASTER_PASSWORD is the password of MASTER_USER on the master.
• MASTER_LOG_FILE is the file MySQL given back (resulting) when you run /running SHOW MASTER STATUS; on the master.
• MASTER_LOG_POS is the position MySQL given back when you run/ running SHOW MASTER STATUS; on the master.
For our Final steps, let’s start the slave server. Still on the MySQL shell we run
START SLAVE;
quit;
Finished!
Every time we update mericdb on the master, all changes will be replicated to mericdb on the slave. I use my phpmyadmin tool to test and update my databases.

Now, you know why it took me a while before it got to post again….
Happy replicating….