编程知识 cdmana.com

Construction and principle of master slave replication in MySQL 5.7

1. Master and slave copy build

 1.1  Environmental preparation

OS: Ubuntu 18.04
master: 192.168.0.3
slave: 192.168.0.6

1.2  Install dependency packages

# Ubuntu
apt-get install -y libaio-dev
# CentOS
yum install -y libaio-devel

1.3  Download binary installation package

# https://downloads.mysql.com/archives/community/
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz

1.4  Unzip the installation package

tar zxf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
mv /usr/local/mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql-5.7.30

1.5  establish mysql Users and data 、 Log directory

useradd mysql -s /usr/sbin/nologin
mkdir -p /data/mysql/3306 /data/mysql/binlog/3306
chown -R mysql:mysql /data/mysql

1.6 Add environment variables

echo "export PATH=/usr/local/mysql-5.7.30/bin:$PATH" > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh

1.7  Initialization data

mysqld --initialize-insecure --basedir=/usr/local/mysql-5.7.30 --datadir=/data/mysql/3306 --user=mysql

1.8  Simple configuration

# master  node 
# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql-5.7.30
datadir=/data/mysql/3306
server_id=3
port=3306
socket=/tmp/mysql.sock
log_bin=/data/mysql/binlog/3306/logbin

[mysql]
socket=/tmp/mysql.sock

# mysqld  The order in which configuration files are loaded at startup 
# mysqld --help --verbose | grep my.cnf
# /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
#  Specify profile ,  start-up  mysqld
# /etc/init.d/mysqld start --defaults-file=/etc/my.cnf
# slave  node 
# cat /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql-5.7.30
datadir=/data/mysql/3306
server_id=6
port=3306
socket=/tmp/mysql.sock

[mysql]
socket=/tmp/mysql.sock

1.9  Copy the startup script

cp /usr/local/mysql-5.7.30/support-files/mysql.server /etc/init.d/mysqld

1.10  start-up mysqld

#  This command is required for the first startup 
/etc/init.d.mysqld start

#  It can be used in the back  systemctl  Command management 
#  start-up / restart / stop it  mysqld
systemctl start/restart/stop mysqld

#  see  mysqld  state 
systemctl status mysqld

1.11  Set up root password

# --initialize-insecure  After initialization of this parameter  mysql  There is no password by default ,  For security, you need to set up  root  User password 
mysqladmin -uroot password
# qq.123

The above operation is to build mysql service , Master and slave nodes have to do .

1.12 Check if the main library is on binlog

#  Operate on the main library 
mysql -uroot -pqq.123 -e "select @@log_bin;"

1.13  The main library creates a copy user

#  Operate on the main library 
mysql -uroot -pqq.123 -e "grant replication slave on *.* to repl@'192.168.0.%' identified by '123';"
mysql -uroot -pqq.123 -e "select user,host from mysql.user;"

1.14  Backup and restore to the main library

#  Because our databases are new , There is no need to operate this step 
#  If the main database of the production environment has been opened earlier, this step is necessary 
#  Operate on the main library 
mysqldump -uroot -pqq.123 -A --master-data=2 --single-transaction > /tmp/all.sql
scp /tmp/all.sql 192.168.0.6:/tmp/

#  Operate on the slave library 
mysql -uroot -pqq.123 < /tmp/all.sql

1.15 Tell to copy information from the library

# grep "\-- CHANGE MASTER TO" /tmp/all.sql
# -- CHANGE MASTER TO MASTER_LOG_FILE='logbin.000001', MASTER_LOG_POS=154;

# help change master to
change master to master_host='192.168.0.3',master_user='repl',master_password='123',master_port=3306,master_log_file='logbin.000001',master_log_pos=154,master_connect_retry=10;

1.16  Start the copy thread from the library

#  Operate on the slave library 
mysql -uroot -pqq.123 -e "start slave;"

1.17  Verify master-slave status

#  Operate on the slave library 
mysql -uroot -pqq.123 -e "show slave status\G" | grep Running:
      # Slave_IO_Running: Yes
      # Slave_SQL_Running: Yes

1.18  If the above steps 12-17 Problems in , You can execute the following command to reset , And then configure it again 12-17 Step .

#  Operate on the slave library 
mysql -uroot -pqq.123 -e "stop slave; reset slave all;"

 

2. Master slave replication principle

2.1  The resources involved in master-slave replication

  • 2.1.1 file

    • 2.1.1.1 Main library  binlog  file

    • 2.1.1.2 Slave Library  relay-log.bin  file , The function is to store and receive binlog, By default, it is in the data directory of the slave database , Define methods manually :relay_log_basename=/data/mysql/3306/${hostname}-relay-bin

    • 2.1.1.3 Slave Library  master.info  file , The function is to store the information connected to the main library , Already received binlog Location point information . By default, it is stored in the data directory of the slave library . Define methods manually :master_info_repository=FILE/TABLE

    • 2.1.1.4 Slave Library  relay-log.info  file , The function is to record and playback  relay-log  The location of , By default, it is stored in the data directory of the slave library . Define methods manually :relay_log_info_repository=FILE/TABLE

  • 2.1.2 Threads

    • 2.1.2.1 Main library  Binlog_dump_Thread  It is used to receive requests from the database , And deliver binlog To the slave Library  show processlist;  Commands can be viewed

    • 2.1.2.2 Slave Library  IO Threads   The function is to request binlog, receive binlog SQL Threads   The purpose is to play back relay-log show slave status;  Commands can be viewed

2.2  The principle of master-slave replication

  

  • 2.2.1 S: change master to ip,port,user,password,binlog The location information is written to  master.info  in , perform  slave start; ( start-up SQL, IO Threads ).

  • 2.2.2 S: Connect the main library

  • 2.2.3 M: Distribute Dump_Thread, Specialized and S_IO signal communication .

  • 2.2.4 S: IO Thread requests new log

  • 2.2.5 M: Dump_T Receiving request , Log interception , Return to S_IO

  • 2.2.6 S: IO Thread receive binlog, The log is in TCP/IP cache , At this point, the network layer returns ACK To the main library . The main library is finished .

  • 2.2.7 S: IO Thread will binlog Finally write to relay-log in , And update the master.info, IO End of thread work .

  • 2.2.8 S: SQL Thread read relay-log.info, Get the location of the last execution .

  • 2.2.9 S: SQL The thread executes the new relay-log, Update again  relay-log.info

Little details :
  • S: relay-log Parameters : relay_log_purge=ON, Delete applied... Periodically relay-log

  • M: Dump Thread real-time monitoring of the main library binlog change , If there is a new change , Send a signal to the slave library .

 

That's all mysql 5.7 A brief description of the process and principle of master-slave replication . The building process is not complicated , If you are interested, you can try it with your hands . If you have any questions, please leave a message in the comments section .

 

版权声明
本文为[Operation and maintenance stack]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201224214721823p.html

Scroll to Top