VPN连接出现LCP: timeout sending Config-Requests和unexpected termination of pppd解决办法

vultr上边新开了个VPS装centos7用来做vpn翻墙,装好ppp和pptpd后卡用户验证这一栏.
看了下系统log,记录如下:

tail -20 /var/log/messages
  n 29 04:14:12 vultr pptpd[17292]: CTRL: Client 113.89.11.152 control connection started
▽n 29 04:14:12 vultr pptpd[17292]: CTRL: Starting call (launching pppd, opening GRE)
Jan 29 04:14:12 vultr pppd[17293]: pppd 2.4.5 started by root, uid 0
Jan 29 04:14:12 vultr pppd[17293]: Using interface ppp0
Jan 29 04:14:12 vultr pppd[17293]: Connect: ppp0 <--> /dev/pts/1
Jan 29 04:14:42 vultr pppd[17293]: LCP: timeout sending Config-Requests
Jan 29 04:14:42 vultr pppd[17293]: Connection terminated.
Jan 29 04:14:42 vultr pppd[17293]: Modem hangup
Jan 29 04:14:42 vultr pppd[17293]: Exit.
Jan 29 04:14:42 vultr pptpd[17292]: GRE: read(fd=6,buffer=7f8d2efe4480,len=8196) from PTY failed: status = -1 error = Input/output error, usually caused by unexpected termination of pppd, check option syntax andpppd logs
Jan 29 04:14:42 vultr pptpd[17292]: CTRL: PTY read or GRE write failed (pty,gre)=(6,7)
Jan 29 04:14:42 vultr pptpd[17292]: CTRL: Client 113.89.11.152 control connection finished

google用关键字”pptp gre”,”usually caused by unexpected termination of pppd”等找出来的各种解决办法,包括注释掉”logwtmp”,”升级路由bois”等等,问题依然.
看来原因还是要自己一步一步找,于是打开”pptpd.conf”的debug选项,再在”/etc/rsyslog.conf”末添加:

*.debug        /var/log/ppp/pptpd.log

重启pptpd后再”cat pptpd.log”:

▽n 29 04:37:06 vultr systemd: Stopping System Logging Service...
Jan 29 04:37:06 vultr systemd: Starting System Logging Service...
Jan 29 04:37:06 vultr systemd: Started System Logging Service.
Jan 29 04:37:15 vultr pptpd[17474]: MGR: Launching /usr/sbin/pptpctrl to handle client
Jan 29 04:37:15 vultr pptpd[17474]: CTRL: local address = 10.10.0.1
Jan 29 04:37:15 vultr pptpd[17474]: CTRL: remote address = 10.10.0.100
Jan 29 04:37:15 vultr pptpd[17474]: CTRL: pppd options file = /etc/ppp/options.pptpd
Jan 29 04:37:15 vultr pptpd[17474]: CTRL: Client 113.89.11.152 control connection started
Jan 29 04:37:15 vultr pptpd[17474]: CTRL: Received PPTP Control Message (type: 1)
Jan 29 04:37:15 vultr pptpd[17474]: CTRL: Made a START CTRL CONN RPLY packet
Jan 29 04:37:15 vultr pptpd[17474]: CTRL: I wrote 156 bytes to the client.
Jan 29 04:37:15 vultr pptpd[17474]: CTRL: Sent packet to client
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: Received PPTP Control Message (type: 7)
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: Set parameters to 100000000 maxbps, 64 window size
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: Made a OUT CALL RPLY packet
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: Starting call (launching pppd, opening GRE)
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: pty_fd = 6
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: tty_fd = 7
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: I wrote 32 bytes to the client.
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: Sent packet to client
Jan 29 04:37:16 vultr pptpd[17475]: CTRL (PPPD Launcher): program binary = /usr/sbin/pppd
Jan 29 04:37:16 vultr pptpd[17475]: CTRL (PPPD Launcher): local address = 10.10.0.1
Jan 29 04:37:16 vultr pptpd[17475]: CTRL (PPPD Launcher): remote address = 10.10.0.100
Jan 29 04:37:16 vultr pppd[17475]: Plugin /usr/lib64/pptpd/pptpd-logwtmp.so loaded.
Jan 29 04:37:16 vultr pppd[17475]: pppd 2.4.5 started by root, uid 0
Jan 29 04:37:16 vultr pppd[17475]: Using interface ppp0
Jan 29 04:37:16 vultr pppd[17475]: Connect: ppp0 <--> /dev/pts/3
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: Received PPTP Control Message (type: 15)
Jan 29 04:37:16 vultr pptpd[17474]: CTRL: Got a SET LINK INFO packet with standard ACCMs
Jan 29 04:37:46 vultr pppd[17475]: LCP: timeout sending Config-Requests
Jan 29 04:37:46 vultr pppd[17475]: Connection terminated.
Jan 29 04:37:46 vultr pppd[17475]: Modem hangup
Jan 29 04:37:46 vultr pppd[17475]: Exit.
Jan 29 04:37:46 vultr pptpd[17474]: GRE: read(fd=6,buffer=7ff7e780e480,len=8196) from PTY failed: status = -1 error = Input/output error, usually caused by unexpected termination of pppd, check option syntax andpppd logs
Jan 29 04:37:46 vultr pptpd[17474]: CTRL: PTY read or GRE write failed (pty,gre)=(6,7)
Jan 29 04:37:46 vultr pptpd[17474]: CTRL: Reaping child PPP[17475]
Jan 29 04:37:46 vultr pptpd[17474]: CTRL: Client 113.89.11.152 control connection finished
Jan 29 04:37:46 vultr pptpd[17474]: CTRL: Exiting now

我去,几乎没有区别麻…只好根据log从上到下一个个排查了.google关键字从”LCP: timeout sending Config-Requests”开始.
终于在”Can’t connect to VPN on Fedora 21“这里找到答案:

Also seeing “LCP: timeout sending Config-Requests” reported in journalctl. I seems that the firewall is blocking GRE, I added rules to accept GRE via the commandline and now I’m able to setup my VPN:

firewall-cmd --direct --add-rule ipv4 filter INPUT 0 -p gre -j ACCEPT
firewall-cmd --direct --add-rule ipv6 filter INPUT 0 -p gre -j ACCEPT
firewall-cmd --reload

Hidde Boonstra (Feb 10 ’15)

顺道记录一下centos7下边的ppp,pptpd安装配置过程:

#rpm -Uvh http://download.fedoraproject.org/pub/epel/beta/7/x86_64/epel-release-7-1.noarch.rpm
#yum -y install ppp pptpd

#echo 'localip 10.10.0.1' >> /etc/pptpd.conf
#echo 'remoteip 10.10.0.100-199' >> /etc/pptpd.conf

#echo 'ms-dns 8.8.8.8' >> /etc/ppp/options.pptpd
#echo 'ms-dns 8.8.4.4' >> /etc/ppp/options.pptpd

#echo 'USERNAME pptpd PASSWORD *' >> /etc/ppp/chap-secrets

#echo 'net.ipv4.ip_forward = 1' >> /etc/sysctl.conf

#sysctl -p

#firewall-cmd --permanent --new-service=pptpd

#cat >/etc/firewalld/services/pptpd.xml<<EOF
<?xml version="1.0" encoding="utf-8"?>
<service>
  <port protocol="tcp" port="1723"/>
</service>
EOF
#firewall-cmd --permanent --zone=public --add-service=pptpd
#firewall-cmd --permanent --zone=public --add-masquerade
#firewall-cmd --direct --add-rule ipv4 filter INPUT 0 -p gre -j ACCEPT
#firewall-cmd --direct --add-rule ipv6 filter INPUT 0 -p gre -j ACCEPT
#firewall-cmd --reload

#systemctl start pptpd
#systemctl enable pptpd.service

MySQL服务器架构

#1,系统分区
文件系统创建
#2,文件存放
文件系统创建2
#3,编译依赖
文件系统创建3
#4,基本编译参数
文件系统创建4
生产环境关闭debug,embedded_server,layout用standle.
#5,cmake && make -j && make install

CFLAGS="-O3 -g -fno-exceptions -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing"
CXX=g++
CXXFLAGS="-O3 -g -fno-exceptions -fno-rtti -static-libgcc -fno-omit-frame-pointer -fno-strict-aliasing"
export CFLAGS CXX CXXFLAGS
cmake . [PARAMETERS]
make -j & make install

#5.TPC-C压力测试模型:
文件系统创建5
系统I/O压力测试工具:fio
fio是系统IO测试的工具,覆盖多种不同类型、不同方式的IO测试,并且简单易用。fio在压力测试中,常用于了解不同文件操作的IOPS极限,可以更加全面的了解系统IO处理能力。
1、编译安装
fio是开源的工具,可以在官方网址 http://freecode.com/projects/fio 查看相关的内容,源码编译和安装如下所示:
1.1、安装依赖

yum install libaio
yum install libaio-devel

1.2、获取源码

wget http://brick.kernel.dk/snaps/fio-2.1.7.tar.bz2

1.3、编译安装

tar -xjf fio-2.1.7.tar.bz2; cd fio-2.1.7
./configure 
make & make install

2、使用说明
fio工具支持多种类型的测试,并且参数非常多,可以通过帮助文档获得使用信息。以下内容简单说明如何查看帮助文档。

主要参数说明:
--help:获得帮助信息。
--cmdhelp:获得命令的帮助文档。
--enghelp:获得引擎的帮助文档。
--debug:通过debug方式查看测试的详细信息。(process, file, io, mem, blktrace, verify, random, parse, diskutil, job, mutex, profile, time, net, rate)
--output:测试结果输出到文件。
--output-format:设置输出格式。(terse, json, normal)
--crctest:测试crc性能。(md5, crc64, crc32, crc32c, crc16, crc7, sha1, sha256, sha512, xxhash:)
--cpuclock-test	:CPU始终测试。

3、测试
fio测试的类型和选项非常多,但是通常情况下,对于MySQL数据库服务器,一般测试随机读、随机写、随机读写三种情况下,sync、fsync的ioengine方式下,IO的性能指标情况。通过测试这些指标,可以对系统的IO处理能力进行资源评估和分配。

fio -filename=/dev/sdb1 -direct=1 -iodepth 1 -rw=randread -ioengine=sync -bs=16k -size=200G -numjobs=10 -runtime=1000 -group_reporting -name=mytest

参数说明
filename:测试的系统盘目录。
direct:测试绕过机器自带的buffer,使测试结果更真实。
iodepth:设置IO队列的深度。
rw:测试读写类型。
ioengine:io引擎方式。
bs:数据块大小。
size:测试文件的大小。
numjobs:测试次数。
runtime:测试时间。
rwmixwrite:在混合读写的模式下,写占的权重。
group_reporting:测试结果汇总每个进程的信息。
lockmem:测试使用内存大小。
zero_buffers:测试过程使用0初始化系统buffer。
nrfiles:测试过程中每个进程生成文件的数量。
4、测试结果
测试结果显示了详细的系统信息,包括io、latency、bandwidth、cpu等信息,详细如下所示:

mytest: (groupid=0, jobs=10): err= 0: pid=10775: Thu Jun 12 08:47:28 2014
  read : io=262560KB, bw=22965KB/s, iops=1435, runt= 11433msec
    clat (usec): min=108, max=57601, avg=488.40, stdev=483.77
     lat (usec): min=108, max=57601, avg=488.62, stdev=483.78
    clat percentiles (usec):
     |  1.00th=[  112],  5.00th=[  314], 10.00th=[  334], 20.00th=[  414],
     | 30.00th=[  442], 40.00th=[  462], 50.00th=[  486], 60.00th=[  506],
     | 70.00th=[  532], 80.00th=[  556], 90.00th=[  620], 95.00th=[  684],
     | 99.00th=[  812], 99.50th=[  868], 99.90th=[ 1144], 99.95th=[ 5024],
     | 99.99th=[10048]
    bw (KB  /s): min=    1, max=42144, per=83.52%, avg=19179.29, stdev=16716.64
    lat (usec) : 250=3.02%, 500=53.40%, 750=41.37%, 1000=2.04%
    lat (msec) : 2=0.08%, 4=0.03%, 10=0.05%, 100=0.01%
  cpu          : usr=0.05%, sys=88.55%, ctx=17703, majf=0, minf=379
  IO depths    : 1=100.0%, 2=0.0%, 4=0.0%, 8=0.0%, 16=0.0%, 32=0.0%, >=64=0.0%
     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%
     issued    : total=r=16410/w=0/d=0, short=r=0/w=0/d=0
     latency   : target=0, window=0, percentile=100.00%, depth=1

Run status group 0 (all jobs):
   READ: io=262560KB, aggrb=22965KB/s, minb=22965KB/s, maxb=22965KB/s, mint=11433msec, maxt=11433msec

Disk stats (read/write):
  sdb: ios=16418/55, merge=57/69, ticks=7780/39, in_queue=7816, util=18.80%

MySQL的左右内连接面试题

题目如下:
mysql左右连接

解题思路:解构,分拆.把复杂的问题分拆成简单问题逐个处理.

先建表:

create table matchtable(
matchid int primary key auto_increment not null,
hostteamid int not null default 0,
guestteamid int not null default 0,
matchresult varchar(10) not null default '',
matchtime date not null default '2006-06-06'
)charset utf8;

insert into matchtable
(hostteamid,guestteamid,matchresult,matchtime)
values
(1,2,'2:0','2006-05-21'),
(2,3,'1:2','2006-06-21'),
(3,1,'2:5','2006-06-25'),
(3,2,'0:0','2006-07-01'),
(2,1,'3:2','2006-07-21');

create table teamtable(
teamid int primary key auto_increment not null,
teamname varchar(10) not null default ''
)charset utf8;

insert into teamtable
(teamname)
values
('国安'),
('申花'),
('恒大');

#1,先筛选出比赛结果:

select hostteamid,guestteamid,matchresult,matchtime from matchtable;

join1
#2,两表连查,分别筛选匹配出对应的主客队ID,队名:

select hostteamid,teamname as hostteam,matchresult,guestteamid,matchtime
from matchtable left join teamtable as hostteamtable
on matchtable.hostteamid=hostteamtable.teamid;

join2

select hostteamid,matchresult,guestteamid,teamname as guestteam,matchtime
from matchtable left join teamtable as guestteamtable
on matchtable.hostteamid=guestteamtable.teamid;


#3,把#2中两表连查出来的两张表组合成一张主客队对战结果表:

select hostteamid,hostteamtable.teamname as hostteam,matchresult,guestteamid,guestteamtable.teamname as guestteam,matchtime
from matchtable left join teamtable as hostteamtable
on matchtable.hostteamid=hostteamtable.teamid
left join teamtable as guestteamtable 
on matchtable.hostteamid=guestteamtable.teamid;


因为hostteamid和guestteamid都是到teamtable里边去对应teamid,所以要分别起别名以区分匹配字段.
#4,最后对#3的结果进行排列筛选:

select hostteamtable.teamname as hostteam,matchresult,guestteamtable.teamname as guestteam,matchtime
from matchtable left join teamtable as hostteamtable
on matchtable.hostteamid=hostteamtable.teamid
left join teamtable as guestteamtable 
on matchtable.guestteamid=guestteamtable.teamid
where matchtime between '2006-06-01' and '2006-07-01';

join5

MySQL having的运用

假设在stu表内有成绩表如下:
QQ图片20160101215857
试查询两门以及两门以上不及格同学的平均分,只允许使用一个select,不使用子查询等.

解题思路:反向思维.
选算出所有人的平均分

select name,avg(score) from stu group by name;

QQ图片20160101220605

然后根据name分组找出不同人挂的科目数量:

select name,sum(score<60) from stu group by name;

QQ图片20160101221059
这里用sum而不是用count,因为count(*)只是算出行数,不管在()里边给予什么值或者条件都不起作用.score<60出来的结果中,”张三”这个name的结果为一个0两个1,sum结果为2,count结果为3.

最后用sum的值作为条件来过滤筛选出最终结果:

select name,sum(score<60) as failnum,avg(score) from stu group by name having failnum>=2;

QQ图片20160101222317