springboot druid配置多数据源

转自:https://my.oschina.net/u/3681868/blog/1813011

一, 引入Jar包:


mysql
mysql-connector-java


com.alibaba
druid-spring-boot-starter
1.1.9

二, 配置参数:

spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
druid:
one: #数据源1
url: jdbc:mysql://localhost:3306/test1?...
username: root
password: root
two: #数据源2
url: jdbc:mysql://localhost:3306/test2?...
username: root
password: root
initial-size: 10
max-active: 100
min-idle: 10
max-wait: 60000
pool-prepared-statements: true
max-pool-prepared-statement-per-connection-size: 20
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
stat-view-servlet:
enabled: true
url-pattern: /druid/* #login-username: admin
#login-password: admin
filter:
stat:
log-slow-sql: true
slow-sql-millis: 1000
merge-sql: true
wall:
config:
multi-statement-allow: true
(参数配置,可参考: https://gitee.com/wenshao/druid/tree/master/druid-spring-boot-starter)

三, 编写配置文件:

1, 定义数据源名称常量 :

package com.gy.fast.common.config.data;

/**

  • 数据源名称
  • @author geYang
  • @date 2018-05-14
    */
    public interface DataSourceNames {
    String ONE = "ONE";
    String TWO = "TWO";
    }
    2, 创建动态数据源:

package com.gy.fast.common.config.data;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import javax.sql.DataSource;
import java.util.Map;

/**

  • 动态数据源

  • @author geYang

  • @date 2018-05-14
    */
    public class DynamicDataSource extends AbstractRoutingDataSource {
    private static final ThreadLocal contextHolder = new ThreadLocal<>();

    /**

    • 配置DataSource, defaultTargetDataSource为主数据库
      */
      public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources) {
      super.setDefaultTargetDataSource(defaultTargetDataSource);
      super.setTargetDataSources(targetDataSources);
      super.afterPropertiesSet();
      }

    @Override
    protected Object determineCurrentLookupKey() {
    return getDataSource();
    }

    public static void setDataSource(String dataSource) {
    contextHolder.set(dataSource);
    }

    public static String getDataSource() {
    return contextHolder.get();
    }

    public static void clearDataSource() {
    contextHolder.remove();
    }

}
3, 动态数据源配置:

package com.gy.fast.common.config.data;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

/**

  • 配置多数据源

  • @author geYang

  • @date 2018-05-14
    */
    @Configuration
    public class DynamicDataSourceConfig {

    /**

    • 创建 DataSource Bean
    • */

    @Bean
    @ConfigurationProperties("spring.datasource.druid.one")
    public DataSource oneDataSource(){
    DataSource dataSource = DruidDataSourceBuilder.create().build();
    return dataSource;
    }

    @Bean
    @ConfigurationProperties("spring.datasource.druid.two")
    public DataSource twoDataSource(){
    DataSource dataSource = DruidDataSourceBuilder.create().build();
    return dataSource;
    }

    /**

    • 如果还有数据源,在这继续添加 DataSource Bean
    • */

    @Bean
    @Primary
    public DynamicDataSource dataSource(DataSource oneDataSource, DataSource twoDataSource) {
    Map<Object, Object> targetDataSources = new HashMap<>(2);
    targetDataSources.put(DataSourceNames.ONE, oneDataSource);
    targetDataSources.put(DataSourceNames.TWO, twoDataSource);
    // 还有数据源,在targetDataSources中继续添加
    System.out.println("DataSources:" + targetDataSources);
    return new DynamicDataSource(oneDataSource, targetDataSources);
    }
    }
    4, 定义动态数据源注解:

package com.gy.fast.common.config.data;

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**

  • 多数据源注解
  • @author geYang
  • @date 2018-05-14
    */
    @Documented
    @Target({ElementType.METHOD})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface DataSource {
    String value() default DataSourceNames.ONE;
    }
    5, 设置数据源 AOP 代理:

package com.gy.fast.common.config.data;

import java.lang.reflect.Method;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;

/**

  • 数据源AOP切面处理

  • @author geYang

  • @date 2018-05-14
    */
    @Aspect
    @Component
    public class DataSourceAspect implements Ordered {
    protected Logger logger = LoggerFactory.getLogger(getClass());

    /**

    • 切点: 所有配置 DataSource 注解的方法
      */
      @Pointcut("@annotation(com.gy.fast.common.config.data.DataSource)")
      public void dataSourcePointCut() {}

    @Around("dataSourcePointCut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
    MethodSignature signature = (MethodSignature) point.getSignature();
    Method method = signature.getMethod();
    DataSource ds = method.getAnnotation(DataSource.class);
    // 通过判断 DataSource 中的值来判断当前方法应用哪个数据源
    DynamicDataSource.setDataSource(ds.value());
    System.out.println("当前数据源: " + ds.value());
    logger.debug("set datasource is " + ds.value());
    try {
    return point.proceed();
    } finally {
    DynamicDataSource.clearDataSource();
    logger.debug("clean datasource");
    }
    }

    @Override
    public int getOrder() {
    return 1;
    }
    }
    四, 修改启动文件:

package com.gy.fast;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.context.annotation.Import;

import com.gy.fast.common.config.data.DynamicDataSourceConfig;

/**

  • 动态数据源配置,需要将自有的配置依赖(DynamicDataSourceConfig),将原有的依赖去除(DataSourceAutoConfiguration)
  • @author geYang
  • @date 2018-05-15
    */
    @Import({DynamicDataSourceConfig.class})
    @SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
    public class FastApplication {
    public static void main(String[] args) {
    SpringApplication.run(FastApplication.class, args);
    }
    }
    五, 配置完成, 进行测试:

package com.gy.fast;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.gy.fast.common.config.data.DataSource;
import com.gy.fast.common.config.data.DataSourceNames;
import com.gy.fast.module.sys.entity.SysUser;
import com.gy.fast.module.sys.service.SysUserService;

/**

  • 测试多数据源

  • @author geYang

  • @date 2018-05-15
    */
    @Service
    public class DataSourceTestService {
    @Autowired
    private SysUserService sysUserService;

    public SysUser test1(Long userId){
    return sysUserService.selectById(userId);
    }

    @DataSource(DataSourceNames.TWO)
    public SysUser test2(Long userId){
    return sysUserService.selectById(userId);
    }
    }
    package com.gy.fast;

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.gy.fast.module.sys.entity.SysUser;

@RunWith(SpringRunner.class)
@SpringBootTest
public class DynamicDataSourceTest {
@Autowired
private DataSourceTestService dataSourceTestService;

@Test
public void test(){
    // 数据源ONE
    SysUser user1 = dataSourceTestService.test1(1L);
    System.out.println(ToStringBuilder.reflectionToString(user1));

    // 数据源TWO
    SysUser user2 = dataSourceTestService.test2(1L);
    System.out.println(ToStringBuilder.reflectionToString(user2));

    // 数据源ONE
    SysUser user3 = dataSourceTestService.test1(1L);
    System.out.println(ToStringBuilder.reflectionToString(user3));
}

}

rsync配合inotify做服务器间文件同步

由于需要添加下载服务器,现在每台服务器的流量比较大了,需要扩充,也就需要多服务器文件同步,本篇文章是在debian下操作的。
1:安装rsync

apt install rsync

2:修改配置文件(/etc/rsyncd.conf)

uid=root
gid=root
use chroot=no
max connections=10
timeout=600
strict modes=yes
port=873
#以下pid等等建议更换位置
pid file=/var/run/rsyncd.pid 
lock file=/var/run/rsyncd.lock
log file=/var/log/rsyncd.log

[apks]
path=/tmp/n2/
comment=rsync test logs
auth users=ciika
uid=root
gid=root
secrets file=/etc/rsyncd.secrets
read only=no
list=no

注意:上面[apks]如果不需要也可以不指定
3:配置用户名密码
/etc/rsyncd.secrets

ciika:ciika@2019

设置权限

chmod 600 /etc/rsyncd.secrets

4:配置客户端密码
/etc/rsync_client.pwd

ciika@2019

设置权限

chmod 600 /etc/rsync_client.pwd

以上3和4步骤分为两个端配置使用

5:启动
启动命令

/usr/bin/rsync --daemon --config=/etc/rsyncd.conf

6:同步命令
假如A向远端B同步数据
需要在A上指定/etc/rsync_client.pwd,在B上配置好apks

/usr/bin/rsync -auvrtzopgP --progress --password-file=/etc/rsync_client.pwd /home/files/ ciika@172.26.31.130::apks

7:安装 inotify

apt-get install inotify-tools

8:rsync组合inotify-tools完成实时同步
大概原理是inotify监控的文件发生变化,通知rsync进行同步,同时建议没固定的时间在全量同步一次
大概脚本为:

#!/bin/bash
src=/home/files/   # 需要同步的源路径
des=apks     # 目标服务器上 rsync --daemon 发布的名称,rsync --daemon这里就不做介绍了,网上搜一下,比较简单。
rsync_passwd_file=/etc/rsync_client.pwd    # rsync验证的密码文件
destip=172.26.31.130 # 目标服务器1
user=ciika    # rsync --daemon定义的验证用户名
cd $src     
inotifywait -mrq --format  '%Xe %w%f' -e modify,create,delete,attrib,close_write,move ./ | while read file # 把监控到有发生更改的"文件路径列表"循环
do
INO_EVENT=$(echo $file | awk '{print $1}')      # 把inotify输出切割 把事件类型部分赋值给INO_EVENT
INO_FILE=$(echo $file | awk '{print $2}')       # 把inotify输出切割 把文件路径部分赋值给INO_FILE
echo "-------------------------------$(date)------------------------------------"
echo $file
#增加、修改、写入完成、移动进事件
if [[ $INO_EVENT =~ 'CREATE' ]] || [[ $INO_EVENT =~ 'MODIFY' ]] || [[ $INO_EVENT =~ 'CLOSE_WRITE' ]] || [[ $INO_EVENT =~ 'MOVED_TO' ]] # 判断事件类型
then
    echo 'CREATE or MODIFY or CLOSE_WRITE or MOVED_TO'
    rsync -vlrtR --password-file=$rsync_passwd_file $(dirname $INO_FILE) $user@$destip::$des
fi
#删除、移动出事件
if [[ $INO_EVENT =~ 'DELETE' ]] || [[ $INO_EVENT =~ 'MOVED_FROM' ]]
then
    echo 'DELETE or MOVED_FROM'
    rsync -avzR --delete --password-file=$rsync_passwd_file $(dirname $INO_FILE) $user@$destip::$des
fi
#修改属性事件 指 touch chgrp chmod chown等操作
if [[ $INO_EVENT =~ 'ATTRIB' ]]
then
    echo 'ATTRIB'
    if [ ! -d "$INO_FILE" ] # 如果修改属性的是目录 则不同步,因为同步目录会发生递归扫描,等此目录下的文件发生同步时,rsync会顺带更新此目录。
    then
        rsync -avzcR --password-file=$rsync_passwd_file $(dirname $INO_FILE) $user@$destip::$des
    fi
fi
done

常见错误:
@ERROR: invalid uid
注意uid是否在远程服务器上存在,建议改成root试试
还有一种是验证失败,需要确定命令里面的用户名和远程配置的是否一致,本地和远程的密码是否一致
另外如果rsync同步速度非常慢,需要看看是否在传输列表的时候消耗的太长时间,如果是,可以直接精准的传输改变的文件。

rsync: failed to connect to 54.147.1.1 (54.147.1.1): Connection timed out (110)
需要在目标机器开始端口873

Gitlab安装和迁移

Gitlab做迁移,有一些麻烦,有如下结论:
1:Gitlab版本要完全一致,mysql版本最好一致(不能相差太大),服务器版本可以不一样(例如:迁移前环境为centos6.5,迁移后环境为centos7.4)
2: 以前如果是用的mysql,那么迁移后也需要用到mysql,缺省情况下,当你安装了gitlab的omnibus安全包后,缺省是不提供mysql2的gem,因此你无法配置为使用mysql数据库,只有Gitlab的OmniBus EE版本可以使用mysql数据库

一、准备工作
1:安装mysql,这里需要和原服务器一样版本的mysql,按需安装。
2: 安装相关依赖

yum -y update
yum whatprovides libmysqlclient*
yum install gcc automake autoconf libtool make
yum install gcc gcc-c++
yum install ruby-devel
yum -y group install "development tools"
yum -y install vim cmake wget
yum -y install readline-devel gdbm-devel openssl-devel expat-devel sqlite-devel libyaml-devel libffi-devel libxml2-devel libxslt-devel libicu-devel system-config-firewall-tui python-devel xmlto logwatch perl-ExtUtils-CBuilder

3:按需卸载gitlab版本
gitlab-ctl stop
rpm -qa|grep git 查看后删除
find / -name "gitlab" | xargs rm -rf
find / -name gitlab |xargs rm -rf

上面工作做好后重启机器

二、正式安装

#cent7 对应的安装包 rpm
具体要下载哪些版本去这个地方下载https://www.gitlab.com.cn/downloads/archives/
例如我的是:

curl -O https://downloads-packages.s3.amazonaws.com/centos-6.5/gitlab-7.2.1_omnibus-1.el6.x86_64.rpm

#安装对应的 openssh服务
yum install openssh-server
#设置开机启动
systemctl enable sshd
#启动sshd服务
systemctl start sshd

安装对应的 postfix服务同样设置开机启动和启动服务
yum install postfix
systemctl enable postfix
systemctl start postfix

#使用rpm 源安装,gitlab omnibus安装包
rpm -i gitlab-7.2.1_omnibus-1.el6.x86_64.rpm
#安装完成之后会出现complete,并提示你配置gitlab

#安装完成之后,配置gitlab
gitlab-ctl reconfigure

#设置服务器的防火墙允许http,和ssh服务
firewall-cmd --permanent --add-service=http

#重新启动防火墙
systemctl reload firewalld

#给gitlab服务绑定域名
vim /etc/gitlab/gitlab.rb
#填写你指定的绑定域名和端口,默认是80端口
external_url 'http://gitlab.ciika.com'

#重新启动一下服务
gitlab-ctl restart

#浏览器,打开绑定的域名及端口,访问
#默认的用户名是 root,密码是 5iveL!fe 登陆成功之后,会修改密码

三、恢复数据:
准备工作:

1:数据库赋权:

GRANT ALL PRIVILEGES ON *.* TO 'gitlab'@'127.0.0.1' identified by 'gitlab';
flush PRIVILEGES;

2:修改链接为mysql

gitlab_rails['db_adapter'] = 'mysql2'
gitlab_rails['db_encoding'] = 'utf8'
gitlab_rails['db_pool'] = 10
gitlab_rails['db_host'] = '127.0.0.1'
gitlab_rails['db_port'] = '3306'
gitlab_rails['db_database'] = 'gitlabhq_production'
gitlab_rails['db_username'] = 'gitlab'
gitlab_rails['db_password'] = 'gitlab'
postgresql['enable'] = false

3:修改config,由mysql改成postgres
/opt/gitlab/embedded/service/gitlab-rails/.bundle/config
修改后为:

BUNDLE_WITHOUT: development:test:postgres

4:安装mysql2

cd /opt/gitlab/embedded/bin/
./gem install -i /opt/gitlab/embedded/service/gem/ruby/2.1.0 mysql2 -v0.3.16

一定要注意-v0.3.16,如果后续报错(Could not find mysql2-0.3.16 in any of the sources),就是这里的版本弄错了

5:检查
gitlab-rake gitlab:check
这里一定要是成功的,如果不成功,看看那个依赖没有安装

6: 初始化Mysql数据,输入yes即可
gitlab-rake gitlab:setup

导入数据:
首先把之前备份的数据放置到/var/opt/gitlab/backups目录下
格式需要为xxx_gitlab_backup.tar
gitlab-rake gitlab:backup:restore
耐心的等待各个仓库的导出,成功后即可用之前的用户名和密码登录

可能会遇到如下报错:
Error: "You have to install development tools first."

Restoring repositories ...
rake aborted!
ActiveRecord::StatementInvalid: PG::Error: ERROR: relation "projects" does not exist
LINE 1: SELECT "projects".* FROM "projects" ORDER BY "projects"."...
^
: SELECT "projects".* FROM "projects" ORDER BY "projects"."id" ASC LIMIT 1000
PG::Error: ERROR: relation "projects" does not exist
LINE 1: SELECT "projects".* FROM "projects" ORDER BY "projects"."...
^
Tasks: TOP => gitlab:backup:repo:restore
(See full trace by running task with --trace)

如果在添加SSH KEY的时候报错:ssh key Fingerprint cannot be generated
编辑<path-to-gitlab>/app/models/key.rb
例如我的是:
/opt/gitlab/embedded/service/gitlab-rails/app/models/key.rb
原本是:cmd_output, cmd_status = popen(%W(ssh-keygen -lf #{file.path}), '/tmp')
改成:cmd_output, cmd_status = popen(%W(ssh-keygen -E md5 -lf #{file.path}), '/tmp')
重启问题解决

centos7.x 安装 MySQL 5.5

centos7默认自带的是mariadb,有些旧业务需要老版本的mysql
卸载原有mariadb

// 检索是否安装了mariadb
# rpm -qa | grep mariadb
mariadb-5.5.56-2.el7.x86_64
mariadb-devel-5.5.56-2.el7.x86_64
mariadb-libs-5.5.56-2.el7.x86_64
mariadb-server-5.5.56-2.el7.x86_64
// 卸载mariadb
# rpm -e --nodeps mariadb-5.5.56-2.el7.x86_64 mariadb-devel-5.5.56-2.el7.x86_64 
mariadb-libs-5.5.56-2.el7.x86_64 mariadb-server-5.5.56-2.el7.x86_64
// 设置rmp 
# rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
# rpm -Uvh https://mirror.webtatic.com/yum/el6/latest.rpm
// 下载安装MySql client and server
# yum install mysql55w mysql55w-server
// 启动MySql
# service mysqld start
// 修改密码(初始密码为空)
# mysqladmin -u root password 'root'
// 登录
# mysql -u root -p