90.152.2.38 at 14:12, 23 October 2014

2014-10-23T14:12:11Z

New page

{{Cat|MySQL|050}}
{{Cat|MariaDB|050}}

<slideshow style="nobleprog" headingmark="⌘" incmark="…" scaled="true" font="Trebuchet MS" >
;title: MySQL Replication
;author: Bernard Szlachta (NobleProg Ltd)
</slideshow>

== What is Replication? ⌘==
* Data from Master server are replicated to one or more Slave server
* Replication is asynchronous - slaves do not need to be connected permanently to receive updates from the master
* Updates can occur over long-distance connections
* You can replicate all databases, selected databases, or even selected tables within a database

== Why replication? ⌘==
* Scale-out solutions
** all writes and updates must take place on the master
** reads take place on one or more slaves
** the master is dedicated to updates, slaves are dedicated for reads
* Data security
** to run "backup" services on the slave without corrupting the corresponding master data
** switch to slave if the master is unavailable or corrupted
* Analytics, Data Warehouses
** live data on the master
** analysis of the information takes place on the slave
* Long-distance data distribution and security
** a branch office would like to work with a copy of your main data
** slave can be used without requiring permanent access to the master

== Synchronous vs. Asynchronous ⌘==
* MySQL support for one-way, asynchronous replication (master -> slave(s))
* The synchronous replication is implemented by Gallera Cluster (master <-> master)

== MariaDB Parallel Replication ==
https://mariadb.com/kb/en/mariadb/documentation/replication/standard-replication/parallel-replication/

== Types of Replication ==
* Statement Based Replication (SBR)
** replicates entire SQL statements
* Row Based Replication (RBR)
** replicates only the changed rows
* Mixed Based Replication (MBR)
** the default mode within MySQL 5.1.12 and later

== How Replication Works (part 1)? ⌘==
* Replication uses binary logging mechanism
* The master writes updates and changes as “events” to the binary log
* Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database
* The master is “dumb” in this scenario (knows nothing about slaves)
* Each slave will receive a copy of the entire contents of the binary log

== How Replication Works (part 2)? ⌘==
* It is the responsibility of the slave to decide which statements in the binary log should be executed
* You cannot configure the master to log only certain events
* If you do not specify otherwise, all events in the master binary log are executed on the slave
* If required, you can configure the slave to process only events that apply to particular databases or tables.
* Each slave keeps a record of the binary log file and position within the log file that it has read and processed from the master (relay)
* This means that multiple slaves can be connected to the master and executing different parts of the same binary log

== How Replication Works (part 3)? ⌘==
* Because the slaves control this process, individual slaves can be connected and disconnected from the server without affecting the master's operation
* Slaves can be disconnected, reconnect and then “catch up” by continuing from the recorded position.
* The master and each slave must have a unique ID (server-id)
* The slave must know the master host name, log file name and position within that file
* '''CHANGE MASTER TO''' statement sets
** the address of the master
** binary log file name
** position in the binary log
* CHANGE MASTER TO statement stores the data in the '''master.info''' file

== How to Set Up Replication (from new database) ⌘==
# Create a separate user that will be used by your slaves to Authenticate with the master to read the binary log for replication (optional)
# Configure the master to support the binary log and configure a unique ID (required)
# Configure a unique ID for each slave (required)
# Configure the slave with the master settings, such as the host name, login credentials and binary log name and positions
# Start replication

== 1. Creating a User for Replication ⌘==
* Each slave must connect to the master using a standard MySQL user name and password
* Any account can be used for this operation, providing it has been granted the REPLICATION SLAVE privilege
* You do not need to create a specific user for replication. you should be aware that the user name and password will be stored in plain text within the master.info file
<syntaxhighlight lang="sql">
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
</syntaxhighlight>

== 2. Setting Master Configuration ⌘==
* You must enable binary logging on the master
* Each server within a replication group must be configured with a unique server-id value
* The server ID is used to identify individual servers within the group, and must be between 1 and 231
* Edit my.cnf or my.ini file
* Add the lines below
[mysqld]
log-bin=mysql-bin
server-id=1

== 3. Setting Slave Configuration ==
* You must set the unique server ID
[mysqld]
server-id=2
* If you do not specify a server-id value, it defaults to 0.
* If you omit server-id (or set it explicitly to 0), a master refuses connections from all slaves, and a slave refuses to connect to a master.
* Omitting server-id is good only for backup with a binary log.
* You do NOT have to enable binary logging on the slave for replication to be enabled

== 4a. Obtaining Master Information ==
* You must determine the master's current point within the master binary log
* Execute FLUSH TABLES WITH READ LOCK; on the master server and leave the session open
* Use the SHOW MASTER STATUS on the master server to get
** Binary Log Position
** Binary Log Filename
* You now have the information you need to enable the slave to start reading from the binary log in the correct place to start replication.

== 4b. Configure slave with master settings ==
* On the master, release the read lock:
** UNLOCK TABLES;
* Start up the MySQL slave
* Execute the CHANGE MASTER TO
CHANGE MASTER TO
MASTER_HOST='localhost',
MASTER_USER='rep',
MASTER_PASSWORD='asdf',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=106;

== 5. Start Replication ==
* START SLAVE starts both of the slave threads.
** The I/O thread reads queries from the master server and stores them in the relay log.
** The SQL thread reads the relay log and executes the queries
* START SLAVE requires the SUPER privilege
* Slave threads can start and then later stop
** for example they do not manage to connect to the master or read its binary logs
** START SLAVE does not warn you about this
** You must check the slave's error log for error messages generated by the slave threads
** SHOW SLAVE STATUS shows whether replication is working

MySQL Replication - Revision history

90.152.2.38 at 14:12, 23 October 2014