One 、 Internet three-tier architecture

 Three layer architecture ：
1. View layer 
2. Control layer 
3. The data layer 
 All nodes are highly available , Dual active standby 

Two 、mariadb Principle of master-slave replication

1.mariadb Basic principle of master-slave replication

 Processing method of data writing at the same time ——
 The first one is ： Locking mechanism 

1. Distributed lock ： When multiple databases ,3 Start at one node , For simultaneous requests to modify the same data in multiple databases , This data is locked , This lock is passed between multiple database servers ,
 There are special nodes to manage locks , When the first request to modify data arrives in the database , This request obtains a lock , Modifying data , Other requests arrive to apply for a lock , Found occupied , Can't modify .
2. Only when the first request is modified , Subsequent requests can obtain this lock .
3. The lock acquisition mechanism comes first .
4. Only solved the problem of reading , Writing is slow .



 The second kind ：
 Master-slave 

2.mariadb Master-slave copy schematic diagram

3、 ... and 、 Semi-synchronous replication

 Solve the reasons for the inconsistency of main data , Main database data hang up , Inconsistent data from the database .
 resolvent ： Semi-synchronous replication 
 In a semi synchronous replication scenario ,master Will monitor all slave, Ensure that at least one of the data is fully synchronized successfully ,master Will return the client information , This data was written successfully .

Four 、 Introduction to the environment

1. Node planning

mster-k8s mysqlr： 192.16.3.50  Master node 
node01-k8s： 192.168.3.51  From the node 
node02-k8s： 192.168.3.52 From the node 

2. Nodes and mariadb edition

[root@node02-k8s ~]# cat /etc/redhat-release 
CentOS Linux release 7.6.1810 (Core) 
[root@node02-k8s ~]# mariadb -v
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.6.7-MariaDB MariaDB Server

5、 ... and 、 Each node is installed mariadb

install mariadb Tutorial links ——
Mariadb User management of basic database operation

6、 ... and 、 Initial configuration of master-slave library

1. Master slave library initialization

[root@node01-k8s yum.repos.d]# mariadb-secure-installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password or using the unix_socket ensures that nobody can log into the MariaDB root user without the proper authorisation. You already have your root account protected, so you can safely answer 'n'. Switch to unix_socket authentication [Y/n] n ... skipping. You already have your root account protected, so you can safely answer 'n'. Change the root password? [Y/n] n ... skipping. By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

2. Modify the main library configuration

[root@mster-k8s mysql]# grep -Ev "^$|^#" /etc/my.cnf.d/server.cnf
[server]
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
server_id = 13    #  Each in a master-slave group id Must be unique . Recommend to use ip digit 
log-bin= mysql-bin #  Binary log , Specify the storage location later . If you just specify a name , Default store in /var/lib/mysql Next 
lower_case_table_names=1 #  Case insensitive 
binlog-format=ROW    #  Binary log file format 
log-slave-updates=True    # slave Whether the update is logged 
sync-master-info=1    #  The value is 1 Make sure that information is not lost 
slave-parallel-threads=3 # How many replication threads are started at the same time , It can be equal to the number of databases to be copied at most 
binlog-checksum=CRC32    #  Validation code 
master-verify-checksum=1    #  Start the main server to verify 
slave-sql-verify-checksum=1   #  Start slave server validation 
[galera]
[embedded]
[mariadb]
[mariadb-10.6]
[root@mster-k8s mysql]# 

3. Modify slave library configuration

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
server_id=14
#log-bin= mysql-bin #log-bin It's binary 
relay_log = relay-bin    #  relay logs ,  Specify the storage location later . If you just specify a name , Default store in /var/lib/mysql Next 
lower_case_table_names=1

4. Restart the master and slave Library Services

systemctl restart mariadb

7、 ... and 、master Node configuration

MariaDB [huawei]> grant replication slave, replication client on *.* to 'repl'@'%' identified by 'replpass';
Query OK, 0 rows affected (0.001 sec)

MariaDB [huawei]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     4992 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [huawei]> select binlog_gtid_pos('mysql-bin.000003', 4992);
+-------------------------------------------+
| binlog_gtid_pos('mysql-bin.000003', 4992) |
+-------------------------------------------+
| 0-13-85                                   |
+-------------------------------------------+
1 row in set (0.000 sec)

MariaDB [huawei]>  flush privileges;

8、 ... and 、slave Node configuration

MariaDB [(none)]> set global gtid_slave_pos='0-13-85';
Query OK, 0 rows affected (0.004 sec)

MariaDB [(none)]>  change master to master_host='192.168.3.50',master_user='repl',master_password='replpass',master_use_gtid=slave_pos;
Query OK, 0 rows affected (0.008 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> 

Nine 、 verification salve state

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Queueing master event to the relay log
                   Master_Host: 192.168.3.50
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000003
           Read_Master_Log_Pos: 5119
                Relay_Log_File: relay-bin.000835
                 Relay_Log_Pos: 303
         Relay_Master_Log_File: mysql-bin.000003
              Slave_IO_Running: Connecting
             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: 4
               Relay_Log_Space: 2146

Ten 、 Synchronize master database data

1. Export master database data

 mysqldump --all-databases > all_databases_backup.sql

2. Copy files to the slave Library

① Deactivate slave Library

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.993 sec)

② Import data

MariaDB [mysql]>  source ./all_databases_backup.sql;

③ Open slave Library

MariaDB [mysql]> start slave;
Query OK, 0 rows affected (0.301 sec)

MariaDB [mysql]> 

3. View the data imported from the library

MariaDB [mysql]> select * from huawei.student;
+----+--------+--------+--------+------+-------+-------+
| id | name   | Course | gender | age  | class | grade |
+----+--------+--------+--------+------+-------+-------+
|  1 |  Zhang San    |  Chinese language and literature    |      0 |   18 |     3 |    98 |
|  2 |  Li Si    |  mathematics    |      0 |   17 |     3 |    95 |
|  3 |  Wang Wu    |  Physics    |      1 |   16 |     2 |    88 |
|  4 |  peak    |  English    |      0 |   22 |     4 |   100 |
|  5 |  Chen Lin    |  chemical    |      1 |   15 |     5 |    99 |
+----+--------+--------+--------+------+-------+-------+
5 rows in set (0.000 sec)

MariaDB [mysql]> 

11、 ... and 、 View master-slave data synchronization

1. Main database data table content

2. The master database modifies the data

MariaDB [(none)]> use huawei;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [huawei]> insert into student ( name, gender, age, class, Course, grade ) values ( " Chen Gong ", "0", "25", "6", " mathematics ", "100"), ( " Long live the ", "1", "16", "6", " Geography ", "49" );
Query OK, 2 rows affected (0.022 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [huawei]> 

3. View data from the library

MariaDB [mysql]> select * from huawei.student;
+----+--------+--------+--------+------+-------+-------+
| id | name   | Course | gender | age  | class | grade |
+----+--------+--------+--------+------+-------+-------+
|  1 |  Zhang San    |  Chinese language and literature    |      0 |   18 |     3 |    98 |
|  2 |  Li Si    |  mathematics    |      0 |   17 |     3 |    95 |
|  3 |  Wang Wu    |  Physics    |      1 |   16 |     2 |    88 |
|  4 |  peak    |  English    |      0 |   22 |     4 |   100 |
|  5 |  Chen Lin    |  chemical    |      1 |   15 |     5 |    99 |
+----+--------+--------+--------+------+-------+-------+
5 rows in set (0.000 sec)

MariaDB [mysql]> select * from huawei.student;
+----+--------+--------+--------+------+-------+-------+
| id | name   | Course | gender | age  | class | grade |
+----+--------+--------+--------+------+-------+-------+
|  1 |  Zhang San    |  Chinese language and literature    |      0 |   18 |     3 |    98 |
|  2 |  Li Si    |  mathematics    |      0 |   17 |     3 |    95 |
|  3 |  Wang Wu    |  Physics    |      1 |   16 |     2 |    88 |
|  4 |  peak    |  English    |      0 |   22 |     4 |   100 |
|  5 |  Chen Lin    |  chemical    |      1 |   15 |     5 |    99 |
|  6 |  Chen Gong    |  mathematics    |      0 |   25 |     6 |   100 |
|  7 |  Long live the    |  Geography    |      1 |   16 |     6 |    49 |
+----+--------+--------+--------+------+-------+-------+
7 rows in set (0.000 sec)

MariaDB [mysql]>