Pages

Saturday, January 7, 2017

F5 (Tips and Tricks)

1. Restoring the BIG-IP configuration to the factory default setting

Impact of procedure: This procedure removes all BIG-IP local traffic objects, network configuration, and BIG-IP module data. Admin and Root password will be reset to default. Your MGMT interface ip address will be kept.

1.1 Log in to the Traffic Management Shell (tmsh) by typing the following command:
tmsh

1.2 To restore the configuration to the factory default setting, type the following command:
load sys config default

1.3 You are prompted with the following confirmation:
Reset the system configuration to factory defaults? (y/n)

To confirm that you want to restore factory default values, press the following key:
y

1.4 Save the change by typing the following command:
save sys config partitions all

1.5 Reboot the BIG-IP device
reboot


2. Upgrade F5 TMOS

2.1 Download the ISO File from F5 Downloads site.
File name is BIGIP-12.1.2.0.0.249.iso. Make shre verify file's MD5 value



2.2 Go to F5 device's System /Software Management page


2.3 Import the new download IOS file BIGIP-12.1.2.0.0.249.iso 


2.4 Install new imported file into HD1.1 partition.



2.5 Active new software


Make sure you install configuration from right Source Volume. Else you will lose all configuration in your new software.



2.6 verify




Reference:




F5 Big IP 2000s Appliance Configuration Step by Step Guide - 2. Configure BIG-IP Objects and HA

In Previous Step by step guide - 1. Initial configuration, we complete following steps:
  • Configure Management Interface
  • License the BIG-IP
  • Complete the Setup Wizard
There are a couple of related posts in this blog:

This post will show how to configure BIG-IP LTM to support an application.


Typical F5 BIG-IP Deployment Topology


F5 Lab Topology

1. High Availability Configuration following HA Wizard:







 






 
 

After built the trust with your peer and the configuraiton sync-ed between members, both nodes looks like this:



2. Vlan Configuration


3. Self IP Configuration










Saturday, December 31, 2016

工作中最常用Excel函数公式大全

工作中最常用Excel函数公式大全

一、数字处理

1、取绝对值
  =ABS(数字)
  2、取整
  =INT(数字)
  3、四舍五入
  =ROUND(数字,小数位数)

二、判断公式

1、把公式产生的错误值显示为空
  公式:C2
  =IFERROR(A2/B2,"")
  说明:如果是错误值则显示为空,否则正常显示。



2、IF多条件判断返回值
  公式:C2
  =IF(AND(A2<500,B2="未到期"),"补款","")
  说明:两个条件同时成立用AND,任一个成立用OR函数.




三、统计公式

1、统计两个表格重复的内容
  公式:B2
  =COUNTIF(Sheet15!A:A,A2)
  说明:如果返回值大于0说明在另一个表中存在,0则不存在。



2、统计不重复的总人数
  公式:C2
  =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))
  说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。




四、求和公式

1、隔列求和
  公式:H3
  =SUMIF($A$2:$G$2,H$2,A3:G3)
  或
  =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)
  说明:如果标题行没有规则用第2个公式



2、单条件求和
  公式:F2
  =SUMIF(A:A,E2,C:C)
  说明:SUMIF函数的基本用法



3、单条件模糊求和
  公式:详见下图
  说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。



4、多条件模糊求和
  公式:C11
  =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)
  说明:在sumifs中可以使用通配符*



5、多表相同位置求和
  公式:b2
  =SUM(Sheet1:Sheet19!B2)
  说明:在表中间删除或添加表后,公式结果会自动更新。



6、按日期和产品求和
  公式:F2
  =SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)
  说明:SUMPRODUCT可以完成多条件求和




五、查找与引用公式

1、单条件查找公式
  公式1:C11
  =VLOOKUP(B11,B3:F7,4,FALSE)
  说明:查找是VLOOKUP最擅长的,基本用法



 2、双向查找公式
  公式:
  =INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))
  说明:利用MATCH函数查找位置,用INDEX函数取值



3、查找最后一条符合条件的记录。
  公式:详见下图
  说明:0/(条件)可以把不符合条件的变成错误值,而lookup可以忽略错误值



4、多条件查找
  公式:详见下图
  说明:公式原理同上一个公式



5、指定区域最后一个非空值查找
  公式;详见下图
  说明:略



6、按数字区域间取对应的值
  公式:详见下图
  公式说明:VLOOKUP和LOOKUP函数都可以按区间取值,一定要注意,销售量列的数字一定要升序排列。




六、字符串处理公式

1、多单元格字符串合并
  公式:c2
  =PHONETIC(A2:A7)
  说明:Phonetic函数只能对字符型内容合并,数字不可以。



2、截取除后3位之外的部分
  公式:
  =LEFT(D1,LEN(D1)-3)
  说明:LEN计算出总长度,LEFT从左边截总长度-3个



3、截取-前的部分
  公式:B2
  =Left(A1,FIND("-",A1)-1)
  说明:用FIND函数查找位置,用LEFT截取。



4、截取字符串中任一段的公式
  公式:B1
  =TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",20)),20,20))
  说明:公式是利用强插N个空字符的方式进行截取



5、字符串查找
  公式:B2
  =IF(COUNT(FIND("河南",A2))=0,"否","是")
  说明: FIND查找成功,返回字符的位置,否则返回错误值,而COUNT可以统计出数字的个数,这里可以用来判断查找是否成功。



6、字符串查找一对多
  公式:B2
  =IF(COUNT(FIND({"辽宁","黑龙江","吉林"},A2))=0,"其他","东北")
  说明:设置FIND第一个参数为常量数组,用COUNT函数统计FIND查找结果




七、日期计算公式

1、两日期相隔的年、月、天数计算
  A1是开始日期(2011-12-1),B1是结束日期(2013-6-10)。计算:
  相隔多少天?=datedif(A1,B1,"d") 结果:557
  相隔多少月? =datedif(A1,B1,"m") 结果:18
  相隔多少年? =datedif(A1,B1,"Y") 结果:1
  不考虑年相隔多少月?=datedif(A1,B1,"Ym") 结果:6
  不考虑年相隔多少天?=datedif(A1,B1,"YD") 结果:192
  不考虑年月相隔多少天?=datedif(A1,B1,"MD") 结果:9
  datedif函数第3个参数说明:
  "Y" 时间段中的整年数。
  "M" 时间段中的整月数。
  "D" 时间段中的天数。
  "MD" 天数的差。忽略日期中的月和年。
  "YM" 月数的差。忽略日期中的日和年。
  "YD" 天数的差。忽略日期中的年。
  2、扣除周末天数的工作日天数
  公式:C2
  =NETWORKDAYS.INTL(IF(B2
  说明:返回两个日期之间的所有工作日数,使用参数指示哪些天是周末,以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日


 

Monday, December 5, 2016

Install Wordpress on Raspberry Pi


Raspberry Pi is mostly ready for use after the basic configuration completed in my previous posts:


The next cool thing to do is to install wordpress on it. This post is just used to record all steps I have followed online instruction installed an instance of a WordPress site to run on Raspberry Pi using the Apache Web Server. There are a lots of more things you may like to know regarding Apache, PHP, Mysql. It is best to search other online resources or take a look those listing at the Reference section to find out more.




1. Check system update

sudo apt-get update && sudo apt-get upgrade

Choose Y if there is any update.

2. Install Apache2

sudo apt-get install apache2 -y


hostname -I
Get the ip address your Raspberry Pi

Open http://<IP> to confirm apache is working. It show the /var/www/index.html in your browser.

3. Install php5

sudo apt-get install php5 libapache2-mod-php5 -y


create a test index.php file
cd /var/www
sudo echo "<?php phpinfo(); ?>" | sudo tee index.php

verify php has been installed properly by using curl command.
pi@raspberrypi /var/www $ curl http://127.0.0.1/index.php
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"><head>
<style type="text/css">
body {background-color: #ffffff; color: #000000;}
body, td, th, h1, h2 {font-family: sans-serif;}
pre {margin: 0px; font-family: monospace;}
a:link {color: #000099; text-decoration: none; background-color: #ffffff;}
a:hover {text-decoration: underline;}
table {border-collapse: collapse;}
.center {text-align: center;}
.center table { margin-left: auto; margin-right: auto; text-align: left;}
.center th { text-align: center !important; }
td, th { border: 1px solid #000000; font-size: 75%; vertical-align: baseline;}
h1 {font-size: 150%;}
h2 {font-size: 125%;}
.p {text-align: left;}
.e {background-color: #ccccff; font-weight: bold; color: #000000;}
.h {background-color: #9999cc; font-weight: bold; color: #000000;}
.v {background-color: #cccccc; color: #000000;}
.vr {background-color: #cccccc; text-align: right; color: #000000;}
img {float: right; border: 0px;}
hr {width: 600px; background-color: #cccccc; border: 0px; height: 1px; color: #000000;}
</style>
<title>phpinfo()</title><meta name="ROBOTS" content="NOINDEX,NOFOLLOW,NOARCHIVE" /></head>
<body><div class="center">
<table border="0" cellpadding="3" width="600">
<tr class="h"><td>
<a href="http://www.php.net/"><img border="0" src="/index.php?=PHPE9568F34-D428-11d2-A769-00AA001ACF42" alt="PHP Logo" /></a><h1 class="p">PHP Version 5.4.45-0+deb7u2</h1>
</td></tr>
......



4. Install mysql database server.

sudo apt-get install mysql-server
During installation of mysql, you will be asked to provide root password.

5. Download wordpress and install it under /var/www/wordpress folder

cd /var/www
sudo chown pi: .
sudo rm *
wget http://wordpress.org/latest.tar.gz


tar xzf latest.tar.gz
//mv wordpress/* .
rm -rf wordpress latest.tar.gz



6. Create wordpress database from mysql command line

mysql -uroot -ppassword
mysql> create database wordpress;
Query OK, 1 row affected (0.00 sec)


7. Open URL "http://<IP>/wordpress" in the browser to start configure Wordpress


During installation wizard, fill out the basic site information as follows:
Database Name:      wordpress
User Name: root
Password: <YOUR PASSWORD>
Database Host: localhost
Table Prefix: wp_





During configuration Wordpress site, there is a problem to write wp-config.php file. To resolve this issue, you will have to manually create it with the contents showing above in the browser.

cd /var/www/wordpress
nano wp-config.php

Paste the copied text from browser into this file, and save and exit with Ctrl + X, then Y for yes and Enter.

Hit Run the install button to continue the installation. The last step is to enter your wordpress site information.




After installation done, the browser will jump to http://<ip>/wordpress/wp-admin. You will need to provide wordpress site username and password to log into Dashboard to manage your new created site.


There is one tip for a problem which is when you are trying to install new plug-in in your Wordpress site, it will prompt a page to ask you ftp username, password and site address. It is because of wordpress folder's permission problem.

The easiest and fastest fix is :
cd /var/www
sudo chown -hR http:http wordpress

You can replace http user with pi user.

8. Test it from Internet
Once you have your dynamic DNS client installed and configured properly based on my previous post:
Remote Access Raspberry Pi from Anywhere and Dynamic DNS Update with Dynu.com

You will need to configure your WordPress to use your registered Dynamic DNS name as following screenshot:

Now your Raspberry Pi WordPress site http://51sec.dynu.com/wordpress can be accessed from Internet:


9. Redirect Homepage to Specific URL


pi@raspberrypi /var/www $ sudo vi index.html

<html>
<head>
<META HTTP-EQUIV="Refresh" Content="3; URL=http://51sec.dynu.com/wordpress">
</head>
<body>
This page will forward to http://51sec.dynu.com/wordpress in three seconds.
<p>
Please update your links.
</body>
</html>





Reference: