Preparing the experimental environment

• Preparing the experimental environment

  1. Create 5 sets node node
  2. Use scp Command to copy installation package to node node
  3. For all node Node Installation mysql service
  4. start-up mysql service
  5. Set password to'Yue3314526!'
  

• Example

  [root@node1 ~]# tar -xf mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar 
  [root@node1 ~]# yum -y install *.rpm
  [root@node1 ~]# systemctl start mysqld
  [root@node1 ~]# grep "password" /var/log/mysqld.log
  [root@node1 ~]# mysql -uroot -p"yb/EtXtWq3jD"
  mysql> alter user root@'localhost' identified by "Yue3314526!";
  

Basic structure pattern

MySQL Master-Slave Synchronization

• Introduction to Master-Slave Synchronization

  • Data structure for automatic data synchronization
  • Master Server: Accept Client Access Connections
  • Slave Server: Automatically synchronize master server data

• topological structure

  master server: 192.168.4.11
   from server: 192.168.4.12
   Client: 192.168.4.9
  

  

• Principle of Master-Slave Synchronization

  Master
    - start-up binlog Journal
    
  Slave
    - Slave_IO: copy master Host binlog In the log file SQL Command to local machine relay-log In a file
    - Slave_SQL: Execute Native Machine relay-log File SQL Statement, implementation and Master Data Consistency
  

  

Configure master-slave synchronization (one master-one slave)

• Specific requirements are as follows

  database server"192.168.4.11"Configure as primary database server
   database server"192.168.4.12"Configured to be from a database server
   Client"192.168.4.9"Test Configuration
  

• Configure Master Server 192.168.4.11

  start-up binlog Log, Authorized User, View binlog log information
  

  # Start binlog log
  [root@node1 ~]# vim /etc/my.cnf
  [mysqld]
  server_id=11
  log_bin=masert11
  
  [root@node1 ~]# systemctl restart mysqld
  [root@node1 ~]# mysql -uroot -p'Yue3314526!' -e "show master status"
  [root@node1 ~]# ls /var/lib/mysql/masert11.*
  
  # Add Authorized User
  mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
  
  

• Configure slave server 192.168.4.12

  - Set up server_id
  - Ensure consistency with master server data
  - Specify Primary Library Information
  - start-up slave program
  - View status information
  

  • Set server_id

    [root@node2 ~]# vim /etc/my.cnf
    [mysqld]
    server_id=12
    
    [root@node2 ~]# systemctl restart mysqld
    

  • Ensure consistency with master server data

    Back up data on the primary server and copy backup files to the secondary server
     Restoring data from server using backup files
     In view backup files from the server binlog log information
    

    • Primary Library Backup Data

      [root@node1 ~]# mysqldump -uroot -p'Yue3314526!' --master-data --all-databases > /root/alldb.sql
      [root@node1 ~]# scp /root/alldb.sql root@192.168.4.12:/opt/
      
      # --master-data records log messages corresponding to the current backup data
      # This is probably an offset from the current time to prevent the data inserted during the build from being recorded
      

    • Recover data from server

      [root@node2 ~]# mysql -uroot -p'Yue3314526!' < /opt/alldb.sql
      [root@node2 ~]# mysql -uroot -p'Yue3314526!' -e "select count(*) from db2.user"
      
      [root@node2 ~]# grep "masert" /root/db2.sql 
      CHANGE MASTER TO MASTER_LOG_FILE='masert11.000001', MASTER_LOG_POS=441;
      # --master-data records an offset for "MASTER_LOG_POS". Master-slave synchronization will start with offset "MASTER_LOG_POS" to prevent missing
      

  • Specify master server information

    mysql> change master to
        -> master_host="192.168.4.11",				# Primary Library IP Address
        -> master_user="repluser",					# Primary Library Authorized User
        -> master_password="123qqq...A",			# Authorized User Password
        -> master_log_file="masert11.000001",		# Primary Library Log Files
        -> master_log_pos=441;						# Log offset
    
    # This offset refers to the'MASTER_recorded in db2.sql LOG_ POS'offset, or location at backup time
    
    mysql> start slave;			# Start slave process
    
    # Master information is automatically saved to the'/var/bin/mysql/master.info'file
    # If you change the primary library information, you should first execute'stop slave;', Modify before executing'start slave;'
    # mysql> stop slave;		# Stop slave process
    # mysql> change master to master_log_file="masert11.000002";	# Modify one
    # mysql> start slave;		# Open slave process
    

  • View Slave status information

    mysql> show slave status \G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.4.11		# Primary Service IP
                      Master_User: repluser			# user
                      Master_Port: 3306				# Primary Server Port
                    Connect_Retry: 60
                  Master_Log_File: masert11.000001
              Read_Master_Log_Pos: 441
                   Relay_Log_File: node2-relay-bin.000002
                    Relay_Log_Pos: 319
            Relay_Master_Log_File: masert11.000001
                 Slave_IO_Running: Yes			# The focus must be Yee
                Slave_SQL_Running: Yes			# The focus must be Yes
    
    

  • Troubleshooting

    Slave_IO_Running: No	# View the following error field Last_ IO_ Tips from Error
    

  • Related files (stored in a database directory)

    master.info					# Primary Library Configuration Information (Primary Library Configuration Information)
    relay-log.info				# Relay Log Configuration Information
     host name-relay-bin.xxxxxx	  # relay logs
     host name-relay-bin.index		  # Index file
    
    Deleting these four files can switch from being a server to being a standalone server
    # In special cases, these four files can be deleted (restart mysqld) and reconfigured (example: relay log does not match relay log configuration file)
    

    [root@node2 ~]# cat /var/lib/mysql/master.info
    

• Verify master-slave synchronization configuration

  • Add Authorized Users to Primary Server for Client Connections

    mysql> create database db3;
    mysql> grant all on db3.* to admin@"%" identified by "123qqq...A";
    mysql> grant select,insert on db2.* to admin@"%";
    
    # If previous experiments had duplicate admin users, delete
    mysql> drop user admin@"192.168.4.%";
    

  • Clients use authorized users to connect to the primary server to access data

    [root@node2 ~]# mysql -h'192.168.4.11' -uadmin -p'123qqq...A'
    
    mysql> show grants;
    
    mysql> insert into db2.user(username) values("lisi");
    mysql> select * from db2.user where username="lisi";
    
    

  • Viewing data from the server host (you can see the same data as the master server)

    [root@node2 ~]# mysql -uroot -p'Yue3314526' -e "select * from db2.user"
    
    # In the experimental environment, a mismatch between the offset and the file occurred, the binlog log log of the primary server was rebuilt, and the offset resolution from the server was reconfigured
    # Read_Master_Log_Pos and Position should be the same
    

Extended Structure Type

MySQL Primary Multi-slave Structure

• topological structure

  from < -- main -- > from
          ↓
          from
  
  Client: admin(192.168.4.9)
  master server: node1(192.168.4.11)
  From Server 1: node2(192.168.4.12)
  From Server 2: node3(192.168.4.13)
  

• Construction ideas

  Configure Master Server
    - start-up binlog Log, Authorized User, View binlog log information
   Configure Slave Server
    - Set up server_id
    - Ensure consistency with master server data
    - Specify Primary Library Information
    - start-up slave program
    - View status information
  

• Implementation process

  Based on one master-slave structure, adding another slave server is enough without much work
   This is not explained in detail here
  # But you need to do it manually
  

  [root@node1 ~]# mysqldump -uroot -p'Yue3314526!' --master-data --all-databases > /root/alldb_two.sql
  [root@node1 ~]# scp /root/alldb_two.sql root@192.168.4.13:/opt/
  

  [root@node3 ~]# vim /etc/my.cnf
  [mysqld]
  server_id=13
  [root@node3 ~]# systemctl restart mysqld
  [root@node3 ~]# mysql -uroot -p'Yue3314526!' < /opt/alldb_two.sql
  
  [root@node3 ~]# vim /opt/alldb_two.sql
  CHANGE MASTER TO MASTER_LOG_FILE='masert11.000001', MASTER_LOG_POS=1422;	# View offset information
  
  [root@node3 ~]# mysql -uroot -p'Yue3314526!'
  # -----Enter mysql operation-----
  
  mysql> change master to
      -> master_host="192.168.4.11",
      -> master_user="repluser",
      -> master_password="123qqq...A",
      -> master_log_file="masert11.000001",
      -> master_log_pos=1422;
  
  mysql> start slave;
  mysql> show slave status \G;
  

• Validation Tests

  Client Access to Master Server(11)Verify additions and deletions
   stay slave(12,13)You can see the same data
  

MySQL Master-Slave Structure

• topological structure

  main <-- from <-- from
  
  Client: admin(192.168.4.9)
  master server: node1(192.168.4.11)
  From Server 1: node2(192.168.4.12)
  From Server 2: node4(192.168.4.14)
  
  # Noe2 is the primary server for Noe4 node and the slave server for Noe1 node in this structure
  # The experiment is extended using the previous master-slave configuration
  
  

• Configure Master Server (node2)

  [root@node2 ~]# vim /etc/my.cnf
  [mysqld]
  server_id=12
  log_bin=master12
  log_slave_updates		# Allow cascading updates (master slave must be specified)
  
  [root@node2 ~]# systemctl restart mysqld
  
  # Add authorized users (this step can be omitted because node2 is a full backup and recovery library for node1)
  # mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
  
  mysql> show master status;
  
  [root@node2 ~]# mysqldump -uroot -p'Yue3314526!' --master-data --all-databases > /root/alldb.sql
  [root@node2 ~]# scp alldb.sql root@192.168.4.14:/opt/
  

• Configure Slave Server (node4)

  [root@node4 ~]# vim /etc/my.cnf
  [mysqld]
  server_id=14
  [root@node4 ~]# systemctl restart mysqld
  
  [root@node4 ~]# mysql -uroot -p'Yue3314526!' < /opt/alldb.sql 
  
  # View log offset for primary (node2) nodes
  [root@node2 ~]# mysql -uroot -p'Yue3314526!' -e "show master status"
  Position: 154
  
  # ---------------------------------------------------
  
  mysql> change master to
      -> master_host="192.168.4.12",
      -> master_user="repluser",
      -> master_password="123qqq...A",
      -> master_log_file="master12.000001",
      -> master_log_pos=154;
  
  mysql> start slave;
  mysql> show slave status \G;
  

• Validation effect

  1. Yes node1 Data is added, deleted, and then modified. node2 Data and node4 Data Node Data View for Validation
  
  2. stay node1 Service Add Authorization to Access Data
  mysql> grant all on db_tes.* to yueyue@"%" identified by "123qqq...A";
  
  3. Client Remote Connection node1，Accessing data
  [root@admin ~]# mysql -uyueyue -h"192.168.4.11" -p'123qqq...A'
  mysql> show grants;
  mysql> show databases;
  mysql> create database db_tes;
  mysql> create table db_tes.a(id int);
  mysql> insert into db_tes.a values(10);
  
  4. Client Remote Connection node2，node4，Looking at the data and tables, you can see and node1 Same data
  mysql> select * from db_tes.a;
  

MySQL Dual Primary Structure (Mutually Primary and Subordinate)

• Requirement

  Put the database node5 and node6 Configuration MySQL Main Main structure
  
  The steps are as follows
    - Create two new virtual machines and configure them IP address node5("192.168.4.15/24"),node6("192.168.4.16/24")
    - Install software, start services, and manage initial login passwords on two database servers
    - Configure Database Service node5
    - Configure Database Service node6
    - hold node6 Configuration node5 Slave server of
    - hold node5 Configuration node6 Slave server of
    - View on 2 servers slave Status information for processes
  
  # Note that after cloning the mirror, the MySQL implementation master and slave must ensure that the uuid is different (the concept of uuid was introduced after MySQL 5.6)
  	- mysql> show variables like '%server_uuid%';
  	- vim /var/lib/mysql/auto.cnf 
  # If the UUIDs are equal and cause an error, you can modify them manually (the length is not changed)
  

• Open binlog log for node5 and node6, respectively

  [root@node5 ~]# vim /etc/my.cnf
  [mysqld]
  server_id=15
  log_bin=master15
  [root@node5 ~]# systemctl restart mysqld
  

  [root@node6 ~]# vim /etc/my.cnf
  [mysqld]
  server_id=16
  log_bin=master16
  [root@node6 ~]# systemctl restart mysqld
  

• Configure a slave server with node6 as node5

  [root@node5 ~]# mysql -uroot -p'Yue3314526!'
  
  # View MySQL offset on node5
  mysql> show master status;
  
  # Noe5 Server Create Authorization Account
  mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
  

  [root@node6 ~]# mysql -uroot -p'Yue3314526!'
  
  mysql> change master to
      -> master_host="192.168.4.15",
      -> master_user="repluser",
      -> master_password="123qqq...A",
      -> master_log_file="master15.000001",
      -> master_log_pos=154;
  
  mysql> start slave;
  
  mysql> show slave status \G
  

• Configure a slave server with node5 as node6

  mysql> show master status;
  mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";
  

  [root@node5 ~]#  mysql -uroot -p'Yue3314526!'
  
  mysql> change master to
      -> master_host="192.168.4.16",
      -> master_user="repluser",
      -> master_password="123qqq...A",
      -> master_log_file="master16.000001",
      -> master_log_pos=154;
  
  mysql> start slave;
  
  mysql> show slave status \G;
  

Master-Slave Synchronous Replication Mode

type

• Asynchronous replication mode (default)

  After a transaction is executed by the primary library, the results are immediately returned to the client, regardless of whether they have been received and processed from the library.
  

• Fully synchronous replication mode

  The result is returned to the client when the primary library has executed a transaction and all the secondary libraries have executed the transaction.
  
  Keyword: MySQL Group Synchronization
  

• Semisynchronous replication in semi-synchronous replication mode

  Between asynchronous and full synchronous replication.
  After a transaction is completed by the primary library, wait for at least one receipt from the library to be written to"relay log"Return the result to the client only in.
  
  Semi-synchronous replication is provided by modules
  

Semi-synchronous replication mode

• Explain

  The experiment follows the previous one MySQL Double Primary Structure(Mutually dominant and subordinate)
  Because each node The server is both a master library and a slave library, so the following module configurations need to be configured
  

• Check to see if dynamic loading of modules is allowed

  # Default Allow
  mysql> show variables like "have_dynamic_loading";
  

• Command Line Load Plugin

  Use database administrator root user to access services

  # install plugin plugin type SONAME Plugin Name
  
  # Execution on primary server
  mysql> install plugin rpl_semi_sync_master SONAME "semisync_master.so";
  
  # Execute from server
  mysql> install plugin rpl_semi_sync_slave SONAME "semisync_slave.so";
  
  mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";
   - plugin_status: ACTIVE
   - plugin_status: ACTIVE
  

• Enable semi-synchronous replication

  Semi-synchronous replication, off by default

  # Primary Server Execution
  mysql> set global rpl_semi_sync_master_enabled=1;
  
  # Execute from Server
  mysql> set global rpl_semi_sync_slave_enabled=1;
  
  mysql> show variables like "rpl_semi_sync_%_enabled";
  

• Permanently enable semi-synchronous replication (write to configuration file)

  Modify Master Profile/ect/my.cnf

  Add under [mysqld]

  # This configuration is done when a server is both primary and slave
  
  plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
  rpl_semi_sync_master_enabled=1;
  rpl_semi_sync_slave_enabled=1;
  
  # Once the configuration is complete, you can restart the MySQL service and review the configuration status for judgment
    - show variables like "rpl_semi_sync_%_enabled";
  

  # This configuration is written to the configuration file for each case when the master and slave are separated
  
  # master server
  plugin-load=rpl_semi_sync_master=semisync_master.so
  rpl_semi_sync_master_enabled=1;
  
  # from server
  plugin-load=rpl_semi_sync_slave=semisync_slave.so
  rpl_semi_sync_slave_enabled=1;