Slide

  • LINUX

    LINUX:In 1969 AT&T made a decision to withdraw Multics and go with GECOS (General Electric Comprehensive Operating Supervisor / System), with AT & T in Bells Lab when Multics was withdrawn some of the programmers named Ken Thompson and Dennis Ritchie decided to rewrite operating system in order to support low cost computer..To Know More

    CLICK HERE

  • MICROSOFT

    MICROSOFT:Microsoft was established to develop and sell BASIC interpreters for the Altair 8800. It rose to dominate the home computer operating system market with MS-DOS in the mid-1980s,followed by the Microsoft Windows line of operating systems. To Know More

    CLICK HERE

  • CISCO

    CISCO:During the early 1980s, there was a married couple namely Len and Sandy Bosack who used to work in two different departments of computer located in Stanford University. This couple was facing problem in making their computers communicate with each other To Know More

    CLICK HERE

showinfo=1

MySQL (Master-Slave) Replication

The MySQL Replication is very useful in terms of Data SecurityFail-over SolutionDatabase Backup from SlaveAnalytics etc. We use the following things to carry the replication process. In your scenario it would be different.
  1. Working Linux OS like CentOS 6.3RedHat 6.3 or Fedora 17
  2. Master and Slave are CentOS 6.3 Linux Servers.
  3. Master IP Address is: 192.168.1.1.
  4. Slave IP Address is: 192.168.1.2.
  5. Master and Slave are on the same LAN network.
  6. Master and Slave has MySQL version installed.
  7. Master allow remote MySQL connections on port 3306.
We have two servers, one is Master with IP (192.168.1.1) and other is Slave as (192.168.1.2). We have divided the setup process in two phases to make things easier for you, In Phase I we will configure Master server and in Phase II with Slave server. Let’s start the replication setup process.

Phase I: Configure Master Server (192.168.1.1) for Replication

In Phase I, we will see the installation of MySQL, setting up Replication and then verifying replication.
Install a MySQL in Master Server
First, proceed with MySQL installation using YUM command. If you already have MySQL installation, you can skip this step.

   # yum install mysql-server mysql
Configure a MySQL in Master Server
Open my.cnf configuration file with VI editor.
# vi /etc/my.cnf
Add the following entries under [mysqld] section and don’t forget to replace cms with database name that you would like to replicate on Slave.
server-id = 1
binlog-do-db=cms
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
Restart the MySQL service.
# /etc/init.d/mysqld restart
Login into MySQL as root user and create the slave user and grant privileges for replication. Replace slave_user with user and your_password with password.
# mysql -u root -p

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 11128001 | cms          |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> quit;
Please write down the File (mysql-bin.000003) and Position (11128001) numbers, we required these numbers later on Slave server. Next apply READ LOCK to databases to export all the database and master database information with mysqldump command.
#  mysqldump -u root -p --all-databases --master-data > /root/dbdump.db
Once you’ve dump all the databases, now again connect to mysql as root user and unlcok tables.
mysql> UNLOCK TABLES;
mysql> quit;
Upload the database dump file on Slave Server (192.168.1.2) using SCP command.
scp /root/dbdump.db root@192.168.1.2:/root/
That’s it we have successfully configured Master server, let’s proceed to Phase II section.

Phase II: Configure Slave Server (192.168.1.2) for Replication

In Phase II, we do the installation of MySQL, setting up Replication and then verifying replication.
Install a MySQL in Slave Server
If you don’t have MySQL installed, then install it using YUM command.
# yum install mysql-server mysql
Configure a MySQL in Slave Server
Open my.cnf configuration file with VI editor.
# vi /etc/my.cnf
Add the following entries under [mysqld] section and don’t forget to replace IP address of Master server, cms with database name etc, that you would like to replicate with Master.
server-id = 2
master-host=192.168.1.1
master-connect-retry=60
master-user=slave_user
master-password=yourpassword
replicate-do-db=cms
relay-log = /var/lib/mysql/mysql-relay-bin
relay-log-index = /var/lib/mysql/mysql-relay-bin.index
log-error = /var/lib/mysql/mysql.err
master-info-file = /var/lib/mysql/mysql-master.info
relay-log-info-file = /var/lib/mysql/mysql-relay-log.info
log-bin = /var/lib/mysql/mysql-bin
Now import the dump file that we exported in earlier command and restart the MySQL service.

# mysql -u root -p
mysql> slave stop;

# mysql -u root -p < /root/dbdump.db
# /etc/init.d/mysqld restart
Login into MySQL as root user and stop the slave. Then tell the slave to where to look for Master log file, that we have write down on master with SHOW MASTER STATUS; command as File (mysql-bin.000003) and Position (11128001) numbers. You must change 192.168.1.1 to the IP address of the Master Server, and change the user and password accordingly.

# mysql -u root -p
mysql> slave stop;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='slave_user', MASTER_PASSWORD='yourpassword', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=11128001;
mysql> slave start;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.1
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 12345100
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 11381900
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: cms
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12345100
Relay_Log_Space: 11382055
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Verifying MySQL Replication on Master and Slave Server

It’s really very important to know that the replication is working perfectly. On Master server create table and insert some values in it.
On Master Server
mysql> use cms;
mysql> CREATE TABLE employee (c int);
mysql> INSERT INTO employee (c) VALUES (1);
mysql> SELECT * FROM employee;
+------+
|  c  |
+------+
|  1  |
+------+
1 row in set (0.00 sec)
On Slave Server
Verifying the SLAVE, by running the same command, it will return the same values in the slave too.
mysql> use cms;
mysql> SELECT * FROM employee;
+------+
|  c  |
+------+
|  1  |
+------+
1 row in set (0.00 sec)
That’s it, finally you’ve configured MySQL Replication in a few simple steps.


18 comments:

  1. Autocarbazar is the leading auto portal in Lajpat Nagar, have several brands used cars at the reasonable price. All the second-hand cars in good condition as per customers requirements.

    Used Cars in Lajpat Nagar

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. Good to know of the data security aspect.

    ReplyDelete
  4. I wish they would change the name.

    ReplyDelete
  5. Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔✔✔Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @
    Packers And Movers Chennai

    ReplyDelete
  6. Hire Best Packers And Movers Mumbai for hassle-free Household Shifting, ***Office Relocation, ###Car Transporation, Loading Unloading, packing Unpacking at affordable ✔✔✔ Price Quotation. Top Rated, Safe and Secure Service Providers who can help you with 24x7 and make sure a Untroubled Relocation Services at Cheapest/Lowest Rate
    Packers And Movers Mumbai

    ReplyDelete
  7. Packers and Movers Pune Provide High Quality ***Household Shifting, Home/Office Relocation, Insurance, Packing, Loading, ###Car Transportation Service Pune and High experiences, Top Rated, Safe and Reliable, Best and Secure Packers and Movers Pune Team List. Get ✔✔✔ Affordable Rate Charts and Compare Quotation and Save Money and Time .
    Packers And Movers Pune

    ReplyDelete
  8. Get Shifting/Relocation Quotation from ###Packers and Movers Delhi. Packers and Movers Delhi 100% Affordable and Reliable ***Household Shifting Services. Compare Transportation Charges and Save Time, Verified and Trusted Packers and Movers in Delhi, Cheap and Safe Local, Domestic House Shifting @
    Packers and Movers Delhi

    ReplyDelete
  9. Packers and Movers Bangalore as a Services providing company can make all the difference to your ###Home Relocation experience. Bangalore based Company which offers versatile solutions, Right team that easily reduce the stress associated with a ✔✔✔Household Shifting, ***Vehicle Transportation. we help things run smoothly and reduce breakages and offer you seamless, Affordable, Reliable Shifting Services, Compare Shifting Charges @
    Packers And Movers Bangalore

    ReplyDelete
  10. Get Packers and Movers Jaipur List of Top Reliable, 100% Affordable, Verified and Secured Service Provider. Get Free ###Packers and Movers Jaipur Price Quotation instantly and Save Cost and Time. Packers and Movers Jaipur ✔✔✔Reviews and Compare Charges for household Shifting, Home/Office Relocation, ***Car Transportation, Pet Relocation, Bike SHifting @
    Packers And Movers Jaipur

    ReplyDelete
  11. Local Packers and Movers Bangalore List, Get Best Price Quotes, Compare Movers and packers Charges, Top, Local Household Shifting Services
    Packers And Movers Bangalore

    ReplyDelete

Note:- Comment as: Option available to post without login select "Anonymous" from the drop down...........

For Latest Updates: Subscribe Now | | Test Your Knowledge, Take a Quiz now Click Here | | Site Best Viewed In Firefox

X