This article is a fraction of a Number of Articles on MySQL, to access them click here.
Replication
Replication is the ability to duplicate data changes that occur on one server to another.
Basic components
| Servers | Master, Slave, Relay Slave |
| Configuration files | |
| Threads | Binlog dump thread, I/O Thread, SQL Thread |
| Sequential files | Binary Log, Relay Log |
Basic uses of Replication
| Use | Description |
| Backup | Run backup operations that require (or not) taking the server offline |
| Scale out | Add more slaves to improve read throughput |
| Hot Standby | Provide a replacement for the master to reduce downtime |
| Data Analysis | Perform resource-intensive operations on a slave to avoid conflicts with other active applications |
| Debugging | Carry diagnoses of complex queries, and refine database design without risking effects to the production databases. |
| Development | Provide near-production-quality data for development of new applications that represent actual data values, ranges, and size |
Replication – how it works?
- Slave connects to Master and I/O Thread asks for data
- Binlog dump thread sends contents to I/O Thread
- SQL Thread applies data
Basic rules
- Slave can have only one Master
- Master can have many Slaves
- Each server must have a unique server_id
- Slave can propagate changes from its Master and be Master for other Slaves (Relay Slave) – enable log_slave_updates
Basic configuration – Master
| 1. Changes in .cnf file |
|
[mysqld] server_id=N log_bin=mysql-bin |
| 2. Set up a special user |
|
mysql> CREATE USER ‘slaveuser’@’%’ IDENTIFIED BY ‘haslo’; mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slaveuser’@’%’; mysql> flush privileges; |
| 3. Get coordinates of binary log |
|
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; mysql> UNLOCK TABLES; |
Basic configuration – Slave
| 1. Changes in .cnf file |
|
[mysqld] server_id=N |
| 2. Connect to Master |
|
mysql> CHANGE MASTER TO MASTER_HOST=’1.1.1.1’, MASTER_USER=’slaveuser’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000001’, MASTER_LOG_POS=107; |
| 3. Start the thread on the slave |
| mysql> START SLAVE; |
Formats of Replication
- SBR (statement-based replication)
- RBR (row-based replication)
- MFL (mixed format logging)
[myslqd]
binlog_format=…
Master/Slave – multiple slaves
- Usefull with few writes and many reads
- Different slave for different roles (indexes, storage engines)
- Slave as standby master (with no traffic than replication)
- One of slaves backup, training, development
- Problems:
–Does not protect from DROP TABLE operation
–No guarantee that data from Master will be transferred correctly on Slave
Master/Master
- Each server is Master and Slave of the other
- e.g. 2 geographically separated offices. Each needs own locally writable copy of data
- Problems:
–Simultaneously changes the same row
–Insert at the same time into table with AUTO_INCREMENT column
1 Comment
Articles on MySQL – Virtono Community · August 7, 2016 at 7:37 AM
[…] MySQL replication […]