Blog Review List
AUTO GENERATION There are 8 blogs written or updated in last 60 days:
AUTO GENERATION There are 8 blogs written or updated in last 60 days:
HOW TO MOVE UP, WIN AT WORK, AND SUCCEED WITH ANY TYPE OF BOSS
Esta es una nota de mi aprendizaje de español.
This blog is about how to perform PITR: 8.0 Reference.
PITR(point-in-time-recovery) is a recovery technique that allows you to restore your database to an exact state at a specific moment in time (e.g. “right before I accidentally dropped that table at 2:30 PM yesterday”).
Enabling the binary log is a prerequisite for performing point-in-time recovery in MySQL. Here’s 2 major phases:
Baseline
): To perform PITR, you first need a full backup of your
database. This backup captures the state of your database at a specific moment.Incremental Changes
): After your full backup, any subsequent
changes to the database are recorded in the binary log.mysqlbinlog
utility is typically used to read the binary log files and
convert them into SQL statements (or other formats), which are then applied to
the restored database.Performing a partial table PITR on the same instance to recover the accidentally dropped table and retain the updates on other tables.
The playground is managed by Vagrant, please launch it before move on to the next section.
On server, let’s create an animals DB and table animals
and food
:
1 | CREATE DATABASE IF NOT EXISTS animals; |
Create a new folder for backup hosting:
1 | # Create a folder to host dump |
At this point we take a full instance backup using mysqlsh
:
1 | mysqlsh \ |
The output is like:
1 | WARNING: Using a password on the command line interface can be insecure. |
The dump file functions in terms of table animals.animals
:
1 | animals@animals@@0.tsv.zst <- compressed table data (actual rows) |
You can view the plain row data with zstd
decoding:
1 | zstd -d < animals@animals@@0.tsv.zst |
Now drop the table animals
, then we do insert on table food
after table
animals
is dropped:
1 | INSERT INTO animals.animals (name, species, age) VALUES |
Now, we want to bring table animals
back and keep the changes on table food
.
First, back up the bin log files:
1 | # You can use SHOW VARIABLES LIKE 'log_bin%'; to check binlog path |
The current master status:
1 | mysql> show master status\G |
Run dump load with mysqlsh
for animals
table only:
1 | # You must specify includeTables, otherwise you will have conflicts errors |
Now we can see animals.animals
table is back at the time of full instance
backup, but the last insert is missing because it is after the full instance
backup:
1 | mysql> SELECT * FROM animals.animals; |
Now check the master status, the Position
is updated from 2374
to 3469
:
1 | mysql> show master status\G |
We then need to check bin log to find the exact moment when the animals.animals
was dropped and we will replay the SQL before it to bring the missing insert row
back.
First go to find the binlog coordinates, it is in @.json
file (on show the
irrelevant lines):
1 | { |
Then we inspect the binlog.000002
and its successors(if they are) to find the
time window between the missing insert and drop of animals.animals
table.
Please note here is just a simple case, as we don’t have SQLs from other tables, otherwise you need some manual work to filter them out.
1 | sudo mysqlbinlog \ |
1 | # 250705 20:41:42 server id 1 end_log_pos 1817 CRC32 0xb27353c1 Write_rows: table id 125 flags: STMT_END_F |
1 | sudo mysqlbinlog \ |
1 | # 250705 20:41:46 server id 1 end_log_pos 2066 CRC32 0x2d60fed1 Query thread_id=8 exec_time=0 error_code=0 Xid = 101 |
Now we can replay the missing insert for table animals.animals
:
1 | # --start-datetime 2025-07-05 20:41:42, inclusive for insert SQL |
Now, checking and we can see the missing insert is back:
1 | mysql> SELECT * FROM animals.animals; |
Updated 2025-07-03
adversity (misfortune) critical bugs due to poor error handling (part)faults vs (whole system )failures faulits tolerant, resilient system deliberately inducing faults -> Chaos monkey
weak correlation of failure
S -> grows a particular way load parameters
throughput, response time latency vs response time
occasional outliers p50, p95, p99
skews the measurement coping with load
rethink on every order of magnitude load increases
O, S, E
relational, document, graph schema: enforced on write, handled on read
SQL: declarative lang vs imperative lang
log: append-only sequence of records index: affacts the performance of queries, updated on writes
SSTable: sorted string table LSM-tree: Log structured Merge-tree, faster for write B-tree: overwrite + WAL (write-ahead log for db crash), faster for read
write amplification SSD wear out performance cost, performance penalty throttle the rate of incoming writes
fuzzy query
in-memory database, Redis
Backward and forward compatibility
server side -> staged rollout client side -> may not install the update
Encoding vs Decoding: in-memory -> file
JSON, XML, CSV -> Binary encoding protocol buffer: tag index not field name, schema evolution
Data flow, through database service call: REST and RPC(location transparency, but don’t treat it a local call) message passing: asynchronous
replication and partitioning: go hand in hand usually
Data is small that single machine can hold entire dataset
leader and followers synchronous and asynchronous: semi-synchronous
MySQL: binlog coordinates PG: log sequence number caught up
node outages: follower: caught-up recovery leader: failover
split brain
replication types:
This blog is about how to perform replication for one MySQL primary and one MySQL replica instance, 8.0 Reference
Here we focus on binary log file position based replication with MySQL version 8.0.42, Reference and MySQL Shell.
The playground is managed by Vagrant, please launch it before move on to the next section.
Briefly, you will have a primary MySQL with bin log enabled and GTID disabled, a replica MySQL the same MySQL version with bin log off and unique server ID set.
On primary, let’s create an animals DB and tables:
1 | CREATE DATABASE IF NOT EXISTS animals; |
Go to the primary and run below commands to create a dedicated replication user
repl
with password ReplP2025!!
for all hosts:
1 | -- Go to the primary and run: |
At this point, you are able to connect to primary from replica using user repl
:
1 | # 172.20.21.30 is primary IP |
For replication user, we need below privileges to be granted to perform dump, load and replication actions.
Go to the primary and run below commands by root:
1 | -- for replication user to connect to the primary (master) and read the binary |
We use mysqlsh dump utilities instead of mysqldump.
MySQL Shell provides parallel dumping with multiple threads, file compression, and progress information display, etc.
Go to the replica and run below commands:
1 | # Create a folder to host dump |
Run mysqlsh as below in replica:
1 | # The dump log file for debugging. |
Please note that the --consistent=true
uses FTWRL, this lock is held just long
enough for all the dump threads to establish their own transactions. Once all
threads have started their consistent snapshot transactions, the global read
lock is released immediately. From that point on, each thread operates within
its own REPEATABLE READ transaction, leveraging InnoDB's MVCC
to read a consistent
view of the data as it existed when the global read lock was first acquired. This
allows concurrent writes to the database to continue without interruption
during the majority of the dump process.
The output is like:
1 | WARNING: Using a password on the command line interface can be insecure. |
Go to the replica and run below commands:
1 | # I am using Unix socket instead of TCP/IP to connect local MySQL. Since I don't |
The output is like:
1 | WARNING: Using a password on the command line interface can be insecure. |
If the load is good, you should find the animals.animals
table presents:
1 | mysql> SELECT * FROM animals.animals; |
Since we use consistent=true
in mysqsh dump, we can find binlog file and
position from the @.json
file in /etc/mysqlsh-dump
folder:
1 | { |
We need to kick off the replication within the expiration of the bin log in the primary after the data dump and load:
1 | mysql> show replica status\G |
Run replication setup in replica:
1 | -- You can set SSL if it is enabled, the fill options please see |
Check replication status, the Replica_IO_Running
, Replica_SQL_Running
are
No
since we haven’t started it yet:
1 | mysql> show replica status\G |
Now start the replication:
1 | mysql> START REPLICA; |
Check the replication status again, the Replica_IO_Running
, Replica_SQL_Running
should be YES
if no issue and Replica_SQL_Running_State
contains the current
state:
1 | mysql> show replica status\G |
On Primary, you can show what replicas actively connected to it, if you don’t start the replication on replica, you won’t see it here:
1 | -- 1001 is the replica1 server ID |
Show all currently running threads, includes the replication:
1 | mysql> show processlist\G |
Now, let’s insert new row on animals.animals
table on primary:
1 | INSERT INTO animals.animals (name, species, age) VALUES |
It will be replicated to replica.
To help recall:
Communication:
Books on persuasion
Crucial Conversations books:
Data Visualization books:
This is a side note made for <<Vagrant Quick Start>>
, for you to recall the
underlying virtual network setup for VM on your host.
Virtualbox is equipped with its own CLI, for example:
1 | # list VMs |
The virtualbox has these Networks types, There is Networks types video to go through.
You can configure below network types for your VM from virtualBox UI.
Not attached
. In this mode, Oracle VM VirtualBox reports to the guest that a
network card is present, but that there is no connection. This is as if no
Ethernet cable was plugged into the card, namely no internet.
Network Address Translation (NAT)
. If all you want is to browse the Web,
download files, and view email inside the guest, then this default mode should be
sufficient for you. The host is used as proxy for VMs.
port forwarding
under NAT, it enables VM be accessible from
host or outside world (if host is accessible from outside).NAT Network
. A NAT network is a type of internal network setup that
allows a group of VMs inter-accessible, you need to create the NAT network from
virtualBox first and use it for your VMs, the VMs inside the same NAT network are
inter-accessible.
Bridged networking
. This is for more advanced networking needs, such as
network simulations and running servers in a guest. When enabled, Oracle VirtualBox
connects to one of your installed network cards and exchanges network packets
directly, circumventing your host operating system’s network stack.
docker0
bridge.Internal networking
. This can be used to create a different kind of
software-based network which is visible to only selected VMs, but not to
applications running on the host or to the outside world.
Host-only networking
. This can be used to create a network containing the
host and a set of VMs, without the need for the host’s physical network interface.
Instead, a virtual network interface, similar to a loopback interface, is created
on the host, providing connectivity among virtual machines and the host.
This issue was exposed when running postgres /bin/psql
with a super long
input (>130KB), the error was:
1 | fork/exec /bin/psql: argument list too long |
In Linux, the arugment total combined size is usually managed by kernal limit, a system defined constant, it can vary by system:
1 | # Byte: 2097152 => 2MB by default |
While ARG_MAX
defines the total combined size of all arguments and environment
variables passed to a new process, MAX_ARG_STRLEN
defines the maximum length
of a single argument or environment variable string.
MAX_ARG_STRLEN
is typically defined in the kernel headers, specifically in
linux/binfmts.h
. For many Linux systems, it’s defined as (PAGE_SIZE * 32)
1 | # MAX_ARG_STRLEN for single argument limit, unit: Byte: 131072 => 128KB |
It is simple to trigger the limit error, for example:
1 | # create a random file with 129KB size |
The possible solution to bypass the limit is to use pipe or filename as the input for the binary, for example:
1 | # Create a 500KB file as fake query (OS limit 128KB): |