Visit Our UNIX and Linux User Community


How can I replicate master master and master master MySQL databse replication and HA?


 
Thread Tools Search this Thread
Special Forums UNIX Desktop Questions & Answers How can I replicate master master and master master MySQL databse replication and HA?
# 1  
Old 05-15-2014
Question How can I replicate master master and master master MySQL databse replication and HA?

I have an application desigend in PHP and MySQl running on apache web server that I is running on a Amazon EC2 server Centos. I want to implement the master-master and master slave replication and high availability disaster recovery on this application database.

For this I have created two servers and have installed the same application and database on both the servers.

I want to achieve something like this:


1. If the user updates (insert,delete,modify) any record on any server then the same should be replicated on the other server.

2. If one of the server goes down the other should be able to serve the request.


Kindly help me to achieve the same.

Previous Thread | Next Thread
Test Your Knowledge in Computers #151
Difficulty: Easy
In 1975, adopting Unix required a license from Bell Laboratories that cost $20,000 USD.
True or False?

5 More Discussions You Might Find Interesting

1. Red Hat

Master logs for all the operations performed on mysql DB

is there any such thing as master logs for all the operations performed on mysql database. thing is i need to find the operations performed on mysql on a linux server running on RHEL 5.7 for the past 2 days. One more thing i found out that the bin logs will be refreshed whenever server goes to... (2 Replies)
Discussion started by: vivek d r
2 Replies

2. Web Development

MySQL Master-Slave Configuration: Don't Replicate a Row of a Table?

Anyone have a clue about this? I have checked the MySQL documentation and it does not seem possible to exclude a row of a table from replication between Master and Slave. It seems that replication in MySQL can only be managed at the table level, not at the row level. Does anyone know a work... (5 Replies)
Discussion started by: Neo
5 Replies

3. AIX

nim master

Hi, I want to know how to create nim master in AIX 5.3. Regards, Manoj (0 Replies)
Discussion started by: manoj.solaris
0 Replies

4. AIX

nim master

Hi, I want to know what are steps required to create nim master in aix? Regards, Manoj (1 Reply)
Discussion started by: manoj.solaris
1 Replies

5. UNIX for Dummies Questions & Answers

Master Console

I was a sys admin on a HP3000. I was able to virtually move the console where ever I needed it for commands that had to be done at the master console. So I could be at home and perform some functions that you would usually have to do from the console. Now I am sys admin newbee on an AIX pseries.... (2 Replies)
Discussion started by: Beetlejuice
2 Replies
MYSQLREPLICATE(1)						  MySQL Utilities						 MYSQLREPLICATE(1)

NAME
mysqlreplicate - Setup replication among two MySQL servers SYNOPSIS
mysqlreplicate [options] DESCRIPTION
This utility permits an administrator to start replication from one server (the master) to another (the slave). The user provides login information for the slave and connection information for connecting to the master. It is also possible to specify a database to be used to test replication. The utility reports conditions where the storage engines on the master and the slave differ. It also reports a warning if the InnoDB stor- age engine differs on the master and slave. For InnoDB to be the same, both servers must be running the same "type" of InnoDB (built-in or the InnoDB Plugin), and InnoDB on both servers must have the same major and minor version numbers and enabled state. By default, the utility issues warnings for mismatches between the sets of storage engines, the default storage engine, and the InnoDB storage engine. To produce errors instead, use the --pedantic option, which requires storage engines to be the same on the master and slave. The -vv option displays any discrepancies between the storage engines and InnoDB values, with or without the --pedantic option. Replication can be started using one of the following strategies. Start from the current position (default) Start replication from the current master binary log file and position. The utility uses the SHOW MASTER STATUS statement to retrieve this information. Start from the beginning Start replication from the first event recorded in the master binary log. To do this, use the --start-from-beginning option. Start from a binary log file Start replication from the first event in a specific master binary log file. To do this, use the --master-log-file option. Start from a specific event Start replication from specific event coordinates (specific binary log file and position). To do this, use the --master-log-file and --master-log-pos options. OPTIONS
mysqlreplicate accepts the following command-line options: --help Display a help message and exit. --master=<master> Connection information for the master server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. --master-log-file=<master_log_file> Begin replication from the beginning of this master log file. --master-log-pos=<master_log_pos> Begin replication from this position in the master log file. This option is not valid unless --master-log-file is given. --pedantic, -p Fail if both servers do not have the same set of storage engines, the same default storage engine, and the same InnoDB storage engine. --rpl-user=<replication_user> The user and password for the replication user, in name:passwd format. The default is rpl:rpl. --slave=<slave> Connection information for the slave server in <user>[:<passwd>]@<host>[:<port>][:<socket>] format. --start-from-beginning, -b Start replication at the beginning of events logged in the master binary log. This option is not valid unless both --master-log-file and --master-log-pos are given. --test-db=<test_database> The database name to use for testing the replication setup. If this option is not given, no testing is done, only error checking. --verbose, -v Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug. --version Display version information and exit. NOTES
The login user for the master server must have the appropriate permissions to grant access to all databases and the ability to create a user account. For example, the user account used to connect to the master must have the WITH GRANT OPTION privilege. The server IDs on the master and slave must be nonzero and unique. The utility reports an error if the server ID is 0 on either server or the same on the master and slave. Set these values before starting this utility. EXAMPLES
To set up replication between two MySQL instances running on different ports of the same host using the default settings, use this command: $ mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3307 --rpl-user=rpl:rpl # master on localhost: ... connected. # slave on localhost: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. The following command uses --pedantic to ensure that replication between the master and slave is successful if and only if both servers have the same storage engines available, the same default storage engine, and the same InnoDB storage engine: $ mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3307 --rpl-user=rpl:rpl -vv --pedantic # master on localhost: ... connected. # slave on localhost: ... connected. # master id = 2 # slave id = 99 # Checking InnoDB statistics for type and version conflicts. # Checking storage engines... # Checking for binary logging on master... # Setting up replication... # Flushing tables on master with read lock... # Connecting slave to master... # CHANGE MASTER TO MASTER_HOST = [...omitted...] # Starting slave... # status: Waiting for master to send event # error: 0: # Unlocking tables on master... # ...done. The following command starts replication from the current position of the master (which is the default): $ mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3307 --rpl-user=rpl:rpl # master on localhost: ... connected. # slave on localhost: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. The following command starts replication from the beginning of recorded events on the master: $ mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3307 --rpl-user=rpl:rpl --start-from-beginning # master on localhost: ... connected. # slave on localhost: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. The following command starts replication from the beginning of a specific master binary log file: $ mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3307 --rpl-user=rpl:rpl --master-log-file=my_log.000003 # master on localhost: ... connected. # slave on localhost: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. The following command starts replication from specific master binary log coordinates (specific log file and position): $ mysqlreplicate --master=root@localhost:3306 --slave=root@localhost:3307 --rpl-user=rpl:rpl --master-log-file=my_log.000001 --master-log-pos=96 # master on localhost: ... connected. # slave on localhost: ... connected. # Checking for binary logging on master... # Setting up replication... # ...done. RECOMMENDATIONS
You should set read_only = 1 in the my.cnf file for the slave to ensure that no accidental data changes, such as INSERT, DELETE, UPDATE, and so forth, are permitted on the slave other than those produced by events read from the master. Use the --pedantic and -vv options for setting up replication on production servers to avoid possible problems with differing storage engines. COPYRIGHT
Copyright (c) 2010, 2012, Oracle and/or its affiliates. All rights reserved. This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MER- CHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA AUTHOR
MySQL Utilities Team COPYRIGHT
2010, Oracle and/or its affiliates. All rights reserved. 1.0.3 May 09, 2012 MYSQLREPLICATE(1)

Featured Tech Videos