• 8.0MGR单主模式搭建_克隆(clone)插件方式

    为了应对事务一致性要求很高的系统对高可用数据库系统的要求，并且增强高可用集群的自管理能力，避免节点故障后的failover需要人工干预或其它辅助工具干预，MySQL5.7新引入了Group Replication，用于搭建更高事务一致性的高可用数据库集群系统。MGR是基于Paxos协议的Group Replication搭建的系统，不仅可以自动进行failover，而且同时保证系统中多个节点之间的事务一致性，避免因节点故障或网络问题而导致的节点间事务不一致。此外还提供了节点管理的能力，真正将整个集群做为一个整体对外提供服务。

    MGR是基于原生复制及paxos协议的组复制技术，并以插件的方式提供，可以采取多主模式和单主模式。

    在单主模式下，会自动选主，只有一个节点可以对外提供写/读事务的服务，而其它所有节点只能提供只读事务的服务，是官方推荐的Group Replication复制模式。（目前最多支持9个节点）

    在多主模式下，每个节点都可以对外提供读写事务的服务。但在多主模式下，多个节点间的事务可能有比较大的冲突，从而影响性能，并且对查询语句也有更多的限制。

    MySQL 8.0.17的克隆clone简介

    MySQL 8.0.17的克隆插件允许在本地或从远程 MySQL 实例在线克隆数据，从此搭建从库可以不再需要备份工具（PXB或mysqldump）来实现了。克隆数据是存储在 InnoDB 其中的数据的物理快照，其中包括库、表、表空间和数据字典元数据。克隆的数据包含一个功能齐全的数据目录，允许使用克隆插件进行 MySQL 服务器配置。

    克隆插件支持两种克隆方式

    本地克隆

    本地克隆：本地克隆操作将启动克隆操作的 MySQL 服务器实例中的数据克隆到同服务器或同节点上的一个目录里。

    远程克隆

    远程克隆：默认情况下，远程克隆操作会删除接受者(recipient)数据目录中的数据，并将其替换为捐赠者(donor)的克隆数据。（可选）您也可以将数据克隆到接受者的其他目录，以避免删除现有数据。

    远程克隆操作和本地克隆操作克隆的数据没有区别，数据是相同的。克隆插件支持复制。除克隆数据外，克隆操作还从捐赠者中提取并传输复制位置信息，并将其应用于接受者，从而可以使用克隆插件来配置组复制或主从复制。使用克隆插件进行配置比复制大量事务要快得多，效率更高。

    MySQL 8.0 clone插件提供从一个实例克隆数据的功能，克隆功能提供了更有效的方式来快速创建MySQL实例，搭建主从复制和组复制。本文介绍使用 MySQL 8.0 clone 插件快速添加组复制（MGR）节点的方法。

    限制条件

    • 版本大于等于8.0.17且不支持跨版本。要求相同版本号，您无法MySQL 5.7和MySQL 8.0之间进行克隆，在8.0.19和8.0.20之间也不可以，而且要求版本>=8.0.17。
    • 克隆操作期间不允许使用 DDL，允许并发DML。
    • 两台机器具有相同的操作系统OS。同一平台同一架构，例如linux to windows、x64 to x32 是不支持。
    • 两台MySQL实例具体相同的 innodb_page_size 和 innodb_data_file_path(ibdata文件名)
    • 同一时刻仅仅允许有一个克隆任务存在
    • recipient 需要设置变量clone_valid_donor_list
    • max_allowed_packet 大于2M
    • doner的undo表空间文件名称不能重复
    • 不会克隆my.cnf文件
    • 不会克隆binlog二进制日志。
    • 仅仅支持innodb引擎。不克隆其他存储引擎数据。MyISAM并且 CSV存储在包括sys模式的任何模式中的表都被克隆为空表。
    • 捐赠者和接受者都需要安装克隆插件
    • 捐赠者和接受者分别需要有至少BACKUP_ADMIN/CLONE_ADMIN权限的账号
    • 不支持通过MySQL router连接到捐赠者实例。
    • 默认情况下，克隆数据后会自动重新启动接受者 MySQL 实例。要自动重新启动，必须在接收方上提供监视进程以检测服务器是否已关闭。否则，在克隆数据后，克隆操作将停止并出现以下错误，并且关闭接受者 MySQL 服务器实例。此错误不表示克隆失败。这意味着必须在克隆数据后手动重新启动接受者的 MySQL 实例。
    ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).

    官网地址：https://dev.mysql.com/doc/refman/8.0/en/clone-plugin.html
     

    服务器规划

    ip

    主机名

    Server ID

    实例端口

    MGR通讯端口

    备注

    192.168.40.152

    MGR01

    1521

    3306

    3306

    3306

    33061

    MGR主

    192.168.40.153

    MGR02

    1531

    MGR备

    192.168.40.154

    MGR03

    1541

    MGR备

    MySQL版本：mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz

    操作系统：CentOS Linux release 7.9.2009

    安装MySQL

    3台主机分别使用脚本一键式安装，注意更改脚本中的package和主机名等相关参数。

    1. cat mysql8_install.sh
    2. #!/bin/bash
    3. #路径端口防火墙策略视情况更改
    4. #2.内核参数视情况修改
    5. #15.back_db备份库视情况修改
    6.  
    7. dir=$(pwd)
    8. package=mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz
    9.  
    10.  
    11. echo  "1.system parameter configure"
    12.  
    13. echo  "1.0.configure hostname"
    14. hostnamectl set-hostname MGR02
    15.  
    16. echo  "1.1.adjust system parameter"
    17. optimizeSystemConf(){
    18. conf_exist=$(cat /etc/sysctl.conf|grep mysql|wc -l)
    19. if [ $conf_exist -eq 0 ]; then
    20.     echo "optimize system core conf"
    21. 	cat >> /etc/sysctl.conf <
    22. #add by mysql
    23. #/proc/sys/kernel/优化
    24. # 10000 connect remain:
    25. kernel.sem = 250 162500 250 650	 
    26.  
    27. #notice: shall shmmax is base on 16GB, you may adjust it for your MEM
    28. #TODO: open blow two paramenter may make error like this: can not fork xxxx, just reboot your computer ~
    29.  
    30. for 2GB Mem:
    31. kernel.shmall = 419430								
    32. kernel.shmmax = 171796918     
    33.  
    34. #for 4GB Mem:
    35. #kernel.shmall = 838860								
    36. #kernel.shmmax = 3435973836  
    37.  
    38. #for 8GB Mem:
    39. #kernel.shmall = 1677721								
    40. #kernel.shmmax = 6871947673  
    41.  
    42. #for 16GB Mem:
    43. #kernel.shmall = 3774873								
    44. #kernel.shmmax = 8589934592 
    45.  
    46. #for 32GB Mem:
    47. #kernel.shmall = 7549747
    48. #kernel.shmmax = 17179869184
    49. #for 64GB Mem:
    50. #kernel.shmall = 15099494
    51. #kernel.shmmax = 34359738368
    52. #for 128GB Mem:
    53. #kernel.shmall = 30198988
    54. #kernel.shmmax = 68719476736
    55. #for 256GB Mem:
    56. #kernel.shmall = 60397977
    57. #kernel.shmmax = 137438953472
    58. #for 512GB Mem:
    59. #kernel.shmall = 120795955
    60. #kernel.shmmax = 274877906944
    61.  
    62. kernel.shmmni = 4096		
    63.  
    64. vm.dirty_background_ratio=2 
    65. vm.dirty_ratio = 40			
    66.  
    67. vm.overcommit_memory = 2	
    68. vm.overcommit_ratio = 90 	
    69.  
    70. vm.swappiness = 0 				
    71.  
    72. fs.aio-max-nr = 1048576		
    73. fs.file-max = 6815744		
    74. fs.nr_open = 20480000       
    75.  
    76. # TCP端口使用范围
    77. net.ipv4.ip_local_port_range = 10000 65000
    78. net.ipv4.tcp_keepalive_time = 1200
    79. net.ipv4.tcp_keepalive_probes = 3
    80. net.ipv4.tcp_keepalive_intvl = 30
    81. net.ipv4.tcp_max_syn_backlog = 8192
    82. net.ipv4.tcp_max_tw_buckets = 6000
    83. # 记录的那些尚未收到客户端确认信息的连接请求的最大值
    84. net.ipv4.tcp_max_syn_backlog = 65536
    85. # 每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
    86. net.core.somaxconn=1024
    87. net.core.netdev_max_backlog = 32768
    88. net.core.wmem_default = 8388608
    89. net.core.wmem_max = 1048576
    90. net.core.rmem_default = 8388608
    91. net.core.rmem_max = 16777216
    92. net.ipv4.tcp_synack_retries = 2
    93. net.ipv4.tcp_fin_timeout = 30
    94. net.ipv4.tcp_syn_retries = 2
    95. net.ipv4.route.gc_timeout = 100
    96. net.ipv4.tcp_wmem = 8192 436600 873200
    97. net.ipv4.tcp_rmem  = 32768 436600 873200
    98. net.ipv4.tcp_mem = 94500000 91500000 92700000
    99. net.ipv4.tcp_max_orphans = 3276800
    100. EOF
    101. else
    102.    echo "system configuration is already optimized, so we do nothing"
    103. fi
    104. }
    105. optimizeSystemConf
    106.  
    107. echo  "1.2.Optimize Limit"
    108. optimizeLimitConf(){
    109. conf_exist=$(cat /etc/security/limits.conf|grep mysql|wc -l)
    110. if [ $conf_exist -eq 0 ]; then
    111. 	echo "optimize limit configuration"
    112. 	cat >> /etc/security/limits.conf << "EOF"
    113. #add by mysql
    114. * soft  nproc   65536
    115. * hard  nproc   65536
    116. * soft  nofile  65536
    117. * hard  nofile  65536
    118. * soft  stack   10240
    119. * hard  stack   32768
    120. * soft core unlimited
    121. * hard core unlimited
    122. EOF
    123. else
    124. 	echo "limit is already optimized, so we do nothing"
    125. fi
    126. }
    127. optimizeLimitConf
    128.  
    129. echo  "1.3.firewall config"
    130. function conf_firewall() {
    131. ##################gt>0
    132. if [ $(systemctl status firewalld.service | grep -c running) -gt 0 ]; then  
    133.      #systemctl stop firewalld.service
    134.      #systemctl disable firewalld.service 
    135.      firewall-cmd --zone=public --add-port=3306/tcp --permanent
    136.      firewall-cmd --zone=public --add-port=22/tcp --permanent
    137.      firewall-cmd --reload
    138.      #禁用防火墙区域偏移
    139.      sed -i 's/^AllowZoneDrifting=yes/AllowZoneDrifting=no/' /etc/firewalld/firewalld.conf 
    140.    else
    141.    echo "firewall not open"
    142. fi
    143. }
    144. conf_firewall
    145.  
    146. echo  "1.4.adjust optimize selinux"
    147. sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
    148. setenforce 0 
    149.  
    150. echo  "1.5关闭 numa和禁用透明大页"
    151. sed -i "s/quiet/quiet numa=off transparent_hugepage=never/g"  /etc/default/grub 
    152. grub2-mkconfig -o /etc/grub2.cfg
    153.  
    154.  
    155. #echo  "1.6.os iso mount"
    156. #mount $dir/*.iso /mnt/
    157. #cat << EOF >> /etc/fstab
    158. #/dev/sr0    /mnt        iso9660 loop            0 0
    159. #EOF
    160. #
    161. #mkdir -p /etc/yum.repos.d/bak
    162. #mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
    163. #cat >> /etc/yum.repos.d/os.repo <<"EOF"
    164. #[OS1]
    165. #name=OS
    166. #baseurl=file:///mnt
    167. #enabled=1
    168. #gpgcheck=0
    169. #EOF
    170.  
    171.  
    172. echo  "2. variable list"
    173. FILE_CONF=/topsoft/mysqldb/my.cnf
    174. DATADIR=/topsoft/mysqldb/data
    175. BASEDIR=/topsoft/mysqldb/mysql
    176. SCRIPTS_DIR=/topsoft/mysqldb/scripts
    177. LOGPATH=/topsoft/mysqldb/data/log
    178.  
    179. echo  "3. mysql exits"
    180. id $mysql >& /dev/null
    181. if [ $? -ne 0 ]
    182. then
    183.         echo "mysql already exits"
    184. else 
    185.         echo "mysql not exits，please create"
    186.         groupadd mysql
    187.         useradd -r -g mysql -s /bin/false mysql
    188. fi
    189.  
    190. echo  "4.create directory"
    191. if [ ! -d /topsoft/mysqldb ]
    192. then
    193.         cd /topsoft
    194. 	    mkdir -p mysqldb/{data,tmp,log,binlog,relaylog,mysql-files,backup,scripts}
    195. else 	
    196.         echo "/topsoft/mysqldb directory exits，please exit"	
    197.         exit 1
    198. fi
    199.  
    200. echo "5. unzip"
    201. cd $dir
    202. tar -xvf $package -C /topsoft/mysqldb
    203. echo "mysql upzip success"
    204. echo "directory rights"
    205. mv /topsoft/mysqldb/mysql*x86_64 /topsoft/mysqldb/mysql
    206. chown -R mysql:mysql /topsoft/mysqldb
    207.  
    208. #-------------------------------install mysql------------------------------------
    209. echo "7. install dependency package"
    210. #强制关掉yum进程
    211. rm -f /var/run/yum.pid 
    212. #yum install -y vim ncurses-devel libaio-devel gcc gcc-c++ cmake autoconf net-tools perl lib
    213.  
    214. echo "9. editor my.cnf"
    215. cat > /topsoft/mysqldb/my.cnf  << "EOF"
    216. [client]
    217. port = 3306
    218. socket = /topsoft/mysqldb/data/mysql.sock
    219. default-character-set = utf8mb4
    220.  
    221. [mysql]
    222. # 设置mysql客户端默认字符集
    223. default-character-set = utf8mb4
    224. socket = /topsoft/mysqldb/data/mysql.sock
    225. prompt="\\u@\\h :\\d \\R:\\m:\\s>" #设置命令行提示符
    226.  
    227. [mysqld]
    228. #操作用户#
    229. user=mysql
    230.  
    231. #目录#
    232. basedir=/topsoft/mysqldb/mysql  #mysql安装根目录
    233. datadir=/topsoft/mysqldb/data #mysql数据文件所在目录
    234. socket = /topsoft/mysqldb/data/mysql.sock
    235.  
    236. #字符集#
    237. character-set-server = utf8mb4 #数据库默认字符集,注意不要再用utf8了
    238. collation-server = utf8mb4_general_ci #数据库字符集对应一些排序规则,要属于character-set-server对应值的集合内
    239. init_connect='SET NAMES utf8mb4' #设置client连接mysql时的字符集,防止乱码
    240.  
    241. #运行实例相关#
    242. server_id = 103 #Mysql服务实例的唯一编号 每个mysql服务实例Id需唯一 可设置成ip最后一位
    243. port = 3306 #服务端口号 默认3306
    244. pid_file=/topsoft/mysqldb/data/mysqld.pid #pid文件的路径
    245. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    246.  
    247. #二进制日志#
    248. log_bin = /topsoft/mysqldb/binlog/mybinlog  #二进制日志文件
    249. binlog_format = ROW
    250. binlog_expire_logs_seconds=604800 #mysql binlog日志文件保存的过期时间7天，过期后自动删除;默认值是0，不限制,这样会占用空间太多 单位秒
    251. max_binlog_size = 1G #限制单个文件大小,默认大小:1,073,741,824,即1G,太大了
    252.  
    253. #慢查询日志#
    254. log_queries_not_using_indexes = 1 #把未使用到索引的sql记录到慢查询日志
    255. slow_query_log = 1 #是否打开慢查询sql日志
    256. slow_query_log_file = /topsoft/mysqldb/log/mysql-slow.log #慢查询sql日志的文件地址
    257. long_query_time = 1 #慢查询执行的秒数，超过这个值则会被记录到慢查询日志
    258.  
    259. #导出文件到指定目录
    260. secure_file_priv=/topsoft/mysqldb/mysql-files
    261.  
    262. #增加临时表空间大小限制参数
    263. innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:1G
    264.  
    265. #时区#
    266. default_time_zone="+8:00" #设置默认服务器时区
    267. log_timestamps = system #解决日志中时间和本地差8小时
    268.  
    269. #认证策略解决登录ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded#
    270. default_authentication_plugin = mysql_native_password 
    271.  
    272. #错误日志#
    273. log_error=/topsoft/mysqldb/log/mysqld_error.log 
    274.  
    275. #性能参数#
    276. open_files_limit = 65535
    277. back_log=600 #连接数达到max_connections时，新来的请求将会被存在堆栈中。数量超过back_log，将不被授予连接资源
    278. max_connections = 1000 #最大并发连接数，过小会影响连接的数量，报Too many connections错误，过大会导致服务资源用完无响应，最大值不能超过100000
    279. max_user_connections=1000 #指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。
    280. table_open_cache = 1024 #能同时打开表的个数
    281. table_definition_cache = 1024
    282. thread_stack = 512K
    283. thread_cache_size = 1500
    284. sort_buffer_size = 12M   #只是在需要的时候才分配，并且在那些操作做完之后就释放了
    285. join_buffer_size = 12M
    286. read_buffer_size = 24M  #读入缓冲区大小,对表进行顺序扫描时将分配1个读入缓冲区。对表的顺序扫描请求非常频繁，并且频繁扫描进行得太慢可增加。只是在需要的时候才分配，并且在操作结束后就释放
    287. read_rnd_buffer_size = 8M  #随机读缓冲区大小,当按任意顺序读取行时(如:排序)，将分配一个随机读缓存区。只是在需要的时候才分配，并且在那些操作做完之后就释放
    288. bulk_insert_buffer_size = 4M
    289. interactive_timeout = 600
    290. wait_timeout = 600
    291. tmp_table_size = 48M  #heap(堆积)表缓冲大小,提高联接查询速度。只是在需要的时候才分配，并且在那些操作做完之后就释放了
    292. max_heap_table_size = 32M
    293. binlog_cache_size = 12M
    294. max_binlog_cache_size = 50M
    295. key_buffer_size=256M  #索引缓冲区大小。内存在4GB左右的服务器该参数可设置为256M或384M
    296.  
    297. #库表名不区分大小写#
    298. lower_case_table_names = 1
    299.  
    300. #数据安全#
    301. innodb_flush_log_at_trx_commit = 2 #每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，但并不会立即刷写到磁盘
    302. innodb_log_buffer_size=64M  #将日志写入日志磁盘文件前的缓冲大小
    303. innodb_log_file_size = 256M #InnoDB redo log大小
    304.  
    305. #最大允许的包#
    306. max_allowed_packet = 48M
    307.  
    308. #超时#
    309. interactive_timeout = 1800 #MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭  MySQL默认的wait_timeout 值为8个小时,
    310. wait_timeout = 1800  #interactive_timeout参数需要同时配置才能生效
    311.  
    312. #禁用域名的解析#
    313. skip_name_resolve = 1 #dns慢的情况下会影响性能，禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间
    314.  
    315. #禁用符号链接以防止各种安全风险
    316. skip_symbolic_links=yes
    317.  
    318. #innodb是否为每个表使用独立的表空间文件#
    319. innodb_file_per_table = 1 #开启该参数的时候，Innodb将每个新创建的表的数据及索引存储在一个独立的.ibd文件里，而不是系统的表空间。
    320.  
    321. #innodb缓冲池的大小设置#
    322. #说明缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数，否则mysql会自动调整为相应的倍数#
    323. innodb_buffer_pool_chunk_size= 256M   定义了buffer中每个chunk的大小  chunk 块
    324. innodb_buffer_pool_size = 16G  #物理服务器内存的50%~75%  缓存 InnoDB存储引擎的表中的数据和索引数据，提高查询访问速度
    325.  
    326. #抑制即不显示 [Warning] [MY-013712] [Server] No suitable 'keyring_component_metadata_query' service implementation found to fulfill the request.
    327. log_error_suppression_list='MY-013712'
    328. EOF
    329. mv /etc/my.cnf /tmp/my.cnf
    330. ln -s /topsoft/mysqldb/my.cnf /etc/my.cnf
    331.  
    332. echo "10. add path to profile --> PASS"
    333. echo 'LANG=en_US.UTF-8' >> /etc/profile
    334. echo 'export PATH=$PATH:/topsoft/mysqldb/mysql/bin' >> /etc/profile
    335. echo 'export MYSQL_DATA=/topsoft/mysqldb/data' >> /etc/profile
    336. echo 'export MYSQL_HOME=/topsoft/mysqldb/mysql' >> /etc/profile
    337. source /etc/profile
    338.  
    339.  
    340. echo "11. directory privileges"
    341. chown -R mysql:mysql /topsoft/mysqldb
    342. chmod -R 755 /topsoft/mysqldb
    343.  
    344. echo  "12. start initialize mysql..."
    345. #--basedir 安装目录
    346. #--datadir 数据目录
    347. /topsoft/mysqldb/mysql/bin/mysqld --initialize  --user=mysql  --basedir=/topsoft/mysqldb/mysql  --datadir=/topsoft/mysqldb/data --console
    348.  
    349. echo "13. auto system start --> PASS"
    350. cat > /usr/lib/systemd/system/mysqld.service << "EOF"
    351. [Unit]
    352. Description=MySQL Server
    353. Documentation=man:mysqld(8)
    354. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
    355. After=network.target
    356. After=syslog.target
    357.  
    358. [Install]
    359. WantedBy=multi-user.target
    360.  
    361. [Service]
    362. User=mysql
    363. Group=mysql
    364. ExecStart=/topsoft/mysqldb/mysql/bin/mysqld --defaults-file=/topsoft/mysqldb/my.cnf
    365. LimitNOFILE=65536  
    366. LimitNPROC=65536
    367. EOF
    368.  
    369. chmod +x /usr/lib/systemd/system/mysqld.service
    370.  
    371. systemctl daemon-reload 
    372. systemctl enable mysqld 
    373. systemctl start mysqld 
    374. systemctl status mysqld 
    375. if [ $? -eq 0 ];then
    376.     echo "start success"
    377. else
    378.     echo "start fail"
    379. fi
    380. sleep 10
    381. if [ -f /topsoft/mysqldb/data/mysql.sock ] 
    382. then 
    383.      echo "mysql.sock exist";
    384. 	 ln -s /topsoft/mysqldb/data/mysql.sock /tmp/mysql.sock
    385. else
    386.      echo "The file doesn't exist"
    387. fi	 
    388.  
    389. echo "14. change mysql root password and root remote visit--> PASS"
    390. passwd=$(grep password /topsoft/mysqldb/log/mysqld_error.log | head -1 | awk 'END {print $NF}')
    391. echo "mysql" > /topsoft/mysqldb/.pass
    392.  
    393. cat > /topsoft/mysqldb/change.sql  << "EOF"
    394. use mysql;
    395. FLUSH PRIVILEGES;
    396. alter user 'root'@'localhost' identified with mysql_native_password by 'mysql';
    397. alter user 'root'@'localhost'  password expire never;
    398. FLUSH PRIVILEGES;
    399. create user 'root'@'%' identified with mysql_native_password by 'mysql';
    400. alter user 'root'@'%'  password expire never;
    401. FLUSH PRIVILEGES;
    402. grant all privileges on *.*  to   'root'@'%' with grant option;
    403. FLUSH PRIVILEGES;
    404. --create user 'root'@'192.168.16.%' identified with mysql_native_password by 'mysql';
    405. --grant all privileges on *.* to 'root'@'192.168.16.%'   with grant option;
    406. --alter user 'root'@'192.168.16.%'  password expire never;
    407. --FLUSH PRIVILEGES;
    408. EOF
    409.  
    410. mysql -uroot -p"$passwd"  -e "source /topsoft/mysqldb/change.sql;"
    411. #创建业务库 智能分析平台
    412. #mysql -uroot -p"${mysql_passwd}" -e "create database operational_platform default charset utf8mb4  collate utf8mb4_general_ci;" 
    413. #mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
    414. #创建普通用户并授权远程访问
    415. #mysql -uroot -p"${mysql_passwd}" -e "create user 'znfxpt'@'%' identified by 'mysql';"
    416. #mysql -uroot -p"${mysql_passwd}" -e "grant all privileges on znfxpt_test.* to 'znfxpt'@'%' with grant option;"
    417. #mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
    418. #修改密码
    419. #mysql -uroot -p"${mysql_passwd}" -e "alter user 'znfxpt'@'%' identified with mysql_native_password by 'mysql';"
    420. #mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
    421. #创建只读用户
    422. #mysql -uroot -p"${mysql_passwd}" -e "create user 'query_user'@'%' identified by 'mysql';"
    423. #mysql -uroot -p"${mysql_passwd}" -e "grant select on 'znfxpt'.* to query_user@'%';
    424. #mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
    426. echo "18.数据库信息"
    427. echo "数据库信息:mysql;密码：mysql;port:3306"

    修改MySQL参数

    group_name的值不能设置为每个节点的uuid,无论有多少个节点（目前mgr最多支持9个），uuid都必须一致。

    1. 增加到[mysqld]模块下面：
    2. -- 节点1
    3. cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
    4. cat >> /etc/my.cnf << "EOF"
    5. #add  Replication configuration parameters
    6. server_id = 1521
    7. binlog_checksum=NONE
    8. log_slave_updates = 1
    9. gtid_mode=ON
    10. enforce_gtid_consistency=on
    11. log_slave_updates=ON
    12.  
    13. master_info_repository=TABLE
    14. relay_log_info_repository=TABLE
    15. relay_log=mgr152-relay-bin
    16.  
    17.  
    18. transaction_write_set_extraction=XXHASH64
    19. plugin_load_add='group_replication.so'
    20. group_replication_group_name="34975c79-405c-11eb-9f4c-5254044caef1"
    21. group_replication_start_on_boot=OFF
    22. group_replication_local_address= "192.168.40.152:33061"
    23. group_replication_group_seeds= "192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061"
    24. group_replication_bootstrap_group=OFF
    25. loose-group_replication_recovery_retry_count=31536000
    26. loose-group_replication_single_primary_mode=on
    27. loose-group_replication_enforce_update_everywhere_checks=off
    28. loose-group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154"
    29. report_host=192.168.40.152
    30. report_port=3306
    31. EOF
    32.  
    33. -- 节点2
    34. cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
    35. cat >> /etc/my.cnf << "EOF"
    36. #add  Replication configuration parameters
    37. server_id = 1531
    38. binlog_checksum=NONE
    39. log_slave_updates = on
    40. gtid_mode=ON
    41. enforce_gtid_consistency=on
    42.  
    43. master_info_repository=TABLE
    44. relay_log_info_repository=TABLE
    45.  
    46. transaction_write_set_extraction=XXHASH64
    47. plugin_load_add='group_replication.so'
    48. group_replication_group_name="34975c79-405c-11eb-9f4c-5254044caef1"
    49. group_replication_start_on_boot=OFF
    50. group_replication_local_address= "192.168.40.153:33061"
    51. group_replication_group_seeds= "192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061"
    52. group_replication_bootstrap_group=OFF
    53. loose-group_replication_recovery_retry_count=31536000
    54. loose-group_replication_single_primary_mode=on
    55. loose-group_replication_enforce_update_everywhere_checks=off
    56. loose-group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154"
    57.  
    58. report_host=192.168.40.153
    59. report_port=3306
    60. EOF
    61.  
    62. -- 节点3
    63. cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
    64. cat >> /etc/my.cnf << "EOF"
    65. #add  Replication configuration parameters
    66. server_id = 1541
    67. binlog_checksum=NONE
    68. log_slave_updates = on
    69. gtid_mode=ON
    70. enforce_gtid_consistency=on
    71.  
    72. master_info_repository=TABLE
    73. relay_log_info_repository=TABLE
    74.  
    75. transaction_write_set_extraction=XXHASH64
    76.  
    77. plugin_load_add='group_replication.so'
    78. group_replication_group_name="34975c79-405c-11eb-9f4c-5254044caef1"
    79. group_replication_start_on_boot=OFF
    80. group_replication_local_address= "192.168.40.154:33061"
    81. group_replication_group_seeds= "192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061"
    82. group_replication_bootstrap_group=OFF
    83. loose-group_replication_recovery_retry_count=31536000
    84. loose-group_replication_single_primary_mode=on
    85. loose-group_replication_enforce_update_everywhere_checks=off
    86. loose-group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154"
    87.  
    88. report_host=192.168.40.154
    89. report_port=3306
    90. EOF

    重启MySQL

    3台主机分别重启MySQL服务

    1. -- 重启MySQL
    2. systemctl restart mysqld
    3. systemctl status mysqld
    4.  
    5. -- 进入MySQL
    6. mysql -uroot -pmysql
    7.  
    8. -- 远程连接MySQL
    9. mysql -uroot -pmysql -h192.168.40.152 -P3306 
    10. mysql -uroot -pmysql -h192.168.40.153 -P3306 
    11. mysql -uroot -pmysql -h192.168.40.154 -P3306
    12.  
    13. -- 查看MySQL日志
    14. tail -100f /topsoft/mysqldb/log/mysqld_error.log
    15. tail -100f /topsoft/mysqldb/log/mysqld_error.log
    16. tail -100f /topsoft/mysqldb/log/mysqld_error.log
    17.  
    18. -- 查看MySQL的主机名、server_id和server_uuid
    19. mysql -uroot -pmysql -h192.168.40.152 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
    20. mysql -uroot -pmysql -h192.168.40.153 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
    21. mysql -uroot -pmysql -h192.168.40.154 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"

    输出结果如下：

    1. --节点1
    2. [root@mysqldb01 log]# mysql -uroot -pmysql -h192.168.40.152 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
    3. mysql: [Warning] Using a password on the command line interface can be insecure.
    4. +------------+-------------+--------------------------------------+
    5. | @@hostname | @@server_id | @@server_uuid                        |
    6. +------------+-------------+--------------------------------------+
    7. | mgr01      |        1521 | 7fb72760-011c-11ef-b7aa-000c29d414b6 |
    8. +------------+-------------+--------------------------------------+
    9.  
    10. --节点2
    11. [root@localhost opt]# mysql -uroot -pmysql -h192.168.40.153 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
    12. mysql: [Warning] Using a password on the command line interface can be insecure.
    13. +------------+-------------+--------------------------------------+
    14. | @@hostname | @@server_id | @@server_uuid                        |
    15. +------------+-------------+--------------------------------------+
    16. | mgr02      |        1531 | 109d5359-0121-11ef-8acf-000c2922d68f |
    17. +------------+-------------+--------------------------------------+
    18.  
    19. --节点3
    20. [root@localhost opt]# mysql -uroot -pmysql -h192.168.40.154 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
    21. mysql: [Warning] Using a password on the command line interface can be insecure.
    22. +------------+-------------+--------------------------------------+
    23. | @@hostname | @@server_id | @@server_uuid                        |
    24. +------------+-------------+--------------------------------------+
    25. | mgr03      |        1541 | 25c048b0-0121-11ef-9874-000c294fdfdd |
    26. +------------+-------------+--------------------------------------+

    安装MGR插件

    所有节点分别安装MGR插件。

    1. mysql -uroot -pmysql -h192.168.40.152 -P3306
    2.  
    3. --安装组复制插件
    4. INSTALL PLUGIN group_replication SONAME 'group_replication.so';
    5.  
    6. -- 如果MySQL版本大于8.0.17，那么建议再安装clone插件
    7. INSTALL PLUGIN clone SONAME 'mysql_clone.so';
    8.  
    9. --查看插件是否安装成功
    10. show plugins;
    11.  
    12. --输出结果如下：
    13. +----------------------------------+----------+--------------------+----------------------+---------+
    14. | Name                             | Status   | Type               | Library              | License |
    15. +----------------------------------+----------+--------------------+----------------------+---------+
    16. | group_replication                | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
    17. | clone                            | ACTIVE   | CLONE              | mysql_clone.so       | GPL     |

    设置复制账号

    1. -- 在主库(192.168.40.152)上执行
    2. CREATE USER repl@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
    3. GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* TO repl@'%';
    4. FLUSH PRIVILEGES;
    5.  
    6. --扩展
    7. ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY 'repl';
    8. ALTER USER 'repl'@'localhost' IDENTIFIED WITH mysql_native_password BY 'repl';
    9. ALTER USER 'repl'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'repl';
    10.  
    11. -- 所有节点执行
    12. CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

    启动MGR(主库192.168.40.152上执行)

    主库192.168.40.152上执行

    1. -- 在主库(192.168.40.152)上执行
    2. SET GLOBAL group_replication_bootstrap_group=ON;
    3. START GROUP_REPLICATION;
    4. SET GLOBAL group_replication_bootstrap_group=OFF;
    5.  
    6. -- 查看MGR组信息 
    7. SELECT * FROM performance_schema.replication_group_members;

    结果输出如下：

    1. root@localhost :(none) 13:33:32>SELECT * FROM performance_schema.replication_group_members;
    2. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    3. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
    4. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    5. | group_replication_applier | 7fb72760-011c-11ef-b7aa-000c29d414b6 | 192.168.40.152 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
    6. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+

    其他节点加入MGR

    在从库（192.168.40.153，192.168.40.154）上执行

    1. --从节点加入MGR
    2. reset master;
    3.  
    4. --加入组复制
    5. START GROUP_REPLICATION;
    6.  
    7. -- 查看MGR集群组状态
    8. SELECT * FROM performance_schema.replication_group_members;
    9.  
    10. --查看复制进度
    11. select
    12. stage,
    13. state,
    14. cast(begin_time as DATETIME) as "START TIME",
    15. cast(end_time as DATETIME) as "FINISH TIME",
    16. lpad(sys.format_time(power(10,12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ') as DURATION,
    17. lpad(concat(format(round(estimate/1024/1024,0), 0), "MB"), 16, ' ') as "Estimate",
    18. case when begin_time is NULL then LPAD('%0', 7, ' ')
    19. when estimate > 0 then
    20. lpad(concat(round(data*100/estimate, 0), "%"), 7, ' ')
    21. when end_time is NULL then lpad('0%', 7, ' ')
    22. else lpad('100%', 7, ' ')
    23. end as "Done(%)"
    24. from performance_schema.clone_progress;

    结果输入如下：

    1. root@localhost :(none) 13:46:54>SELECT * FROM performance_schema.replication_group_members;
    2. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    3. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
    4. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    5. | group_replication_applier | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
    6. | group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
    7. | group_replication_applier | 7fb72760-011c-11ef-b7aa-000c29d414b6 | 192.168.40.152 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
    8. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    9. 3 rows in set (0.01 sec)

    测试数据同步

    在主节点上执行以下命令，然后在其它节点查询：

    1. --主节点上构建测试数据
    2. create database dxj;
    3. CREATE TABLE dxj.`tb1` (
    4.  `id` int(11) NOT NULL AUTO_INCREMENT,
    5.  `hostname` varchar(100) DEFAULT NULL,
    6.  `server_id` varchar(100) DEFAULT NULL,
    7.  PRIMARY KEY (`id`)
    8. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    9.  
    10.  
    11. insert into dxj.tb1(hostname,server_id) select @@hostname,@@server_id;
    12. select * from dxj.tb1;
    13.  
    14. --主节点上查询测试数据
    15. root@localhost :(none) 13:49:47>select * from dxj.tb1;
    16. +----+----------+-----------+
    17. | id | hostname | server_id |
    18. +----+----------+-----------+
    19. |  1 | mgr01    | 1521      |
    20. +----+----------+-----------+
    21. 1 row in set (0.00 sec)
    22.  
    23. --从节点查询
    24. root@localhost :(none) 13:49:47>select * from dxj.tb1;
    25. +----+----------+-----------+
    26. | id | hostname | server_id |
    27. +----+----------+-----------+
    28. |  1 | mgr01    | 1521      |
    29. +----+----------+-----------+
    30. 1 row in set (0.00 sec)

    主备自动切换测试

    关闭主库

    1. root@localhost :(none) 13:49:48>shutdown ;
    2. Query OK, 0 rows affected (0.01 sec)

    登陆原从库1查询

    结果：原从库1成为新主库

    1. root@localhost :mysql 13:50:00>select uuid();
    2. +--------------------------------------+
    3. | uuid()                               |
    4. +--------------------------------------+
    5. | 0e67c440-0136-11ef-b0cd-000c2922d68f |
    6. +--------------------------------------+
    7. 1 row in set (0.00 sec)
    8.  
    9. --查看MGR集群组状态
    10. root@localhost :mysql 13:55:15>SELECT * FROM performance_schema.replication_group_members;
    11. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    12. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
    13. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    14. | group_replication_applier | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
    15. | group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
    16. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+

    登陆原从库2查询

    结果：原从库2自动升级为新主库的从库

    1. root@localhost :(none) 13:50:06>select uuid();
    2. +--------------------------------------+
    3. | uuid()                               |
    4. +--------------------------------------+
    5. | 554d671c-0136-11ef-a8d8-000c294fdfdd |
    6. +--------------------------------------+
    7. 1 row in set (0.03 sec)
    8.  
    9. --查看MGR集群组状态
    10. root@localhost :(none) 13:57:14>SELECT * FROM performance_schema.replication_group_members;
    11. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    12. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
    13. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    14. | group_replication_applier | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
    15. | group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
    16. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    17. 2 rows in set (0.00 sec)

    原主库修复后拉起，并加入group replication组

    1. --原主库启库
    2. [root@mysqldb01 log]# systemctl start mysqld
    3.  
    4. --加入group replication组
    5. root@not_connected :(none) 14:00:19>CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';
    6. No connection. Trying to reconnect...
    7. Connection id:    10
    8. Current database: *** NONE ***
    9.  
    10. Query OK, 0 rows affected, 5 warnings (0.01 sec)
    11.  
    12. --开始同步
    13. root@localhost :(none) 14:00:41>START GROUP_REPLICATION;
    14. Query OK, 0 rows affected, 1 warning (2.91 sec)
    15.  
    16. --查看MGR集群组状态
    17. root@localhost :(none) 14:01:00>SELECT * FROM performance_schema.replication_group_members;
    18. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    19. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
    20. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    21. | group_replication_applier | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
    22. | group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
    23. | group_replication_applier | 7fb72760-011c-11ef-b7aa-000c29d414b6 | 192.168.40.152 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
    24. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    25. 3 rows in set (0.02 sec)
    26.  
    27. root@localhost :(none) 14:01:09>select uuid();
    28. +--------------------------------------+
    29. | uuid()                               |
    30. +--------------------------------------+
    31. | e8855fef-0136-11ef-8632-000c29d414b6 |
    32. +--------------------------------------+
    33. 1 row in set (0.02 sec)

    在新主节点上插入新数据

    1. --在新主节点上插入新数据
    2. insert into dxj.tb1(hostname,server_id) select @@hostname,@@server_id;
    3.  
    4. --在旧主即新从节点上查看
    5. root@localhost :(none) 14:04:52>select * from dxj.tb1;
    6. +----+----------+-----------+
    7. | id | hostname | server_id |
    8. +----+----------+-----------+
    9. |  1 | mgr01    | 1521      |
    10. |  2 | mgr02    | 1531      |
    11. +----+----------+-----------+
    12. 2 rows in set (0.01 sec)

    结果：数据与主库一致，MGR故障恢复测试成功。

    在现有的3节点中再加入1从节点

    新从节点信息：192.168.40.155

    安装MySQL

    使用脚本一键式安装，注意更改脚本中的package和主机名等相关参数。

    1. cat mysql8_install.sh
    2. #!/bin/bash
    3. #路径端口防火墙策略视情况更改
    4. #2.内核参数视情况修改
    5. #15.back_db备份库视情况修改
    6.  
    7. dir=$(pwd)
    8. package=mysql-8.0.36-linux-glibc2.17-x86_64.tar.xz
    9.  
    10.  
    11. echo  "1.system parameter configure"
    12.  
    13. echo  "1.0.configure hostname"
    14. hostnamectl set-hostname MGR02
    15.  
    16. echo  "1.1.adjust system parameter"
    17. optimizeSystemConf(){
    18. conf_exist=$(cat /etc/sysctl.conf|grep mysql|wc -l)
    19. if [ $conf_exist -eq 0 ]; then
    20.     echo "optimize system core conf"
    21. 	cat >> /etc/sysctl.conf <
    22. #add by mysql
    23. #/proc/sys/kernel/优化
    24. # 10000 connect remain:
    25. kernel.sem = 250 162500 250 650	 
    26.  
    27. #notice: shall shmmax is base on 16GB, you may adjust it for your MEM
    28. #TODO: open blow two paramenter may make error like this: can not fork xxxx, just reboot your computer ~
    29.  
    30. for 2GB Mem:
    31. kernel.shmall = 419430								
    32. kernel.shmmax = 171796918     
    33.  
    34. #for 4GB Mem:
    35. #kernel.shmall = 838860								
    36. #kernel.shmmax = 3435973836  
    37.  
    38. #for 8GB Mem:
    39. #kernel.shmall = 1677721								
    40. #kernel.shmmax = 6871947673  
    41.  
    42. #for 16GB Mem:
    43. #kernel.shmall = 3774873								
    44. #kernel.shmmax = 8589934592 
    45.  
    46. #for 32GB Mem:
    47. #kernel.shmall = 7549747
    48. #kernel.shmmax = 17179869184
    49. #for 64GB Mem:
    50. #kernel.shmall = 15099494
    51. #kernel.shmmax = 34359738368
    52. #for 128GB Mem:
    53. #kernel.shmall = 30198988
    54. #kernel.shmmax = 68719476736
    55. #for 256GB Mem:
    56. #kernel.shmall = 60397977
    57. #kernel.shmmax = 137438953472
    58. #for 512GB Mem:
    59. #kernel.shmall = 120795955
    60. #kernel.shmmax = 274877906944
    61.  
    62. kernel.shmmni = 4096		
    63.  
    64. vm.dirty_background_ratio=2 
    65. vm.dirty_ratio = 40			
    66.  
    67. vm.overcommit_memory = 2	
    68. vm.overcommit_ratio = 90 	
    69.  
    70. vm.swappiness = 0 				
    71.  
    72. fs.aio-max-nr = 1048576		
    73. fs.file-max = 6815744		
    74. fs.nr_open = 20480000       
    75.  
    76. # TCP端口使用范围
    77. net.ipv4.ip_local_port_range = 10000 65000
    78. net.ipv4.tcp_keepalive_time = 1200
    79. net.ipv4.tcp_keepalive_probes = 3
    80. net.ipv4.tcp_keepalive_intvl = 30
    81. net.ipv4.tcp_max_syn_backlog = 8192
    82. net.ipv4.tcp_max_tw_buckets = 6000
    83. # 记录的那些尚未收到客户端确认信息的连接请求的最大值
    84. net.ipv4.tcp_max_syn_backlog = 65536
    85. # 每个网络接口接收数据包的速率比内核处理这些包的速率快时，允许送到队列的数据包的最大数目
    86. net.core.somaxconn=1024
    87. net.core.netdev_max_backlog = 32768
    88. net.core.wmem_default = 8388608
    89. net.core.wmem_max = 1048576
    90. net.core.rmem_default = 8388608
    91. net.core.rmem_max = 16777216
    92. net.ipv4.tcp_synack_retries = 2
    93. net.ipv4.tcp_fin_timeout = 30
    94. net.ipv4.tcp_syn_retries = 2
    95. net.ipv4.route.gc_timeout = 100
    96. net.ipv4.tcp_wmem = 8192 436600 873200
    97. net.ipv4.tcp_rmem  = 32768 436600 873200
    98. net.ipv4.tcp_mem = 94500000 91500000 92700000
    99. net.ipv4.tcp_max_orphans = 3276800
    100. EOF
    101. else
    102.    echo "system configuration is already optimized, so we do nothing"
    103. fi
    104. }
    105. optimizeSystemConf
    106.  
    107. echo  "1.2.Optimize Limit"
    108. optimizeLimitConf(){
    109. conf_exist=$(cat /etc/security/limits.conf|grep mysql|wc -l)
    110. if [ $conf_exist -eq 0 ]; then
    111. 	echo "optimize limit configuration"
    112. 	cat >> /etc/security/limits.conf << "EOF"
    113. #add by mysql
    114. * soft  nproc   65536
    115. * hard  nproc   65536
    116. * soft  nofile  65536
    117. * hard  nofile  65536
    118. * soft  stack   10240
    119. * hard  stack   32768
    120. * soft core unlimited
    121. * hard core unlimited
    122. EOF
    123. else
    124. 	echo "limit is already optimized, so we do nothing"
    125. fi
    126. }
    127. optimizeLimitConf
    128.  
    129. echo  "1.3.firewall config"
    130. function conf_firewall() {
    131. ##################gt>0
    132. if [ $(systemctl status firewalld.service | grep -c running) -gt 0 ]; then  
    133.      #systemctl stop firewalld.service
    134.      #systemctl disable firewalld.service 
    135.      firewall-cmd --zone=public --add-port=3306/tcp --permanent
    136.      firewall-cmd --zone=public --add-port=22/tcp --permanent
    137.      firewall-cmd --reload
    138.      #禁用防火墙区域偏移
    139.      sed -i 's/^AllowZoneDrifting=yes/AllowZoneDrifting=no/' /etc/firewalld/firewalld.conf 
    140.    else
    141.    echo "firewall not open"
    142. fi
    143. }
    144. conf_firewall
    145.  
    146. echo  "1.4.adjust optimize selinux"
    147. sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config
    148. setenforce 0 
    149.  
    150. echo  "1.5关闭 numa和禁用透明大页"
    151. sed -i "s/quiet/quiet numa=off transparent_hugepage=never/g"  /etc/default/grub 
    152. grub2-mkconfig -o /etc/grub2.cfg
    153.  
    154.  
    155. #echo  "1.6.os iso mount"
    156. #mount $dir/*.iso /mnt/
    157. #cat << EOF >> /etc/fstab
    158. #/dev/sr0    /mnt        iso9660 loop            0 0
    159. #EOF
    160. #
    161. #mkdir -p /etc/yum.repos.d/bak
    162. #mv /etc/yum.repos.d/*.repo /etc/yum.repos.d/bak
    163. #cat >> /etc/yum.repos.d/os.repo <<"EOF"
    164. #[OS1]
    165. #name=OS
    166. #baseurl=file:///mnt
    167. #enabled=1
    168. #gpgcheck=0
    169. #EOF
    170.  
    171.  
    172. echo  "2. variable list"
    173. FILE_CONF=/topsoft/mysqldb/my.cnf
    174. DATADIR=/topsoft/mysqldb/data
    175. BASEDIR=/topsoft/mysqldb/mysql
    176. SCRIPTS_DIR=/topsoft/mysqldb/scripts
    177. LOGPATH=/topsoft/mysqldb/data/log
    178.  
    179. echo  "3. mysql exits"
    180. id $mysql >& /dev/null
    181. if [ $? -ne 0 ]
    182. then
    183.         echo "mysql already exits"
    184. else 
    185.         echo "mysql not exits，please create"
    186.         groupadd mysql
    187.         useradd -r -g mysql -s /bin/false mysql
    188. fi
    189.  
    190. echo  "4.create directory"
    191. if [ ! -d /topsoft/mysqldb ]
    192. then
    193.         cd /topsoft
    194. 	    mkdir -p mysqldb/{data,tmp,log,binlog,relaylog,mysql-files,backup,scripts}
    195. else 	
    196.         echo "/topsoft/mysqldb directory exits，please exit"	
    197.         exit 1
    198. fi
    199.  
    200. echo "5. unzip"
    201. cd $dir
    202. tar -xvf $package -C /topsoft/mysqldb
    203. echo "mysql upzip success"
    204. echo "directory rights"
    205. mv /topsoft/mysqldb/mysql*x86_64 /topsoft/mysqldb/mysql
    206. chown -R mysql:mysql /topsoft/mysqldb
    207.  
    208. #-------------------------------install mysql------------------------------------
    209. echo "7. install dependency package"
    210. #强制关掉yum进程
    211. rm -f /var/run/yum.pid 
    212. #yum install -y vim ncurses-devel libaio-devel gcc gcc-c++ cmake autoconf net-tools perl lib
    213.  
    214. echo "9. editor my.cnf"
    215. cat > /topsoft/mysqldb/my.cnf  << "EOF"
    216. [client]
    217. port = 3306
    218. socket = /topsoft/mysqldb/data/mysql.sock
    219. default-character-set = utf8mb4
    220.  
    221. [mysql]
    222. # 设置mysql客户端默认字符集
    223. default-character-set = utf8mb4
    224. socket = /topsoft/mysqldb/data/mysql.sock
    225. prompt="\\u@\\h :\\d \\R:\\m:\\s>" #设置命令行提示符
    226.  
    227. [mysqld]
    228. #操作用户#
    229. user=mysql
    230.  
    231. #目录#
    232. basedir=/topsoft/mysqldb/mysql  #mysql安装根目录
    233. datadir=/topsoft/mysqldb/data #mysql数据文件所在目录
    234. socket = /topsoft/mysqldb/data/mysql.sock
    235.  
    236. #字符集#
    237. character-set-server = utf8mb4 #数据库默认字符集,注意不要再用utf8了
    238. collation-server = utf8mb4_general_ci #数据库字符集对应一些排序规则,要属于character-set-server对应值的集合内
    239. init_connect='SET NAMES utf8mb4' #设置client连接mysql时的字符集,防止乱码
    240.  
    241. #运行实例相关#
    242. server_id = 103 #Mysql服务实例的唯一编号 每个mysql服务实例Id需唯一 可设置成ip最后一位
    243. port = 3306 #服务端口号 默认3306
    244. pid_file=/topsoft/mysqldb/data/mysqld.pid #pid文件的路径
    245. sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    246.  
    247. #二进制日志#
    248. log_bin = /topsoft/mysqldb/binlog/mybinlog  #二进制日志文件
    249. binlog_format = ROW
    250. binlog_expire_logs_seconds=604800 #mysql binlog日志文件保存的过期时间7天，过期后自动删除;默认值是0，不限制,这样会占用空间太多 单位秒
    251. max_binlog_size = 1G #限制单个文件大小,默认大小:1,073,741,824,即1G,太大了
    252.  
    253. #慢查询日志#
    254. log_queries_not_using_indexes = 1 #把未使用到索引的sql记录到慢查询日志
    255. slow_query_log = 1 #是否打开慢查询sql日志
    256. slow_query_log_file = /topsoft/mysqldb/log/mysql-slow.log #慢查询sql日志的文件地址
    257. long_query_time = 1 #慢查询执行的秒数，超过这个值则会被记录到慢查询日志
    258.  
    259. #导出文件到指定目录
    260. secure_file_priv=/topsoft/mysqldb/mysql-files
    261.  
    262. #增加临时表空间大小限制参数
    263. innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:1G
    264.  
    265. #时区#
    266. default_time_zone="+8:00" #设置默认服务器时区
    267. log_timestamps = system #解决日志中时间和本地差8小时
    268.  
    269. #认证策略解决登录ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded#
    270. default_authentication_plugin = mysql_native_password 
    271.  
    272. #错误日志#
    273. log_error=/topsoft/mysqldb/log/mysqld_error.log 
    274.  
    275. #性能参数#
    276. open_files_limit = 65535
    277. back_log=600 #连接数达到max_connections时，新来的请求将会被存在堆栈中。数量超过back_log，将不被授予连接资源
    278. max_connections = 1000 #最大并发连接数，过小会影响连接的数量，报Too many connections错误，过大会导致服务资源用完无响应，最大值不能超过100000
    279. max_user_connections=1000 #指同一个账号能够同时连接到mysql服务的最大连接数。设置为0表示不限制。
    280. table_open_cache = 1024 #能同时打开表的个数
    281. table_definition_cache = 1024
    282. thread_stack = 512K
    283. thread_cache_size = 1500
    284. sort_buffer_size = 12M   #只是在需要的时候才分配，并且在那些操作做完之后就释放了
    285. join_buffer_size = 12M
    286. read_buffer_size = 24M  #读入缓冲区大小,对表进行顺序扫描时将分配1个读入缓冲区。对表的顺序扫描请求非常频繁，并且频繁扫描进行得太慢可增加。只是在需要的时候才分配，并且在操作结束后就释放
    287. read_rnd_buffer_size = 8M  #随机读缓冲区大小,当按任意顺序读取行时(如:排序)，将分配一个随机读缓存区。只是在需要的时候才分配，并且在那些操作做完之后就释放
    288. bulk_insert_buffer_size = 4M
    289. interactive_timeout = 600
    290. wait_timeout = 600
    291. tmp_table_size = 48M  #heap(堆积)表缓冲大小,提高联接查询速度。只是在需要的时候才分配，并且在那些操作做完之后就释放了
    292. max_heap_table_size = 32M
    293. binlog_cache_size = 12M
    294. max_binlog_cache_size = 50M
    295. key_buffer_size=256M  #索引缓冲区大小。内存在4GB左右的服务器该参数可设置为256M或384M
    296.  
    297. #库表名不区分大小写#
    298. lower_case_table_names = 1
    299.  
    300. #数据安全#
    301. innodb_flush_log_at_trx_commit = 2 #每次事务提交时，将存储引擎log buffer中的redo日志写入到log file，但并不会立即刷写到磁盘
    302. innodb_log_buffer_size=64M  #将日志写入日志磁盘文件前的缓冲大小
    303. innodb_log_file_size = 256M #InnoDB redo log大小
    304.  
    305. #最大允许的包#
    306. max_allowed_packet = 48M
    307.  
    308. #超时#
    309. interactive_timeout = 1800 #MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭  MySQL默认的wait_timeout 值为8个小时,
    310. wait_timeout = 1800  #interactive_timeout参数需要同时配置才能生效
    311.  
    312. #禁用域名的解析#
    313. skip_name_resolve = 1 #dns慢的情况下会影响性能，禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间
    314.  
    315. #禁用符号链接以防止各种安全风险
    316. skip_symbolic_links=yes
    317.  
    318. #innodb是否为每个表使用独立的表空间文件#
    319. innodb_file_per_table = 1 #开启该参数的时候，Innodb将每个新创建的表的数据及索引存储在一个独立的.ibd文件里，而不是系统的表空间。
    320.  
    321. #innodb缓冲池的大小设置#
    322. #说明缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数，否则mysql会自动调整为相应的倍数#
    323. innodb_buffer_pool_chunk_size= 256M   定义了buffer中每个chunk的大小  chunk 块
    324. innodb_buffer_pool_size = 16G  #物理服务器内存的50%~75%  缓存 InnoDB存储引擎的表中的数据和索引数据，提高查询访问速度
    325.  
    326. #抑制即不显示 [Warning] [MY-013712] [Server] No suitable 'keyring_component_metadata_query' service implementation found to fulfill the request.
    327. log_error_suppression_list='MY-013712'
    328. EOF
    329. mv /etc/my.cnf /tmp/my.cnf
    330. ln -s /topsoft/mysqldb/my.cnf /etc/my.cnf
    331.  
    332. echo "10. add path to profile --> PASS"
    333. echo 'LANG=en_US.UTF-8' >> /etc/profile
    334. echo 'export PATH=$PATH:/topsoft/mysqldb/mysql/bin' >> /etc/profile
    335. echo 'export MYSQL_DATA=/topsoft/mysqldb/data' >> /etc/profile
    336. echo 'export MYSQL_HOME=/topsoft/mysqldb/mysql' >> /etc/profile
    337. source /etc/profile
    338.  
    339.  
    340. echo "11. directory privileges"
    341. chown -R mysql:mysql /topsoft/mysqldb
    342. chmod -R 755 /topsoft/mysqldb
    343.  
    344. echo  "12. start initialize mysql..."
    345. #--basedir 安装目录
    346. #--datadir 数据目录
    347. /topsoft/mysqldb/mysql/bin/mysqld --initialize  --user=mysql  --basedir=/topsoft/mysqldb/mysql  --datadir=/topsoft/mysqldb/data --console
    348.  
    349. echo "13. auto system start --> PASS"
    350. cat > /usr/lib/systemd/system/mysqld.service << "EOF"
    351. [Unit]
    352. Description=MySQL Server
    353. Documentation=man:mysqld(8)
    354. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
    355. After=network.target
    356. After=syslog.target
    357.  
    358. [Install]
    359. WantedBy=multi-user.target
    360.  
    361. [Service]
    362. User=mysql
    363. Group=mysql
    364. ExecStart=/topsoft/mysqldb/mysql/bin/mysqld --defaults-file=/topsoft/mysqldb/my.cnf
    365. LimitNOFILE=65536  
    366. LimitNPROC=65536
    367. EOF
    368.  
    369. chmod +x /usr/lib/systemd/system/mysqld.service
    370.  
    371. systemctl daemon-reload 
    372. systemctl enable mysqld 
    373. systemctl start mysqld 
    374. systemctl status mysqld 
    375. if [ $? -eq 0 ];then
    376.     echo "start success"
    377. else
    378.     echo "start fail"
    379. fi
    380. sleep 10
    381. if [ -f /topsoft/mysqldb/data/mysql.sock ] 
    382. then 
    383.      echo "mysql.sock exist";
    384. 	 ln -s /topsoft/mysqldb/data/mysql.sock /tmp/mysql.sock
    385. else
    386.      echo "The file doesn't exist"
    387. fi	 
    388.  
    389. echo "14. change mysql root password and root remote visit--> PASS"
    390. passwd=$(grep password /topsoft/mysqldb/log/mysqld_error.log | head -1 | awk 'END {print $NF}')
    391. echo "mysql" > /topsoft/mysqldb/.pass
    392.  
    393. cat > /topsoft/mysqldb/change.sql  << "EOF"
    394. use mysql;
    395. FLUSH PRIVILEGES;
    396. alter user 'root'@'localhost' identified with mysql_native_password by 'mysql';
    397. alter user 'root'@'localhost'  password expire never;
    398. FLUSH PRIVILEGES;
    399. create user 'root'@'%' identified with mysql_native_password by 'mysql';
    400. alter user 'root'@'%'  password expire never;
    401. FLUSH PRIVILEGES;
    402. grant all privileges on *.*  to   'root'@'%' with grant option;
    403. FLUSH PRIVILEGES;
    404. --create user 'root'@'192.168.16.%' identified with mysql_native_password by 'mysql';
    405. --grant all privileges on *.* to 'root'@'192.168.16.%'   with grant option;
    406. --alter user 'root'@'192.168.16.%'  password expire never;
    407. --FLUSH PRIVILEGES;
    408. EOF
    409.  
    410. mysql -uroot -p"$passwd"  -e "source /topsoft/mysqldb/change.sql;"
    411. #创建业务库 智能分析平台
    412. #mysql -uroot -p"${mysql_passwd}" -e "create database operational_platform default charset utf8mb4  collate utf8mb4_general_ci;" 
    413. #mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
    414. #创建普通用户并授权远程访问
    415. #mysql -uroot -p"${mysql_passwd}" -e "create user 'znfxpt'@'%' identified by 'mysql';"
    416. #mysql -uroot -p"${mysql_passwd}" -e "grant all privileges on znfxpt_test.* to 'znfxpt'@'%' with grant option;"
    417. #mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
    418. #修改密码
    419. #mysql -uroot -p"${mysql_passwd}" -e "alter user 'znfxpt'@'%' identified with mysql_native_password by 'mysql';"
    420. #mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
    421. #创建只读用户
    422. #mysql -uroot -p"${mysql_passwd}" -e "create user 'query_user'@'%' identified by 'mysql';"
    423. #mysql -uroot -p"${mysql_passwd}" -e "grant select on 'znfxpt'.* to query_user@'%';
    424. #mysql -uroot -p"${mysql_passwd}" -e "flush privileges;"
    426. echo "18.数据库信息"
    427. echo "数据库信息:mysql;密码：mysql;port:3306"

    修改MySQL参数

    group_name的值不能设置为每个节点的uuid,无论有多少个节点（目前mgr最多支持9个），uuid都必须一致。

    1. 增加到[mysqld]模块下面：
    2. -- 节点4
    3. cp /etc/my.cnf /etc/my.cnf_bak_`date +%F`
    4. cat >> /etc/my.cnf << "EOF"
    5. #add  Replication configuration parameters
    6. server_id = 1551
    7. binlog_checksum=NONE
    8. log_slave_updates = on
    9. gtid_mode=ON
    10. enforce_gtid_consistency=on
    11.  
    12. master_info_repository=TABLE
    13. relay_log_info_repository=TABLE
    14.  
    15. transaction_write_set_extraction=XXHASH64
    16.  
    17. plugin_load_add='group_replication.so'
    18. group_replication_group_name="34975c79-405c-11eb-9f4c-5254044caef1"
    19. group_replication_start_on_boot=OFF
    20. group_replication_local_address= "192.168.40.155:33061"
    21. group_replication_group_seeds= "192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061,192.168.40.155:33061"
    22. group_replication_bootstrap_group=OFF
    23. loose-group_replication_recovery_retry_count=31536000
    24. loose-group_replication_single_primary_mode=on
    25. loose-group_replication_enforce_update_everywhere_checks=off
    26. loose-group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154,192.168.40.155"
    27.  
    28. report_host=192.168.40.155
    29. report_port=3306
    30. EOF

    重启MySQL

    重启MySQL服务

    1. -- 重启MySQL
    2. systemctl restart mysqld
    3. systemctl status mysqld
    4.  
    5. -- 进入MySQL
    6. mysql -uroot -pmysql
    7.  
    8. -- 远程连接MySQL
    9. mysql -uroot -pmysql -h192.168.40.155 -P3306
    10.  
    11. -- 查看MySQL日志
    12. tail -100f /topsoft/mysqldb/log/mysqld_error.log
    13.  
    14. -- 查看MySQL的主机名、server_id和server_uuid
    15. mysql -uroot -pmysql -h192.168.40.155 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"

    输出结果如下：

    1. --节点4
    2. [root@localhost opt]# mysql -uroot -pmysql -h192.168.40.154 -P3306 -e "select @@hostname,@@server_id,@@server_uuid"
    3. mysql: [Warning] Using a password on the command line interface can be insecure.
    4. +------------+-------------+--------------------------------------+
    5. | @@hostname | @@server_id | @@server_uuid                        |
    6. +------------+-------------+--------------------------------------+
    7. | mgr04      |        1551 | 648169f4-0141-11ef-9cd0-000c29667289 |
    8. +------------+-------------+--------------------------------------+

    安装MGR插件

    安装MGR插件。

    1. mysql -uroot -pmysql -h192.168.40.155 -P3306
    2.  
    3. --安装组复制插件
    4. INSTALL PLUGIN group_replication SONAME 'group_replication.so';
    5.  
    6. -- 如果MySQL版本大于8.0.17，那么建议再安装clone插件
    7. INSTALL PLUGIN clone SONAME 'mysql_clone.so';
    8.  
    9. --查看插件是否安装成功
    10. show plugins;
    11.  
    12. --输出结果如下：
    13. +----------------------------------+----------+--------------------+----------------------+---------+
    14. | Name                             | Status   | Type               | Library              | License |
    15. +----------------------------------+----------+--------------------+----------------------+---------+
    16. | group_replication                | ACTIVE   | GROUP REPLICATION  | group_replication.so | GPL     |
    17. | clone                            | ACTIVE   | CLONE              | mysql_clone.so       | GPL     |

    新节点执行克隆任务

    查看MGR集群组状态

    现有mgr中 查看MGR集群组状态

    1. --现有mgr中 查看MGR集群组状态
    2. root@localhost :(none) 15:09:10>SELECT * FROM performance_schema.replication_group_members;
    3. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    4. | CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | MEMBER_COMMUNICATION_STACK |
    5. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    6. | group_replication_applier | 109d5359-0121-11ef-8acf-000c2922d68f | 192.168.40.153 |        3306 | ONLINE       | PRIMARY     | 8.0.36         | XCom                       |
    7. | group_replication_applier | 25c048b0-0121-11ef-9874-000c294fdfdd | 192.168.40.154 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
    8. | group_replication_applier | 7fb72760-011c-11ef-b7aa-000c29d414b6 | 192.168.40.152 |        3306 | ONLINE       | SECONDARY   | 8.0.36         | XCom                       |
    9. +---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+----------------------------+
    10. 3 rows in set (0.03 sec)

    在原3节点执行修改参数

    查看参数

    在原3节点执行修改参数

    1. --查看参数
    2. root@localhost :(none) 15:30:34>show variables like '%group_replication_group_seeds%';
    3. +-------------------------------+----------------------------------------------------------------+
    4. | Variable_name                 | Value                                                          |
    5. +-------------------------------+----------------------------------------------------------------+
    6. | group_replication_group_seeds | 192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061 |
    7. +-------------------------------+----------------------------------------------------------------+
    8. 1 row in set (0.05 sec)
    9.  
    10.  
    11. root@localhost :(none) 15:38:37>show variables like '%group_replication_ip_whitelist%';
    12. +--------------------------------+----------------------------------------------+
    13. | Variable_name                  | Value                                        |
    14. +--------------------------------+----------------------------------------------+
    15. | group_replication_ip_whitelist | 192.168.40.152,192.168.40.153,192.168.40.154 |
    16. +--------------------------------+----------------------------------------------+
    17. 1 row in set (0.01 sec)
    更改参数

    在原3节点执行修改参数

    1. set global group_replication_group_seeds='192.168.40.152:33061,192.168.40.153:33061,192.168.40.154:33061,192.168.40.155:33061';
    2. stop group_replication;
    3. set global group_replication_ip_whitelist="192.168.40.152,192.168.40.153,192.168.40.154,192.168.40.155";
    4. start group_replication;

    配置主节点的复制账号

    1. -- 所有节点执行
    2. CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='repl' FOR CHANNEL 'group_replication_recovery';

    新节点执行克隆任务

    1. --开始同步
    2. start group_replication;
    3.  
    4. -- 查看组复制成员及状态
    5. SELECT * FROM performance_schema.replication_group_members;
    6.  
    7.  
    8. -- 查看克隆进度和状态
    9. SELECT * FROM performance_schema.clone_status \G
    10.  
    11.  
    12. MySQL [(none)]> select
    13. stage,
    14. state,
    15. cast(begin_time as DATETIME) as "START TIME",
    16. cast(end_time as DATETIME) as "FINISH TIME",
    17. lpad(sys.format_time(power(10,12) * (unix_timestamp(end_time) - unix_timestamp(begin_time))), 10, ' ') as DURATION,
    18. lpad(concat(format(round(estimate/1024/1024,0), 0), "MB"), 16, ' ') as "Estimate",
    19. case when begin_time is NULL then LPAD('%0', 7, ' ')
    20. when estimate > 0 then
    21. lpad(concat(round(data*100/estimate, 0), "%"), 7, ' ')
    22. when end_time is NULL then lpad('0%', 7, ' ')
    23. else lpad('100%', 7, ' ')
    24. end as "Done(%)"
    25. from performance_schema.clone_progress;

    查看数据是否同步

    1. --从节点查询
    2. root@localhost :(none) 13:49:47>select * from dxj.tb1;
    3. +----+----------+-----------+
    4. | id | hostname | server_id |
    5. +----+----------+-----------+
    6. |  1 | mgr01    | 1521      |
    7. +----+----------+-----------+
    8. 1 row in set (0.00 sec)

    至此，通过clone插件的方式添加MGR节点已成功，非常简单也非常快速。

    问题处理

    插入数据报错

    解决方法：

    1. --给表添加主键
    2. mysql> alter table test3 add primary key(id);
    3. Query OK, 0 rows affected (0.05 sec)
    4. Records: 0 Duplicates: 0 Warnings: 0
    5.  
    6. --再次对表执行插入数据操作，成功
    7. mysql> insert into test3 values(1,'aaa');
    8. Query OK, 1 row affected (0.01 sec)

    主库一旦执行事务，备库就退出group

    主库全备传到备库，备库恢复后，发现未给复制组用户授权，在主、备库上分别执行授权后发现 一旦执行事务，备库就退出group

    原因：主备库单独执行了事务，写入了自己的binlog,导致不一致

    解决方法：

    发现不一致时执行关闭写入binlog命令，授权完毕后再开启。

    1. SET SQL_LOG_BIN=0;
    2. GRANT BACKUP_ADMIN ON *.* TO repl_user@'%';
    3. FLUSH PRIVILEGES;
    4. SET SQL_LOG_BIN=1;

    若已经不一致了，需在备库reset master再执行加入group的命令。

    主库一写数据，secondary库就离线

    主库一写数据，secondary库就离线。在日志中查询为如下错误：

    select * from performance_schema.replication_connection_status \G;

    原因：mysql为8.0.22默认使用 caching_sha2_password 身份验证机制——从原来的 mysql_native_password 更改为 caching_sha2_password。 从 5.7 升级 8.0 版本的不会改变现有用户的身份验证方法，但新用户会默认使用新的 caching_sha2_password

    解决方法：

    1. ALTER USER 'rpl_user'@'%' IDENTIFIED WITH mysql_native_password BY 'rpl_user';
    2. ALTER USER 'rpl_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rpl_user';
    3. ALTER USER 'rpl_user'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'rpl_user';

    Primary库与secondary库间数据传递正常，问题解决。

    Primary库与secondary库间数据不一致，复制数据时发生致命错误，备库离开replication 组

    解决方法：

    1. 清空从节点的数据

    2. 从主库备份数据传至备库进行恢复

    3. 在备库执行reset master

    4. 重新执行 CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='rpl_user' FOR CHANNEL 'group_replication_recovery'

    5. START GROUP_REPLICATION

    参考链接：

    https://www.cnblogs.com/lhrbest/p/14590968.html

    手把手教你搭建Mysql8.0.22 MGR高可用集群 - 知乎

    【DB宝35】使用MySQL 8.0 克隆(clone)插件快速添加MGR节点

  • 相关阅读:
    tomcat web.xml文件中的session-config
    数据中心网络架构的问题与演进 — NFV
    计算机毕业设计Java校园生活信息服务平台(源码+系统+mysql数据库+Lw文档）
    高效的数据压缩编码方式 Protobuf
    ThinkPHP5+七牛云文件操作 方法梳理
    Java习题：第三章 面向对象
    [架构之路-247]：目标系统 - 设计方法 - 软件工程 - 结构化方法的基本思想、本质、特点以及在软件开发、在生活中的应用
    C++编程题目2
    java计算机毕业设计ssm的“萨丁”留学资讯网的设计与实现
    Appium的使用教程
  • 原文地址：https://blog.csdn.net/qq961573863/article/details/138144268