手把手教学-MySQL主从复制架构转换MGR架构(mysq_shell版)

谷粒商城 - 个人笔记(集群部署篇一) 电商后台管理系统+电商系统,掌握微服务的全套方案。该系统有商品服务、仓储服务、订单服务、购物车服务、检索服务、认证中心服务、网关服务、用户服务、秒杀服务、第三方服务。使用SpringBoot+SpringCloud并配套SpringCloud Alibaba系列,引入全套微服务治理方案:Nacos注册中心/配置中心、Feign远程调用、Gateway网关、Redisson缓存、基于ElasticSearch7全文检索、异步编排与线程池、RabbitMQ柔性事务方案、Redisson分布式锁、分布式信号量等。 阅读详情

来源:公众号 帅萌的杂谈铺

环境信息

IPportroleinfo
192.168.188.813316node1master
192.168.188.823316node2slave1
192.168.188.833316node3slave2
  • CentOS Linux release 7.6.1810 (Core)

  • MySQL Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

  • MySQL Router Ver 8.0.20 for Linux on x86_64 (MySQL Community - GPL)

  • MySQL Shell Ver 8.0.20 for Linux on x86_64 - for MySQL 8.0.20 (MySQL Community Server (GPL))

软件位置

在三个节点上部署好MySQL、MySQL Router、MySQL Shell。

[root@ms81 ~]# ll /usr/local
total 40
drwxr-xr-x 2 root root 4096 Apr 11  2018 bin
drwxr-xr-x 2 root root 4096 Apr 11  2018 etc
drwxr-xr-x 2 root root 4096 Apr 11  2018 games
drwxr-xr-x 2 root root 4096 Apr 11  2018 include
drwxr-xr-x 2 root root 4096 Apr 11  2018 lib
drwxr-xr-x 2 root root 4096 Apr 11  2018 lib64
drwxr-xr-x 2 root root 4096 Apr 11  2018 libexec
lrwxrwxrwx 1 root root   47 May 13 14:22 myrouter -> /opt/mysql-router-8.0.20-linux-glibc2.12-x86_64
lrwxrwxrwx 1 root root   49 May 13 14:22 myshell -> /opt/mysql-shell-8.0.20-linux-glibc2.12-x86-64bit
lrwxrwxrwx 1 root root   41 May 13 14:23 mysql -> /opt/mysql-8.0.19-linux-glibc2.12-x86_64/
drwxr-xr-x 2 root root 4096 Apr 11  2018 sbin
drwxr-xr-x 5 root root 4096 Dec  4  2018 share
drwxr-xr-x 2 root root 4096 Apr 11  2018 src

搭建复制环境,并开启增强半同步

  • 所有节点配置

root@localhost [(none)]>set global super_read_only=0;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>create user 'rep'@'192.168.188.%' identified by 'rep';
Query OK, 0 rows affected (0.02 sec)

root@localhost [(none)]>grant replication slave on *.* to 'rep'@'192.168.188.%';
Query OK, 0 rows affected (0.02 sec)

root@localhost [(none)]>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)]>install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.02 sec)
  • master节点配置

root@localhost [(none)]>set global rpl_semi_sync_master_enabled=ON;
Query OK, 0 rows affected (0.01 sec)

root@localhost [(none)]>show global variables like '%semi%';
+-------------------------------------------+------------+
| 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_semi_sync_slave_enabled               | OFF        |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)

root@localhost [(none)]>reset master;
Query OK, 0 rows affected (0.04 sec)
  • slave节点配置

root@localhost [(none)]>set global rpl_semi_sync_slave_enabled=ON;
Query OK, 0 rows affected (0.00 sec)

root@localhost [(none)]>change master to master_host='192.168.188.81',master_port=3316,master_user='rep',master_password='rep',master_auto_position=1,get_master_public_key=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

root@localhost [(none)]>reset master;
Query OK, 0 rows affected (0.04 sec)
  • slave 启动复制

root@localhost [(none)]>start slave;
Query OK, 0 rows affected (0.03 sec)

root@localhost [(none)]>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.188.81
                  Master_User: rep
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 155
               Relay_Log_File: ms82-relay-bin.000002
                Relay_Log_Pos: 369
        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: 155
              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: 813316
                  Master_UUID: 70396ba6-9661-11ea-902e-0242c0a8bc51
             Master_Info_File: mysql.slave_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:
       Master_public_key_path:
        Get_master_public_key: 1
            Network_Namespace:
1 row in set (0.01 sec)
  • master查看半同步状态

root@localhost [(none)]>show global status like '%semi%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |
| 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                 | OFF   |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)

模拟业务,使用脚本产生事务

  • 建表

root@localhost [(none)]>create database kk;
Query OK, 1 row affected (0.03 sec)
root@localhost [(none)]>use kk
Database changed
root@localhost [kk]>create table k1 ( id int auto_increment primary key , dtl varchar(20) default 'abc');
Query OK, 0 rows affected (0.05 sec)
  • 开启一个session,运行脚本产生事务

[root@ms81 ~]# while :; do  echo "insert into kk.k1(dtl) values('duangduangduang');" | mysql -S /data/mysql/mysql3316/tmp/mysql.sock; sleep 1;done

转换MGR

Master在线转换为MGR

  • master配置

root@localhost [kk]>create user 'mgr'@'192.168.188.%' identified by 'mgr';
Query OK, 0 rows affected (0.01 sec)

root@localhost [kk]>grant all privileges on *.* to 'mgr'@'192.168.188.%' with grant option;
Query OK, 0 rows affected (0.02 sec)

root@localhost [kk]>set global binlog_checksum=none;
Query OK, 0 rows affected (0.02 sec)
  • 使用mysh将master转为MGR

[root@ms81 ~]# mysqlsh
MySQL Shell 8.0.20

Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
 MySQL  JS > \c mgr@192.168.188.81:3306
Creating a session to 'mgr@192.168.188.81:3306'
MySQL Error 2003 (HY000): Can't connect to MySQL server on '192.168.188.81' (111)
 MySQL  JS > \c mgr@192.168.188.81:3316
Creating a session to 'mgr@192.168.188.81:3316'
Please provide the password for 'mgr@192.168.188.81:3316': ***
Save password for 'mgr@192.168.188.81:3316'? [Y]es/[N]o/Ne[v]er (default No): Y
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 863
Server version: 8.0.19 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.


 MySQL  192.168.188.81:3316 ssl  JS > var cl = dba.createCluster('kk')
A new InnoDB cluster will be created on instance '192.168.188.81:3316'.

Validating instance configuration at 192.168.188.81:3316...

This instance reports its own address as ms81:3316

Instance configuration is suitable.
NOTE: Group Replication will communicate with other members using 'ms81:33161'. Use the localAddress option to override.

Creating InnoDB cluster 'kk' on 'ms81:3316'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

  • 将slave1加入到MGR

 MySQL  192.168.188.81:3316 ssl  JS > cl.addInstance('mgr@192.168.188.82:3316')
Please provide the password for 'mgr@192.168.188.82:3316': ***
Save password for 'mgr@192.168.188.82:3316'? [Y]es/[N]o/Ne[v]er (default No): y
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'ms82:3316' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Incremental state recovery was selected because it seems to be safely usable.

ERROR: Cannot add instance '192.168.188.82:3316' to the cluster because it has asynchronous (master-slave) replication configured and running. Please stop the slave threads by executing the query: 'STOP SLAVE;'
Cluster.addInstance: The instance '192.168.188.82:3316' is running asynchronous (master-slave) replication. (RuntimeError)

由于复制在运行,无法转换。

  • slave1停止复制

root@localhost [(none)]>stop slave;
Query OK, 0 rows affected (0.01 sec)

  • 重新用mysh将slave1加入MGR

 MySQL  192.168.188.81:3316 ssl  JS > cl.addInstance('mgr@192.168.188.82:3316')
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'ms82:3316' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Incremental state recovery was selected because it seems to be safely usable.

NOTE: Group Replication will communicate with other members using 'ms82:33161'. Use the localAddress option to override.

Validating instance configuration at 192.168.188.82:3316...

This instance reports its own address as ms82:3316

NOTE: Some configuration options need to be fixed:
+-----------------+---------------+----------------+----------------------------+
| Variable        | Current Value | Required Value | Note                       |
+-----------------+---------------+----------------+----------------------------+
| binlog_checksum | CRC32         | NONE           | Update the server variable |
+-----------------+---------------+----------------+----------------------------+

NOTE: Please use the dba.configureInstance() command to repair these issues.

ERROR: Instance must be configured and validated with dba.checkInstanceConfiguration() and dba.configureInstance() before it can be used in an InnoDB cluster.
Cluster.addInstance: Instance check failed (RuntimeError)
  • slaves 关闭binlog_checksum

root@localhost [(none)]>set global binlog_checksum=0;
Query OK, 0 rows affected (0.03 sec)
  • 重新用mysh将slave1加入MGR

 MySQL  192.168.188.81:3316 ssl  JS > cl.addInstance('mgr@192.168.188.82:3316')
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'ms82:3316' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Incremental state recovery was selected because it seems to be safely usable.

NOTE: Group Replication will communicate with other members using 'ms82:33161'. Use the localAddress option to override.
Validating instance configuration at 192.168.188.82:3316...

This instance reports its own address as ms82:3316

Instance configuration is suitable.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
Incremental state recovery is now in progress.

* Waiting for distributed recovery to finish...
NOTE: 'ms82:3316' is being recovered from 'ms81:3316'
* Distributed recovery has finished

The instance '192.168.188.82:3316' was successfully added to the cluster.

这么轻松?!

  • 配置slave2参数

root@localhost [(none)]>set global binlog_checksum=0;
Query OK, 0 rows affected (0.03 sec)

root@localhost [(none)]>stop slave;
Query OK, 0 rows affected (0.01 sec)
  • 加slave2进MGR

 MySQL  192.168.188.81:3316 ssl  JS > cl.addInstance('mgr@192.168.188.83:3316')
The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'ms83:3316' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'.

The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'.

Incremental state recovery was selected because it seems to be safely usable.

NOTE: Group Replication will communicate with other members using 'ms83:33161'. Use the localAddress option to override.

Validating instance configuration at 192.168.188.83:3316...

This instance reports its own address as ms83:3316

Instance configuration is suitable.
A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster...

Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background.
State recovery already finished for 'ms83:3316'


The instance '192.168.188.83:3316' was successfully added to the cluster.

好牛逼啊!!!!

通过查看master的error.log ,可以发现

2020-05-15T12:06:22.887737+08:00 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2020-05-15T12:06:26.438849+08:00 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2020-05-15T12:06:26.439047+08:00 0 [System] [MY-010116] [Server] /opt/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.19) starting as process 132
2020-05-15T12:06:27.486314+08:00 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-05-15T12:06:27.533114+08:00 0 [System] [MY-010931] [Server] /opt/mysql-8.0.19-linux-glibc2.12-x86_64/bin/mysqld: ready for connections. Version: '8.0.19'  socket: '/data/mysql/mysql3316/tmp/mysql.sock'  port: 3316  MySQL Community Server - GPL.
2020-05-15T12:06:27.785050+08:00 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060
2020-05-15T12:41:33.767064+08:00 915 [ERROR] [MY-011685] [Repl] Plugin group_replication reported: 'The group name option is mandatory'
2020-05-15T12:41:33.767558+08:00 915 [ERROR] [MY-011660] [Repl] Plugin group_replication reported: 'Unable to start Group Replication on boot'
2020-05-15T12:41:33.784632+08:00 915 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv4 localhost address to the whitelist. It is mandatory that it is added.'
2020-05-15T12:41:33.784661+08:00 915 [Warning] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] Automatically adding IPv6 localhost address to the whitelist. It is mandatory that it is added.'
2020-05-15T12:41:33.793816+08:00 919 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=ms81-relay-bin' to avoid this problem.
2020-05-15T12:41:33.811301+08:00 919 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_applier' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='<NULL>', master_port= 0, master_log_file='', master_log_pos= 4, master_bind=''.
2020-05-15T12:41:40.441772+08:00 915 [System] [MY-010597] [Repl] 'CHANGE MASTER TO FOR CHANNEL 'group_replication_recovery' executed'. Previous state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''. New state master_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bind=''.
2020-05-15T12:45:55.135351+08:00 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001158 - Got an error reading communication packets
2020-05-15T12:45:56.808568+08:00 12 [ERROR] [MY-011161] [Server] Semi-sync master failed on net_flush() before waiting for slave reply.
2020-05-15T12:49:18.526465+08:00 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001158 - Got an error reading communication packets
2020-05-15T12:49:19.589392+08:00 1400 [ERROR] [MY-011161] [Server] Semi-sync master failed on net_flush() before waiting for slave reply.
2020-05-15T12:50:17.725710+08:00 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001158 - Got an error reading communication packets
2020-05-15T12:50:27.883726+08:00 1462 [Warning] [MY-011153] [Server] Timeout waiting for reply of binlog (file: mysql-bin.000002, pos: 322577), semi-sync up to file mysql-bin.000002, position 322226.
2020-05-15T12:50:28.912419+08:00 11 [ERROR] [MY-011161] [Server] Semi-sync master failed on net_flush() before waiting for slave reply.
2020-05-15T12:50:36.516850+08:00 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001158 - Got an error reading communication packets
2020-05-15T12:50:47.085868+08:00 1479 [Warning] [MY-011153] [Server] Timeout waiting for reply of binlog (file: mysql-bin.000002, pos: 326533), semi-sync up to file mysql-bin.000002, position 326182.
2020-05-15T12:50:47.106412+08:00 1478 [ERROR] [MY-011161] [Server] Semi-sync master failed on net_flush() before waiting for slave reply.

查看事务session

[root@ms81 ~]# while :; do  echo "insert into kk.k1(dtl) values('duangduangduang');" | mysql -S /data/mysql/mysql3316/tmp/mysql.sock; sleep 1;done
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement
ERROR 1290 (HY000) at line 1: The MySQL server is running with the --super-read-only option so it cannot execute this statement

可以推断出, 在转为MGR过程中,由于有选举动作的产生,原事务对master 地址的访问很可能因为原master角色变更而失败,这一点需要注意。

下面停止事务,并检查三节点事务状态:

master:
root@localhost [performance_schema]>select count(*) from kk.k1;
+----------+
| count(*) |
+----------+
|     2334 |
+----------+
1 row in set (0.00 sec)

root@localhost [performance_schema]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                       |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| mysql-bin.000002 |   653354 |              |                  | 5a7ef74f-9666-11ea-b09c-0242c0a8bc51:1-1482,
70396ba6-9661-11ea-902e-0242c0a8bc51:1-904 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

slave1:

root@localhost [(none)]>select count(*) from kk.k1;
+----------+
| count(*) |
+----------+
|     2334 |
+----------+
1 row in set (0.00 sec)

root@localhost [(none)]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                       |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| mysql-bin.000002 |   422814 |              |                  | 5a7ef74f-9666-11ea-b09c-0242c0a8bc51:1-1482,
70396ba6-9661-11ea-902e-0242c0a8bc51:1-904 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

slave2:

root@localhost [(none)]>select count(*) from kk.k1;
+----------+
| count(*) |
+----------+
|     2334 |
+----------+
1 row in set (0.00 sec)

root@localhost [(none)]>show master status;
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                       |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| mysql-bin.000002 |   363306 |              |                  | 5a7ef74f-9666-11ea-b09c-0242c0a8bc51:1-1482,
70396ba6-9661-11ea-902e-0242c0a8bc51:1-904 |
+------------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

全文完。

Enjoy MySQL 8.0 :)


叶老师的「MySQL核心优化」大课已升级到MySQL 8.0,扫码开启MySQL 8.0修行之旅吧

智慧城市项目实战:如何将你的地理数据从城市坐标系(如上海2000)转换到国家CGCS2000? 本文详细介绍了智慧城市项目中地理数据坐标系转换的全流程解决方案,重点讲解如何将城市坐标系(如上海2000)转换到国家CGCS2000。通过七参数布尔莎模型、ArcGIS Pro、QGIS开源方案及Python编程实现,帮助实施团队解决坐标系不统一的技术难题,确保空间分析精度。 阅读详情

相关推荐

ClearML中文文档实战:从安装到第一个实验的保姆级教程

本文提供ClearML从零开始的实战指南,涵盖核心组件理解、Python SDK安装、服务器配置及首个自动化实验的完整流程。通过改造PyTorch训练脚本的实例,详细演示了如何利用ClearML自动记录实验参数、指标与模型,实现机器学习实验的可复现性与高效管理,并简要介绍了进阶的自动化工作流。

weixin_29216049的博客 360

shell mysql8,mysql shell安装 mysql8.0 MGR

mysql shell安装 mysql8.0 MGR1.节点信息hostnameIP地址mysql MGRmysql1172.16.230.51mysql2172.16.230.52mysql3172.16.230.532.三个节点安装mysql8实例3.每个节点初始化mysql账号create user 'fengjian'@'%' identified by '123456';grant al...

weixin_31642531的博客 355

Unity零基础到入门 ☀️| 小万字教程 对 Unity 中的 协程 ❤️全面解析+实战演练❤️

Unity,为了让Unity基础知识专栏更加完善,又肝了一篇协程来学习。 协程在Unity中还是很常用的,一起来看一下协程的基本概念和使用方法吧!

努力前行,总会成为自己心中的那道光 8万+

mysql shell 配置mysql_mysql shell安装 mysql8.0 MGR

1.节点信息hostnameIP地址mysql MGRmysql1172.16.230.51mysql2172.16.230.52mysql3172.16.230.532.三个节点安装mysql8实例3.每个节点初始化mysql账号create user 'fengjian'@'%' identified by '123456';grant all on*.* to 'fengjian...

weixin_33506815的博客 396

使用dbca添加new instance时报

10.2.0.5 on oracle linux 5.8 已经有两个节点SDB1,SDB2 ,现在要使用DBCA添加第三个节点,在进行到以下步骤时出现错误。 SID given in the connect descriptor could not be resolved. Connection to the database cannot be established be

loryliu的专栏 1792

Windows系统错误代码-----故障检测用

Microsoft Windows 系统错误代码 0000 操作已成功完成。 0001 错误的函数。 0002 系统找不到指定的文件。 0003 系统找不到指定的路径。 0004 系统无法打开文件。 0005 拒绝访问。 000...

cuiyingchan0663的博客 2545

Mysql Group Replication搭建

1. MGR介绍 Group Replication is a built-in MySQL plugin provided with MySQL Server 5.7.17 and later. MGR利用的是mysql(社区或商用)5.7.17后内置的group_replication的插件实现的,有单主多从模式和多主模式,oracle官方推荐单主多从模式,实现读写分离 由若干个节点(最多9个)共同组成一个复制组,一个事务的提交,必须经过组内大多数节点(N / 2 + 1)决议并通过,才...

Du.的博客 1280

mgr cap mysql_手把手教学-MySQL主从复制架构转换MGR架构mysq_shell

来源:公众号帅萌的杂谈铺环境信息IPportroleinfo192.168.188.813316node1master192.168.188.823316node2slave1192.168.188.833316node3slave2CentOS Linux release 7.6.1810 (Core)MySQL Ver 8.0.19 for linux-glibc2.12 on x86_64...

weixin_42513814的博客 279

MySQL-通过mysql-shell部署MGR

通过mysqlshell部署MGR

MZZHAO23的博客 1878

使用mysql shell搭建MGR

资源有限只需要准备一台Linux主机腾讯云主机159.75.158.18(1)规划部署(2)准备实例配置文件编辑3个配置文件,分别是my3381.cnfmy3382.cnfmy3383.cnf配置文件内容以my3383.cnf为例将其中对应的数字3383 改为对应端口到不同配置文件(3)创建目录(4)初始化和启动库(5)创建账号为了简化步骤,直接执行此步骤不规范,正常操作应当是#在本地通过socket方式登入执行选择数据用户和密码在使用。

DBA_ChenJR的博客 1297

MySQL MGR

ProxySQL 介绍 ProxySQL 是基于 MySQL 的一款开源的中间件的产品,是一个灵活的 MySQL 代理层,可以实现读写分离,支持 Query 路由功能,支持动态指定某个 SQL 进行缓存,支持动态加载(无需重启 ProxySQL 服务),故障切换和一些 SQL 的过滤功能。 MGR 单主 + VIP MGR MGRMySQL Group Replication)是MySQL自带的一个插件,可以灵活部署。MySQL ...

summer_fish的专栏 5029

mysql mgr 启动_使用MySQL Shell创建MGR

本篇知识点:配置MGR所需的参数使用MySQL Shell配置MGRshell.connect()var 设定临时变量dba.createCluster()dba.getCluster()dba.addInstance()dba.removeInstance()dba.switchToMultiPrimaryMode()dba.switchToSinglePrimaryMode()完全依靠MySQ...

weixin_42332647的博客 938

mysql mgr搭建_MySQL Shell副本集和MGR快速搭建详解

墨墨导读:本文是 MySQL Shell 快速搭建 副本集 和 MGR集群的全过程,分享至此,希望对大家有帮助。简介MySQL ShellMySQL Server的高级客户端和代码编辑器。除了和mysql命令行客户端程序一样,使用常规的SQL功能外,MySQL Shell还提供了JavaScript和Python的脚本功能,并包含多个API,其中的AdminAPI用于操作InnoDB Clust...

weixin_36163504的博客 245

【数据库】MySQL InnoDB Cluster 高可用架构部署(MGR 3节点)

InnoDB Cluster是MySQL官方的高可用方案,基于Group Replication实现,包含三个核心组件:MySQL Server节点(数据同步)、MySQL Shell(集群管理工具)和MySQL Router(请求路由)。本实践采用3节点架构(1主2从),支持自动故障转移与强一致性。所有节点运行MySQL 8.0.43,配置统一但需调整server-id和通信地址。关键配置包括GTID同步、组复制插件加载及单主模式设定,确保高可用性和透明扩展能力。架构通过PAXOS协议保障数据一致

FixPng的博客 1276

mysql副本集_MySQL Shell副本集和MGR快速搭建详解

####配置#vi /etc/profiltexport PATH=/opt/idc/mysql-shell8.0.20/bin:$PATH#Source /etc/profile说明:https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-connections.html备注:是不是很简单,但需要注意每个mysql本都有对应shell...

weixin_42327217的博客 530

mysql+分布+框架_MySQL mgr+mysqlrouter高可用架构及配置

一、MySQL Group Replicationmysql官方基于组复制概念并充分参考MariaDBGalera Cluster和Percona XtraDB Cluster结合而来的新的高可用集群架构。只支持5.7以上mgr 优点:高一致性,基于原生复制及paxos协议的组复制技术.高容错性,有自动检测机制,当出现宕机后,会自动剔除问题节点,其他节点可以正常使用(类似zk集群),当不同节点...

weixin_39636645的博客 1442

[MY-013129] [Server]

通过这些步骤,你可以更全面地了解问题原因,并采取相应的措施来解决它。如果问题依然存在,可能需要更深入的调查或联系 MySQL 社区或专业支持获取帮助。似乎来自 MySQL 服务器的错误日志。

喝醉酒的小白 2480

mysql8 MGR集群安装,组复制,单主模式,多主模式

mysql8 MGR集群安装,组复制,单主模式,多主模式

2234

GreatSQL FAQ

GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。 [toc] 关于GreatSQL及MGR的FAQ,持续更新中。 0. GreatSQL简介 GreatSQL是由万里数据库维护的MySQL分支,开源、免费。GreatSQL基于Percona Server,在其基础上进一步提升MGRMySQL Group Replication)的性能及可靠性。此外,GreatSQL合并了华为鲲鹏计算团队贡献的Patch,实现了InnoDB并行查询特性,以及对InnoDB事务锁的优化。 .

GreatSQL2021的博客 762

技术分享 | innodb-cluster 扫盲-安装篇

作者:杨涛涛 本文介绍用 MySQL Shell 搭建 MGR 的详细过程。 1、使用前,关掉防火墙,包括 selinux,firewalld,或者 MySQL 企业的firewall(如果用了企业的话) 2、两台机器:(4 台 MySQL 实例) 192.168.2.219 centos-ytt57-1 3311/3312 192.168.2.229 centos-ytt57-...

ActionTech的博客 1988

MGR 问题处理

将my.cnf中的参数group_replication_group_seeds设置为只包含除自身外其他group成员的ip地址以及内部通信端口,如果写成group所有成员的IP地址则会出现这个错误,这和相同网段的MGR部署方式有些差异。解决方案:有些成员有组内其它成员没有的事务,set global group_replication_allow_local_disjoint_gtids_join=ON;解决方法:配置group_replication_ip_whitelist。

chenglong411的博客 1869

Dba.checkInstanceConfiguration: This function is not available through a session to an instance

文章目录问题描述解决方法 在配置MySQL InnoDB Cluster时出现报错,本5.7.24 问题描述 在MySQL Shell中执行如下命令时,出错 MySQL localhost:3307 JS &gt; dba.checkInstanceConfiguration('root@localhost:3307') Dba.checkInstanceConfiguration: Thi...

小白 6056

matlab开发-AstronomyToolbox

matlab开发-AstronomyToolbox。基础天文学标准

微博评论爬虫_爬取微博评论_微博分析_评论情感分析

该项目名为“weibo-comment-crawler-master”,主要利用编程技术爬取微博评论数据,进行分析并评估评论情感。爬虫技术是项目基础,使用Python的requests库获取网页内容,用BeautifulSoup或lxml解析HTML,提取数据。可能需要模拟登录微博,涉及cookie、session和验证码识别。数据保存可使用CSV或MySQL数据库,Python的pandas库可将数据结构化并保存为CSV,而mysql-connector-python可将数据存入MySQL。词频分析使用nltk或jieba进行中文分词,统计词频并生成词云图。情感分析使用SnowNLP或HanLP等预训练模型,基于LSTM或BERT等深度学习技术判断评论情感倾向。可通过设置阈值或情感评分区分积极与消极评论。该项目涵盖了网络爬虫、数据存储、文本分析和情感分析等多个IT技术,是一个综合性的实战项目,对提升数据分析和NLP技能有帮助。资源来源于网络分享,仅用于学习交流使用,请勿用于商业,如有侵权请联系我删除!

上一篇: 死锁案例三
下一篇: 手把手教学-MySQL主从复制架构转换MGR架构(手动版)
老叶茶馆_
博客等级 码龄9年 1137粉丝 411原创
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值