You are here

Home » 記事 » サイト運用 technical note

サイト運用 technical note

Google Sheets

  • extract title from MahjongSoul_EN Droraichimon tweets text.
=REGEXEXTRACT(REGEXREPLACE(X2,".*Four-Frame Manga.*",""),"[\S ]+")
  • rgb to hex

ex. "rgb(245, 209, 175)" -> "#F5D1AF"

=CONCATENATE("#",ArrayFormula(DEC2HEX(REGEXEXTRACT($B2, "(\d+)[,\s]*(\d+)[,\s]*(\d+)"),2)))
  • generate short titles

ex. チョコの立直棒->チョコ, 和了演出：桜吹雪->桜吹雪

=regexreplace($B69,"[""“”]|^([Tt]he\s+|Riichi(\s*Bet)?|Winning|(Animated\s*|動態|动态|라이브\s*)?(Tablecloth|桌布|작탁)|(Tile Call( Indicator)?|鳴牌指示|후로 연출)|(Tile Back|마작패)|(Portrait Frame|[头頭]像框|アイコンフレーム|프로필 테두리)|(Shrine Yard|神社の庭|신사 정원)|立直(棒)*|(和牌|和了演出|화료 연출)|(牌背|立直演出|리치 연출))\s*[:·・：－-]\s*|の?(立直棒|の手|(うごく)?麻?雀卓|麻雀牌|の(アイコン)?フレーム|Paw|和牌|立直|桌[面布]|牌背|\s*[-]?\s*[0-9]{4}|\(.*\))$|[「」『』]|[（(].*[）)]|\s*(version|버전)","")

Twitter API v2

$ export BEARER_TOKEN='xxx'
  • tweets by ids -> CSV
$ curl -sS "https://api.twitter.com/2/tweets?ids=1173627603871100928,1174579047218630656,1176768299134324738&tweet.fields=text" -H "Authorization: Bearer $BEARER_TOKEN" | jq -r '.data[] | [.id, .text] | @csv'
"1173627603871100928","Mahjong Soul Four-Frame Manga – App Test

For the coming mobile version, Chiori would like to help, but something seems strange…?

Artist info: @flet06

APP Pre-registration: https://t.co/9g2dMIcSrQ

#MahjongSoul #Yostar https://t.co/x5HHCEoK1z"
"1174579047218630656","Four-Frame Manga DoraIchihimon - Episode 1

No one can read Ichihime on mahjong table.

Artist info: flet06(@flet06)

Four-frame manga DoraIchihimon will be updated on every Wednesday. The next episode is coming on 9/25 (PDT)!

#MahjongSoul #Yostar #DoraIchihimon https://t.co/Pir0kaKTHu"
"1176768299134324738","Four-Frame Manga DoraIchihimon - Episode 2

Don’t break Mahjong rules or you will…

Artist info: flet06(@flet06)

Four-frame manga DoraIchihimon will be updated on every Wednesday. The next episode is coming on 10/2 (PDT)!

#MahjongSoul #Yostar #DoraIchihimon https://t.co/Ttni4VWdFv"
  • account id
$ curl -s https://api.twitter.com/2/users/by/username/MahjongSoul_JP -H "Authorization: Bearer $BEARER_TOKEN" | jq . -c
{"data":{"id":"1108269664751087617","name":"【公式】雀魂-じゃんたま-","username":"MahjongSoul_JP"}} 
$ curl -s https://api.twitter.com/2/users/by/username/MahjongSoul_EN -H "Authorization: Bearer $BEARER_TOKEN" | jq . -c
{"data":{"id":"1108001348438523904","name":"Mahjong Soul Official","username":"MahjongSoul_EN"}}
  • tweets by account id
$ curl -sS "https://api.twitter.com/2/users/1108269664751087617/tweets?tweet.fields=created_at&expansions=author_id,attachments.media_keys&media.fields=preview_image_url,url&max_results=5" -H "Authorization: Bearer $BEARER_TOKEN" | jq -c
{"data":[{"id":"1575760836198510593","attachments":{"media_keys":["3_1575760567372955649"]},"text":"【復刻着せ替え記念イラスト】\n\n「涼宮 杏樹」「小鳥遊 雛田」\nの記念イラストを公開します！\n\n「杏樹ちゃん！今度はあそこに行ってみよ～！」\n「杏樹ではなくKR-976です。…まあいいでしょう、今日は特別に許可します。」\n\n画:村上ゆいち(@ebitenm)様\n\n#雀魂 #じゃんたま https://t.co/5EcisNgcOq","created_at":"2022-09-30T08:13:54.000Z","edit_history_tweet_ids":["1575760836198510593"],"author_id":"1108269664751087617"},{"id":"1575471587712675842","text":"【お知らせ】\nApp Storeで発生していた決済システムの障害が解消され、商品の購入が正常に行えることを確認いたしました。\n雀士の皆様にはご迷惑をおかけしましたこと、お詫び申し上げます。\n\n※ゲームの再読み込みまたは再起動をお願いします。\n#雀魂 #じゃんたま https://t.co/KNn582ejDi","created_at":"2022-09-29T13:04:32.000Z","edit_history_tweet_ids":["1575471587712675842"],"author_id":"1108269664751087617"},{"id":"1575410577643425793","text":"【連携方法2/2】\n正常に連携が完了した場合⑥「メールアドレス連携しました」と表示され、\n⑦更新後、該当ページに連携をしたメールアドレスが表示されます。\n\n連携したメールアドレスは、ログイン時に「Yostarログイン」でご利用いただけます。\n\n#雀魂 #じゃんたま","created_at":"2022-09-29T09:02:06.000Z","edit_history_tweet_ids":["1575410577643425793"],"author_id":"1108269664751087617"},{"id":"1575410539911557120","text":"【連携方法1/2】\n①「設定」②「その他」下部ページより、③「接続されない」をクリック。\nメールアドレスを入力後④「認証コード送信」をクリック、入力したメールアドレスに認証コードが送信されます。\n認証コードを入力後⑤「連携」クリックすることで連携が完了します。\n\n#雀魂 #じゃんたま","created_at":"2022-09-29T09:01:57.000Z","edit_history_tweet_ids":["1575410539911557120"],"author_id":"1108269664751087617"},{"id":"1575410063455444993","attachments":{"media_keys":["3_1575397819489214464","3_1575397831929524224","3_1575397839731011585","3_1575397848585076738"]},"text":"【アカウント連携】\nメールアドレス以外の方法でログインしたアカウントでも、メールアドレスと連携する事ができます。\n\niOS課金機能一時停止中でも、web版より課金を行うことができますので、Yostarアカウントの連携をお勧め致します。\n\n#雀魂 #じゃんたま https://t.co/d8GFGIxZmz","created_at":"2022-09-29T09:00:03.000Z","edit_history_tweet_ids":["1575410063455444993"],"author_id":"1108269664751087617"}],"includes":{"media":[{"media_key":"3_1575760567372955649","type":"photo","url":"https://pbs.twimg.com/media/Fd454yVVQAElNrG.jpg"},{"media_key":"3_1575397819489214464","type":"photo","url":"https://pbs.twimg.com/media/Fdzv-FKUYAAUuUi.jpg"},{"media_key":"3_1575397831929524224","type":"photo","url":"https://pbs.twimg.com/media/Fdzv-zgUcAAWoqc.png"},{"media_key":"3_1575397839731011585","type":"photo","url":"https://pbs.twimg.com/media/Fdzv_QkVsAEcIzT.jpg"},{"media_key":"3_1575397848585076738","type":"photo","url":"https://pbs.twimg.com/media/Fdzv_xjUAAI7fum.jpg"}],"users":[{"id":"1108269664751087617","name":"【公式】雀魂-じゃんたま-","username":"MahjongSoul_JP"}]},"meta":{"next_token":"7140dibdnow9c7btw423hxegvdmlpxweq5hqrl1np92cz","result_count":5,"newest_id":"1575760836198510593","oldest_id":"1575410063455444993"}}

MariaDB

  • CACHE HIT RATE
MariaDB [db_mahjongsoul.club]> SELECT (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'QCACHE_HITS')/(SELECT SUM(VARIABLE_VALUE)
 FROM INFORMATION_SCHEMA.GLOBAL_STATUS
 WHERE VARIABLE_NAME IN ('QCACHE_HITS','QCACHE_INSERTS','QCACHE_NOT_CACHED'))*100 AS CACHE_HIT_RATE;
  • tables by size
MariaDB [(none)]> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES WHERE table_schema = "db_mahjongsoul.club" ORDER BY (data_length + index_length) ASC;
  • users
MariaDB [db_mahjongsoul.club]> SELECT user, host, db FROM mysql.db;
MariaDB [db_mahjongsoul.club]> ALTER USER 'mahjongsoul.club'@'localhost' IDENTIFIED BY 'PASSWORD';
  • process
MariaDB [db_mahjongsoul.club]> show processlist;
MariaDB [db_mahjongsoul.club]> KILL QUERY 3259;

Server

log
$ sudo multitail -f /var/log/{{fail2ban,mysql/error,nginx/error,redis/redis-server,php*-fpm,drupal,monit}.log,syslog}
$ sudo watch -n 0.1 "dmesg | tail -n $((LINES-6))"

resources
$ htop
$ ps aux --sort=-%mem | head
$ uptime
$ vmstat -s

service
$ service --status-all
$ sudo monit status
$ sudo systemctl status nginx mariadb redis php*-fpm.service ssh
$ sudo mytop

network
$ sudo netstat -plnt
$ netstat -a
$ sudo iftop

files
$ sudo lsof -p 123,456
$ sudo lsof -i:80
$ sudo iotop
$ ncdu
$ sudo hdparm -Tt /dev/sda
$ iostat -mhx 2

login
$ last
$ sudo lastb
$ lastlog

server
$ uname -a
$ lsb_release -a
$ sudo lspci > lspci.log　# devices
$ sudo lsmod > lsmod.log # modules
$ ifconfig > ifconfig.log # network setting

redis

  • cache
$ redis-cli KEYS "mahjongsoul.club*" | xargs redis-cli DEL

Drupal

  • cache
$ echo "SHOW TABLES LIKE 'cache%'" | eval $(drush sql-connect) | tail -n +2 | xargs -L1 -I% echo "TRUNCATE TABLE %;" | eval $(drush sql-connect) -v             
$ sudo drush cc all
  • update
$ sudo sh -c "export DRUSH_PHP='/usr/bin/php7.4';drush pm-updatestatus 2>/dev/null && drush -y pm-update && drush -y updatedb && drush cron -d && drush cc all && chown -R www-data:www-data . && find . -type f -exec chmod 444 '{}' \; && find . -type d -exec chmod 555 '{}' \; && chmod a+w sites/default/files && chmod 444 sites/default/settings.php && find sites/default/files -type d -print0 | xargs -0 chmod 755"
  • dump
$ sudo sh -c "export DRUSH_PHP='/usr/bin/php7.4';drush archive-dump --tar-options='--exclude=.git --exclude=sites/default/files/styles' --destination=/var/www/html/db_${PWD##*/}.`date +%Y%m%d_%H%M%S`.tar.gz"
  • restore
$ DBPASSWORD=$(cat /dev/urandom | tr -dc 'a-zA-Z0-9!#$%^&*()_+-=[]{}<>?' | fold -w 10 | head -n 1)
$ sudo drush archive-restore /var/www/html/db_mahjongsoul.club.20191216_024803.tar.gz --destination=/var/www/html/mahjongsoul.club --db-url=mysql://mahjongsoul.club:${DBPASSWORD}@localhost/db_mahjongsoul.club --db-su=root
  • 133 reads