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
(一)基础环境配置
源库 | 目标库 | |
操作系统版本 | 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
Here, The “!” tells GoldenGate to ignore the checkpoint table
Refer:
实现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);