西维蜀黍

【MySQL】Insert and Update

Situation

Often you have the situation that you need to check if an table entry exists, before you can make an update. If it does not exist, you have to do an insert first.

The simple straightforward approach is this:

(The example is for an entry in the WordPress wp_postmeta table)
SELECT meta_id FROM wp_postmeta
WHERE post_id = ?id AND meta_key = page_title

If ResultCount == 0

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
VALUES (?id, page_title, ?page_title)

Else

UPDATE wp_postmeta SET meta_value = ?page_title
WHERE post_id = ?id AND meta_key = page_title
  ...


【Linux】命令 - df

Usage

# Display all filesystems and their disk usage:
$ df

# Display all filesystems and their disk usage in human readable form:
$ df -h

# Display the filesystem and its disk usage containing the given file or directory:
$ df path/to/file_or_directory

# Display statistics on the number of free inodes:
$ df -i

# Display filesystems but exclude the specified types:
$ df -x squashfs -x tmpfs
  ...


【Linux】命令 - stat

Usage

# Show file properties such as size, permissions, creation and access dates among others:
$ stat file

# Same as above but verbose (more similar to linux's stat):
$ stat -x file

# Show only octal file permissions:
$ stat -f %Mp%Lp file

# Show owner and group of the file:
$ stat -f "%Su %Sg" file

# Show the size of the file in bytes:
$ stat -f "%z %N" file
  ...


【Linux】命令 - du

du Command

du command, short for disk usage, is used to estimate file space usage.

The du command can be used to track the files and directories which are consuming excessive amount of space on hard disk drive.

  ...


【Linux】File Systems

Demo

# Create a 10G big file
$ fallocate -l 10G 10g_file.img

$ mkdir sw_test

$ time cp 10g_file.img sw_test/
cp 10g_file.img sw_test/  0.08s user 8.18s system 97% cpu 8.459 total

$ du -h 10g_file.img
11G	10g_file.img

$ du -h sw_test
11G	sw_test

$ stat 10g_file.img
  File: 10g_file.img
  Size: 10737418240	Blocks: 20971528   IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 524359      Links: 1
Access: (0664/-rw-rw-r--)  Uid: ( 1000/      sw)   Gid: ( 1000/      sw)
Access: 2021-10-20 00:05:29.925335381 +0800
Modify: 2021-10-20 00:04:17.067027796 +0800
Change: 2021-10-20 00:04:17.067027796 +0800
 Birth: -
$ stat sw_test/10g_file.img
  File: sw_test/10g_file.img
  Size: 10737418240	Blocks: 20971528   IO Block: 4096   regular file
Device: fd00h/64768d	Inode: 2228230     Links: 1
Access: (0664/-rw-rw-r--)  Uid: ( 1000/      sw)   Gid: ( 1000/      sw)
Access: 2021-10-20 00:05:29.925335381 +0800
Modify: 2021-10-20 00:05:58.946511217 +0800
Change: 2021-10-20 00:05:58.946511217 +0800
  ...


【Linux】磁盘空间不足 - 扩容

Context

使用 Docker 拉取 MySQL 镜像时发现磁盘空间不够:no space left on device

root@ubuntu:~# docker pull mysql:5.7.29
5.7.29: Pulling from library/mysql
68ced04f60ab: Pull complete 
f9748e016a5c: Pull complete 
da54b038fed1: Pull complete 
6895ec5eb2c0: Pull complete 
111ba0647b87: Pull complete 
c1dce60f2f1a: Pull complete 
702ec598d0af: Pull complete 
63cca87a5d4d: Pull complete 
ec05b7b1c5c7: Extracting [==================================================>]  112.2MB/112.2MB
834b1d9f49b0: Download complete 
8ded6a30c87c: Download complete 
failed to register layer: Error processing tar file(exit status 1): write /usr/sbin/mysqld: no space left on device
  ...


【MySQL】MySQL in Docker

Context

容器的定义:容器是为了解决“在切换运行环境时,如何保证软件能够正常运行”这一问题。

目前,容器和 Docker 依旧是技术领域最热门的词语,无状态的服务容器化已经是大势所趋,同时也带来了一个热点问题被大家所争论不以:数据库 MySQL 是否需要容器化

认真分析大家的各种观点,发现赞同者仅仅是从容器优势的角度来阐述 MySQL 需要容器化,几乎没有什么业务场景进行验证自己的观点;反过来再看反对者,他们从性能、数据安全等多个因素进行阐述 MySQL不需要容器化,也举证了一些不适合的业务场景。之前推送过一篇文章:

下面,我们就聊一下 Docker 不适合跑 MySQL 的 N 个原因!

  ...


【Engineering】服务降级(Downgrade)

Background

When the issue is coming from our service (like a bug in our code), usually we need to fix the issue ourself quickly. When, the issue is coming from external party, usually we can’t fix the issue directly, we need other team to fix the issue for us. In other hand, we have some downgrades that we can do to reduce the impact of the issue. For example, if there is an issue with voucher recommendation service, we can downgrade the API call to voucher recommendation service. By doing this, we won’t get the error from voucher recommendation service. Initially, when the voucher recommendation service returns us an error, we will return an error to the buyer, but when we downgrade the service, we will skip the voucher recommendation. As a result, the buyer might not get the best voucher, but it is better than they can’t checkout at all.

  ...


【Store】Strong Consistency Store

  ...


【MySQL】Replication - GTID

Background

In MySQL 5.5, resuming a broken replication setup required you to determine the last binary log file and position, which are distinct on nodes if binary logging is enabled. If the MySQL master fails, replication breaks and the slave will need to switch to another master. You will need to promote the most updated slave node to be a master, and manually determine a new binary log file and position of the last transaction executed by the slave. Another option is to dump the data from the new master node, restore it on slave and start replication with the new master node. These options are of course doable, but not very practical in production.

How GTID Solves the Problem

GTID (Global Transaction Identifier) provides a better transactions mapping across nodes. In MySQL 5.5. or before, Replication works in such a way that all nodes will generate different binlog files. Binlog events are the same and in the same order, but binlog file offsets may vary. With GTID, slaves can see a unique transaction coming in from several masters and this can easily be mapped into the slave execution list if it needs to restart or resume replication.

Every transaction has a unique identifier which identifies it in the same way on every server. It’s not important anymore in which binary log position a transaction was recorded, all you need to know is the GTID: ‘966073f3-b6a4-11e4-af2c-080027880ca6:4’. GTID is built from two parts - the unique identifier of a server where a transaction was first executed, and a sequence number. In the above example, we can see that the transaction was executed by the server with server_uuid of ‘966073f3-b6a4-11e4-af2c-080027880ca6’ and it’s 4th transaction executed there. This information is enough to perform complex topology changes - MySQL knows which transactions have been executed and therefore it knows which transactions need to be executed next. Forget about binary logs, it’s now all in the GTID.

All necessary information for synchronizing with the master can be obtained directly from the replication stream. When you are using GTIDs for replication, you do not need to include MASTER_LOG_FILE or MASTER_LOG_POS options in the CHANGE MASTER TO statement; instead, it is necessary only to enable the MASTER_AUTO_POSITION option.

GTIDs (global transaction identifiers) - Transaction-based Replication

GTID (global transaction identifier) 即全局事务 ID,一个事务对应一个 GTID,保证了在每个在主库上提交的事务在集群中有一个唯一的 ID。

When using GTIDs, each transaction can be identified and tracked as it is committed on the originating server and applied by any replicas; this means that it is not necessary when using GTIDs to refer to log files or positions within those files when starting a new replica or failing over to a new source, which greatly simplifies these tasks. Because GTID-based replication is completely transaction-based, it is simple to determine whether sources and replicas are consistent; as long as all transactions committed on a source are also committed on a replica, consistency between the two is guaranteed.

  ...