TRY AND ERRΦR

気になったこと、勉強したこと、その他雑記など色々メモしていきます。。Sometimes these posts will be written in English.,

The hard bug in MacOS High Sierra

WTF\(^o^)/ナンテコッター

There's a hard bug in MacOS High Sierra if multiple users are accepted to login.
In above, anyone seems to be able to get a root privilege of the mac without any specific technical skills.

Anyway you'd better to see this page for a detail.
www.wired.com


The person who first discovered this bug had definitely to be freak out, I guess.

Modified behavior of jquery plugins [Counter-Up] to effect at only first viewed.

I think this is the most fantastic plugin for increasing the number text dynamically.

Counter-Up/jquery.counterup.js at master · bfintal/Counter-Up · GitHub

But it's been getting a little old at recent, so there's a problem that the option named "triggerOnce" doesn't work.
The detail of the problem is that the count up effect occurs every time when display is scrolled into the element even though I set a "triggerOnce" option. Anyway, I hope that the effect occurs at just only the first time if I set that option into a initializer, so I fixed the problem to put below code into the jquery.counterup.js.

// Start the count up
setTimeout($this.data('counterup-func'), $settings.delay);
$this.attr("data-counterup_finished", true);    // Add

And

var counterUpper = function() {

    // Add start
    if ($this.data("counterup_finished") == true) {
        return false;
    }
    // Add end

    var nums = [];
    var divisions = $settings.time / $settings.delay;
    var num = $this.text();

    ...


It's the end.

Make replication between external Mysql and RDS-Aurora.

My external Mysql, which means non-AWS-RDS, has a large capacity records in it, and have been replicating between master and slave which are both external Mysql.It's difficult and too annoying about its slowness to dump data and to import them to Aurora.Instead of mysqldump, I tried to use percona-xtrabackup which is a third-party tools to migrate Mysql Database from S3, but restoring to Aurora with percona-xtrabackup didn't work since mysql version wasn't supported.(Accoding to error message, restoring by S3 data is accepted ver5.6 of source Mysql database.)
So I've given up to using percona-xtrabackup, and I'll show you the snippets how to make replication between external Mysql and RDS-Aurora with mysqldump.


# Refer to this for details.
http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html


This is my environments.

CentOS Linux 7.2.1511
mysql 5.7.16
innobackupex 2.4.8

First of all, in source Mysql you need to add a replication user with Aurora.

mysql > CREATE USER 'repl_aurora'@'aurora_host' IDENTIFIED BY '<password>';
mysql > GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_aurora'@'aurora_host' IDENTIFIED BY '<password>';


And then you make backup as gz archived file. At the same time you need to set option "--master-data=2" to put out MASTER_LOG_FILE and MASTER_LOG_POS statements.

$ MYSQL_PWD="xxxxxxxxxxxx" mysqldump --opt --all-databases --events --default-character-set=binary --master-data=2 -u myuser | gzip > ./backup.sql.gz


After the backup is finished, you need to search MASTER_LOG_FILE and MASTER_LOG_POS statements like this.
With zgrep command, you can grep in gz archived file as it is.

$ zgrep -i "CHANGE MASTER TO" backup.sql.gz > grepped.txt

Import backup data to Aurora.

$ zcat ./backup.sql.gz | mysql -u root -p -h aurora_host da_name 


Set master database info to Aurora with prepared stored procedure.
Then 'mysql bin file' and 'position' are able to be fetched from grepped.txt.

mysql > CALL mysql.rds_set_external_master ('source mysql host', port, 'repl_aurora', 'aurora password', 'mysql bin file', position, 0); 


Start replication.

mysql > CALL mysql.rds_start_replication;


Watch replication status.

SHOW SLAVE STATUS \G;

What a awesome awk!!

awk!! You're so fantastic!!


I'd love to use "awk" which is a linux command easy to format some kind of text to whatever you want.


For instance as below, you're able to get the most recent loadaverage.

$ uptime
 00:08:33 up 134 days,  7:25,  2 users,  load average: 0.03, 0.03, 0.05

$ uptime | awk -F'[ ]+' '{print $11}' |sed -e 's/,//'
0.03


Besides, you're also able to do like this.

$ echo ansible_inventory.file
192.168.33.10 ansible_ssh_user=vagrant ansible_ssh_port=66666 ansible_ssh_pass=xxxxxxx

$ cat ansible_inventory.file | awk -F'[ =]' '{print "sshpass -p "$7" ssh -p "$5" "$3"@"$1}'
sshpass -p xxxxxxx ssh -p 66666 vagrant@192.168.33.10


Using awk makes me pretty comfortable!

My recent favorite Podcast

I'm going to introduce my recent favorite Podcast programs for Learning English.

Frequently, English is going to be required when we're in a middle of some programming.
When we're reading docs, watching movies that someone speeches about technology, or stuff like that, there is a case in we have to understand them in English. These below are my favorite programs to Learn English.

  • Bilingual News

Mami and Michael, the personality on this program, talk about some interesting topics.
This have been published every week.

バイリンガルニュース (Bilingual News)

バイリンガルニュース (Bilingual News)

  • Michael & Mami
  • 言語コース
  • ¥0

  • 台本なし英会話レッスン

Japanese Sota and American Nate talk about English grammar, vocabulary, and some episode that how to learn English.

https://ja-jp.facebook.com/daihonnashi/

台本なし英会話レッスン

台本なし英会話レッスン

  • 英語のそーた & Nate
  • Language Courses
  • USD 0

.htaccessのリダイレクトなどでつかう正規表現の%とか$とか

.htaccessで以下の様にサブドメを継承し、かつ最初のディレクトリをカットするということがしたくて色々やってハマったのでメモ。

http://xxx.from.com/yyy/zzz...

http://xxx.to.com/zzz...
にリダイレクトしたい。。。


結論、こんな感じになりました。

RewriteEngine on
RewriteCond %{REQUEST_URI} ^/yyy.*$
RewriteCond %{HTTP_HOST} ^(.*).from.com$
RewriteRule ^yyy/(.*)$ http://%1.to.com/$1 [R=301,L]


RewriteCondのキャプチャパターンをRewriteRuleで使う場合、%Nで指定する。
mod_rewrite - Apache HTTP Server Version 2.4

RewriteRule backreferences: These are backreferences of the form $N (0 <= N <= 9). $1 to $9 provide access to the grouped parts (in parentheses) of the pattern, from the RewriteRule which is subject to the current set of RewriteCond conditions. $0 provides access to the whole string matched by that pattern.
RewriteCond backreferences: These are backreferences of the form %N (0 <= N <= 9). %1 to %9 provide access to the grouped parts (again, in parentheses) of the pattern, from the last matched RewriteCond in the current set of conditions. %0 provides access to the whole string matched by that pattern.<<



なお、RewriteCondの順番を逆にすると%1に値が引き継がれないのですが、これは複数のRewriteCondの中で上から順に下に継承されるからだそう。

www.sitepoint.com


こちらのサイトが参考になります!
.htaccess で RewriteCond の後方参照 ← Neo Inspiration

GoogleAdwordsAPIで指定したキーワードの月間平均検索ボリュームと競合性を取得する。


Google Adwords APIでキーワードの検索ボリュームを取得する話。
前提として、MCCアカウントの取得やOAuthまわりの設定などを済ませておくこと。

キーワード候補を取得するRequestType::IDEASのサンプルはよく見かけるけど、任意のキーワードのボリュームだけ取得したいという場合に使うRequestType::STATSのサンプルはほぼ見つかりませんでした。
なので、今回はキーワード候補取得の公式サンプルをベースに、キーワードのボリュームと競合性を取得するサンプルをPHP書いてみました。

なお、キーワードを配列で複数指定することもできるんですが、返される順番がバラバラだったり、キーワードの大文字小文字などが加工される場合があったりと、
いろいろ面倒なので今回はシングルワードでやってみました。

RateExceededErrorが返された場合、その中にスリープのイターバルが指定されるので、getRetryAfterSeconds()で取得してその秒数分スリープします。
APIの利用数制限が設定されているので、超過しないようにリクエストの間隔を調整する必要があります。
apiに迷惑をかけないよう、ループで処理する際にはあらかじめスリープをかけておくべきでしょう。


  • クライアントライブラリ

https://developers.google.com/adwords/api/docs/clientlibraries?hl=ja
今回はPHPで試してみました。

<?php

require __DIR__ . '/./googleads-php-lib/vendor/autoload.php';
use Google\AdsApi\AdWords\AdWordsServices;
use Google\AdsApi\AdWords\AdWordsSession;
use Google\AdsApi\AdWords\AdWordsSessionBuilder;
use Google\AdsApi\AdWords\v201708\cm\Keyword;
use Google\AdsApi\AdWords\v201708\cm\Language;
use Google\AdsApi\AdWords\v201708\cm\NetworkSetting;
use Google\AdsApi\AdWords\v201708\cm\Paging;
use Google\AdsApi\AdWords\v201708\cm\ApiException;
use Google\AdsApi\AdWords\v201708\cm\RateExceededError;
use Google\AdsApi\AdWords\v201708\o\AttributeType;
use Google\AdsApi\AdWords\v201708\o\IdeaType;
use Google\AdsApi\AdWords\v201708\o\LanguageSearchParameter;
use Google\AdsApi\AdWords\v201708\o\NetworkSearchParameter;
use Google\AdsApi\AdWords\v201708\o\RelatedToQuerySearchParameter;
use Google\AdsApi\AdWords\v201708\o\RequestType;
use Google\AdsApi\AdWords\v201708\o\TargetingIdeaSelector;
use Google\AdsApi\AdWords\v201708\o\TargetingIdeaService;
use Google\AdsApi\Common\OAuth2TokenBuilder;
use Google\AdsApi\Common\Util\MapEntries;

class GoogleAdwords {

    const INI_FILE = "path/to/adsapi_php.ini";

    public static function main($keyword) {
        $oAuth2Credential = (new OAuth2TokenBuilder())
            ->fromFile(self::INI_FILE)
            ->build();

        $session = (new AdWordsSessionBuilder())
            ->fromFile(self::INI_FILE)
            ->withOAuth2Credential($oAuth2Credential)
            ->build();

        return self::getVolume(new AdWordsServices(), $session, $keyword);
    }

    public static function get(AdWordsServices $adWordsServices, AdWordsSession $session, $keyword) {
        $ret = [];
        $ret["original_keyword"] = $keyword;
        $ret["keyword"] = "";
        $ret["volume"] = 0;
        $ret["competition"] = 0;

        try {
            $targetingIdeaService = $adWordsServices->get($session, TargetingIdeaService::class);
            $selector = new TargetingIdeaSelector();

            // Set "STATS" to Request type.
            $selector->setRequestType(RequestType::STATS);
            $selector->setIdeaType(IdeaType::KEYWORD);
            // Set Attributes you want to get.
            $selector->setRequestedAttributeTypes([
                AttributeType::KEYWORD_TEXT,
                AttributeType::COMPETITION,
                AttributeType::TARGETED_MONTHLY_SEARCHES,
            ]);
            $searchParameters = [];
            $relatedToQuerySearchParameter = new RelatedToQuerySearchParameter();
            $relatedToQuerySearchParameter->setQueries([$keyword]);
            $searchParameters[] = $relatedToQuerySearchParameter;
            $languageParameter = new LanguageSearchParameter();
            // Japanese
            $jp = new Language();
            $jp->setId(1005);
            $languageParameter->setLanguages([$jp]);
            $searchParameters[] = $languageParameter;
            $networkSetting = new NetworkSetting();
            $networkSetting->setTargetGoogleSearch(true);
            $networkSetting->setTargetSearchNetwork(false);
            $networkSetting->setTargetContentNetwork(false);
            $networkSetting->setTargetPartnerSearchNetwork(false);
            $networkSearchParameter = new NetworkSearchParameter();
            $networkSearchParameter->setNetworkSetting($networkSetting);
            $searchParameters[] = $networkSearchParameter;
            $selector->setSearchParameters($searchParameters);
            $selector->setPaging(new Paging(0, 1));

            $page = $targetingIdeaService->get($selector);
            if ($page->getEntries() !== null && $page->getTotalNumEntries() > 0) {
                foreach ($page->getEntries() as $targetingIdea) {
                    $data = MapEntries::toAssociativeArray($targetingIdea->getData());
                    $ret["keyword"] = $data[AttributeType::KEYWORD_TEXT]->getValue();
                    // getValue()は月間平均検索ボリュームを月別に配列で返してくれる。今回はlatestを取得
                    $ret["volume"] =
                        ($data[AttributeType::TARGETED_MONTHLY_SEARCHES]->getValue() !== null) ? $data[AttributeType::TARGETED_MONTHLY_SEARCHES]->getValue()[0]->getCount() : 0;
                    $ret["competition"] = ($data[AttributeType::COMPETITION]->getValue() !== null) ? $data[AttributeType::COMPETITION]->getValue() : 0;
                    $ret["competition"] = round($ret["competition"],4);
                }
            }

        } catch (ApiException $ae) {
            foreach ($ae->getErrors() as $error) {
                if ($error instanceof RateExceededError) {
                    // You had better handle exeptions with based on this.
                    // $ret["retry_interval"] = $error->getRetryAfterSeconds() + 1;
                }
            }

        } catch (\Exception $e) {
            // Do something...

        } finally {
            return $ret;
        }
    }
}

以下の様に呼び出せばキーワードのボリュームと競合性が取得できます。

// ループで処理する場合はスリープを入れる
$ret = GoogleAdwords::main("Any keyword");


githubにもあります。
https://github.com/kentaro-a/GoogleAdwordsApiSample/blob/master/GoogleAdwords.php