MySQL Master-slave replication

Learn about MySQL master-slave replication:
MySQL's replication capability is the foundation for building large, high-performance applications. Master-slave replication refers to the fact that one server acts as the primary database server (master), and another or more servers act as slaves from the database server (slave), and the data from the master server is automatically copied to the slave server. MySQL master-slave replication is based on the primary server modifying the database and logging the binary log, which automatically performs updates from the server through the primary server's binary logs.

Replication-Resolved issues:

• Data distribution (distribution)
• Load balancer (Load Balancing)
• Highly available and fault tolerant (high availability and failover)
• Backup (Backups)

Types of MySQL replication:

• SBR (statement based replicate): statement-based replication, SQL statements executed on the primary server, executing the same statement from the server.
• RBR (Row based replicate): row-based replication that copies the changed content from the primary server to the past.
• MIX (mixed): statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

MySQL master-slave replication principle:

1. Master logs the changed content into binary logs (binary log), which is called the binary log event (binary logs events);
2. Slave receives the binary log of master via IO thread and writes to Slave's Realay log (trunk log), SQL thread reads events from Realay log and updates slave data.

MySQL master configuration:

Master configuration:
Disable Gtid:gtid_mode=off
server_id:103306 (recommended IP after two digits plus database port number)
Log_bin:log_bin=on
Binlog_format:binlog_format=row (Optional: mixed|statement)
To create a copy account:

' Repl '@'%'password';

To view the master status:

Show Master Status\g;

View Binlog Dump:

Show Processlist\g;

Command:binlog Dump
State:master have sent all binlog to slave; Waiting for more updates

Slave configuration:
Disable Gtid:gtid_mode=off
server_id:113306 (recommended IP after two digits plus database port number)
Log_bin:log_bin=on
Log_slave_updates:log_slave_updates=on
To connect to the main library:

change MASTER to Master_host='192.168.100.10', master_user=' Repl ' , Master_password='PASSWORD', master_port=3306 , Master_log_file='mysql-bin.000002', master_log_pos= 3141;

Turn on Sync:

Start slave;

Other commands:

Stop slave: Turn off synchronization start slave io_thread: Turn on Io thread, stop close start slave sql_thread: Start SQL thread, stop off

View slave Status:

show slave status\g;

Slave_io_running:yes
Slave_sql_running:yes
IO thead and SQL thead threads:

Show Processlist\g;

Command:connect
State:waiting for Master to send event
Command:connect
State:slave have read all relay log; Waiting for more updates

MySQL Master-slave replication