Muscle Memory Makeover
Docker just streamlined Docker Compose by integrating it as a plugin. Great news, but it means us old hats need to retrain our fingers. Here's a quick fix for your .bashrc
to keep things smooth:
alias docker-compose='docker compose'
MySQL in a Flash
As a programmer and Linux admin, I juggle multiple MySQL servers with different group suffixes. Typing --defaults-group-suffix
every time was a drag. This handy bash function saves the day:
m() {
mysql --defaults-group-suffix=$1
}
Now, connecting to a database is as easy as:
m specific-suffix
This keeps your workflow concise and saves you precious keystrokes.
Put them into you .bashrc
or .zshrc
now and let our life easier!
You want to add a unique index to a table, and unfortunately, there are already many duplicate rows in it. Manually find and delete these rows is time-wasting and error-prone. So why won't we just write one SQL statement and quickly resolve it?
First try, I wrote the following statement, and it won't work:
DELETE FROM PromotionSkus A
WHERE
A.SkuId IN (SELECT SkuId FROM PromotionSkus B GROUP BY B.SkuId HAVING COUNT(B.SkuId) > 1)
AND
A.Id NOT IN (SELECT MIN(Id) FROM PromotionSkus C GROUP BY C.SkuId HAVING COUNT(C.SkuId) > 1);
AND this one below works!
DELETE FROM PromotionSkus A
WHERE
A.Id NOT IN (SELECT Id FROM (SELECT MIN(Id) AS Id, COUNT(SkuId) AS Total FROM PromotionSkus GROUP BY SkuId HAVING Total > 1) AS B)
AND
A.SkuId IN (SELECT SkuId FROM (SELECT SkuId FROM PromotionSkus GROUP BY SkuId HAVING COUNT(SkuId) > 1) AS C);
The reason is well explained in this brilliant article.
Another mysql tip: using mysqldump export a table with one line one row.
mysqldump --databases YourDataBaseName --tables YourTableName --skip-extended-insert
Why do we need that? It is much easier to compare !
- put a .my.cnf file into your home directory, for example
$ cat ~/.my.cnf
[client]
user=yourDbUserName
password=yourDbPassword
host=yourDbHost
- write a bash script to update the database
#!/bin/bash
set -ex
variable=`TZ="Africa/Abidjan" date '+%Y-%m-%d %H:%M:%S'`
sql="Your SQL statement with ${variable}"
logDir=/data/Logs/`date '+%Y'`/`date '+%m'`
mkdir -p "${logDir}"
echo "${sql}" >> "${logDir}/`date '+%d'`.sql"
mysql -v -v -e "${sql}" >> "${logDir}/`date '+%d'`.result.txt"
- put a line by run crontab -e
the end.