pagehelper-spring-boot-starter配置多数据源的分页


需求:同时连接Mysql和Oracle两个数据源,需要配置自动分页。

首先参考:https://www.cnblogs.com/xuwujing/p/8964927.html

补充说明,按照参考文档进行配置后,还是无法正确解析Oracle和Mysql的分页,所以,最终放弃通过application.yml配置文件进行配置,直接在Mysql和Oracle的SessionFactory分别写死如下代码:

Interceptor interceptor = new PageInterceptor();
Properties properties = new Properties(); properties.setProperty(“helperDialect”, “mysql”);//Oracle数据库时设置为oracle properties.setProperty(“reasonable”, “true”); interceptor.setProperties(properties);
sessionFactory.setPlugins(new Interceptor[] {interceptor});

最后SpringBoot启动时,设置排除自动配置:

@SpringBootApplication(exclude = PageHelperAutoConfiguration.class)

总结,两个数据源总的思路是:分别设置Mysql文件解析目录,设置不同的Mybatis配置,对应不同的sessionfactory和事务管理器,然后在sessionfactory中对pagehelper的方言进行正确设置,即可正在在两套数据源中分别进行切换。服务层调用Mapper的时候,是自动完成sessionFactory映射的,所以配置完后,在服务层调用的时候并不知晓数据的差异。

Advertisements

Oracle使用spool快速导出超大表


为了将Oracle的数据迁移到Mysql,使用Navicat类的工具迁移太慢,针对超1亿级别的个别表,使用spool先导出成CSV文件,然后在mysql中使用load命令装载CSV文件中的数据到表中。

注意点如下,一定要关掉命令输出,关掉之前,一秒钟1万条,关掉后,一秒钟大约20万条:

set SERVEROUT off
set TERM off

第二个注意点,要设置行的长度,防止换行,且需要设置截断行后多余的空白字符、防止分页

SET LINESIZE 2500
set trimspool on
set pagesize 0

outfile.sql文件命令如下:

set colsep ,
set feedback off
set heading off
set trimout on
set pagesize 0
set echo off
set SERVEROUT off
set TERM off
set trimspool on
SET LINESIZE 2500
spool /data/output.csv
select ‘”‘ || C1 || ‘”,”‘ || C2 || ‘”,”‘ || C3 || ‘”,”‘ || PKID || ‘”‘ from SCHEMA.TABLE_NAME;
spool off
exit

启动命令:

sqlplus -s user/password@orcl @outfile.sql

Mysql导入命令:

load data infile “/var/lib/mysql-files/output.csv” into table SCHEMA.TABLE_NAME fields terminated by ‘,’ enclosed by ‘”‘;

设置好行分割符和字段使用双引号括起来。

Nginx配置图片服务器


前端网站需要显示图片,在Nginx配置了一个imagelib URL过滤器,用于拦截图片请求,直接读取图片文件进行回显
server {
listen 80;
server_name 10.132.252.121;
access_log /var/log/nginx/default.access.log main;
location / {
root /data/html;
index index.html index.htm;
}
location ^~ /imagelib {
expires 24h;
root /data/imagelib/;
access_log /var/log/nginx/images.access.log;
proxy_store on;
proxy_store_access user:rw group:rw all:rw;
proxy_temp_path /data/imagelib/;
proxy_redirect off;
proxy_set_header Host 127.0.0.1;
client_max_body_size 10m;
client_body_buffer_size 1280k;
proxy_connect_timeout 900;
proxy_send_timeout 900;
proxy_read_timeout 900;
proxy_buffer_size 40k;
proxy_buffers 40 320k;
proxy_busy_buffers_size 640k;
proxy_temp_file_write_size 640k;
}
}

 

Linux制作堡垒机


参考:https://aws.amazon.com/cn/blogs/security/how-to-record-ssh-sessions-established-through-a-bastion-host/

按照亚马逊原始方案一步一步操作即可,注意一下红色字体部分,修复了script命令方法差异。

# Create a new folder for the log files
mkdir /var/log/bastion

# Allow ec2-user only to access this folder and its content
chown ec2-user:ec2-user /var/log/bastion
chmod -R 770 /var/log/bastion
setfacl -Rdm other:0 /var/log/bastion

# Make OpenSSH execute a custom script on logins
echo -e “\nForceCommand /usr/bin/bastion/shell” >> /etc/ssh/sshd_config

# Block some SSH features that bastion host users could use to circumvent
# the solution
awk ‘!/AllowTcpForwarding/’ /etc/ssh/sshd_config > temp && mv temp /etc/ssh/sshd_config
awk ‘!/X11Forwarding/’ /etc/ssh/sshd_config > temp && mv temp /etc/ssh/sshd_config
echo “AllowTcpForwarding no” >> /etc/ssh/sshd_config
echo “X11Forwarding no” >> /etc/ssh/sshd_config

mkdir /usr/bin/bastion

cat > /usr/bin/bastion/shell << ‘EOF’

# Check that the SSH client did not supply a command
if [[ -z $SSH_ORIGINAL_COMMAND ]]; then

# The format of log files is /var/log/bastion/YYYY-MM-DD_HH-MM-SS_user
LOG_FILE=”`date –date=”today” “+%Y-%m-%d_%H-%M-%S”`_`whoami`”
LOG_DIR=”/var/log/bastion/”

# Print a welcome message
echo “”
echo “NOTE: This SSH session will be recorded”
echo “AUDIT KEY: $LOG_FILE”
echo “”

# I suffix the log file name with a random string. I explain why
# later on.
SUFFIX=`mktemp -u _XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX`

# Wrap an interactive shell into “script” to record the SSH session
script -qf -t $LOG_DIR$LOG_FILE$SUFFIX.time $LOG_DIR$LOG_FILE$SUFFIX.data -c /bin/bash

else

# The “script” program could be circumvented with some commands
# (e.g. bash, nc). Therefore, I intentionally prevent users
# from supplying commands.

echo “This bastion supports interactive sessions only. Do not supply a command”
exit 1

fi

EOF

# Make the custom script executable
chmod a+x /usr/bin/bastion/shell

# Bastion host users could overwrite and tamper with an existing log file
# using “script” if they knew the exact file name. I take several measures
# to obfuscate the file name:
# 1. Add a random suffix to the log file name.
# 2. Prevent bastion host users from listing the folder containing log
# files.
# This is done by changing the group owner of “script” and setting GID.
chown root:ec2-user /usr/bin/script
chmod g+s /usr/bin/script

# 3. Prevent bastion host users from viewing processes owned by other
# users, because the log file name is one of the “script”
# execution parameters.
mount -o remount,rw,hidepid=2 /proc
awk ‘!/proc/’ /etc/fstab > temp && mv temp /etc/fstab
echo “proc /proc proc defaults,hidepid=2 0 0” >> /etc/fstab

# Restart the SSH service to apply /etc/ssh/sshd_config modifications.
service sshd restart