Dec 1

MySQL Replication (the easy way)

Tags:

———————————————————————
– OVERVIEW / THOUGHT PROCESS –
———————————————————————

Recently, there was a hardware failure on my primary mail server, which left me (and my clients) without email for an entire day.  Although it was possible to use one of my backups to get us up and running again, I decided to take another route.  After some fun research, and a lot of playing around, I decided to create a redundant server just in case this hardware failure ever happens again.

This article will concentrate on the procedures that I used to replicate mysql through a master/slave server.  Each server has its own public IP address and is housed in separate areas of the continent.  Using a load-balancer, I am able to send all of the email/web traffic to the master… until the master no longer responds.  When it doesn’t respond, the load balancer will redirect the traffic to the slave, which is replicated through mysql master/slave, and Lsync/Rsync.

———————————————————————
– THE PROCEDURES –
———————————————————————

1. change the master/slave unique number:

MASTER:
shell> sudo nano /etc/my.cfg

# add this to the [mysqld] section of the file
server-id=1 # this is the master’s unique number
log-bin=/var/lib/mysql/log-bin.log

# now add the log-bin.log file to your system with correct permissions
shell> sudo nano /var/lib/mysql/log-bin.log
shell> sudo chown mysql:mysql /var/lib/mysql/log-bin.log
shell> sudo chmod 660 /var/lib/mysql/log-bin.log
shell> sudo service mysqld restart

SLAVE:
shell> sudo nano /etc/my.cfg

# add this to the [mysqld] section of the file
server-id=2 # this is the slave’s unique number

shell> sudo service mysqld restart

NOTE: by default, the value of server-id will be zero which tells mysql that it is a stand-alone system. By changing the value to one or two, the

2. Create a user for replication on the master:

MASTER:
mysql> CREATE USER ‘replicate-user’@‘localhost’ IDENTIFIED BY ‘password’;
mysql> USE MYSQL;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replicate-user’@‘slave-ip-address’;
mysql> FLUSH PRIVILEGES;

# NOTE: it may not be required to ‘USE MYSQL’ however by doing this you will avoid certain issues encountered by other users.

3. find the master’s binary log coordinates

MASTER:
TERMINAL 1 (keep this terminal open):
mysql> FLUSH TABLES WITH READ LOCK;
# NOTE: this will not work if you haven’t added the ‘log-bin’ to my.cfg from step 1.

mysql > SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 73 | test | manual,mysql |
+——————+———-+————–+——————+

4. perform mysqldump (backup):
MASTER:
TERMINAL 2:
shell> mysqldump –all-databases –master-data > replicate.db;

TERMINAL 2:
# now send the new dumped file to the slave
shell> scp replicate.db root@slave-ip-address:~/

5. unlock the master’s table
MASTER:
TERMINAL 1 (this is the original terminal in step 3):
mysql> UNLOCK TABLES;

6. copy the database to the slave:
SLAVE:
shell> sudo service mysqld start –skip-slave-start
shell> mysql < replicate.db

7. use CHANGE MASTER TO command:
SLAVE:
mysql> CHANGE MASTER TO
-> MASTER_HOST=‘master-ip-address or name’,
-> MASTER_USER=‘replicate-user’,
-> MASTER_PASSWORD=‘password’,
-> MASTER_LOG_FILE=‘mysql-bin.000003’,
-> MASTER_LOG_POS=73;

NOTE: the ‘MASTER_LOG_FILE’ and ‘MASTER_LOG_POS’ is only an example in this article.  Each instance will be different.

———————————————————————
– CHECK YOUR WORK –
———————————————————————

CHECK YOUR CONNECTION:
When each of the above steps have been completed successfully, you will want to make sure that both servers are, in fact, talking to each other.  To do this, first look at a list of your mysql users on the master.  Then, try to connect to the master through the slave.  Below is an example of both an unsuccessful and a successful attempt to connect from the slave to the master.  Following the successful connection, you may also want to verify that the information in each database is synced.  An example of this is also shown below.

VERIFY YOUR USERS:
MASTER:
mysql> select User,Host from mysql.user;
# NOTE: you should see the user you created and the IP of your slave.

NO USER IDENTIFIED ON MASTER:
shell> mysql -u replicate-user -p -h master-ip-address
Enter password:
ERROR 1130 (HY000): Host ‘slave-ip-address’ is not allowed to connect to this MySQL server

USER IS AUTHENTICATED:
shell> mysql -h master-ip-address -u replicate-user -p
Enter password:
Welcome to the MySQL monitor.

SLAVE IS SYNCED:
# NOTE: if ‘Seconds_Behind_Master’ shows zero, the slave is synced to the master.
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master-ip-address
Master_User: replicate-user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 73

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

—————————————–

Always remember… WHAT IF AND WHY NOT?!?