数据库同步

2022/7/4 2:24:10

编程Tag： mysql export 数据库同步 DB master

本文主要是介绍数据库同步，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

各大数据库同步方案都不同，以下分别介绍SQLServer、MySql、Postgres的方法。

SQLServer数据库同步方法：

sqlserver发布订阅方式需要用实际的服务器名称，不能使用服务器的IP地址进行。能发布的信息包括【表】、【存储过程】、【用户函数】如果使用IP会有错误，如下图：

具体发布过程如下：

一、发布

1、找到数据库服务器下的【复制】--【本地发布】，选择【新建发布】。如下图：

2.选择待发布的数据库。如下图：

3、选择发布类型。这里选择的默认类型【快照发布】。几种发布类型的区别，SQL SERVER都在下面给出了说明。如下图：

4、选择待发布的内容。如下图：

上图中右侧就是筛选的SQL语句。

5、设置快照代理。如下图：

更改同步频率如下图：

6、设置代理安全性。如下图：

7、填写发布名称

8、完成发布。如下图：

二、订阅

订阅是对数据库发布的快照进行同步，将发布的数据源数据同步到目标数据库。具体订阅过程如下；

1、找到数据库服务器下的【复制】--【本地订阅】，选择【新建订阅】。如下图：

2、选择订阅的发布。如下图：

3、选择分发代理的位置；如下图：

4、选择订阅服务器上存放同步过来的数据的一个或者多个目标数据库。如下图：

若要添加多个订阅数据库，则点击【添加订阅服务器】。如下图：

5、设置分发代理的安全性。如下图：

6、设置同步计划。如下图：

7、完成订阅。如下图：

这样就完成了发布与订阅的整个流程。

MySql数据库同步方法：

以下用主从方式举例，先准备两台mysql服务器，如下：

master: 192.168.1.1
slave: 192.168.1.2

如果是win机子修改c:/winnt/my.ini 如果是linux的机子请修改 /etc/my.cnf

一、master操作
1、修改my.ini
##############
server-id=1
log-bin=c:/mysql/logs/mysql_binary_log
binlog-do-db=test
binlog-ignore-db=mysql
##############

说明
binlog-do-db=test                  允许同步的数据库
binlog-ignore-db=mysql      不允许同步的数据库

2、然后重启mysql
c:/mysql/bin/mysql restart 或 /etc/init.d/mysql restart

3、在master上增加一个同步的用户名
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
注:
如果mysql版本在4.0.2以前的版本请用
mysql> GRANT FILE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';

4、接下来操作要master上要同步的数据库
mysql> USE test;
mysql> FLUSH TABLES WITH READ LOCK;       #锁定要同步的test表，然后导出数据结构

执行如下命令查看master的状态
mysql> SHOW MASTER STATUS;

得到如下结果
Code:
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_do_db | Binlog_ignore_db |
+----------------------+----------+--------------+------------------+
| mysql_binary_log.025 | 796947    | test            |  mysql              |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

接下来备份要同步数据库（为导入slave作准备）
$ mysqldump --opt test > test.sql

mysql> UNLOCK TABLES;                   #已做好同步数据库结构导出后，解锁这个表

二、slave操作
1、把master里导出的同步数据库结构再导入slave的mysql里
mysql tes < test.sql

2、修改slave的my.ini
####################
log-bin=c:/mysql/logs/mysql_binary_log
server-id=2
master-host=192.168.1.1
master-user=backup
master-password=123456
master-connect-retry=60
replicate-do-db=test
####################

说明：
master-host=192.168.1.1 #master的IP
master-user=backup                            #master上作为同步用的用户名
master-password=123456                 #同步用户名的密码
master-connect-retry=60                     #设置同步的时间
replicate-do-db=test                             #需要同步的数据库

3、重新启用mysql
c:/mysql/bin/mysql restart 或 /etc/init.d/mysql restart

4、进入slave的mysql，对mysql进行操作
mysql> stop slave;                  #停止slave服务器

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.1.1',
    ->     MASTER_USER='backup',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_LOG_FILE='mysql_binary_log.025',
    ->     MASTER_LOG_POS=796947;

mysql> START SLAVE;         #开启slave服务器就可以同步了

注：
MASTER_LOG_FILE='mysql_binary_log.025',
MASTER_LOG_POS=796947;
上面这两条是一开始从master上进入mysql，运行 SHOW MASTER STATUS; 查看到的,在实际操作中也可以不加的。

PostgreSQL数据库同步方法：

源库: source_db
目标库： des_db
源库表：

skytf.hk_tbl_charge_oversea
skytf.hk_tbl_charge_oversea_dsm

目标库表：

hk_tbl_charge_oversea
hk_tbl_charge_oversea_dsm
需求：每天将源库指定表的前一天数据同步到目标库

编写同步脚本，这套脚本有两个功能：

每天定时执行，同步源库 source_db 指定表的前一天数据；
如果由于种种原因，数据同步失败，可以用这套脚本补数据。

exec_sync_crp.sh 脚本代码，如下：

#!/bin/bash
export PGPORT=1921  
export PGDATA=/database/pgdata1921  
export LANG=en_US.utf8  
export PGHOME=/app/pgsql  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib  
export DATE=`date +"%Y%m%d%H%M"`  
export PATH=$PGHOME/bin:$PATH:.  
export MANPATH=$PGHOME/man:$MANPATH
  
yesterday=`date -d '-1 day' +%F`  
script_dir=/home/postgres/script/tf/sync_script
  
#Call script sync_crp.sh  
${script_dir}/sync_crp.sh ${yesterday}

备注：这个脚本调用来调用脚本 sync_crp_part.sh

sync_crp.sh 脚本代码

#!/bin/bash
if [ $# != 1 ]; then  
echo Usage: ./sync_crp.sh arg1  
echo Examples:  
echo To get the date of 2011-09-01, enter:  
echo ' sync_crp.sh 2011-09-01 > 2011-09-01.out'  
exit 0  
fi
  
export PGPORT=1921  
export PGDATA=/database/pgdata1921  
export LANG=en_US.utf8  
export PGHOME=/app/pgsql  
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib  
export DATE=`date +"%Y%m%d%H%M"`  
export PATH=$PGHOME/bin:$PATH:.  
export MANPATH=$PGHOME/man:$MANPATH
today=$1  
nextday=`date -d "${today} +1 days" +%F`  
host_ip='192.168.xx.xx'  
v_email="[Francs3@163.com](mailto:Francs3@163.com)"
  
#Make SQL strings, the sql of getting database source_db data  
sql1="select * from skytf.hk_tbl_charge_oversea where createtime >= '${today}' and createtime <'${nextday}' "  
sql2="select * from skytf.hk_tbl_charge_oversea_dsm where createtime >= '${today}' and createtime <'${nextday}' "
  
################## The real get data process ######################  
## 同步表 crp.hk_tbl_charge_oversea  
echo "`date +%F %T` Begin sync report data from data source_db to crp database ! "  
psql -h 192.168.xx.xx -p 1921 source_db francs -c "copy ($sql1) to stdout " | psql des_db des_db -c "copy crp.hk_tbl_charge_oversea from stdin"
if [ $? -ne 0 ]  
then  
 echo "`date +%F %T` ERROR: Sync table hk_tbl_charge_oversea error "  
 echo -e "`date +%F %T`nIP: ${host_ip}nHostname: `hostname`nnAuthor: francs(DBA)" | mutt -s " ERROR: Sync table hk_tbl_charge_oversea error " ${v_email}  
else  
 echo "`date +%F %T` Table hk_tbl_charge_oversea is finished "  
fi
  
## 同步表 crp.hk_tbl_charge_oversea_dsm  
psql -h 192.168.xx.xx -p 1921 source_db francs -c "copy ($sql2) to stdout " | psql des_db des_db -c "copy crp.hk_tbl_charge_oversea_dsm from stdin"
if [ $? -ne 0 ]  
then  
 echo "`date +%F %T` ERROR: Sync table hk_tbl_charge_oversea_dsm error "  
 echo -e "`date +%F %T`nIP: ${host_ip}nHostname: `hostname`nnAuthor: francs(DBA)" | mutt -s " ERROR: Sync table hk_tbl_charge_oversea_dsm error " ${v_email}  
else  
 echo "`date +%F %T` Table hk_tbl_charge_oversea_dsm is finished "  
fi

备注：脚本里只同步了两张表，如果还有其它表，可以继续加代码，如果表名一样，可以写个 for 循环，由于这里源表名，目标表名不一样，所以没用 for 循环, 这个脚本按天同步数据，如果需要补数据,只要加上日期参数即可；

设置任务计划

加入 crontab 定时执行，如下

1 2	# Sync crp data from data warehouse 6* * * * /home/postgres/script/tf/sync_script/exec_sync_crp.sh >> /home/postgres/script/tf/sync_script/exec_sync_crp.log 2>&1

到此，三种不同类型数据库数据同步方式介绍完毕。

更多内容欢迎star我的的github：https://github.com/yswenli
如果发现本文有什么问题和任何建议，也随时欢迎交流~

这篇关于数据库同步的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！