How to install a OpenVPN System Based On User/Password Authentication with mysql & Day Control (libpam-mysql)

Source Page: http://sysadmin.compxtreme.ro/how-to-install-a-openvpn-system-based-on-userpassword-authentication-with-mysql-day-control-libpam-mysql/

I have added some notes through the article.

here comes the article:

This document describes how to install a OpenVPN server with User/Password authentication with mysql and day control using libpam-mysql. This will be a brief, but a very practical document.

Install mysql server

apt-get install mysql-server

Create a mysql user and a database to be used later

Create user

mysql -u root -p
CREATE DATABASE openvpn;
USE openvpn;

create database

CREATE USER 'openvpn'@'localhost' IDENTIFIED BY 'lNPg5TAIy82zFpEn';
GRANT ALL PRIVILEGES ON `openvpn`.* TO 'openvpn'@'localhost';
FLUSH PRIVILEGES;

Create user table

CREATE TABLE IF NOT EXISTS `user` (
    `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `user_pass` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1234',
    `user_mail` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `user_phone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `user_start_date` date NOT NULL,
    `user_end_date` date NOT NULL,
    `user_online` enum('yes','no') NOT NULL DEFAULT 'no',
    `user_enable` enum('yes','no') NOT NULL DEFAULT 'yes',
PRIMARY KEY (`user_id`),
KEY `user_pass` (`user_pass`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Create log table

CREATE TABLE IF NOT EXISTS `log` (
    `log_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `log_trusted_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_trusted_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_remote_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_remote_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `log_start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `log_end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `log_received` float NOT NULL DEFAULT '0',
    `log_send` float NOT NULL DEFAULT '0',
PRIMARY KEY (`log_id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Install OpenVPN

apt-get install openvpn

Generate keys

apt-get install openssl

Copy certificate generate file from example

cp -R /usr/share/doc/openvpn/examples/easy-rsa /etc/openvpn/.
cd /etc/openvpn/easy-rsa/2.0/

Replace some options (I have no idea why author want to do this)

sed -i -e 's/--interact //g' build-key

search and replace the following values in /etc/openvpn/easy-keys/2.0/vars

vim vars
export KEY_SIZE=2048

export KEY_COUNTRY="SE"
export KEY_PROVINCE="SE"
export KEY_CITY="STOCKHOLM"
export KEY_ORG="Company Name"
export KEY_EMAIL="email@example.org"
export KEY_CN=vpn.example.org
export KEY_NAME=operations
export KEY_OU=operations
export PKCS11_MODULE_PATH=changeme
export PKCS11_PIN=1234

save and exit

Disable unqiue subject

sed -i -e 's/unique_subject = yes/unique_subject = no/g' /etc/openvpn/easy-rsa/2.0/keys/index.txt.attr

Start to bulid certificates

source ./vars
./clean-all
./build-ca
./build-key-server vpn.example.org
./build-dh
cp -a keys /etc/openvpn/.

Install libpam-mysql and setup pam authentication based on it

apt-get install libpam-mysql

Add a new file inside pam

touch /etc/pam.d/openvpn

Paste the following content into the file we just created

auth sufficient pam_mysql.so user=openvpn passwd=lNPg5TAIy82zFpEn host=localhost db=openvpn [table=user] usercolumn=user.user_id passwdcolumn=user.user_pass [where=user.user_enable=1 AND user.user_start_date!=user.user_end_date AND TO_DAYS(now()) >= TO_DAYS(user.user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user.user_end_date) OR user.user_end_date='0000-00-00')] sqllog=0 crypt=0
account required pam_mysql.so user=openvpn passwd=lNPg5TAIy82zFpEn host=localhost db=openvpn [table=user] usercolumn=user.user_id passwdcolumn=user.user_pass [where=user.user_enable=1 AND user.user_start_date!=user.user_end_date AND TO_DAYS(now()) >= TO_DAYS(user.user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user.user_end_date) OR user.user_end_date='0000-00-00')] sqllog=0 crypt=0

Create scripts to log OpenVPN access activity

mkdir /etc/openvpn/scripts/ && cd $_

Create the following file

vim /etc/openvpn/scripts/config.sh
#!/bin/bash
##Dababase Server
HOST='127.0.0.1'
#Default port = 3306
PORT='3306'
#Username
USER='openvpn'
#Password
PASS='lNPg5TAIy82zFpEn'
#database name
DB='openvpn'
vim /etc/openvpn/scripts/connect.sh
#!/bin/bash
. /etc/openvpn/scripts/config.sh
##insert data connection to table log
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "INSERT INTO log (log_id,user_id,log_trusted_ip,log_trusted_port,log_remote_ip,log_remote_port,log_start_time,log_end_time,log_received,log_send) VALUES(NULL,'$common_name','$trusted_ip','$trusted_port','$ifconfig_pool_remote_ip','$remote_port_1',now(),'0000-00-00 00:00:00','$bytes_received','$bytes_sent')"
##set status online to user connected
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online='yes' WHERE user_id='$common_name'"
vim /etc/openvpn/scripts/disconnect.sh
#!/bin/bash
. /etc/openvpn/scripts/config.sh
##set status offline to user disconnected
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online='no' WHERE user_id='$common_name'"
##insert data disconnected to table log
mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE log SET log_end_time=now(),log_received='$bytes_received',log_send='$bytes_sent' WHERE log_trusted_ip='$trusted_ip' AND log_trusted_port='$trusted_port' AND user_id='$common_name' AND log_end_time='0000-00-00 00:00:00'"

Change the permission of the files we just created

chmod 755 /etc/openvpn/scripts/*.sh

Create the password file for accessing OpenVPN management interface via telnet

echo "wYYoFlaQa8nGQoO8" > /etc/openvpn/pw-management-file
chmod 600 /etc/openvpn/pw-management-file

Configure OpenVPN

vim /etc/openvpn/vpn.example.org.conf
##general settings
port 1194
proto udp
dev tun

##keys
ca /etc/openvpn/keys/ca.crt
cert /etc/openvpn/keys/vpn.example.org.crt
key /etc/openvpn/keys/vpn.example.org.key
dh /etc/openvpn/keys/dh2048.pem

##FIXME: ip for the clients
server 10.0.1.0 255.255.255.0
ifconfig-pool-persist ipp.txt
##FIXME: routes pushed to the client
push "route 172.16.1.0 255.255.255.0"
push "route 10.0.0.0 255.0.0.0"
push "route 195.248.229.19 255.255.255.255"

##Auth
comp-lzo
user nobody
#group nogroup
client-to-client
#client-cert-not-required
username-as-common-name

##user/pass auth from mysql
plugin /usr/lib/openvpn/openvpn-auth-pam.so openvpn

##script connect-disconnect
script-security 3 system
client-connect /etc/openvpn/scripts/connect.sh
client-disconnect /etc/openvpn/scripts/disconnect.sh

##management
management localhost 1194 pw-management-file

keepalive 10 120
persist-key
persist-tun
status status.log
verb 3

Start OpenVPN

/etc/init.d/openvpn start

Note: I also created a script to manage OpenVPN users. If you would like to have it, please post a comment.
Note2: feel free to use my user management script! I am pretty sure it has a lot of bugs and things to improve!!! Use it on your own risk!

the 2 notes are written by original author.

webpage to statistic OTRS time

最近好无聊啊,被老板要求统计OTRS里面所用的时间,OTRS本身的PDF报靠可读性不是很友好,而且,这个时间统计本身是不支持的,就开始自己动手写一个了。。。

由于SQL比较长,我就先做了几个视图。。。

time_stats视图(所有时间):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`)) group by `u`.`id`,`t`.`type_id`

time_stats_3mons视图(最近三个月内):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_3mons` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (`ta`.`create_time` between (now() - interval 3 month) and now())) group by `u`.`id`,`t`.`type_id`

time_stats_6mons视图(最近6个月内):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_6mons` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (`ta`.`create_time` between (now() - interval 6 month) and now())) group by `u`.`id`,`t`.`type_id`

time_stats_lastmon视图(上个月):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_lastmon` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (date_format(`ta`.`create_time`,'%y-%m') = date_format((curdate() - interval 1 month),'%y-%m'))) group by `u`.`id`,`t`.`type_id`

time_stats_lastweek视图(上周):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_lastweek` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (yearweek(`ta`.`create_time`,0) = (yearweek(now(),0) - 1))) group by `u`.`id`,`t`.`type_id`

time_stats_thismon视图(本月):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_thismon` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (date_format(`ta`.`create_time`,'%y-%m') = date_format(now(),'%y-%m'))) group by `u`.`id`,`t`.`type_id`

time_stats_thisweek视图(本周):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `time_stats_thismon` AS select concat(`u`.`first_name`,' ',`u`.`last_name`) AS `name`,`u`.`id` AS `uid`,`tt`.`name` AS `type`,`tt`.`id` AS `tid`,sum(`ta`.`time_unit`) AS `minutes` from (((`time_accounting` `ta` join `users` `u`) join `ticket` `t`) join `ticket_type` `tt`) where ((`ta`.`change_by` = `u`.`id`) and (`ta`.`ticket_id` = `t`.`id`) and (`t`.`type_id` = `tt`.`id`) and (date_format(`ta`.`create_time`,'%y-%m') = date_format(now(),'%y-%m'))) group by `u`.`id`,`t`.`type_id`

然后就是php代码。。。。

<?php
        $remote_ip = $_SERVER["REMOTE_ADDR"];
        $page = $_SERVER["SCRIPT_NAME"];
        $ips=array(
                        '10.0.0.1',        //Sample King IP
                        '10.0.0.2',      //Brutex IP
                        '10.0.0.3',
                );
        if(!in_array($remote_ip,$ips)){
                die("<h1>Permession Denied!</h1>");
        }
?>


<?php

        echo "<!DOCTYPE html>\n";
        echo "<html>\n";
        echo "<head>\n";
        echo "<title>OTRS Time Stats</title>\n";
        echo "<style>\ntable,th,td\n{\n\tborder:1px solid black;\n\tborder-collapse:collapse\n}\n</style>\n";
        echo "</head>\n";
        echo "<body>\n";

?>

<?php
        $times = array(
                        "time_stats_thisweek" => "This week",
                        "time_stats_lastweek" => "Last week",
                        "time_stats_thismon" => "This month",
                        "time_stats_lastmon" => "Last month",
                        "time_stats_3mons" => "Latest 3 months",
                        "time_stats_6mons" => "Latest 6 months",
                        "time_stats" => "All time",
                );

        $timeperiod = $_POST['timeperiod'];
        if(!array_key_exists($timeperiod,$times)){
                echo "<h2>Select a time period you want to query:</h2>\n";
                echo "<form action=\"$page\" method=\"post\">\n";
                echo "<select name=\"timeperiod\">\n";
                foreach($times as $key => $val){
                        echo "\t\t<option value=\"$key\">".$val."</option>\n";
                }
                echo "\t</select>\n";
                echo "<input type=submit>\n</form>";
        }else{
?>

<?php

        $tablename = $timeperiod;

        $conn = @mysql_connect("localhost","readonly","readonly") or die("Cannot connect to DB");
        mysql_select_db("otrs",$conn);
        $user_list = mysql_query("select distinct name from $tablename order by uid",$conn);
        while($row = mysql_fetch_array($user_list)){
                //$newuids[] = $row['uid'];
                $newusers[] = $row['name'];
        }
        $type_list = mysql_query("select distinct type,tid from $tablename order by tid",$conn);
        while($row = mysql_fetch_array($type_list)){
                $newtids[] = $row['tid'];
                $newtypes[] = $row['type'];
        }

        echo "<h3>Time Statistics for <em><font color=red>".$times["$tablename"]."</font></em></h3>\n";
        echo "<table border=1>\n\t<tr><td>&nbsp;</td>";
        foreach($newtypes as $val){
                echo "<td>".$val."</td>";
        }
        echo "<td><b>Sum</b></td></tr>\n";

        foreach($newusers as $val){
                echo "\t<tr><td>$val</td>";
                $timesum = 0;
                foreach($newtids as $val2){
                        $time_res = mysql_query("select minutes from $tablename where tid=$val2 and name='$val'",$conn);
                        #$time_res = mysql_query("select round(minutes/60,1) as minutes from $tablename where tid=$val2 and name='$val'",$conn);
                        if(!time_res){
                                echo "<td>&nbsp;</td>";
                        }else{
                                $time = mysql_result($time_res,0);
                                echo "<td>".round($time/60,1)."</td>";
                                $timesum = $timesum+$time;
                        }
                }
                echo "<td>".round($timesum/60,1)."</td></tr>\n";
        }

        echo "\t<tr><td><b>Total</b></td>";
        foreach($newtids as $val){
                $time_res = mysql_query("select round(sum(minutes)/60,1) as minutes from $tablename where tid=$val",$conn);
                if(!time_res){
                        echo "<td>&nbsp;</td>";
                }else{
                        $time = mysql_result($time_res,0);
                        echo "<td>".$time."</td>";
                }
        }
        $timesum_res = mysql_query("select round(sum(minutes)/60,1) as minutes from $tablename",$conn);
        $timesum = mysql_result($timesum_res,0);
        echo "<td>".$timesum."</td></tr>\n";

        echo "</table>\n";
        echo "<br>\n";
        echo "<div><a href=\"".$page."\">Back</a></div>\n";
        mysql_close($conn);
?>

<?php
        }
?>
<?php
        echo "</body>\n";
        echo "</html>\n";
?>

mysqldump at replica master

一直以来,数据库备份就是我最头痛的问题,特别是从库完全挂掉的时候,以前总是人把所有的程序完全停止,现在好了,终算让我看到这个参数了

mysqldump --master-data=1 --all-databases > all-databases.sql

–lock-all-tables:在备份全过程中锁住所有的表,默认不启用。当启用时会自动关闭–single-transaction和–lock-tables参数。

–master-data:当值为0时,什么也不做。当值为1时,在SQL中自动加上CHANGE MASTER命令,当值为2时自动加上CHANGE MASTER命令,但是被注释的,导入时并不会自动执行。启用这个参数时如果没有启用–single-transaction,就会自动启用–lock-all-tables,当然了–lock-tables永远都是关闭的。

-x, --lock-all-tables
                      Locks all tables across all databases. This is achieved
                      by taking a global read lock for the duration of the
                      whole dump. Automatically turns --single-transaction and
                      --lock-tables off.
  --master-data[=#]   This causes the binary log position and filename to be
                      appended to the output. If equal to 1, will print it as a
                      CHANGE MASTER command; if equal to 2, that command will
                      be prefixed with a comment symbol. This option will turn
                      --lock-all-tables on, unless --single-transaction is
                      specified too (in which case a global read lock is only
                      taken a short time at the beginning of the dump; don't
                      forget to read about --single-transaction below). In all
                      cases, any action on logs will happen at the exact moment
                      of the dump. Option automatically turns --lock-tables
                      off.

php script to monitor OTRS queue size from SNMP

最近公司上线OTRS,感觉还不错,顾问居然还给了数据库的结构图,闲下来就写了这个php脚本给Opsview用来监控OTRS支持队列的大小。别问我为什么用php…因为我实在是不会pel….

测试环境:

DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=12.04
DISTRIB_CODENAME=precise
DISTRIB_DESCRIPTION="Ubuntu 12.04.4 LTS"

废话不说,先帖脚本:

#!/usr/bin/php
<?php

# Tomas Tang on 18/02/2014
# Check the OTRS support queue size

# Disable error report
error_reporting(0);

# get arguments
$procname = $argv[0];
$queuename = $argv[1];
$warnlevel = $argv[2];
$critilevel = $argv[3];

# keep it for future develop
#$QUEUE = array(
#               "allnew",
#               "escalated",
#               "second_level_support",
#               "first_level_support",
#);


# set query SQL and help
if("$queuename"=='allnew'){
        # all the tickets with state new but not in delete queue
        $sql = "select count(1) from ticket where ticket_state_id=1 and queue_id!=3;";
}elseif("$queuename"=='escalated'){
        # not in use yet, to be finished
        $sql = "select count(1) from ticket where ticket_state_id=1 and queue_id!=3;";
}elseif("$queuename"=='first_level_support'){
        # all tickets which are not (pending to) closed
        $sql = "select count(1) from ticket where ticket_state_id in (1,4,6,11) and queue_id=5;";
}elseif("$queuename"=='second_level_support'){
        # all tickets which are not (pending to) closed
        $sql = "select count(1) from ticket where ticket_state_id in (1,4,6,11) and queue_id=6;";
}else{
        echo "Usage: $procname  <QueueName>  <WarnNumber>  <CriticalNumber>\n";
        echo "      QueueName:      Queueu name to query, right now only in \"allnew\",\"escalated\",\"first_level_support\" and \"second_level_support\"\n";
        echo "      WarnNumber:     Tickets quantity start to be warn, required\n";
        echo "      CriticalNumber: Tickets quantity start to be critical, must greater than WarnNumber, required\n";
        exit(3);
}

# Double check query SQL
if(empty($sql)){
        echo "UNKNOWN - Unknown error\n";
        exit(3);
}

# Check arguments again.
if(!is_numeric("$warnlevel")||!is_numeric("$critilevel")){
        echo "UNKNOWN - Incorrect arguments.\n";
        exit(3);
}elseif($warnlevel>=$critilevel){
        echo "UNKNOWN - Incorrect warn/critical level.\n";
        exit(3);
}

# connect to database
$conn = @mysql_connect("localhost","readonly","readonly") or die("Cannot connect to DB");
mysql_select_db("otrs",$conn);

# fetch data
$result = mysql_query("$sql",$conn) or die("Query failed");
$result = mysql_result($result,0);

# close db
mysql_close($conn);

if ($result>=$critilevel){
        echo "CRITICAL - Queue size is $result, queue is huge!!!\n";
        exit(2);
}elseif($result>=$warnlevel){
        echo "WARN - Queue size is $result, queue is big!\n";
        exit(1);
}else{
        echo "OK - Queue size is $result, queue is OK.\n";
        exit(0);
}

?>

脚本本身没什么好看的,不过最开始用来传参的东西还是蛮好玩的,这种脚本不能跟在web里面一直的传参,只能通过这个叫$argv的数组。

把脚本保存为

/usr/local/bin/check_otrs_queue

修改/etc/snmp/snmpd.conf,加上下面几行

exec otrs_new_tickets /usr/local/bin/check_otrs_queue allnew 15 20
exec first_level_support /usr/local/bin/check_otrs_queue first_level_support 30 50
exec second_level_support /usr/local/bin/check_otrs_queue second_level_support 10 15

最后就是使用check_snmp_exec.sh检测了~~~

Error: /usr/bin/mysqlcheck doesn’t support multiple contradicting commands.

MySQL 5.5对比MySQL 5.0和5.1改变还真不少,就连我以前常用的修复表命令到了5.5时代居然都不能用了。。。

/usr/bin/mysqlcheck --check --auto-repair --optimize --all-databases

一运行就给我报:

Error: /usr/bin/mysqlcheck doesn't support multiple contradicting commands.

而且这个错误在Google里面都好像没有太多的答案,最好在一个阿拉伯文的论坛里面找到了解决方案:其实只要去掉–check就好了

/usr/bin/mysqlcheck --auto-repair --optimize --all-databases

mysqldump迁移编码不同的数据库

来源:http://hi.baidu.com/seaweaver/blog/item/fbd9a8ec933c532562d09f5c.html

第一步,导出旧库

mysqldump --default-character-set latin1 -uroot -pXXX --database db >     /tmp/old.sql

第二步,转换编码

iconv -t utf-8 -f gb2312 -c /tmp/old.sql > /tmp/new.sql

第三步,导入新库
修改new.sql,增加一条sql语句: “SET NAMES utf8;”,保存。

mysql -hlocalhost -uroot db < new.sql

这次进行论坛迁移时,这个小blog居然一再出现乱码,实在是让小可我实在是汗顏。
进行深入分析时,发现dump出来的文件里面就已经是乱码了。用file进行分析时,发现文件已经是UTF8。
最后仔细想想新旧服务器之间的区别,可能只有mysql server字符集的区别。老服务器用的是默认的latin1,而新服务器我们改成了utf8。
在笔记本的ubuntu上装了个mysql,把sql文件导入,select了一下,发现数据居然是正常了的。
这样子问题就很明显了,马上在笔记本上执行

mysqldump  --default-character-set=latin1  -uroot -p123456 myhnet > myhnet.sql

再用cat看了一个myhnet.sql的内容,里面的中文已经是正常了。
用vi打开文件,把里面所有的latin1替换成utf8,上传到服务器,再导入,果然一切正常了~~~~

ubuntu下进行MySQL的移植

最近一直很忙,忙着加班进行服务器升级。

服务器全部升到了Ubuntu 1004 LTS,但是在进行mysql移植的时候碰到了个大问题,直接到原来的数据文件同步过来,mysql却无论如果也启动不了。

最后那边的老大告诉我说,是AppArmor的问题,让哥关掉这个什么AppArmor,果然,数据库就起来了。
简单查了一个,这个AppArmor应该是跟SELinux差不多的东西,但级别好像并没有SELinux那么高,直接停用服务就没用了。相关资料:https://wiki.ubuntu.com/AppArmor

另外还有两个问题:

1, socket文件的如果不能写入,数据库也是起不来的,所以,还要关注一下你指定的socket文件的位置的权限
2, 从5.0升级到5.1有很多麻烦,建议最好是做逻辑备份而不是做直接物理升级

好像直接升级也有问题,等待进一步的更新。

OPENVPN使用PAM及MYSQL进行用户认证【转】

1.使用PAM模块
PAM简介:
Pluggable Authentication Module (PAM) 是行业标准验证框架。
PAM 为系统管理员提供了选择系统上可用的任何验证服务来执行验证的灵活性。使用 PAM 框架还可以插入新的验证服务模块,并且无需修改应用程序即可使用,

包含帐户管理,用户验证,口令管理,会话管理四个模块.指定如何处理同一个 service-name 和 module-type 的多个定义的关键字。它为下列值之一
required模块测试必须成功。
optional模块测试可以失败。
sufficient如果测试成功,则不执行进一步的测试。

1>OPENVPN服务器端配置:

plugin /usr/sharelib/openvpn-auth-pam.so login //指定模块的位置,该动态链接库在OPENVPN发生包里需要编译
client-cert-not-required //客户端数字证书无需指定
username-as-common-name //用户名作为common name

2>OPENVPN客户端配置:

auth-user-pass

3>服务器设置:
需要增加相应的用户名及密码,使用useradd password命令
重新启动OPENVPN,则在客户端登录OPENVPN的时候会提示输入用户名及密码

2.使用PAM-MYSQL
1>安装MYSQL,添加用户名vpn 密码设置为vpn,用户表为库openvpn中的user

2>安装pam_mysql ,下载位置http://internap.dl.sourceforge.net/sourceforge/pam-mysql/pam_mysql-0.5.tar.gz

3>配置pam配置文件,在/etc/pam.d中新建openvpn内容如下

auth    required        pam_mysql.so          user=vpn passwd=vpn host=192.168.1.11 db=openvpn table=user usercolumn=username passwdcolumn=password
account required        pam_mysql.so                   user=vpn passwd=vpn host=192.168.1.11 db=openvpn   table=user usercolumn=username   passwdcolumn=password

注意:将pam_mysql.so文件拷入 /lib/security/位置;并且保证系统中有libmysqlclient.so文件,如果连接远程MYSQL数据库无需此文件

4>OPENVPN服务器配置
plugin /usr/sharelib/openvpn-auth-pam.so openvpn 其余配置与上同//加载/etc/pam.d/openvpn配置文件

5>客户端配置与上同