GoldenGate for mysql to mysql:单向同步

最新推荐文章于 2024-03-23 23:32:53 发布

jerry-89

最新推荐文章于 2024-03-23 23:32:53 发布

阅读量1.6k

点赞数

分类专栏：实时数据传输

本文链接：https://blog.csdn.net/eagle89/article/details/104892601

版权

实时数据传输专栏收录该内容

9 篇文章 2 订阅

订阅专栏

GoldenGate用于在各种企业系统间以亚秒级速度复制和集成事务数据，是同类最佳的、易于部署的产品。GoldenGate 可以灵活地在同类和异类系统（包括不同版本、不同的硬件平台）之间移动数据。

Oracle 于 2009 年收购了 GoldenGate，我们可以看到在全球各行业超过 4,000 个解决方案中实施了 Oracle GoldenGate。该软件执行实时的、基于日志的更改数据捕获 (CDC)，能够在异构数据库间以极低的延迟和很小的空间移动大量事务数据。

GoldenGate TDM 是基于日志的实时CDC软件平台，提供异构环境下大量交易数据的实时捕捉、变换和投递，同时保持亚秒一级的数据延迟。

基于GoldenGate TDM，我们提供高可用/容灾和实时数据集成的解决方案。

GoldenGate TDM 采用高内聚低耦合的结构，能够支持多种拓扑结构，包括一对一，一对多，多对一，多对多，层叠和双向复制。

1:GoldenGate 工作原理

2:交易数据管理

GoldenGate提供异构环境下交易数据的实时捕捉、变换、投递。

3:GoldenGate变化数据捕捉的优势

1:都提供基于日志的CDC

2:直接从数据库日志中通过自己的专有程序抽取数据

3:支持在备份系统上抓取变化数据

4:对一些数据类型如LONG,XML,BLOB都提供了支持

4:基于日志的实时数据复制

•亚秒级复制
•不依赖源数据库的触发器和规则，对源数据库影响小。

5:GG在数据传输方面的优势

1:GG有很好的机制来保证传输的交易一致性和断点续传。
2:事务完整性-事务级粒度
3:只复制成功提交的事务
4:防止源和目标的不一致性

实验：goldengate for mysql to mysql

下载安装OGG 安装包

官网地址（需要注册ORACLE账户）：https://www.oracle.com/technetwork/cn/middleware/goldengate/downloads/index.html

http://www.oracle.com/technetwork/cn/middleware/goldengate/downloads/index.html

（1）、点击下载 Oracle GoldenGate

ogg下载目录.png

（一）基础环境配置

	源库	目标库
操作系统版本	CentOS Linux release 7.4	CentOS Linux release 7.4
IP地址	192.168.10.11	192.168.10.12
数据库版本	社区版 5.7.24	社区版 5.7.24
需要同步的数据库	testdb（所有表）	testdb_repl
GoldenGate版本	OGG-19.1.0.0.3	OGG-19.1.0.0.3

（二）MySQL数据库初始信息

	源库	目标库
MySQL安装位置（basedir）	/usr/local/mysql	/usr/local/mysql
数据存放位置（datadir）	/mysql/data	/mysql/data
二进制日志位置（log_bin）	/mysql/binlog	/mysql/binlog

（三）MySQL数据库开启二进制日志

注：GoldenGate的官方文档明确表示，GoldenGate需要将MySQL的日志格式（binlog_format）设置为ROW，其他两种格式（MIXED or STATEMENT）是不支持。

########################################################################官方描述如下：

binlog_format: This parameter sets the format of the logs. It must be set to the value of ROW, which directs the database to log DML statements in binary format. Any other log format (MIXED or STATEMENT) causes Extract to abend.

########################################################################

但是MySQL在版本5.1.5之前是不支持ROW和MIXED格式（MySQL 5.1.5引入ROW，5.1.8引入MIXED）

#### 官方描述如下：http://dev.mysql.com/doc/refman/5.1/en/binary-log-formats.html

Support for row-based logging was added in MySQL 5.1.5. Mixed logging is available beginning with MySQL 5.1.8. In MySQL 5.1.12, MIXED become the default logging mode; in 5.1.29, the default was changed back to STATEMENT for compatibility with MySQL 5.0.

########################################################################

目前客户的MySQL版本是5.0.6-beta版：

D:\MySQL Server 5.0\bin>mysql -u root -p

Enter password: *****

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 111 to server version: 5.0.6-beta-nt-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select version();

+-------------------+

| version() |

+-------------------+

| 5.0.6-beta-nt-log|

+-------------------+

1 row in set (0.02 sec)

这个版本是不支持binlog_format=ROW，所以安装好GolenGate后，启动抽取进程时，进程abend

########################################################################下面是出错信息：

2014-09-16 16:55:16 ERROR OGG-00146 VAM function VAMRead returned unexpected result: error 600 - VAM Client Report <CAUSE OF FAILURE : STATEMENT or MIXED level logging found so abending

WHEN FAILED : While reading log event from binary log

WHERE FAILED : MySQLBinLog Reader Module

CONTEXT OF FAILURE : No Information Available!>

因此如果想要采用GoldenGate来实现MySQL的数据实时同步，需要将MySQL版本升级到5.1.5以上。

要使用ogg抽取数据，必须开启二进制日志。在mysql的配置文件/etc/my.cnf中配置二进制日志,重启数据库生效。

[mysqld]

# binlog配置
server_id = 1
binlog_format=ROW
log_bin=/mysql/binlog/master-bin
sync_binlog=1 
expire_logs_days=1

（四）源端和目标端安装配置GoldenGate（源端和目标端都要执行）

（4.1）源端和目标端创建安装ogg，实际上ogg并不需要安装，解压就可以使用

创建ogg安装路径

[root@masterdb ~]# mkdir /ogg

将安装包拷贝到该路径下解压：

[root@masterdb ogg]# ls
ggs_Linux_x64_MySQL_64bit.tar
[root@masterdb ogg]# tar -xvf ggs_Linux_x64_MySQL_64bit.tar 
./
./mysql_checklist.sql
...略
./libicuuc.so.56.1
[root@masterdb ogg]# ls
bcpfmt.tpl              emsclnt                        libggnnzitp.so        mysql.txt
bcrypt.txt              extract                        libggparam.so         notices.txt
cachefiledump           freeBSD.txt                    libggperf.so          oggerr
checkprm                ggcmd                          libggrepo.so          prvtclkm.plb
chkpt_mysql_create.sql  ggMessage.dat                  libicudata.so.56      replicat
convchk                 ggparam.dat                    libicudata.so.56.1    retrace
convprm                 ggsci                          libicui18n.so.56      reverse
db2cntl.tpl             ggs_Linux_x64_MySQL_64bit.tar  libicui18n.so.56.1    server
defgen                  help.txt                       libicuuc.so.56        sqlldr.tpl
demo_mysql_create.sql   keygen                         libicuuc.so.56.1      tcperrs
demo_mysql_insert.sql   lib                            libmysqlclient.so.18  ucharset.h
demo_mysql_load.sql     lib12                          libxerces-c-3.1.so    UserExitExamples
demo_mysql_misc.sql     libantlr3c.so                  logdump               usrdecs.h
dirout                  libdb-6.1.so                   mgr                   zlib.txt
dirwww                  libgglog.so                    mysql_checklist.sql

（4.2）使用ggsci创建ogg相关路径

[root@masterdb ogg]# pwd
/ogg
[root@masterdb ogg]# ./ggsci

Oracle GoldenGate Command Interpreter for MySQL
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419
Linux, x64, 64bit (optimized), MySQL Enterprise on Jun 30 2017 06:32:17
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (masterdb) 1> create subdirs

Creating subdirectories under current directory /ogg

Parameter files                /ogg/dirprm: created
Report files                   /ogg/dirrpt: created
Checkpoint files               /ogg/dirchk: created
Process status files           /ogg/dirpcs: created
SQL script files               /ogg/dirsql: created
Database definitions files     /ogg/dirdef: created
Extract data files             /ogg/dirdat: created
Temporary files                /ogg/dirtmp: created
Credential store files         /ogg/dircrd: created
Masterkey wallet files         /ogg/dirwlt: created
Dump files                     /ogg/dirdmp: created

（五）源端配置GoldenGate（源端执行）

（5.1）创建数据库用户ogg，该用户用来同步数据

mysql> GRANT ALL PRIVILEGES ON `testdb`.* TO 'ogg'@'%'IDENTIFIED BY 'ogg';

（5.2）配置管理进程mgr

GGSCI (masterdb) 1> edit param mgr
port 7809  
dynamicportlist 7840-7939  
purgeoldextracts /ogg/dirdat/*,usecheckpoints, minkeepdays 1 

GGSCI (masterdb) 1> view param mgr
port 7809
dynamicportlist 7840-7939
purgeoldextracts /ogg/dirdat/*,usecheckpoints, minkeepdays 1 


GGSCI (masterdb) 2> start mgr
Manager started.


GGSCI (masterdb) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

(5.3)配置捕获进程exta

GGSCI (masterdb) 5> edit param exta
extract exta  
sourcedb testdb@192.168.10.11:3306 userid ogg password ogg  
exttrail /ogg/dirdat/ea
discardfile  /ogg/dirrpt/exta.dsc,append  
TranLogOptions AltLogDest /mysql/binlog/master-bin.index 
table testdb.*; 

GGSCI (masterdb) 5> view param exta
extract exta
sourcedb testdb@192.168.10.11:3306 userid ogg password ogg
exttrail /ogg/dirdat/ea
discardfile /ogg/dirrpt/exta.dsc,append
TranLogOptions AltLogDest /mysql/binlog/master-bin.index
table testdb.*; 

GGSCI (masterdb) 10> add extract exta,tranlog,begin now  
EXTRACT added.

GGSCI (masterdb) 11> add exttrail /ogg/dirdat/ea,extract exta  
EXTTRAIL added.

(5.4)配置投递进程dmpa

GGSCI (masterdb) 15> edit param dmpa 
extract dmpa 
passthru  
sourcedb testdb@192.168.10.11:3306 userid ogg password ogg  
rmthost  192.168.10.12,mgrport 7809,compress  
rmttrail  /ogg/dirdat/da  
dynamicresolution  
numfiles 3000  
table testdb.*; 

GGSCI (masterdb) 16> add extract dmpa ,exttrailsource /ogg/dirdat/ea  
EXTRACT added.

GGSCI (masterdb) 17> add rmttrail /ogg/dirdat/da,extract dmpa 
RMTTRAIL added.

(5.5)创建表定义文件，传送到目标端

（5.5.1）创建表定义文件

GGSCI (masterdb) 23> edit param defgen  
defsfile ./dirdef/testdb.def  
sourcedb testdb@192.168.10.11:3306 userid ogg, password ogg  
table testdb.*;  

GGSCI (masterdb) 26> exit

（5.5.2）生成表定义

[root@masterdb ogg]# ./defgen paramfile ./dirprm/defgen.prm  

***********************************************************************
        Oracle GoldenGate Table Definition Generator for MySQL
      Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419
 Linux, x64, 64bit (optimized), MySQL Enterprise on Jun 30 2017 07:27:58
 
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2020-02-08 18:15:40
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Tue Aug 22 21:09:27 UTC 2017, Release 3.10.0-693.el7.x86_64
Node: masterdb
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 2510

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile ./dirdef/testdb.def
sourcedb testdb@192.168.10.11:3306 userid ogg, password ***
table testdb.*;
Expanding wildcard table specification testdb.*:

Retrieving definition for testdb.test01.
Retrieving definition for testdb.test02.
Retrieving definition for testdb.test03.


Definitions generated for 3 tables in ./dirdef/testdb.def.

注： ERROR OGG-00768 The MyISAM storage type tables are not supported for Extraction. Please, create table (biz_scrm.app_activity) with ENGINE=InnoDB. SQL error (0).
注：表的引擎为INNODB

查询数据库有什么引擎：
select table_name,`engine` from information_schema.tables where table_schema = 'database_name';
查询表引擎（查看MYISAM）
SELECT CONCAT(table_name,' ', engine) FROM information_schema.tables WHERE table_schema="bpm" AND ENGINE="MyISAM";
生成修改sql（把MYISAM改成INNODB）
select CONCAT('alter table ',table_name,' engine=InnoDB;') FROM information_schema.tables WHERE table_schema="bpm" AND ENGINE="MyISAM";

ERROR 1067 (42000): Invalid default value for 'cur_date'
set @@sql_mode ='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

ERROR 1264 (22003): Out of range value for column 'uid' at row 1
set@@sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

（5.5.3）传送到目标端

[root@masterdb ogg]# scp /ogg/dirdef/testdb.def 192.168.10.12:/ogg/dirdef/
root@192.168.10.12's password: 
testdb.def                            100% 1785     1.9MB/s   00:00

（六）目标端配置GoldenGate（目标端执行）

（6.1）创建数据库用户ogg，该用户用来同步数据

mysql> GRANT ALL PRIVILEGES ON `testdb_repl`.* TO 'ogg'@'%'IDENTIFIED BY 'ogg';

（6.2）配置管理进程mgr

create subdirs

GGSCI (slavedb) 3> edit param mgr
port 7809  
dynamicportlist 7840-7939  
purgeoldextracts /ogg/dirdat/*,usecheckpoints, minkeepdays 1 

GGSCI (slavedb) 5> start mgr
Manager started.

GGSCI (slavedb) 6> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

（6.3）配置检查表

GGSCI (slavedb) 9> dblogin sourcedb testdb_repl@192.168.10.12:3306 userid ogg password ogg 
Successfully logged into database.

GGSCI (slavedb DBLOGIN as ogg) 10> add checkpointtable testdb_repl.checkpoint  

Successfully created checkpoint table testdb_repl.checkpoint.

GGSCI (slavedb DBLOGIN as ogg) 11> edit params ./GLOBALS 
checkpointtable testdb_repl.checkpoint

（6.4）配置应用进程repa

GGSCI (slavedb DBLOGIN as ogg) 13> edit params repa

replicat repa 
targetdb testdb_repl@192.168.10.12:3306 userid ogg password ogg  
handlecollisions  
sourcedefs /ogg/dirdef/testdb.def  
discardfile /ogg/dirrpt/repa.dsc,purge  
map testdb.*, target testdb_repl.*;

GGSCI (slavedb DBLOGIN as ogg) 16> add replicat repa,exttrail /ogg/dirdat/da,checkpointtable testdb_repl.checkpoint
REPLICAT added.

ggsci>Info All

ggsci>Info repa

ggsci>Info repa, Detail

ggsci>Send Replicat repa, Report

ggsci>View Report repa

ggsci>Send Replicat repa, Stats

（七）导入初始化数据

将源库需要同步的表导出，然后导入到目标数据库

# 源库导出，并传到目标数据库服务器上
[root@masterdb ~]# mysqldump -uroot -p123456 -h192.168.10.11  --single-transaction --flush-logs --master-data=2 testdb  > testdb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@masterdb ~]# ls
anaconda-ks.cfg  testdb.sql  wgetlog-02-05-20-22:27.log  wgetlog-02-05-20-22:28.log  wget.sh

[root@masterdb ~]# scp testdb.sql root@192.168.10.12:/root
root@192.168.10.12's password: 
testdb.sql                                                                                100%   15MB  68.9MB/s   00:00

# 目标数据库执行导入
[root@slavedb ~]# mysql -uroot -p123456 testdb_repl < testdb.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.

（八）开启ogg同步进程

(8.1)源端开启捕获进程（源端执行）

由于数据库数据一直在变化，所以对于导出的testdb.sql文件，我们需要记录二进制日志文件的log_file和log_pos

[root@slavedb ~]# cat testdb.sql |grep "CHANGE MASTER TO MASTER_LOG_FILE"
-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000005', MASTER_LOG_POS=154;

然后从导出时的binlog开始抽取

GGSCI (masterdb) 5> alter extract exta,vam,lognum 5,logpos 154
EXTRACT altered.
GGSCI (masterdb) 6> start exta

Sending START request to MANAGER ...
EXTRACT EXTA starting

（8.2）开启投递进程（源端执行）

GGSCI (masterdb) 19> start dmpa

Sending START request to MANAGER ...
EXTRACT DMPA starting

Viewing Reports：

ggsci>Send Extract exta, Report

ggsci>View Report exta

Viewing Statistics：

ggsci>Send Extract exta, Stats

（8.3）开启应用进程（目标端执行）

GGSCI (slavedb DBLOGIN as ogg) 18> start repa

Sending START request to MANAGER ...
REPLICAT REPA starting

（九）确认同步状态

（9.1）使用info all查看进程是否运行正常

所有进程状态为“running”代表正常

源端：

GGSCI (masterdb) 8> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DMPA        00:00:00      00:00:08    
EXTRACT     RUNNING     EXTA        00:00:00      00:00:04

如果状态不正常可以用以下命令查看原因

查看日志：

view ggsevt

目标端replicat报错
view report rt1

目标端：

GGSCI (slavedb DBLOGIN as ogg) 23> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPA        01:14:40      00:00:0

（9.2）源端手动插入数据，查看目标端是否同步

源端执行：

insert into test03 values(1,'a');

目标端查看数据是否同步过来：

mysql> use testdb_repl ;
Database changed

mysql> select * from test03;
+-----+------+
| id3 | name |
+-----+------+
|   1 | a    |
+-----+------+
1 row in set (0.04 sec)

shell下通过下列脚本过滤出OGG进程状态，留作监控使用

(echo info all;echo exit)|./ggsci|grep -E "EXTRACT|REPLICAT" |awk 'BEGIN {FS=" +"} {print $1","$3","$2","$4","$5}'

注：在源库上添加表时，目标库没有会报如下：

源库：添加表

目标库上

看同步软件状态时不正常，重启即可；

mysql:

create user 'oggadm1'@'%' identified by '123456';

grant all privileges on *.* to 'oggadm1'@'%';

flush privileges;

create database oggdb1;

exit

rpm -q *odbc*

yum install mysql-connector-odbc.x86_64

cat /etc/odbcinst.ini #Mysql 驱动

vim /etc/odbc.ini

[mysql]
Driver = MySQL #MySQL 是 /etc/odbcinst.ini
Server = 192.168.0.235
Port = 3306
User = oggadm1
Password = 123456
Database = oggdb1
Option = 3
Socket = /tmp/mysql.sock

测试：

isql mysql

ERROR: Could not delete DB checkpoint for REPLICAT RLISDBCC (Database login required to delete datab

在OGG的replicat进程配置完成后，发现配置错误，或者想删除某个进程的时候，报‘ERROR: Could not delete DB checkpoint for REPLICAT RLISDBCC (Database login required to delete database checkpoint).’

GGSCI (centerdb) 22> delete rlisdbcc
ERROR: Could not delete DB checkpoint for REPLICAT RLISDBCC (Database login required to delete database checkpoint).

解决过程如下：
GGSCI (centerdb) 23> dblogin userid ogg,password 7540000E
Successfully logged into database.

GGSCI (centerdb) 24> delete rlisdbcc
Deleted REPLICAT RLISDBCC.

需要在OGG中登录目标数据库后，在执行删除命令就可以成功。
因为你的checkpointtable OGG.checkpointtab 这个是在OGG中登录数据库后创建的，所以也只能登录数据库后才能删除。

OGG强制删除replicat

GGSCI (WINDOWS-0QO8SQ0) 6> dblogin userid system, password *****
Successfully logged into database.

GGSCI (WINDOWS-0QO8SQ0) 7> delete rep2
ERROR: Could not delete DB checkpoint for REPLICAT REP2 (OCI Error ORA-00942: ta
ble or view does not exist (status = 942). Deleting from checkpoint table ggate.
checkpoint, group 'REP2', key 2986303197 (0xb1ff5edd), SQL <DELETE FROM ggate.ch
eckpoint WHERE group_name = 'REP2' AND group_key = 2986303197>).

GGSCI (WINDOWS-0QO8SQ0) 8> delete rep2 force
ERROR: Could not delete DB checkpoint for REPLICAT REP2 (OCI Error ORA-00942: ta
ble or view does not exist (status = 942). Deleting from checkpoint table ggate.
checkpoint, group 'REP2', key 2986303197 (0xb1ff5edd), SQL <DELETE FROM ggate.ch
eckpoint WHERE group_name = 'REP2' AND group_key = 2986303197>).

GGSCI (WINDOWS-0QO8SQ0) 9> delete replicat rep2!
WARNING: Could not delete DB checkpoint for REPLICAT REP2 (OCI Error ORA-00942:
table or view does not exist (status = 942). Deleting from checkpoint table ggat
e.checkpoint, group 'REP2', key 2986303197 (0xb1ff5edd), SQL <DELETE FROM ggate.
checkpoint WHERE group_name = 'REP2' AND group_key = 2986303197>).

GGSCI (WINDOWS-0QO8SQ0) 10> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
JAGENT STOPPED
REPLICAT RUNNING REP1 00:00:00 00:00:01

直接删除时，报错Could not delete DB checkpoint for REPLICAT REP2 (OCI Error ORA-00942.
因原OGG对应用户ggate已经被删除。
只好采用强制删除
delete replicat rep2!
通过惊叹号来进行强制删除

参考：http://ranjitnagi.blogspot.com/2013/01/gg-checkpoint-table-errorerror-could.html
http://chandu208.blogspot.com/2015/02/error-could-not-delete-db-checkpoint.html

ERROR: Could not delete DB checkpoint for REPLICAT

DB version: 11.2.0.2.0

OGG version: 11.2.1.0.3

OS Version: RHEL 6

GGSCI (oracledev10) 10> delete Replicat REP

ERROR: Could not delete DB checkpoint for REPLICAT REP (OCI Error ORA-00942: table or view does not exist (status = 942). Deleting from checkpoint table ggs_admin.ggchkpt, group 'REP', key 2292316344 (0x88a1f8b8), SQL <DELETE FROM ggs_admin.ggchkpt WHERE group_name = 'REP' AND group_key = 2292316344>).

The above error was due to REPLICAT process added without specifying NODBCHECKPOINT argument when it got created initially.

Solution: Try deleting with "!" option

GGSCI (oracledev10) 12> delete replicat REP !

WARNING: Could not delete DB checkpoint for REPLICAT REP (OCI Error ORA-00942: table or view does not exist (status = 942). Deleting from checkpoint table ggs_admin.ggchkpt, group 'REP', key 2292316344 (0x88a1f8b8), SQL <DELETE FROM ggs_admin.ggchkpt WHERE group_name = 'REP' AND group_key = 2292316344>).

Replicat REP Deleted now

Here, The “!” tells GoldenGate to ignore the checkpoint table

If we wish to create Replicat without checkpoint then use below command to create

ADD REPLICAT <group>, EXTTRAIL <trail>, NODBCHECKPOINT

If we want to create Replicat WITH checkpoint then use below command to create

ADD CHECKPOINTTABLE ggs_admin.ggchkpt (create if not exists)

ADD REPLICAT <group>, EXTTRAIL <trail>, CHECKPOINTTABLE ggs_admin.ggchkpt

Refer:

How Can I Delete A REPLICAT Created Without A Checkpoint Table? (Doc ID 965689.1)

实现mysql到mysql之间DDL复制的一些限制

19.1 DDL复制的一些限制

1、基于DDL的复制需要mysql的版本在5.7.10及以上
2、不支持双向DDL复制
3、不支持远程捕获
4、DDL复制使用ddl_rewriter and ddl_metadata两个插件作业共享库来实现DDL语句的复制，在开始DDL复制前需要提前在MYSQL服务器上安装这两个库
5、在存储过程中执行DDL语句不能被捕获
CREATE PROCEDURE atssrc.generate_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 800 DO
SET i = i + 1;
IF (i = 100) then
alter table atssrc.ddl6 add col2 DATE after id;
ELSEIF (i = 200) then
alter table atssrc.ddl6 add col3 DATETIME after datetime;
ELSEIF (i = 300) then
alter table atssrc.ddl6 add col4 timestamp NULL DEFAULT NULL after
channel;
ELSEIF (i = 400) then
alter table atssrc.ddl6 add col5 YEAR after value;
END IF;
END WHILE;
END$$
DELIMITER ;
call atssrc.generate_data();

安装DDL复制插件

[root@lineqi ogg_target]# ./gg
ggcmd ggMessage.dat ggparam.dat ggsci
[root@lineqi ogg_target]# ./ddl_install.sh install ogg_repl Ogg#1234 3306
checking MySQL version
DDL is supported for your installed MySQL version.
plugin_dir: /usr/lib64/mysql/plugin/

nohup: appending output to ‘nohup.out’
The metadata_server started successfully.

copying of ddl_rewriter.so succeeded
copying of ddl_metadata.so succeeded

ERROR: Not able to drop database oggddl. See ddl_install.log for details

mysql> grant create,insert,select,delete,drop on . to ‘ogg_repl’@’%’;

[root@lineqi ogg_target]# ./ddl_install.sh install ogg_repl Ogg#1234 3306
checking MySQL version
DDL is supported for your installed MySQL version.
plugin_dir: /usr/lib64/mysql/plugin/

nohup: appending output to ‘nohup.out’
The metadata_server started successfully.

copying of ddl_rewriter.so succeeded
copying of ddl_metadata.so succeeded

Plugins installation was successful.

Installation was successful.

CREATE TABLE employee_person (
    id int unsigned not null primary key,
    address varchar(60),
    phone int,
    email varchar(60),
    birthday DATE,
    sex ENUM('M', 'F'),
    m_status ENUM('Y','N'),
    s_name varchar(40),
    children int
);

INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, '200, Regina Street', 7176666, 'net@hotmail.com', '1971-04-26', 'M', 'Y', 'Ane Regina');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, '1232 Alberta Road', 5553312, 'jo@hotmail.com', '1968-03-02', 'M', 'Y', 'Jane Van', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, '90 Potter A', 3331211, 'gp@ymail.com', '1967-09-22', 'M', 'N', 'Sandhya Pil', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, '202, Donna Street', 7176167, 'twink@hotmail.com', '1976-08-09', 'F', 'Y', 'Manish Sha');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, 'Apartment #8, UBC, Van Street', 8973242, 'holy@ymail.com', '1974-10-14', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, '46 SFU Street', '6451234', 'kill@el.com', '1978-12-31', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, '432 Mercury Ave', 7932232, 'mac@hotmail.com', '1966-8-21', 'M', 'Y', 'Mary Shelly', '3');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, '8 Little YaleTown', 5442994, 'edd@gmail.com', '1975-01-14', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, '64 Temp Road', 4327652, 'nan@pmail.com', '1969-05-19', 'M', 'Y', 'Man Nanda', '1');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, '132 Metro House, Henry Street', 5552376, 'ra@hotmail.com', '1968-07-06', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, '1 Grace Town, Van Avenue', 5433879, 'soundofsilence@boxer.net', '1957-11-04', 'M', 'Y', 'Muriel Lovelace', '4');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, '97 Oakland Road', 5423311, 'kingarthur@roundtable.org', '1968-02-15', 'M', 'Y', 'Rina Brighton', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, '543 Applegate Lane', 3434343, 'levy@cmail.com', '1968-09-03', 'F', 'Y', 'Matt Shi', '2');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, '76 Fish Street', 7432433, 'tink@email.com', '1965-04-28', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, '98 Gun Street', 6500787, 'danny@fhardy.com', '1966-06-23', 'M', 'Y', 'Betty Cudly', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, '#5 Winnepag Homes', 5433243, 'mike@cmail.com', '1964-03-06', 'M', 'Y', 'Stella Stevens', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, '652 Devon Building, 6th Jade Avenue', 5537885, 'mona@darling.com', '1970-04-18', 'F', 'Y', 'Edgar Alan', 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, 'Apartment #9, Together Towers', 5476565, 'odessey@hotmail.com', '1973-10-09', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, 'Apartment #9, West Towers', 5476565, 'jire@hotmail.com', '1973-1-20', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, '90 Yale Town', 7528326, 'help@more.org', '1968-01-25', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, '4329 Eucalyptus Avenue', 4254863, 'money@cold.com', '1964-06-13', 'M', 'Y', 'Ruby Richer', 2);

select * from employee_person;

INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (21, '200, Regina Street', 7176666, 'net@hotmail.com', '1971-04-26', 'M', 'Y', 'Ane Regina');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (22, '1232 Alberta Road', 5553312, 'jo@hotmail.com', '1968-03-02', 'M', 'Y', 'Jane Van', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (23, '90 Potter A', 3331211, 'gp@ymail.com', '1967-09-22', 'M', 'N', 'Sandhya Pil', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (24, '202, Donna Street', 7176167, 'twink@hotmail.com', '1976-08-09', 'F', 'Y', 'Manish Sha');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (25, 'Apartment #8, UBC, Van Street', 8973242, 'holy@ymail.com', '1974-10-14', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (26, '46 SFU Street', '6451234', 'kill@el.com', '1978-12-31', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (27, '432 Mercury Ave', 7932232, 'mac@hotmail.com', '1966-8-21', 'M', 'Y', 'Mary Shelly', '3');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (28, '8 Little YaleTown', 5442994, 'edd@gmail.com', '1975-01-14', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (29, '64 Temp Road', 4327652, 'nan@pmail.com', '1969-05-19', 'M', 'Y', 'Man Nanda', '1');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (30, '132 Metro House, Henry Street', 5552376, 'ra@hotmail.com', '1968-07-06', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (31, '1 Grace Town, Van Avenue', 5433879, 'soundofsilence@boxer.net', '1957-11-04', 'M', 'Y', 'Muriel Lovelace', '4');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (32, '97 Oakland Road', 5423311, 'kingarthur@roundtable.org', '1968-02-15', 'M', 'Y', 'Rina Brighton', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (33, '543 Applegate Lane', 3434343, 'levy@cmail.com', '1968-09-03', 'F', 'Y', 'Matt Shi', '2');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (34, '76 Fish Street', 7432433, 'tink@email.com', '1965-04-28', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (35, '98 Gun Street', 6500787, 'danny@fhardy.com', '1966-06-23', 'M', 'Y', 'Betty Cudly', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (36, '#5 Winnepag Homes', 5433243, 'mike@cmail.com', '1964-03-06', 'M', 'Y', 'Stella Stevens', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (37, '652 Devon Building, 6th Jade Avenue', 5537885, 'mona@darling.com', '1970-04-18', 'F', 'Y', 'Edgar Alan', 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (38, 'Apartment #9, Together Towers', 5476565, 'odessey@hotmail.com', '1973-10-09', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (39, 'Apartment #9, West Towers', 5476565, 'jire@hotmail.com', '1973-1-20', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (40, '90 Yale Town', 7528326, 'help@more.org', '1968-01-25', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (41, '4329 Eucalyptus Avenue', 4254863, 'money@cold.com', '1964-06-13', 'M', 'Y', 'Ruby Richer', 2);

select * from employee_person;

INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (42, '1232 Alberta Road', 5553312, 'jo@hotmail.com', '1968-03-02', 'M', 'Y', 'Jane Van', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (43, '90 Potter A', 3331211, 'gp@ymail.com', '1967-09-22', 'M', 'N', 'Sandhya Pil', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (44, '202, Donna Street', 7176167, 'twink@hotmail.com', '1976-08-09', 'F', 'Y', 'Manish Sha');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (45, 'Apartment #8, UBC, Van Street', 8973242, 'holy@ymail.com', '1974-10-14', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (46, '46 SFU Street', '6451234', 'kill@el.com', '1978-12-31', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (47, '432 Mercury Ave', 7932232, 'mac@hotmail.com', '1966-8-21', 'M', 'Y', 'Mary Shelly', '3');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (48, '8 Little YaleTown', 5442994, 'edd@gmail.com', '1975-01-14', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (49, '64 Temp Road', 4327652, 'nan@pmail.com', '1969-05-19', 'M', 'Y', 'Man Nanda', '1');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (50, '132 Metro House, Henry Street', 5552376, 'ra@hotmail.com', '1968-07-06', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (51, '1 Grace Town, Van Avenue', 5433879, 'soundofsilence@boxer.net', '1957-11-04', 'M', 'Y', 'Muriel Lovelace', '4');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (52, '97 Oakland Road', 5423311, 'kingarthur@roundtable.org', '1968-02-15', 'M', 'Y', 'Rina Brighton', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (53, '543 Applegate Lane', 3434343, 'levy@cmail.com', '1968-09-03', 'F', 'Y', 'Matt Shi', '2');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (54, '76 Fish Street', 7432433, 'tink@email.com', '1965-04-28', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (55, '98 Gun Street', 6500787, 'danny@fhardy.com', '1966-06-23', 'M', 'Y', 'Betty Cudly', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (56, '#5 Winnepag Homes', 5433243, 'mike@cmail.com', '1964-03-06', 'M', 'Y', 'Stella Stevens', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (57, '652 Devon Building, 6th Jade Avenue', 5537885, 'mona@darling.com', '1970-04-18', 'F', 'Y', 'Edgar Alan', 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (58, 'Apartment #9, Together Towers', 5476565, 'odessey@hotmail.com', '1973-10-09', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (59, 'Apartment #9, West Towers', 5476565, 'jire@hotmail.com', '1973-1-20', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (60, '90 Yale Town', 7528326, 'help@more.org', '1968-01-25', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (61, '4329 Eucalyptus Avenue', 4254863, 'money@cold.com', '1964-06-13', 'M', 'Y', 'Ruby Richer', 2);

select * from employee_person;

CREATE TABLE t_person (FIdNumber VARCHAR(20),
FName VARCHAR(20),FBirthDay DATETIME,
FRegDay DATETIME,FWeight DECIMAL(10,2));

INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789120','Tom','1981-03-22','1998-05-01',56.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789121','Jim','1987-01-18','1999-08-21',36.17);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789122','Lily','1987-11-08','2001-09-18',40.33);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789123','Kelly','1982-07-12','2000-03-01',46.23);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789124','Sam','1983-02-16','1998-05-01',48.68);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789125','Kerry','1984-08-07','1999-03-01',66.67);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789126','Smith','1980-01-09','2002-09-23',51.28);
INSERT INTO T_Person(FIdNumber,FName,FBirthDay,FRegDay,FWeight)
VALUES ('123456789127','BillGates','1972-07-18','1995-06-19',60.32);

create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);

insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);

create table dept (
    deptno numeric(2),
    dname varchar(14),
    loc varchar(13)
);

insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');

create table bonus (
    empno numeric(4),
    job varchar(9),
    sal numeric,
    comm numeric
);

create table salgrade (
    grade numeric,
    losal numeric,
    hisal numeric
);

insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);

Mysql批量插入千万条数据

1、生成思路

利用mysql内存表插入速度快的特点，先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中

2、创建普通表及内存表

    普通表
    CREATE TABLE `vote_record` (
       `id` INT (11) NOT NULL AUTO_INCREMENT,
       `user_id` VARCHAR (20) NOT NULL,
       `vote_id` INT (11) NOT NULL,
       `group_id` INT (11) NOT NULL,
       `create_time` datetime NOT NULL,
       PRIMARY KEY (`id`),
       KEY `index_user_id` (`user_id`) USING HASH
    ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

    内存表
    CREATE TABLE `vote_record_memory` (
       `id` INT (11) NOT NULL AUTO_INCREMENT,
       `user_id` VARCHAR (20) NOT NULL,
       `vote_id` INT (11) NOT NULL,
       `group_id` INT (11) NOT NULL,
       `create_time` datetime NOT NULL,
       PRIMARY KEY (`id`),
       KEY `index_id` (`user_id`) USING HASH
    ) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

3、创建函数及存储过程

    CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET latin1
    BEGIN
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE return_str varchar(255) DEFAULT '' ;
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
    SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
    SET i = i +1;
    END WHILE;
    RETURN return_str;
    END

    CREATE PROCEDURE `add_vote_memory`(IN n int)
    BEGIN
      DECLARE i INT DEFAULT 1;
        WHILE (i <= n ) DO
          INSERT into vote_record_memory (user_id,vote_id,group_id,create_time ) VALUEs (rand_string(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100) ,now() );
               set i=i+1;
        END WHILE;
    END

4、调用存储过程（生成内存表数据）

CALL add_vote_memory(1000000)

如果内存不足，wind8.1中添加一下两段代码。路径为 C:\ProgramData\MySQL\MySQL Server 5.7

tmp_table_size=1G
max_heap_table_size = 1G

5、插入普通表中

INSERT into vote_record SELECT * from vote_record_memory

Mysql批量插入千万条数据

#建库建表
create database bigData;
use bigData;

create table if not exists dept(
  id int unsigned primary key auto_increment,
  deptno mediumint unsigned not null default 0,
  dname varchar(20) not null default '',
  loc varchar(13) not null default ''
);

create table if not exists emp(
  id int unsigned primary key auto_increment,
  empno mediumint unsigned not null default 0,
  ename varchar(20) not null default '',
  job varchar(9) not null default '',
  mgr mediumint unsigned not null default 0,
  hiredate date not null ,
  sal decimal(7,2) not null ,
  comm decimal(7,2) not null ,
  deptno mediumint unsigned not null default 0
);


#创建生成随机字符串和部门编号的函数
delimiter $
create function rand_string(n int) returns varchar(255)
  begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i<n do
      set return_str=concat(return_str,substr(chars_str,FLOOR(1+rand()*52),1));
      set i=i+1;
    end while ;
    return return_str;
  end $


delimiter $
create function rand_num() returns int(5)
  begin
    declare i int default 0;
    set i = floor(100+rand()*10);
    return i;
  end $


#创建批量插入数据的存储过程
delimiter $
create procedure insert_emp(in start int(10),in max_num int(10))
  begin
    declare i int default 0;
    set autocommit =0;
    repeat
      set i=i+1;
      insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values
        ((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
    until i=max_num end repeat ;
    commit ;
  end $



delimiter $
create procedure insert_dept(in start int(10),in max_num int(10))
  begin
    declare i int default 0;
    set autocommit =0;
    repeat
      set i=i+1;
      insert into dept(deptno, dname, loc) values ((start+i),rand_string(10),rand_string(8));
    until i=max_num  end repeat ;
    commit ;
  end $

#插入部门数据
call insert_dept(100,10);

#插入员工数据（每次插入50万条）
call insert_emp(100001,500000);

jerry-89

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
GoldenGate for mysql to mysql:单向同步

GoldenGate用于在各种企业系统间以亚秒级速度复制和集成事务数据，是同类最佳的、易于部署的产品。GoldenGate 可以灵活地在同类和异类系统（包括不同版本、不同的硬件平台）之间移动数据。 Oracle 于 2009 年收购了 GoldenGate，我们可以看到在全球各行业超过 4,000 个解决方案中实施了 Oracle GoldenGate。该软件执行实时的、基于日志的更改数据捕...
复制链接

扫一扫

专栏目录