MySQL

Enable query log

SET global general_log_file='/tmp/mysql-query.log';
SET global log_output = 'file';
SET global general_log = on;

Dump

• --no-data - schema only

/usr/bin/mysqldump database_name --lock-tables=FALSE --column-statistics=0 --no-data --dump-date=false

MySQL 8.0 docker

docker pull mysql:8.0

• 跑 mysql docker

docker run --name mysql8 \
    --restart unless-stopped \
    -e MYSQL_ROOT_PASSWORD=changeit \
    -v mysql8-data:/var/lib/mysql \
    -p 3306:3306 \
    -d \
    mysql:8.0

• MySQL 8 docker 跑起來後大約要等十幾秒鐘後才能連線

mysql -h 127.0.0.1 -P 3306 -u root -p

docker phpmyadmin

docker pull phpmyadmin:latest

docker run --name phpmyadmin -d \
    --restart unless-stopped \
    --link mysql57:db \
    -e PMA_ARBITRARY=1 \
    -p 9980:80 \
    -v /Users/wmh/.config/phpmyadmin/config.user.inc.php:/etc/phpmyadmin/config.user.inc.php \
    phpmyadmin

Timezone

Client 要明確表示自己的時區，Date/Time/Timestamp 才會正確顯示及處理

mysql client

SET time_zone='+08:00'

phpmyadmin

$cfg['Servers'][$i]['SessionTimeZone'] = '+08:00';

sqlx - golang

• go 比較特別，內部都是 UTC 時間，作為 Client 時，要表明自己為 UTC 時間

const dsn = "root@tcp(localhost:3306)/test?parseTime=true&time_zone=%27%2B00:00%27"

or

db.MustExec(`SET time_zone='+00:00'`)

5.7 root without password

use mysql;
update user set authentication_string=password(''), plugin='mysql_native_password' where user='root';
flush privileges;

JSON

SET tags=JSON_ARRAY_APPEND(tags, '$', 'Tag4')

SET tags=JSON_SET(tags, "$[0]", "Tag5")

select JSON_SEARCH(tags, 'all', 'Tag3') from t1;

select id, tags->"$[0]" from t1;

Geometry

load ip-country to mysql

SHOW VARIABLES LIKE "secure_file_priv";

$ sudo cp ipcountry.csv /var/lib/mysql-files/

LOAD DATA INFILE '/var/lib/mysql-files/ipcountry.csv'
INTO TABLE ipcountry
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
/* IGNORE 1 ROWS */
;