MySQL master-slave replication building (one master and two slaves)

1, Environment introduction

The data replication between MySQL is based on binary log files. Once the binary log is enabled in MySQL database, all operations in its database will be recorded in the binary log in the form of events as Master. Other databases as Slave will keep communication with the Master server through an I/O thread, and monitor the change of the binary log file of Master. If the binary log file of Master changes, the change will be copied Then a SQL thread of Slave will execute the relevant events into its own database, so as to achieve the consistency between the Slave database and the Master database, and realize the Master-Slave replication.

name IP system port edition
Master (master database) 192.168.10.175 CentOS6.10 3306 MySQL 5.6.45
Slave (from database) 192.168.10.176 CentOS6.10 3306 MySQL 5.6.45
Slave (from database) 192.168.10.177 CentOS6.10 3306 MySQL 5.6.45
2, Master slave construction
1. Install MySQL database from master-slave server
yum -y install mysql*
2. Modify MySQL database password
service mysqld restart
mysql -u root -proot
use mysql;
set password for root@localhost = password('root');
3. Configure master
vim /etc/my.cnf
[mysqld]
# Add the following
# Enable binary logging
log-bin=mysql-bin
# The unique ID of the server. The default is 1. The last bit of IP is used here
server-id=175
4. Configure slave database (Slave)
vim /etc/my.cnf
[mysqld]
# Add the following
# Enable binary logging
log-bin=mysql-bin
# The unique ID of the server. The default is 1. The last bit of IP is used here
server-id=176
# Each corresponding database needs to be modified to server id = IP
5. Restart the master-slave database
service mysqld restart
mysql -u root -proot
use mysql;
--Permission of operation
grant all privileges on *.* to 'root'@'192.168.10.110' identified by 'root' with grant option;
6. Establish an account and authorize (Slave) on the master database
update user set password=password('root') where user='root' and host='root' or host='localhost';
flush privileges;
grant replication slave on *.* to 'root'@'192.168.10.176' identified by 'root';
grant replication slave on *.* to 'root'@'192.168.10.177' identified by 'root';
# Refresh the authorization authority of master-slave database
flush privileges;

– parameter interpretation:
-h: specify the MySQL host name that the client wants to log in. Log in to the local machine (localhost or 127.0.0.1) this parameter can be omitted;
-u: login user name;
-p: tell the server that a password will be used to log in. If the user name and password to log in are empty, this option can be ignored.
-REPLICATION SLAVE: assign replication rights
-.: can operate that database
-root: user name
-'%': you can log in with this account and password on all computers

7. Log in to the Master database and query the Master status
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
8. Configure slave database (Slave)
stop slave;
# Each slave database needs to be configured
change master to master_host="192.168.10.175", master_user="root", master_password="root", master_log_file="mysql-bin.000004", master_log_pos=106;
flush privileges;
start slave;

– parameter interpretation:
-master_host: set the IP address of the primary database to connect to
-master_user: set the user name of the primary database to connect to
-master_password: set the password of the primary database to connect to
-master_log_file: set the log name of the bin log of the primary database to be connected (show master status);
-master_log_pos: set the bin log record location (show master status) of the primary database to be connected (note here that the last entry does not need to be quoted. Otherwise, configuration fails)

9. Check the function status of copying from database
show slave status\G;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.176
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 552
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0 #This cannot be used as the judgment standard of master-slave delay. 0 does not completely mean that the slave database has caught up with the master database. It may be a network outage, but the timeout has not yet arrived and the slave database has not been found.
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

– parameter interpretation:
-Slave_IO_Running=Yes
-Slave_SQL_Running=Yes
– if Yes, the configuration is successful
– note: Slave_IO and slave_ The SQL process must be running normally, that is, YES, otherwise it is in the wrong state (for example, one of NO is an error).
– from library: Last_SQL_Errno: 1008
– move the synchronization pointer down one and repeat the operation
-stop slave;
-set global sql_slave_skip_counter=1;
-start slave;
-show slave status\G;

3, Master slave test
1. Master-slave database test:

In the master database, establish a database, and create tables in this database to add, delete, and modify data

create database cluster;
use cluster;
create table user(id int(3),name char(10));
insert into user values(001,'SERE');
insert into user values(002,'Tom');
insert into user values(003,'Luck');
delete from user where id='2';
update user set name='Lilei' where id='1';
show databases;
show tables;
select * from user;
2. Querying from the database (Slave)
show databases;
use cluster;
show tables;
select * from user;

Tags: Database MySQL SQL vim

Posted on Wed, 24 Jun 2020 02:45:40 -0400 by Wetzut