|
|
|
|
|
## 黄海的步骤:
|
|
|
|
|
|
|
|
|
|
|
|
1、用ESF直接迁移完整的表
|
|
|
|
|
|
|
|
|
|
|
|
2、用 修改小写.sql 脚本将SQL修改表名和字段名修改为小写
|
|
|
|
|
|
|
|
|
|
|
|
3、将 loader_table_list.txt 维护好，这个项目是60张表
|
|
|
|
|
|
|
|
|
|
|
|
4、
|
|
|
|
|
|
dsideal_db_8da._tmp_dbsync
|
|
|
|
|
|
dsideal_db_8da_temp.public
|
|
|
|
|
|
|
|
|
|
|
|
将两个库+架构下的所有临时表删除掉。
|
|
|
|
|
|
|
|
|
|
|
|
5、在Mysql库中执行
|
|
|
|
|
|
|
|
|
|
|
|
show master status;
|
|
|
|
|
|
|
|
|
|
|
|
记录下
|
|
|
|
|
|
|
|
|
|
|
|
binlogfile = "mysql-bin.001250"
|
|
|
|
|
|
binlogfile_offset = "715724074"
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
5、配置好my.cfg
|
|
|
|
|
|
|
|
|
|
|
|
binlogfile = "mysql-bin.001250"
|
|
|
|
|
|
binlogfile_offset = "715724074"
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
6、
|
|
|
|
|
|
#启动 binlog 拉取进程
|
|
|
|
|
|
nohup ./binlog_miner 1>minner.log 2>&1 &
|
|
|
|
|
|
|
|
|
|
|
|
#启动 binlog 写入进程
|
|
|
|
|
|
nohup ./binlog_loader 1>loader.log 2>&1 &
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-----------------------------------------------
|
|
|
|
|
|
https://www.cnblogs.com/kgdxpr/p/15783345.html
|
|
|
|
|
|
|
|
|
|
|
|
1.执行 ./mysql2pgsql -l loader_table_list.txt -d -n 生成创建PG格式数据表的SQL
|
|
|
|
|
|
|
|
|
|
|
|
2.Navicat Premium，创建一个查询，粘贴SQL，ctrl+a,ctrl+u转小写，修改<distribution key>为主键，执行，完成创建动作。
|
|
|
|
|
|
|
|
|
|
|
|
3../mysql2pgsql -l loader_table_list.txt 完成全量数据初始化
|
|
|
|
|
|
|
|
|
|
|
|
./mysql2pgsql -l huanghai.txt
|
|
|
|
|
|
|
|
|
|
|
|
# 备份办法
|
|
|
|
|
|
/home/postgres/pg10.19/backuppg.sh
|
|
|
|
|
|
|
|
|
|
|
|
# 定时任务 每6个小时执行一次
|
|
|
|
|
|
crontab -e
|
|
|
|
|
|
0 */6 * * * bash /home/postgres/pg10.19/backuppg.sh
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
#启动 binlog 拉取进程
|
|
|
|
|
|
nohup ./binlog_miner 1>minner.log 2>&1 &
|
|
|
|
|
|
|
|
|
|
|
|
#启动 binlog 写入进程
|
|
|
|
|
|
nohup ./binlog_loader 1>loader.log 2>&1 &
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
# 杀掉binlog进程
|
|
|
|
|
|
ps -ef | grep binlog | grep -v grep | awk '{print $2}' | xargs kill -9
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
# 获取mysql表中 binlog偏移量
|
|
|
|
|
|
mysql -uroot -h10.10.2.3 -P22066 -pDsideaL147258369 -e "show master status"
|
|
|
|
|
|
# mysql -uroot -h10.10.2.3 -P22066 -pDsideaL147258369 -e "show master status"|grep my|awk '{print $1} {print $2}'
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
# 命令行
|
|
|
|
|
|
PGPASSWORD=DsideaL147258369 /home/postgres/pg10.19/bin/psql -h 127.0.0.1 -p 5432 -U postgres -d dsideal_db_8da
|
|
|
|
|
|
|
|
|
|
|
|
# 删除临时库下所有表(有误删除表的风险，不再使用)
|
|
|
|
|
|
#select 'drop table _tmp_dbsync.' || tablename || ';' from pg_tables where schemaname in('dsideal_db_8da','_tmp_dbsync')
|
|
|
|
|
|
#union all
|
|
|
|
|
|
#select 'drop table public.'|| tablename || ';' from pg_tables where schemaname in('dsideal_db_8da_temp','public');
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
# 下载安装包
|
|
|
|
|
|
wget https://get.enterprisedb.com/postgresql/postgresql-10.19-1-linux-x64-binaries.tar.gz
|
