做app网站有哪些,深圳网站推广策划,知东莞app下载,网络广告一般是怎么收费#x1f4e2;#x1f4e2;#x1f4e2;#x1f4e3;#x1f4e3;#x1f4e3; 哈喽#xff01;大家好#xff0c;我是【IT邦德】#xff0c;江湖人称jeames007#xff0c;10余年DBA及大数据工作经验 一位上进心十足的【大数据领域博主】#xff01;#x1f61c; 哈喽大家好我是【IT邦德】江湖人称jeames00710余年DBA及大数据工作经验 一位上进心十足的【大数据领域博主】 中国DBA联盟(ACDU)成员目前服务于工业互联网 擅长主流Oracle、MySQL、PG、高斯及Greenplum运维开发备份恢复安装迁移性能优化、故障应急处理等。 ✨ 如果有对【数据库】感兴趣的【小可爱】欢迎关注【IT邦德】 ❤️❤️❤️感谢各位大可爱小可爱❤️❤️❤️ 文章目录 前言 1.架构设计 2.OGG for MySQL部署✨ 2.1 安装客户端✨ 2.2 安装目录✨ 2.3.解压缩安装✨ 2.4.环境变量设置✨ 2.5.初始化 3.MySQL配置✨ 3.1 源端配置✨ 3.2 目标端配置✨ 3.3 用户及库创建 4.MySQL OGG配置✨ 4.1 配置mgr进程✨ 4.2 配置EXTRACT进程✨ 4.3 配置REPLICAT进程✨ 4.4 启动所有进程 5.数据验证 前言 本文详细阐述了基于OGG实现MySQL实时同步全过程 1.架构设计 2.OGG for MySQL部署
✨ 2.1 安装客户端 yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm yum install mysql-community-server --nogpgcheck ✨ 2.2 安装目录 groupadd -g 1005 ogg useradd -g ogg -u 1005 -m oggm mkdir -p /oggmysql cp /opt/213000_ggs_Linux_x64_MySQL_64bit.zip /oggmysql chown -R oggm:ogg /oggmysql chmod -R 775 /oggmysql ✨ 2.3.解压缩安装 su - oggm cd /oggmysql/ unzip 213000_ggs_Linux_x64_MySQL_64bit.zip tar -xf ggs_Linux_x64_MySQL_64bit.tar ✨ 2.4.环境变量设置
vi .bash_profileexport GG_HOME/oggmysql
export PATH$PATH:$HOME/bin:$GG_HOME
alias ggscicd $GG_HOME;ggscisource .bash_profile[oggmoggmysql ~]$ ggsci -V
Oracle GoldenGate Command Interpreter for MySQL
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.✨ 2.5.初始化 [oggmoggmysql ~]$ ggsci Oracle GoldenGate Command Interpreter for MySQL Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047 Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46 Operating system character set identified as US-ASCII. Copyright © 1995, 2021, Oracle and/or its affiliates. All rights reserved. GGSCI (oggmysql) 1 create subdirs 3.MySQL配置
✨ 3.1 源端配置
# vi /etc/my.cnf
[mysqld]
datadir/usr/local/mysql/data
basedir/usr/local/mysql
socket/tmp/mysql.sock
usermysql
port3306
character-set-serverutf8mb4
symbolic-links0server_id 80350
log_bin mysql-bin
expire_logs_days 1
binlog_format row
binlog_row_metadatafull[mysqld_safe]
log-error/var/log/mysqld.log
pid-file/var/run/mysqld/mysqld.pid参数说明
symbolic-links0为是否支持符号链接即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录为0不开启。
禁用symbolic-links选项 、服务配置禁用符号链接以防止各种安全风险--重启mysql
systemctl restart mysqld[rootmysql8p /]# systemctl restart mysqld
[rootmysql8p /]# systemctl status mysqld
● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sun 2023-11-26 19:20:59 CST; 14s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 267 ExecStartPre/usr/bin/mysqld_pre_systemd (codeexited, status0/SUCCESS)Main PID: 288 (mysqld)Status: Server is operationalCGroup: /docker/5e0b5d157afd83dfc5d78352343bf241bafcd7e02685033ee314bf8fbbb7f836/system.slice/mysqld.service└─288 /usr/sbin/mysqld‣ 288 /usr/sbin/mysqldNov 26 19:20:58 mysql8p systemd[1]: Stopped MySQL Server.
Nov 26 19:20:58 mysql8p systemd[1]: Starting MySQL Server...
Nov 26 19:20:59 mysql8p systemd[1]: Started MySQL Server.--登录Mysql
mysql -uroot -proot -h172.18.12.66✨ 3.2 目标端配置
# vi /etc/my.cnf
[mysqld]
datadir/usr/local/mysql/data
basedir/usr/local/mysql
socket/tmp/mysql.sock
usermysql
port3306
character-set-serverutf8mb4
symbolic-links0server_id 80351
log_bin mysql-bin
expire_logs_days 1
binlog_format row
binlog_row_metadatafull[mysqld_safe]
log-error/var/log/mysqld.log
pid-file/var/run/mysqld/mysqld.pid参数说明
symbolic-links0为是否支持符号链接即数据库或表可以存储在my.cnf中指定datadir之外的分区或目录为0不开启。
禁用symbolic-links选项 、服务配置禁用符号链接以防止各种安全风险--重启mysql
[rootmysql8s /]# systemctl restart mysqld
[rootmysql8s /]# systemctl status mysqld
● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: active (running) since Sun 2023-11-26 19:25:46 CST; 10s agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlProcess: 217 ExecStartPre/usr/bin/mysqld_pre_systemd (codeexited, status0/SUCCESS)Main PID: 238 (mysqld)Status: Server is operationalCGroup: /docker/65c131b1ebf8f0ae267e48b986a9cffe9b44b232461e479e47d641222f32677e/system.slice/mysqld.service└─238 /usr/sbin/mysqld‣ 238 /usr/sbin/mysqldNov 26 19:25:45 mysql8s systemd[1]: Stopped MySQL Server.
Nov 26 19:25:45 mysql8s systemd[1]: Starting MySQL Server...
Nov 26 19:25:46 mysql8s systemd[1]: Started MySQL Server.--登录Mysql
mysql -uroot -proot -h172.18.12.67✨ 3.3 用户及库创建 1.主备数据库创建同步用户并附权 mysql -uroot -proot -h172.18.12.66 -P3306 mysql -uroot -proot -h172.18.12.67 -P3306 CREATE USER ‘ogg’‘%’ IDENTIFIED BY ‘Jeames123’; GRANT ALL PRIVILEGES ON . TO ‘ogg’‘%’ WITH GRANT OPTION; FLUSH PRIVILEGES; 2.创建数据库 create database rptdb charset utf8mb4; 4.MySQL OGG配置
✨ 4.1 配置mgr进程 [oggmoggmysql ~]$ ggsci Oracle GoldenGate Command Interpreter for MySQL Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047 Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46 Operating system character set identified as US-ASCII. Copyright © 1995, 2021, Oracle and/or its affiliates. All rights reserved. GGSCI (oggmysql) 1 edit param mgr PORT 7809 DYNAMICPORTLIST 7810-7909 AUTORESTART EXTRACT ,RETRIES 5,WAITMINUTES 3 PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 3 GGSCI (oggmysql) 2 info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER STOPPED ✨ 4.2 配置EXTRACT进程 GGSCI (oggmysql) 1 edit param exta extract exta sourcedb rptdb172.18.12.66:3306, userid ogg, password Jeames123 tranLogOptions altlogDest REMOTE ddl include mapped exttrail ./dirdat/ma table rptdb.*; GGSCI (oggmysql) 2 add ext exta, tranlog, begin now GGSCI (oggmysql) 3 add exttrail ./dirdat/ma, ext exta ✨ 4.3 配置REPLICAT进程 GGSCI (oggmysql) 1 edit param repa replicat repa targetdb rptdb172.18.12.67:3306, userid ogg, password Jeames123 DDLERROR DEFAULT IGNORE RETRYOP map rptdb., target rptdb.; GGSCI (oggmysql) 2 add replicat repa, exttrail ./dirdat/ma , nodbcheckpoint GGSCI (oggmysql) 11 info all Program Status Group Lag at Chkpt Time Since ChkptMANAGER STOPPED
EXTRACT STOPPED EXTA 00:00:00 00:01:30
REPLICAT STOPPED REPA 00:00:00 00:00:05注意配置REPLICAT进程,在21C的版本中已经不用配置pump进程
✨ 4.4 启动所有进程 [oggmoggmysql ~]$ ggsci Oracle GoldenGate Command Interpreter for MySQL Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047 Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46 Operating system character set identified as US-ASCII. Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
GGSCI (oggmysql) 1 info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER STOPPED
EXTRACT STOPPED EXTA 00:00:00 00:02:36
REPLICAT STOPPED REPA 00:00:00 00:01:10 GGSCI (oggmysql) 2 start mgr Manager started. GGSCI (oggmysql) 3 start exta Sending START request to Manager … Extract group EXTA starting. GGSCI (oggmysql) 4 start repa Sending START request to Manager … Replicat group REPA starting. GGSCI (oggmysql) 5 info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING
EXTRACT RUNNING EXTA 00:02:58 00:00:10
REPLICAT RUNNING REPA 00:00:00 00:00:02 GGSCI (oggmysql) 6 info * Extract EXTA Last Started 2023-11-26 20:11 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Process ID 188
VAM Read Checkpoint 2023-11-26 20:08:38.528816Replicat REPA Last Started 2023-11-26 20:11 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 196
Log Read Checkpoint File ./dirdat/ma000000000First Record RBA 1326 5.数据验证
--源端创建数据
[rootmysql8p /]# mysql -uroot -proot -h172.18.12.66
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| mysql |
| performance_schema |
| rptdb |
| sys |
--------------------
5 rows in set (0.01 sec)mysql use rptdb
mysql show tables;1.新建表插入数据验证数据
DROP TABLE IF EXISTS Customers;
CREATE TABLE IF NOT EXISTS Customers(cust_id VARCHAR(255) DEFAULT NULL
);
INSERT Customers VALUES (A),(B),(C);##目标端数据同步
mysql show tables;
-----------------
| Tables_in_rptdb |
-----------------
| Customers |
-----------------
1 row in set (0.00 sec)mysql select * from Customers;
---------
| cust_id |
---------
| A |
| B |
| C |
---------
3 rows in set (0.00 sec)2.源端建表,验证数据
drop table if exists product_tb;
CREATE TABLE product_tb(
item_id char(10) NOT NULL,
style_id char(10) NOT NULL,
tag_price int(10) NOT NULL,
inventory int(10) NOT NULL
);##目标端数据同步
mysql show tables;
-----------------
| Tables_in_rptdb |
-----------------
| Customers |
| product_tb |
-----------------
2 rows in set (0.00 sec)3.源端新建一列验证数据
ALTER TABLE product_tb ADD COLUMN address VARCHAR(50);
mysql show create table product_tb\G
*************************** 1. row ***************************Table: product_tb
Create Table: CREATE TABLE product_tb (item_id char(10) NOT NULL,style_id char(10) NOT NULL,tag_price int NOT NULL,inventory int NOT NULL,address varchar(50) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)##目标端数据同步
mysql show create table product_tb\G
*************************** 1. row ***************************Table: product_tb
Create Table: CREATE TABLE product_tb (item_id char(10) NOT NULL,style_id char(10) NOT NULL,tag_price int NOT NULL,inventory int NOT NULL,address varchar(50) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)以上验证可以看到DDL和DML都可以同步。
