MySQL Replication

title

MySQL Replication

author

Bernard Szlachta (NobleProg Ltd)

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) ⌘

1. Create a separate user that will be used by your slaves to Authenticate with the master to read the binary log for replication (optional)
2. Configure the master to support the binary log and configure a unique ID (required)
3. Configure a unique ID for each slave (required)
4. Configure the slave with the master settings, such as the host name, login credentials and binary log name and positions
5. 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

 GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';

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