简述mysql 主从复制原理及其工作过程，配置一主两从并验证

第一种基于binlog的主从同步

首先对主库进行配置：

[root@openEuler-1 ~]# vim /etc/my.cnf

启动服务

[root@openEuler-1 ~]# systemctl enable --now mysqld

主库的配置

从库的配置

第一个从库

[root@openEuler-1 ~]# vim /etc/my.cnf

[root@openEuler-1 ~]# systemctl enable --now mysqld

mysql> change master to
    -> master_host='192.168.1.13',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='binlog.000001',
    -> master_log_pos=678;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

第二个从库

[root@openEuler-1 ~]# vim /etc/my.cnf

[root@openEuler-1 ~]# systemctl enable --now mysqld

mysql> change master to
    -> master_host='192.168.1.13',
    -> master_user='rep',
    -> master_password='123456',
    -> master_log_file='binlog.000001',
    -> master_log_pos=678;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

进行测试

在主库中

mysql> create database test;
mysql> use test;
mysql> create table td1(id int(10),name varchar(20));
mysql> insert into td1 values (1,'张三'),(2,'李遂');
从库中

第二种基于gtid的主从同步配置

在主库和从库中分别开启gtid

[root@openEuler-1 ~]# vim /etc/my.cnf

[root@openEuler-1 ~]# systemctl restart mysqld.service

从库1：mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> change master to
    -> master_host='192.168.1.13',
    -> master_user='rep',
    -> master_password='123456',
    -> master_auto_position=1;
Query OK, 0 rows affected, 7 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status \G
*************************** 1. row ***************************
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            
 

从库2：

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change replication source to
    -> source_host='192.168.1.13',
    -> source_user='rep',
    -> source_password='123456',
    -> source_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start replica;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status \G
*************************** 1. row ***************************
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试：

主库：

mysql> create database py;
Query OK, 1 row affected (0.01 sec)

mysql> use py;
Database changed
mysql> create table yp(id int(10), name varchar(20));
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> insert into yp values (1,'张三');
Query OK, 1 row affected (0.01 sec)

从库中