MYSQL replication

Applicable to Centos Versions:
• Centos 5.x
• Centos 6.x
Requirements
Explanation of requirements.
1. Root access to your MySQL servers. (note: this is different than root access to the server itself)
2. Root access to both of the servers.
3. 2 Separate computers you want to use for this purpose.
Doing the Work
This assumes you have MySQL currently installed on both machines and have at least one database you wish to

replicate. We’ll be using the database “mydb” as an example, you’ll need to change this to your actual database.

We’ll assume the IP of the master MySQL server is 192.168.0.6
1. Configuring the Master:
we need to tell MySQL where to write the logs in /etc/my.cnf:
Original my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

/etc/my.cnf after editing
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=1
log-bin=/home/mysql/mysql-bin.log
binlog-do-db=mydb(Database name which we are going to replicate)
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Important Note::the path of log-bin can be any but the folder of mysql should be owned by mysql otherwise it will

give you a error as by default the folder created is owned by root, so for changing the ownership you can use

following command
chown -R mysql:mysql mysql
2. Restart MySQL daemon:
# service mysqld restart
3. Login to MySQL shell as the root user:
# mysql -u root -p
(note: this command assumes you are not root on the system, but should work even if you are.)
4. Now that we’re logged in as the root user we need to create a user with database replication privileges:
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave_user’@’%’ IDENTIFIED BY ‘<some_password>’;
mysql> FLUSH PRIVILEGES;
5. Next, while still logged into the MySQL shell:
mysql> USE mydb;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

Which should give us output similar to this:

+—————+———-+————–+——————+
| File | Position | Binlog_do_db | Binlog_ignore_db |
+—————+———-+————–+——————+
| mysql-bin.007 | 77 | mydb | |
+—————+———-+————–+——————+
1 row in set (0.00 sec)

(note: copy this information, we’ll need it for the slave setup later.)
Also, DO NOT logout of the MySQL shell after “FLUSH TABLES WITH READ LOCK;” or you will
lose the lock.
6. While still logged in to the MySQL shell, open another terminal on the system (not a MySQL shell):

mysqldump -u root -p<password> –opt mydb > mydb.sql

Important: There is no space between -p and <password>
alternatively: If you leave out ‘-p<password>’ mysqldump will prompt you for it before executing your command.

This will create an SQL dump of mydb in the current working directory with the filename:mydb.sql. Transfer this

file to your slave server.
There are two ways of transferring either do scp or use rsync.

This step is important because the database will replicate from current status so before the replication the

database should be backed up and restored to slave.
7. Now, go back to the open MySQL shell and issue the following MySQL commands:
mysql> UNLOCK TABLES;
mysql> FLUSH PRIVILEGES;
mysql> exit;

This concludes the MySQL master setup.
8. Configuring the MySQL slave, login to the MySQL server as root on the slave system:
]$ mysql -u root -p
(note: this command assumes you are not root on the system, but should work even if you are.)
9. Create the database “mydb” on the slave MySQL server:
mysql> CREATE DATABASE mydb;
mysql> exit;
10. If you have made an SQL dump of mydb on the master and have transferred it to the slave, then it is now time to

import the SQL dump into our newly created mydb on the slave::
]# mysql -u root -p<password> mydb < /path/to/mydb.sql
Important: There is no space between -p and <password>)
alternatively: If you leave out ‘-p<password>’ mysqldump will prompt you for it before
executing your command.
11. Next we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.6, and that the

master database to watch is mydb. Therefore we add the following lines to /etc/my.cnf:
Original my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
/etc/my.cnf after editing
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2
master-host=192.168.0.6
master-user=slave_user
master-password=slave_user’s password
master-connect-retry=60
replicate-do-db=mydb
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Note::Serverid’s used can be any numeric number it should be distinguished from any other pc on your server

12. Restart the MySQL server on the slave:
service mysqld restart

13. Final steps (still logged in to MySQL shell):
mysql> SLAVE STOP;

(note: make sure to change the example values here appropriately.)
mysql> CHANGE MASTER TO MASTER_HOST=’192.168.0.6′, MASTER_USER=’slave_user’, MASTER_PASSWORD='<some_password>’,

MASTER_LOG_FILE=’mysql-bin.006′, MASTER_LOG_POS=167;
mysql> START SLAVE;
mysql> FLUSH PRIVILEGES;
mysql> exit;

* MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.6).
* 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 gave back when you ran SHOW MASTER STATUS; on the master.
* MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

After these steps you should get this output then your databse will replicate
Just login to mysql of slave and type
Show slave status \G
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.6
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.006
Read_Master_Log_Pos: 427
Relay_Log_File:
Relay_Log_Pos: 564
Relay_Master_Log_File:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

1 row in set (0.00 sec)

If u will get this output then replication of your database has been started.

Cheers

Advertisements
Leave a comment

Leave your opinion

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: