阮胜昌的技术记录站-LinuxMySQL数据库运维

在同一台服务器，同一个mysql实例中，使用Goldengate完成Mysql到Mysql的数据同步，源库的hexin库同步到目标库的product_center库，表名hexin_erp_product_supplier

实验准备：


系统: CentOS 6.9


mysql版本 :  5.7.18


mysql-1:192.168.67.151    库：hexin  表：hexin_erp_product_supplier


mysql-2:192.168.67.151    库：hexin 表：hexin_erp_product_supplier


OGG版本(OGG-12.3.0.15)：123015_ggs_Linux_x64_MySQL_64bit.zip




rlwrap 可以用来支持oracle下sqlplus历史命令的回调功能，和使用退格键直接删除命令提高效率。
方法如下
安装步骤如下：
yum install readline* gcc gcc-c++
# wget http://utopia.knoware.nl/~hlub/rlwrap/rlwrap-0.37.tar.gz
# tar zxvf rlwrap-0.37.tar.gz
# cd rlwrap-0.37/
# ./configure
# make
# make install


但是在执行完./configure后报如下错误：
configure: WARNING: No termcap nor curses library found
checking for readline in -lreadline... no
configure: error:
You need the GNU readline library(ftp://ftp.gnu.org/gnu/readline/ ) to build
this program!


原因是因为缺省了rpm包
解决办法
#  yum -y install readline*
# ./configure
# make
# make install


# vim /home/oracle/.bash_profile
在尾部添加：


alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'




1、初始化


1）目标库创建数据库


mysql> create database product_center;
2）导入数据
将源库表hexin_erp_product_supplier数据导出，然后导入到目标库中
mysqldump -uroot -p123456  --single-transaction --flush-logs --master-data=2 -B hexin --tables hexin_erp_product_supplier > /tmp/hexin_erp_product_supplier20151021.sql


将数据导入目标库中：
mysql -uroot -p123456 product_center <  /tmp/hexin_erp_product_supplier20151021.sql


2、源库


1）修改参数


在/etc/my.cnf的[mysqld]下添加如下参数


binlog_format=row
重启
service mysqld restat
2）创建用户并授权


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


3）解压安装创建相应目录
unzip ggs_123015_Linux_x64_MySQL_64bit.zip -d /home/goldengate
cd /home/goldengate
tar xvf ggs_Linux_x64_MySQL_64bit.tar


错误日志位置：/home/goldengate/ggserr.log


修改环境变量
vi ~/.bash_profile
加入如下三个变量
export GGHOME=/home/goldengate
export PATH=$PATH:$GGHOME
export LD_LIBRARY_PATH=/ogg:$LD_LIBRARY_PATH
source /root/.bash_profile
cd /home/goldengate


ggsci


GGSCI (hadooptest05) 1> create subdirs


Creating subdirectories under current directory /home/goldengate


Parameter files                /home/goldengate/dirprm: already exists
Report files                   /home/goldengate/dirrpt: created
Checkpoint files               /home/goldengate/dirchk: created
Process status files           /home/goldengate/dirpcs: created
SQL script files               /home/goldengate/dirsql: created
Database definitions files     /home/goldengate/dirdef: created
Extract data files             /home/goldengate/dirdat: created
Temporary files                /home/goldengate/dirtmp: created
Credential store files         /home/goldengate/dircrd: created
Masterkey wallet files         /home/goldengate/dirwlt: created
Dump files                     /home/goldengate/dirdmp: created
4）配置管理进程(每个OGG只需要一个MGR进程)
GGSCI (hadooptest05) 2>  edit params mgr
port 7809
dynamicportlist 7840-7939
autorestart er *, retries 5, waitminutes 3
purgeoldextracts /home/goldengate/dirdat/*,usecheckpoints, minkeepdays 2
GGSCI (hadooptest05) 3> start mgr
        Manager started.


5）配置抽取进程（每个进程对应一张表，如果有多张表需要复制，可以启动多个进程）
GGSCI (hadooptest05) 4> edit param mce1
extract mce1
sourcedb hexin@192.168.67.151:3306 userid ogg password 123456
exttrail /home/goldengate/dirdat/me
discardfile  /home/goldengate/dirrpt/mce1.dsc,append
TranLogOptions AltLogDest /var/lib/mysql/mysql-bin.index     #mysqld directory
table hexin.hexin_erp_product_supplier;        
GGSCI (hadooptest05) 5> add extract mce1,tranlog,begin now
GGSCI (hadooptest05) 6> add exttrail /home/goldengate/dirdat/me,extract mce1
GGSCI (hadooptest05) 7> start mce1


GGSCI (node151) 15> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING                                          
EXTRACT     RUNNING     MCE1        00:00:00      00:00:08  


错误日志查看：tail -f  /home/goldengate/ggserr.log
  
6）配置传递进程,(需要在目标端配置GGSCI启动MGR后才可以启动成功,每个进程对应一张表，如果有多张表需要复制，可以启动多个进程)
GGSCI (hadooptest05) 8> edit param mcp1
extract mcp1
passthru
sourcedb hexin@192.168.67.151:3306 userid ogg password 123456
rmthost  192.168.67.152,mgrport 7809,compress   #目标端的地址信息
rmttrail  /home/goldengate/dirdat/mp                #目标端的路径
dynamicresolution
numfiles 3000
table hexin.hexin_erp_product_supplier;
GGSCI (hadooptest05) 9> add extract mcp1,exttrailsource /home/goldengate/dirdat/me
GGSCI (hadooptest05) 10> add rmttrail /home/goldengate/dirdat/mp,extract mcp1
GGSCI (hadooptest05) 11> start mcp1


7）创建表的定义文件


#创建参数文件(每个进程对应一个参数文件，如果需要加多个表，就里要创建多个参数文件)
GGSCI (hadooptest05) 10>  edit param defgen
defsfile ./dirdef/gmqdsjsjp.def
sourcedb hexin@192.168.67.151:3306 userid ogg password 123456
table hexin.hexin_erp_product_supplier;
#生成表定义文件
./defgen paramfile ./dirprm/defgen.prm
#传至目标库目录
scp /home/goldengate/dirdef/gmqdsjsjp.def 192.168.67.152:/home/goldengate/dirdef/




3、目标库


1）创建用户


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


 修改参数


在/etc/my.cnf的[mysqld]下添加如下参数


binlog_format=row
重启
service mysqld restat
 
2）同样解压安装并创建目录
unzip ggs_123015_Linux_x64_MySQL_64bit.zip -d /home/goldengate
cd /home/goldengate
tar xvf ggs_Linux_x64_MySQL_64bit.tar
./ggsci


GGSCI (db) 1> create subdirs


Creating subdirectories under current directory /home/goldengate


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


3）配置管理进程(一个OGG只需要配置一个管理进程)


GGSCI (db) 3>  edit params mgr
port 7809
dynamicportlist 7840-7939
autorestart er *, retries 5, waitminutes 3
purgeoldextracts /home/goldengate/dirdat/*,usecheckpoints, minkeepdays 2
GGSCI (db) 4> start mgr
        Manager started.


4）添加检查点表(全局配置，可以多个进程共享，目标数据库配置一次就可以了)
GGSCI (db) 5>  dblogin sourcedb product_center@192.168.67.151:3306 userid ogg password 123456
GGSCI (db DBLOGIN as ogg) 6>  add checkpointtable product_center.checkpoint
GGSCI (db DBLOGIN as ogg) 7>  edit params ./GLOBALS
checkpointtable product_center.checkpoint


5）配置应用进程（每个表对应一个应用进程）
GGSCI (db) 1>  edit params mcr1
replicat mcr1
targetdb product_center@192.168.67.151:3306 userid ogg password 123456
handlecollisions
sourcedefs /home/goldengate/dirdef/gmqdsjsjp.def
discardfile /home/goldengate/dirrpt/mcr1.dsc,purge
map hexin.hexin_erp_product_supplier, target product_center.hexin_erp_product_supplier;


GGSCI (db) 2> add replicat mcr1,exttrail /home/goldengate/dirdat/mp,checkpointtable product_center.checkpoint
GGSCI (db) 3> start mcr1


4、测试数据同步