2.6 lnmp架构_MySQL2

最新推荐文章于 2023-03-11 16:04:54 发布
最新推荐文章于 2023-03-11 16:04:54 发布
阅读量226 收藏
点赞数
分类专栏： 企业实战 文章标签： 数据库 mysql linux
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_47133613/article/details/115604707
版权

在将数据scp给别的mysql时，需要先将mysql stop；

数据的备份与恢复

2：关闭apache
关闭memcache
3：关闭apache
关闭memcache

把二进制日志，复制到本地上

一主两从

1。首先要合并数据：：：：

123数据mysql必须保持一致

3同步数据：
2将数据scp给3
脚本my.cnf
init.d
修改环境变量
3创建用户，useradd mysql
记得删除data目录，这个目录里是server2的数据

[root@server1 local]# /etc/init.d/mysqld stop
Shutting down MySQL............ SUCCESS! 
[root@server1 local]# scp -r mysql/ server3:/usr/local/
[root@server1 local]# scp /etc/init.d/mysqld server3:/etc/init.d/
[root@server2 local]# scp /etc/my.cnf server3:/etc/

[root@server3 ~]# vim /etc/my.cnf		//修改
####
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=3
####
[root@server3 ~]# useradd -M -d /usr/local/mysql/ -s /sbin/nologin mysql	//创建用户
[root@server3 ~]# vim .bash_profile 	//修改环境变量
####
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH
####
[root@server3 ~]# source .bash_profile 
[root@server3 ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/root/bin:/usr/local/mysql/bin

[root@server3 ~]# cd /usr/local/mysql/
[root@server3 mysql]# cd data/
[root@server3 data]# rm -fr *			//删除data目录中server2的数据

3进行mysql初始化
start启动数据库

不要激活plugin，回车就是no

[root@server3 data]# mysqld --initialize --user=mysql	//初始化数据
2021-04-10T02:54:01.229393Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-10T02:54:01.683738Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-10T02:54:01.812624Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-10T02:54:01.901260Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 01392f72-99a8-11eb-a8f5-5254003540d0.
2021-04-10T02:54:01.907702Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-04-10T02:54:02.327794Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-10T02:54:02.351887Z 1 [Note] A temporary password is generated for root@localhost: Xw#k_uBQ&9r(		//这是密码

[root@server3 data]# /etc/init.d/mysqld start		//脚本启动mysql
Starting MySQL.Logging to '/usr/local/mysql/data/server3.err'.
 SUCCESS! 						//启动成功
[root@server3 data]# mysql_secure_installation 		//设置密码

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?		//不要设置密码强壮度检测

Press y|Y for Yes, any other key for No: 
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : 

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

master主库备份数据发送给3，
mysqldump
mysqldump存在问题：会删除数据

[root@server1 local]# /etc/init.d/mysqld start		//server1脚本启动mysql
Starting MySQL. SUCCESS! 
[root@server1 local]# mysql -pwestos
@@@@
mysql> use westos;
Database changed
mysql> create table user_tb (
    -> username varchar(25) not null,
    -> password varchar(50) not null);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into user_tb values ('user1','1111');
Query OK, 1 row affected (0.02 sec)

mysql> insert into user_tb values ('user2','2222');
Query OK, 1 row affected (0.01 sec)
@@@@
[root@server1 ~]# mysqldump -uroot -pwestos westos > dump.db	//备份数据
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@server1 ~]# scp dump.db server3:/root		//将数据发送给server3

3：创建mysql的用户
mysqladmin创建用户

将之前master主库的备份数据拷到server3的westos库里

[root@server3 data]# mysqladmin create westos -pwestos
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@server3 data]# mysql -pwestos westos < /root/dump.db 
mysql: [Warning] Using a password on the command line interface can be insecure.	//这个警告是因为-p后面直接跟了密码

[root@server3 data]# mysql -pwestos	//数据和master一致
@@@@
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| westos             |
+--------------------+
5 rows in set (0.01 sec)

mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 1111     |
| user2    | 2222     |
+----------+----------+
2 rows in set (0.00 sec)
@@@@

A到B，A到C

A到B，B到C

  1. 2:vim /etc/my.cnf
    restart
    2作为3的主库
    mysql-bin是二进制的名字
    updates：将relay写到自己的二进制日志里面
[root@server2 local]# vim /etc/my.cnf
####
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=2
log-slave-updates
log-bin=mysql-bin
####
[root@server2 local]# /etc/init.d/mysqld restart	//重启服务
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

[root@server2 data]# mysql -pwestos		//server2，server3的主库进行授权

mysql> grant replication slave on *.* to repl@'%' identified by 'westos';
Query OK, 0 rows affected, 1 warning (0.01 sec)
  1. 在server3上验证刚刚创建的用户是否可以成功登陆
    在3上验证2的用户westos h
    验证结束，要退出
[root@server3 data]# mysql -h 172.25.21.3 -urepl -pwestos	//可以成功登陆
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.31-log Source distribution

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

登陆自己的
change master
记得查看2的master状态

[root@server2 data]# mysql -pwestos
@@@@
mysql> show master status;		//查看master的状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      437 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
@@@@
[root@server3 data]# mysql -pwestos
@@@@
mysql> change master to master_host='172.25.21.3', master_user='repl', master_password='westos', master_log_file='mysql-bin.000001', master_log_pos=437;				//设置主库
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;				//启用从库
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;			//查看从库的状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.21.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 437		//日志和server2主库一致
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             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: 437
              Relay_Log_Space: 529
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 8d0ad1b1-999a-11eb-b301-525400e26f27
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified
@@@@
  1. 测试
    在主库server1的westos的表中写入数据
[root@server1 ~]# mysql -pwestos
@@@@
mysql> use westos
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
mysql> insert into user_tb values ('user3','3333');
Query OK, 1 row affected (0.01 sec)

@@@@

从库server3可以查看到主库的数据
而server3的主库是server2，server2的主库是server1

[root@server3 ~]# mysql -pwestos
@@@@
mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 1111     |
| user2    | 2222     |
| user3    | 3333     |
+----------+----------+
3 rows in set (0.01 sec)
@@@@

server2也可以查看一下

[root@server2 ~]# mysql -pwestos
mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 1111     |
| user2    | 2222     |
| user3    | 3333     |
+----------+----------+
3 rows in set (0.00 sec)

全局识别

每次重启，master的号（日志也会新建一份）会改变，但是slave不会改变

要求日志不要总是在同一个日志里写

当master挂了，会将slave2作为新的主这样
这样就必须知道slave2上的二进制日志和号

每个主从都有个下一跳

全局识别ID，只关心next就可以，下一跳

如果读的和执行的一致，那么，是没有延迟的

设置GTID

  1. 1：mycnf：强制
    必须开启gtid
    master starr 改变号
[root@server1 ~]# vim /etc/my.cnf
####
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
####
[root@server1 ~]# /etc/init.d/mysqld restart 
Shutting down MySQL........... SUCCESS! 
Starting MySQL. SUCCESS! 
[root@server1 ~]# mysql -pwestos
@@@@
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000005
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
@@@@
  1. 看看server2 有没有变化
    在my.cnf写入内容和1一样的内容
    登陆mysql勘查是否一致
[root@server2 data]# vim /etc/my.cnf
####
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0
server-id=2
log-slave-updates
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
####
[root@server2 data]# /etc/init.d/mysqld restart
Shutting down MySQL............ SUCCESS! 
Starting MySQL. SUCCESS! 
[root@server2 data]# mysql -pwestos
@@@@
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.21.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: server2-relay-bin.000011
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000005
             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: 154
              Relay_Log_Space: 742
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: d9624fe3-99a2-11eb-aa09-5254005fb835
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified
@@@@
  1. 将2的slavestop
    将2改成2master
    1：自动的
    开启slave
    show
[root@server2 data]# mysql -pwestos
@@@@
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='172.25.21.2', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.21.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: server2-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000005
             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: 154
              Relay_Log_Space: 576
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: d9624fe3-99a2-11eb-aa09-5254005fb835
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified
@@@@
  1. server3也写my.cnf
    重启
    进入mysql
    stop
    change 2的IP
    start slace
    show
[root@server3 data]# vim /etc/my.cnf
####
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=3
gtid_mode=ON
enforce-gtid-consistency=ON
####
[root@server3 data]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

[root@server3 data]# mysql -pwestos
@@@@
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='172.25.21.3', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.21.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql-bin.000002
             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: 154
              Relay_Log_Space: 576
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 8d0ad1b1-999a-11eb-b301-525400e26f27
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified
@@@@
  1. 测试
    server1，master主库写入
[root@server1 data]# mysql -pwestos
@@@@
mysql> use westos;
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
mysql> insert into user_tb values ('user4','4444');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 1111     |
| user2    | 2222     |
| user3    | 3333     |
| user4    | 4444     |
+----------+----------+
4 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000005 |      428 |              |                  | d9624fe3-99a2-11eb-aa09-5254005fb835:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)
@@@@

server3，slave从库可以查看到

[root@server3 data]# mysql -pwestos
@@@@
mysql> select * from westos.user_tb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 1111     |
| user2    | 2222     |
| user3    | 3333     |
| user4    | 4444     |
+----------+----------+
4 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.21.3
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 417
               Relay_Log_File: server3-relay-bin.000002
                Relay_Log_Pos: 630
        Relay_Master_Log_File: mysql-bin.000002
             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: 417
              Relay_Log_Space: 839
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 8d0ad1b1-999a-11eb-b301-525400e26f27
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1
            Executed_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

@@@@

下面这两条GTID和server1一样：
Retrieved_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1
Executed_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1

半同步复制

假如，IO线程=NO：auth有问题，也有可能是server1主库火墙开着

master上有个线程：dump
（（（（（（他的作用：回看：13点39））））））

异步：更快一些，matser发送二进制日志变化给slave，
master只负责发送，不确保slave是否收到

（（（（（（工作原理必须要会！！！）））））））

半同步机制（5.6）：
slave只有在relaylog之后，才会给master发送ack给master
sync：刷盘
确认ack是在隐形提交之后
所以，在没有收到ack一致，IO已经完全成功提交之后，再返回用户OK，隐形提交

after sync（5.7）：
这个模式默认等待10s

（（（（（（（（理解工作原理 ！！！！！！！））））））））

半同步复制

  1. 在masterserver1安装插件
    在server2slave插件，也安个master插件
    server3安装插件
[root@server1 ~]# mysql -pwestos
@@@@
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)


[root@server2 data]# mysql -pwestos
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.01 sec)


mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

@@@@
  1. 群集生效
    1的意思：激活
    timeout：默认是时
    重启IO线程
[root@server1 ~]# mysql -pwestos
@@@@
mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

[root@server2 data]# mysql -pwestos

mysql> set global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
@@@@

server1：查询rpl的enabled是否激活
工作模式
有一个slave
超时的设置
（做金融，必须将超时设置为无穷大）

[root@server1 ~]# mysql -pwestos

mysql> show variables like ‘rpl%’;
±------------------------------------------±-----------+
| Variable_name | Value |
±------------------------------------------±-----------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
±------------------------------------------±-----------+
7 rows in set (0.00 sec)

salve2：
slaveenable是ON
要看slave状态
先停下IO线程，再启动
[root@server2 data]# mysql -pwestos

mysql> show status like ‘rpl%’;
±-------------------------------------------±------+
| Variable_name | Value |
±-------------------------------------------±------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_status | OFF |
±-------------------------------------------±------+
15 rows in set (0.01 sec)

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

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

mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> show status like ‘rpl%’;
±-------------------------------------------±------+
| Variable_name | Value |
±-------------------------------------------±------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
| Rpl_semi_sync_slave_status | ON |
±-------------------------------------------±------+
15 rows in set (0.01 sec)

3。 做23

master2：

slave3：安装插件
stop
statr

[root@server3 data]# mysql -pwestos
mysql> install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;
Query OK, 0 rows affected (0.01 sec)

mysql> set global rpl_semi_sync_slave_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

4。测试
master1：输入数据
[root@server1 data]# mysql -pwestos

mysql> use westos
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
mysql> insert into user_tb values (‘user5’,‘5555’);
Query OK, 1 row affected (0.00 sec)

5。 server2：show，倒数2行：半同步模式成功
[root@server2 data]# mysql -pwestos

mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
±---------±---------+
5 rows in set (0.00 sec)

[root@server3 data]# mysql -pwestos
mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
±---------±---------+
5 rows in set (0.00 sec)

半同步模式失败

停掉2的IO线程
[root@server3 data]# mysql -pwestos
mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

1输入数据
[root@server1 data]# mysql -pwestos
mysql> insert into user_tb values (‘user6’,‘6666’);
Query OK, 1 row affected (10.00 sec)
mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
| user6 | 6666 |
±---------±---------+
6 rows in set (0.00 sec)

mysql> show status like ‘rpl%’;
±-------------------------------------------±------+
| Variable_name | Value |
±-------------------------------------------±------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 1 |
| Rpl_semi_sync_master_no_times | 1 |
| Rpl_semi_sync_master_no_tx | 1 |
| Rpl_semi_sync_master_status | OFF |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 594 |
| Rpl_semi_sync_master_tx_wait_time | 594 |
| Rpl_semi_sync_master_tx_waits | 1 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 1 |
±-------------------------------------------±------+
14 rows in set (0.01 sec)

2上没有该数据
3更没有
[root@server2 data]# mysql -pwestos
mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
±---------±---------+
5 rows in set (0.00 sec)

[root@server3 data]# mysql -pwestos
mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
±---------±---------+
5 rows in set (0.00 sec)

1插入数据之后，超时10s还是返回了OK

超时：生产环境要将时间设置为无穷大

怎么恢复

2statr

[root@server2 data]# mysql -pwestos

mysql> start slave IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

master又是on

[root@server2 data]# mysql -pwestos

mysql> show status like ‘rpl%’;
±-------------------------------------------±------+
| Variable_name | Value |
±-------------------------------------------±------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 2 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 1024 |
| Rpl_semi_sync_master_tx_wait_time | 2048 |
| Rpl_semi_sync_master_tx_waits | 2 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 2 |
| Rpl_semi_sync_slave_status | ON |
±-------------------------------------------±------+
15 rows in set (0.00 sec)

2,3可以查看数据
[root@server2 data]# mysql -pwestos

mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
| user6 | 6666 |
±---------±---------+
6 rows in set (0.00 sec)

[root@server3 data]# mysql -pwestos

mysql> show status like ‘rpl%’;
±---------------------------±------+
| Variable_name | Value |
±---------------------------±------+
| Rpl_semi_sync_slave_status | ON |
±---------------------------±------+
1 row in set (0.00 sec)

mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
| user6 | 6666 |
±---------±---------+
6 rows in set (0.00 sec)

mysql异步复制（默认工作模式）

查看binlog
（（（（（搞明白明白二进制日志）））））））

[root@server1 data]# mysql -pwestos
mysql> show variables like ‘%bin%’;
±-------------------------------------------±--------------------------------------+
| Variable_name | Value |
±-------------------------------------------±--------------------------------------+
| bind_address | * |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_statements_unsafe_for_binlog | ON |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sql_log_bin | ON |
| sync_binlog | 1 |
±-------------------------------------------±--------------------------------------+
29 rows in set (0.00 sec)

mysql组复制协议

集群

延迟复制

好处：
在主库上不小心delete，那么从库不会立即执行delete；
在这段延迟，从库可以回滚主库，恢复数据

在2上

stop SQL——THREAD
30
start
[root@server2 data]# mysql -pwestos
mysql> stop slave SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_delay = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 976
Relay_Log_File: server2-relay-bin.000004
Relay_Log_Pos: 728
Relay_Master_Log_File: mysql-bin.000005
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: 976
Relay_Log_Space: 1511
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d9624fe3-99a2-11eb-aa09-5254005fb835
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 30
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1-3
Executed_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

1：插入数据

mysql> insert into user_tb values (‘user7’,‘7777’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
| user6 | 6666 |
| user7 | 7777 |
±---------±---------+
7 rows in set (0.00 sec)

2：show master status\G；（到了30才一样）

mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
| user6 | 6666 |
±---------±---------+
6 rows in set (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 1524
Relay_Log_File: server2-relay-bin.000004
Relay_Log_Pos: 1002
Relay_Master_Log_File: mysql-bin.000005
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: 1250
Relay_Log_Space: 2059
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: 3
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d9624fe3-99a2-11eb-aa09-5254005fb835
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 30
SQL_Remaining_Delay: 27
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1-5
Executed_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 1524
Relay_Log_File: server2-relay-bin.000004
Relay_Log_Pos: 1002
Relay_Master_Log_File: mysql-bin.000005
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: 1250
Relay_Log_Space: 2059
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: 10
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d9624fe3-99a2-11eb-aa09-5254005fb835
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 30
SQL_Remaining_Delay: 20
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1-5
Executed_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 1250
Relay_Log_File: server2-relay-bin.000004
Relay_Log_Pos: 1002
Relay_Master_Log_File: mysql-bin.000005
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: 1250
Relay_Log_Space: 1785
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d9624fe3-99a2-11eb-aa09-5254005fb835
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 30
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1-4
Executed_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select * from westos.user_tb;
±---------±---------+
| username | password |
±---------±---------+
| user1 | 1111 |
| user2 | 2222 |
| user3 | 3333 |
| user4 | 4444 |
| user5 | 5555 |
| user6 | 6666 |
| user7 | 7777 |
±---------±---------+
7 rows in set (0.00 sec)

并行复制

把半同步的参数写到朱配置文件里，让它主动生效
（有个masterr和slave）
1。 2：vim my。cnf
上面调IO
下面是优化SQL

[root@server2 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0
server-id=2
log-slave-updates
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1

slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
[root@server2 data]# /etc/init.d/mysqld restart
Shutting down MySQL… SUCCESS!
Starting MySQL. SUCCESS!

master。info记录：变更的数据，是时刻变化的

2。 在2的mysql库中
有个master和slave的表
现在为空
mysql> use mysql;
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
mysql> show tables;
±--------------------------+
| Tables_in_mysql |
±--------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
±--------------------------+
31 rows in set (0.00 sec)

mysql> select * from slave_master_info\G;
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name: mysql-bin.000005
Master_log_pos: 154
Host: 172.25.21.2
User_name: repl
User_password: westos
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: d9624fe3-99a2-11eb-aa09-5254005fb835
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 1
Channel_name:
Tls_version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show variables like ‘%info%’; //因为在配置文件里写入了相应的参数，所以，现在slave_master_info文件是以table的形式存在
±-------------------------------±---------------+
| Variable_name | Value |
±-------------------------------±---------------+
| master_info_repository | TABLE |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE |
| session_track_transaction_info | OFF |
| sync_master_info | 10000 |
| sync_relay_log_info | 10000 |
±-------------------------------±---------------+
6 rows in set (0.00 sec)

3。 init。d restart

4。 show process

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.21.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 1524
Relay_Log_File: server2-relay-bin.000006
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000005
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: 1524
Relay_Log_Space: 623
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: d9624fe3-99a2-11eb-aa09-5254005fb835
Master_Info_File: mysql.slave_master_info
SQL_Delay: 30
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: d9624fe3-99a2-11eb-aa09-5254005fb835:1-5
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> select * from slave_master_info\G;
*************************** 1. row ***************************
Number_of_lines: 25
Master_log_name: mysql-bin.000005
Master_log_pos: 154
Host: 172.25.21.2
User_name: repl
User_password: westos
Port: 3306
Connect_retry: 60
Enabled_ssl: 0
Ssl_ca:
Ssl_capath:
Ssl_cert:
Ssl_cipher:
Ssl_key:
Ssl_verify_server_cert: 0
Heartbeat: 30
Bind:
Ignored_server_ids: 0
Uuid: d9624fe3-99a2-11eb-aa09-5254005fb835
Retry_count: 86400
Ssl_crl:
Ssl_crlpath:
Enabled_auto_position: 1
Channel_name:
Tls_version:
1 row in set (0.00 sec)

ERROR:
No query specified

组复制

这个集群最多是9

从是不允许写的

可以写入任意节点，然后，将写入的数据写入到所有的节点

基于mysql主从，半同步复制机制

分布式系统：去中心化，不需要再做读写分离

组复制

1。3个节点停掉
stop

[root@server1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

[root@server2 data]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

[root@server3 data]# /etc/init.d/mysqld stop
Shutting down MySQL… SUCCESS!

2。 vim

1：删了一些
1：重新初始化

[root@server1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

#disabled_storage_engines=“MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
#server_id=1
#gtid_mode=ON
#enforce_gtid_consistency=ON
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
#binlog_checksum=NONE
#log_slave_updates=ON
#log_bin=binlog
#binlog_format=ROW

[root@server1 ~]# cd /usr/local/mysql/data/
[root@server1 data]# rm -fr *
[root@server1 data]# mysqld --initialize --user=mysql
2021-04-10T08:51:46.646916Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-10T08:51:47.016478Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-10T08:51:47.107894Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-10T08:51:47.174358Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fb85ede0-99d9-11eb-bc3d-5254005fb835.
2021-04-10T08:51:47.176707Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-04-10T08:51:47.542441Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-10T08:51:47.797245Z 1 [Note] A temporary password is generated for root@localhost: rzppg;+2AaA+
[root@server1 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to ‘/usr/local/mysql/data/server1.err’.
SUCCESS!

rm

start（#）

去掉#
还要复制一些东西
修改

[root@server1 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines=“MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add=‘group_replication.so’
transaction_write_set_extraction=XXHASH64
group_replication_group_name=“aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa”
group_replication_start_on_boot=off
group_replication_local_address= “172.25.21.2:33061”
group_replication_group_seeds= “172.25.21.2:33061,172.25.21.3:33061,172.25.21.4:33061”
group_replication_bootstrap_group=off
group_replication_ip_whitelist=“172.25.21.0/24,127.0.0.1/8”
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON

[root@server1 data]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!

再复制，写入自己的网段

restart

3。 用初始化的密码（临时的密码）

[root@server1 data]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.31-log

Copyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

首先修改密码
alter

mysql> alter user root@localhost identified by ‘westos’;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)

创建用户
create

0：县不要计入日志

create
flush：刷新

mysql> set SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user rpl_user@’%’ identified by ‘westos’
-> ;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on . to rpl_user@’%’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

1：开始计入日志

change

mysql> set SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user=‘rpl_user’, master_password=‘westos’ for channel ‘group_replication_recovery’;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

show pl

配置全局：

set global=ON

statr

OFF（其他节点不用）

mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected (2.09 sec)

mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

slect：一定是online模式

mysql> select * from performance_schema.replication_group_members;
±--------------------------±-------------------------------------±------------±------------±-------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
±--------------------------±-------------------------------------±------------±------------±-------------+
| group_replication_applier | fb85ede0-99d9-11eb-bc3d-5254005fb835 | server1 | 3306 | ONLINE |
±--------------------------±-------------------------------------±------------±------------±-------------+
1 row in set (0.00 sec)

2： rm data

删除my的下面内容

[root@server2 data]# rm -fr *
[root@server2 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

初始化

[root@server2 data]# mysqld --initialize --user=mysql
2021-04-10T09:08:22.657768Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-10T09:08:23.106521Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-10T09:08:23.206703Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-10T09:08:23.285523Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4d406e30-99dc-11eb-b5c2-525400e26f27.
2021-04-10T09:08:23.289796Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-04-10T09:08:23.668776Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-10T09:08:23.973952Z 1 [Note] A temporary password is generated for root@localhost: G%F8bwsXD0?l

vim （复制1的内容）

[root@server2 data]# vim /etc/my.cnf
mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines=“MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add=‘group_replication.so’
transaction_write_set_extraction=XXHASH64
group_replication_group_name=“aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa”
group_replication_start_on_boot=off
group_replication_local_address= “172.25.0.2:33061”
group_replication_group_seeds= “172.25.21.2:33061,172.25.21.3:33061,172.25.21.4:33061”
group_replication_bootstrap_group=off
group_replication_ip_whitelist=“172.25.21.0/24,127.0.0.1/8”
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON

启动

[root@server2 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to ‘/usr/local/mysql/data/server2.err’.
SUCCESS!

mysql -p
进入，改密码

[root@server2 data]# mysql -pG%F8bwsXD0?l
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-log

Copyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

set 0：不计入日志

按照1的步骤

mysql> alter user root@localhost identified by ‘westos’;
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)

mysql> set SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user rpl_user@’%’ identified by ‘westos’;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on . to rpl_user@’%’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user=‘rpl_user’, master_password=‘westos’ for channel ‘group_replication_recovery’;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;

start GROUP——pr；

退出，看日志
把错误的选项加到

vim my。cnnf

mysql

set gblobal（不需要脚本重启）

[root@server2 data]# vim /etc/my.cnf
[root@server2 data]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!

[root@server2 data]# mysql -pwestos
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.31-log Source distribution

Copyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (5.71 sec)

mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members;
±--------------------------±-------------------------------------±------------±------------±-------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
±--------------------------±-------------------------------------±------------±------------±-------------+
| group_replication_applier | 4d406e30-99dc-11eb-b5c2-525400e26f27 | server2 | 3306 | ONLINE |
| group_replication_applier | fb85ede0-99d9-11eb-bc3d-5254005fb835 | server1 | 3306 | ONLINE |
±--------------------------±-------------------------------------±------------±------------±-------------+
2 rows in set (0.00 sec)

3：

rm

[root@server3 data]# rm -fr *

删除配置

[root@server3 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

初始化

[root@server3 data]# mysqld --initialize --user=mysql
2021-04-10T09:29:10.032886Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-04-10T09:29:10.454962Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-04-10T09:29:10.556615Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-04-10T09:29:10.624850Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 34b94e9e-99df-11eb-b783-5254003540d0.
2021-04-10T09:29:10.627624Z 0 [Warning] Gtid table is not ready to be used. Table ‘mysql.gtid_executed’ cannot be opened.
2021-04-10T09:29:11.125782Z 0 [Warning] CA certificate ca.pem is self signed.
2021-04-10T09:29:11.477879Z 1 [Note] A temporary password is generated for root@localhost: wV.Fwo47_<YR

写配置（复制2）

[root@server3 data]# vim /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
symbolic-links=0

disabled_storage_engines=“MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY”
server_id=3
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

plugin_load_add=‘group_replication.so’
transaction_write_set_extraction=XXHASH64
group_replication_group_name=“aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa”
group_replication_start_on_boot=off
group_replication_local_address= “172.25.21.4:33061”
group_replication_group_seeds= “172.25.21.2:33061,172.25.21.3:33061,172.25.21.4:33061”
group_replication_bootstrap_group=off
group_replication_ip_whitelist=“172.25.21.0/24,127.0.0.1/8”
group_replication_single_primary_mode=OFF
group_replication_enforce_update_everywhere_checks=ON
group_replication_allow_local_disjoint_gtids_join=ON

statr

[root@server3 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to ‘/usr/local/mysql/data/server3.err’.
SUCCESS!

[root@server3 data]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-log

Copyright © 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> alter user root@localhost identified by ‘westos’;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
±-------------------+
4 rows in set (0.00 sec)

mysql> set SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create user rpl_user@’%’ identified by ‘westos’;
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on . to rpl_user@’%’;
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> set SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_user=‘rpl_user’, master_password=‘westos’ for channel ‘group_replication_recovery’;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> set global group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> start group_replication;
Query OK, 0 rows affected, 1 warning (2.07 sec)

mysql> set global group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.replication_group_members;
±--------------------------±-------------------------------------±------------±------------±-------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
±--------------------------±--------------- ----------------------±------------±------------±-------------+
| group_replication_applier | 34b94e9e-99df-11eb-b783-5254003540d0 | server3 | 3306 | ONLINE |
±--------------------------±-------------------------------------±------------±------------±-------------+
1 row in set (0.00 sec)

登陆mysql，改密码

set 0

授权

刷新哦年

4。测试是否同步

数据库必须要有主键
1：create

2：查看，插入

3：

（去中心化，没有主，大家都是主）

1：stop：故障

2：插入

1：statr
1不能同步数据
因为1不再集村里

start group——

select * from

（（（（（（（（mysql发展趋势：分布式，集群））））））））

1是引导节点
23是加进去的
不能重复打开

Outlook(^_^)
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql 进阶
m0_71618402的博客
07-27 821
下载二进制格式的mysql软件包（包要去mysql.com官网查询）1,环境变量bin2，头文件include3，库文件lib4，man文档。写隐藏文件.my.cnf配置文件，在客户端上用用户和密码登录。修改目录/usr/local/mysql的属主属组。解压软件至/usr/local/###二进制格式mysql安装。修改密码，使用临时密码登录。###mysql配置文件。##mysql进阶。###数据库密码破解。......
CentOS 安装mysql 5.7.35
lchmyhua88的博客
08-04 1719
一、安装mysql软件 先检查之前是否有安装过MySQL： 如果有的话，要先卸载： yum remove mysql mysql-server 卸载干净了！ 接着我们来安装。 下载并安装MySQL官方的 Yum Repository,Mysql版本5.7.35 wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm 下载后直接yum安装 安装MySQL服务器 yu..
Linux安装mysql
NBSP的博客
12-06 712
Linux安装mysql
MySQL5.7.18 安装时遇到的坑以及解决方案
热门推荐
vector_yuge的博客
07-08 1万+
学校教的是MSSQL，本着与校外接轨的心情，开始入MySQL的坑。结果安装就遇到了许多大坑（1045错误，3534错误，2003错误之类的）。真是够点背的。。。。。。 下面就把我遇到的坑贴出来，方便大家能跳过这些坑吧。
net start mysql MySQL MySQL 服务无法启动。服务没有报告任何错误。请键入 NET HELPMSG 3534 以获得更多的帮助。
0629的博客
03-09 8564
在win10手动安装mysql遇到的错误小记
Centos 7.4 LNMP架构_部署 glpi 9.4.3.docx
12-09
Centos 7.4 Lnmp(源码安装） 架构（Nginx 1.15.9 PHP 7.1.31 Mysql 5.7.25) ，部署glpi 9.4.3
redis服务器环境下mysql实现lnmp架构缓存
09-09
主要介绍了redis系统环境下mysql实现lnmp架构缓存，本文给大家介绍的非常详细，具有一定的参考借鉴价值,需要的朋友可以参考下
Debian+Nginx+PHP(FastCGI)+MySQL搭建LNMP服务器.rar_LNMP服务器_debian_fast
09-25
Debian+Nginx+PHP(FastCGI)+MySQL搭建LNMP服务器
LNMP架构搭建Discuz论坛教程
最新发布
04-29
LNMP架构搭建Discuz论坛教程
Lnmp.zip_lnmp_nginx
09-21
centos64系统下，php+apache+nginx+mysql编译安装文档
Windows系统上MySQL Community Server下载与安装配置
拾年一剑 的专栏
04-23 2123
详细记录MySQL Community Server下载与安装配置
mysql 5.7 mysql init_mysql 5.7安装过程中，初始化的问题
weixin_39769406的博客
01-27 336
2017-07-27T03:41:16.224833Z 0 [Warning] InnoDB: New log files created, LSN=45791mysqld:Table 'mysql.plugin' doesn't existmysqld: Table'mysql.gtid_executed'doesn't exist2017-07-27T03:41:16.371369Z 0 [W...
MySQL安装与配置
黄叶一片片
05-31 266
安装方式 编译安装： 如果对MySQL不是很熟悉，并且机器性能不咋的，不建议 软件包安装 解压缩安装
MySQL主从
weixin_60012466的博客
08-30 319
文章目录MySQL主从主从介绍主从作用主从形式主从复制原理主从复制配置二进制安装MySQLMySQL主从配置(数据一致时)在主数据库里创建一个同步账户授权给从数据库使用配置主数据库配置从数据库验证MySQL主从配置(数据不一致时)在主数据库里创建一个同步账号授权给从数据库使用配置主数据库配置从数据库验证 MySQL主从 主从介绍 在现代企业中，数据显得尤为重要，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。 想几个问题： 用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎
mysql 进阶（二）
m0_71618402的博客
07-28 141
右连接查询时，可以查询出"表2"中的所有记录和"表1"中匹配连接条件的记录。一般来说，表连接（内连接和外连接等）都可以用子查询替换，但反过来却不一定，有的子查询不能用表连接来替换。查询tb_course表中的id字段和tb_students_info表中的course_id字段相等的内容，SQL语句和运行结果如下//加where命令。在tb_students_info表和tb_course表之间，使用内连接查询学生姓名和相对应的课程名称，SQL语句和运行结果如下。...
MYSQL 多实例部署和xtrabackup
多回访
08-27 201
mysql 多实例部署软件下载配置用户并解压二进制程序至/usr/local下创建各实例数据存放的目录初始化各实例安装perl、ncuress-compat-libs依赖包配置配置文件/etc/my.cnf启动各实例初始化密码登录测试设置开机自启测试 软件下载 //配置网络源 [root@hhr ~]# curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-8.repo //下载二进制格式的mysql
安装 | MySQL
weixin_41709724的博客
03-11 589
MSQL安装部署
CA 和 Self-signed Certificate 客户端 如何验证其合法性？
DGenerationX的专栏
03-04 8152
CA         了解 CA 签发的的过程，以及他的合法性                          http://www.texnet.com.cn/help/cklc/618.html          CA 是如何 工作的?                           http://technet.microsoft.com/en-us/l
如何描述lnmp架构项目
05-20
LNMP架构项目是一个基于Linux、Nginx、MySQL和PHP的Web服务器架构。其中，Linux作为操作系统，Nginx作为Web服务器，MySQL作为关系型数据库，PHP作为服务器端动态脚本语言。该架构项目主要用于搭建高性能、高可用性的Web应用程序，通常用于支持大流量、高并发的网站。 在LNMP架构项目中，Nginx作为Web服务器具有高性能、高并发、低内存消耗等优点，能够有效地支持大量的并发访问请求。MySQL作为关系型数据库，提供了可靠的数据存储和管理能力，可以满足Web应用程序对数据的高效处理和存储需求。PHP作为服务器端脚本语言，可以实现动态网页的生成和交互逻辑的处理，提升网站的交互性和用户体验。 整个LNMP架构项目可以通过一些优化和调整来实现更高的性能和可用性，比如使用缓存技术、负载均衡策略、数据库优化等。同时，该架构也具有良好的扩展性和灵活性，可以根据实际需求进行定制和配置。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值