MySQL master-slave replication principle and configuration detailed process and the implementation of master-slave replication Cluster Automation deployment

I. Overview of replication

MySQL's built-in replication capabilities are the foundation for building large, high-performance applications. The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

Note that when you make a copy, all updates to the tables in the replication must be made on the primary server. Otherwise, you must be careful to avoid conflicts between updates to tables on the primary server and updates made to tables on the server.

Second, MySQL support the type of replication is the binary log format:

1. Statement-based replication: An SQL statement executed on the primary server that executes the same statement from the server. MySQL defaults to statement-based replication for high efficiency
2. Row-based replication: Copy the changed content past, rather than executing the command from the server again. Support Starting from mysql5.0
3. Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

Third, the characteristics of replication:

• Data distribution
• Load Balancing
• Backup
• High availability and available rows

Iv. implementation of the replication process:

MySQL Replication (replication) is an asynchronous or semi-synchronous copy, copied from one MySQL instance (called master) to another MySQL instance (called slave). The implementation of the entire replication operation is mainly done by three processes, of which two processes are in slave (SQL process and IO process), and the other process is on Master (IO process).
To implement replication, you must first turn on the binary log (Bin-log) function on the master side, otherwise it cannot be implemented. Because the entire replication process is actually a variety of operations that are logged in the execution log that slave obtains the log from the master side and then executes it in its own full sequence.

The process is as follows:

• Slave The above IO process connects to master, and requests the log content from the specified location (or from the beginning of the log) to the designated log file;
• After master receives a request from the IO process from slave, the IO process that is responsible for the replication reads the log information after the specified location of the log according to the requested information and returns the IO process to the slave. In addition to the information contained in the log, the returned information includes the name of the Bin-log file returned to the master side and the location of the Bin-log;
• After the slave IO process receives the information, the received log content is added to the end of the Relay-log file on the slave side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. In order to be able to tell Master clearly at the next reading, "I need to start from somewhere in the Bin-log log content, please send me";
• Slave's SQL process detects new additions to the relay-log, and immediately resolves the contents of Relay-log as executable content at the real execution time on the master side and executes on its own.

Five, the configuration process:

MySQL master-slave replication configuration:

Environment:
master.test.com-->172.16.3.201
slave.test.com-->172.16.3.203

master.test.com-->172.16.3.201

1. Enable the binary log
Vim/etc/mysql/my.cnf
# Make sure the following statement is in [mysqld]
Log-bin=mysql-bin

# Save Exit

2. Set a server-id that is unique in the current cluster
Vim/etc/mysql/mys.cnf
# Navigate to the following line
Server-id = 1
# Set the value to 11, or you can set the other
Server-id = 11
# Save Exit
The above process is implemented with SED:
Sed-i '/^server-id/[email protected]@[email protected] '/etc/mysql/my.cnf
3. Create an account with copy rights including [Replication Slave,replication client]:
MYSQL-E "Grant replication client,replication slave on * * to ' repluser ' @ ' 172.16.3.203 ' identified by ' replpass '; Flush privileges; "
4, Service mysqld restart

slave.test.com-->172.16.3.203

1. Enable the relay log
Vim/etc/mysql/my.cnf
# Add the following in the Mysqld section: Turn on the relay log
Relay-log = Relay-bin
# Save Exit
Implemented with SED:
Sed-i '/\[mysqld\]/a relay-log = Relay-bin '/etc/mysql/my.cnf
2. Set a server-id that is unique in the current cluster
Vim/etc/mysql/my.cnf
# Navigate to the following line
Server-id =1
# set its value to 21, or set it to another
Server-id =21
# Save Exit
# implemented with SED
Sed-i '/^server-id/[email protected]@[email protected] '/etc/mysql/my.cnf
3. Set MySQL read-only
Implemented with SED:
Sed-i '/\[mysqld\]/a read-only = On '/etc/mysql/my.cnf
4, Service mysqld restart
5. Connect to the primary server using a copy-rights account
Mysql-e "Change Master to master_host= ' 172.16.3.201 ', master_user= ' repluser ', master_password= ' Replpass ', Master_log_ File= ' binlog.000033 ', master_log_pos=553, master_connect_retry=5, master_heartbeat_period=2 "
6. Start from the server
Mysql-e "Start Slave"

errors you may encounter:

ERROR 1201 (HY000): Could not initialize master info structure; More error messages can is found in the MariaDB error log

Workaround:

If this error occurs, it will appear in slave, with the following methods:
MariaDB [(none)]> reset slave;
MariaDB [(none)]> change master to master_host= ' 172.16.3.201 ', master_user= ' repluser ', master_password= ' Replpass ', Master_log_file= ' binlog.000033 ', master_log_pos=245, master_connect_retry=5, master_heartbeat_period=2;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(None)]> start slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show Slave status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.3.201
Master_user:repluser
master_port:3306
Connect_retry:5
master_log_file:binlog.000033
read_master_log_pos:245
relay_log_file:relay-bin.000002
relay_log_pos:526
relay_master_log_file:binlog.000033
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:

Automating the deployment of MySQL master-slave replication Cluster "script implementation":

Description: Through the shell script to automate the deployment of MySQL master-slave replication cluster, here I do not explain the meaning of each step, in the previous introduction, there is a detailed explanation of each step, I this script for your reference only, after my pro-test, can be automated deployment, if there is insufficient also please give some advice, I am also a novice, we learn together, hey

master_slave.sh

#!/bin/Bashif[[$#-eq0]]; Then        Echo "Usage:/bin/bash ' basename $ ' master_ip slave_ip"Exit1fimaster_ip=$1slave_ip=$2sed-I.'/^server-id/[email Protected]@[email protected]'/etc/mysql/my.cnf MySQL-E"Grant Replication client,replication slave on * * to ' repluser ' @"${SLAVE_IP}"identified by ' replpass '; flush privileges;"Service mysqld RestartCat> Test_master_slave.SH<<eof#!/bin/Bashsed-I.'/\[mysqld\]/a relay-log = Relay-bin'/etc/mysql/my.cnfsed-I.'/^server-id/[email Protected]@[email protected]'/etc/mysql/my.cnfsed-I.'/\[mysqld\]/a read-only = on'/etc/mysql/my.cnf service mysqld restart MySQL-E"Change master to master_host= ' 172.16.3.101 ', master_user= ' repluser ', master_password= ' Replpass ', master_log_file = ' Binlog ', master_log_pos=55, master_connect_retry=5, master_heartbeat_period=2"MySQL-E"start Slave"Eofmaster_log= ' Mysql-e"Show Master Status"|grep 'bin'|awk '{print $}'' Master_log_position= ' Mysql-e"Show Master Status"|grep 'bin'|awk '{print $}'`sed-I."/binlog/[email protected]@${master_log}@"Test_master_slave.SHsed-I."/log_pos/[email protected]@${master_log_position}@"Test_master_slave.SHsed-I."/master_host/[email protected]@${master_ip}@"Test_master_slave.SHEcho${master_log}Echo${master_log_position}SCPTest_master_slave.SHnode2:/RootSSHNode2"/bin/bash/root/test_master_slave.sh"

The script uses the basic knowledge of the shell, as well as the fundamental knowledge of sed, awk, etc.

MySQL master-slave replication principle and configuration detailed process and the implementation of master-slave replication Cluster Automation deployment