海胆阶段's Blog

Posted on Edited on In

This blog is about how to perform PITR: 8.0 Reference.

What is PITR

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:

  • Full Backup (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.
  • Binary Log (Incremental Changes): After your full backup, any subsequent changes to the database are recorded in the binary log.

The PITR Process

  • You restore your database from the most recent full backup.
  • You then use the binary log to roll forward the database by replaying all the changes that occurred after that full backup, up to the desired point in time.
  • The 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.

Goal

Performing a partial table PITR on the same instance to recover the accidentally dropped table and retain the updates on other tables.

Vagrant

The playground is managed by Vagrant, please launch it before move on to the next section.

Data Seed

On server, let’s create an animals DB and table animals and food:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE DATABASE IF NOT EXISTS animals;

CREATE TABLE animals.animals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    species VARCHAR(50),
    age INT
);

INSERT INTO animals.animals (name, species, age) VALUES
('Bella', 'Dog', 3),
('Milo', 'Cat', 2);


CREATE TABLE animals.food (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    price INT
);

INSERT INTO animals.food (name, price) VALUES
('Meat', 100);

SELECT * FROM animals.animals;
SELECT * FROM animals.food;

Full Instance Backup

Create a new folder for backup hosting:

1
2
3
# Create a folder to host dump
sudo mkdir /etc/mysqlsh-dump
sudo chown vagrant:vagrant /etc/mysqlsh-dump

At this point we take a full instance backup using mysqlsh:

1
2
3
4
5
6
7
8
9
10
mysqlsh \
--user='root' \
--password='easyone' \
--socket=/var/lib/mysql/mysql.sock \
--log-file=./dump_mysqlsh.log \
-- util dump-instance /etc/mysqlsh-dump \
--compression='zstd' \
--consistent=true \
--threads=2 \
--dry-run=false

The output is like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
WARNING: Using a password on the command line interface can be insecure.
Acquiring global read lock
Global read lock acquired

Initializing - done
1 out of 5 schemas will be dumped and within them 2 tables, 0 views.
1 out of 4 users will be dumped.

Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL

NOTE: Table statistics not available for `animals`.`food`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `animals`.`food`;' first.

Running data dump using 2 threads.

NOTE: Progress information uses estimated values and may not be accurate.

Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
150% (3 rows / ~2 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 2
Uncompressed data size: 38 bytes
Compressed data size: 56 bytes
Compression ratio: 0.7
Rows written: 3
Bytes written: 56 bytes
Average uncompressed throughput: 38.00 B/s
Average compressed throughput: 56.00 B/s

The dump file functions in terms of table animals.animals:

1
2
3
animals@animals@@0.tsv.zst      <- compressed table data (actual rows)
animals@animals.sql             <- schema (CREATE TABLE, etc.)
animals@animals.json            <- metadata

You can view the plain row data with zstd decoding:

1
2
3
4
5
6
zstd -d < animals@animals@@0.tsv.zst
1	Bella	Dog	3
2	Milo	Cat	2

zstd -d < animals@food@@0.tsv.zst
1	Meat	100

Drop Table

Now drop the table animals, then we do insert on table food after table animals is dropped:

1
2
3
4
5
6
7
8
INSERT INTO animals.animals (name, species, age) VALUES
('Maria', 'Fish', 12);

DROP TABLE animals.animals;

INSERT INTO animals.food (name, price) VALUES
('SUGAR', 18),
('APPLE', 19);

Partial Table PITR

Now, we want to bring table animals back and keep the changes on table food.

First, back up the bin log files:

1
2
3
4
5
6
# You can use SHOW VARIABLES LIKE 'log_bin%'; to check binlog path
# Create a folder to host bin log backups
sudo mkdir /etc/binlog-backup
sudo chown vagrant:vagrant /etc/binlog-backup

sudo cp -R /var/lib/mysql /etc/binlog-backup

Replay Full Backup

The current master status:

1
2
3
4
5
6
7
8
9
mysql> show master status\G

*************************** 1. row ***************************
             File: binlog.000002
         Position: 2374
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Run dump load with mysqlsh for animals table only:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# You must specify includeTables, otherwise you will have conflicts errors
# because animals.food table still exists.
# See filter options:
# https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html#mysql-shell-utilities-load-dump-opt-filtering

# -e: to use fine controlled load, we need --py and this flag, is it different
# from the format we use for dump above.
mysqlsh \
--user='root' \
--password='easyone' \
--socket=/var/lib/mysql/mysql.sock \
--log-file=./load_mysqlsh.log \
--py \
-e "util.load_dump('/etc/mysqlsh-dump', {
  'schema': 'animals',
  'includeTables': ['animals.animals'],
  'threads': 2,
  'deferTableIndexes': 'all',
  'DryRun': 'False'
})"

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
2
3
4
5
6
7
8
9
mysql> SELECT * FROM animals.animals;

+----+-------+---------+------+
| id | name  | species | age  |
+----+-------+---------+------+
|  1 | Bella | Dog     |    3 |
|  2 | Milo  | Cat     |    2 |
+----+-------+---------+------+
2 rows in set (0.00 sec)

Now check the master status, the Position is updated from 2374 to 3469:

1
2
3
4
5
6
7
8
9
mysql> show master status\G

*************************** 1. row ***************************
             File: binlog.000002
         Position: 3469
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Inspect Binlog

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
2
3
4
5
6
7
{
    ...
    "binlogFile": "binlog.000002",
    "binlogPosition": 1544,
    "begin": "2025-07-05 20:41:29"
    ...
}

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
2
3
4
5
sudo mysqlbinlog \
--base64-output=DECODE-ROWS \
--verbose \
/etc/binlog-backup/mysql/binlog.000002 |
grep -i 'INSERT INTO `animals`.`animals`' -B 2 -A 5
1
2
3
4
5
6
7
# 250705 20:41:42 server id 1  end_log_pos 1817 CRC32 0xb27353c1 	Write_rows: table id 125 flags: STMT_END_F
### INSERT INTO `animals`.`animals`
### SET
###   @1=3
###   @2='Maria'
###   @3='Fish'
###   @4=12
1
2
3
4
5
sudo mysqlbinlog \
--base64-output=DECODE-ROWS \
--verbose \
/etc/binlog-backup/mysql/binlog.000002 |
grep -i 'DROP TABLE' -B 2
1
2
3
# 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
SET TIMESTAMP=1751748106/*!*/;
DROP TABLE `animals`.`animals` /* generated by server */

Replay Binlog

Now we can replay the missing insert for table animals.animals:

1
2
3
4
5
6
7
8
9
# --start-datetime 2025-07-05 20:41:42, inclusive for insert SQL
# --stop-datetime 2025-07-05 20:41:46, exclusive, right before drop
# --start-position=1544 from @.json file
sudo mysqlbinlog \
--start-datetime="2025-07-05 20:41:42" \
--stop-datetime="2025-07-05 20:41:46" \
--start-position=1544 \
--skip-gtids \
/etc/binlog-backup/mysql/binlog.000002 | mysql -uroot -p

Verification

Now, checking and we can see the missing insert is back:

1
2
3
4
5
6
7
8
9
10
mysql> SELECT * FROM animals.animals;

+----+-------+---------+------+
| id | name  | species | age  |
+----+-------+---------+------+
|  1 | Bella | Dog     |    3 |
|  2 | Milo  | Cat     |    2 |
|  3 | Maria | Fish    |   12 |
+----+-------+---------+------+
3 rows in set (0.00 sec)

Posted on Edited on In

Updated 2025-07-03

R, S, Maintainability

R

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

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

M

O, S, E

Data Models and Query Languages

relational, document, graph schema: enforced on write, handled on read

SQL: declarative lang vs imperative lang

Storage and Retrieval

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

Encoding and Evolution

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

Distributed Data

replication and partitioning: go hand in hand usually

Replication

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:

  • statement based (error-prone)
  • WAL (physical or logical)
  • row-based (logical)
  • triggered-based (flexible)

Posted on Edited on In

This blog is about how to perform replication for one MySQL primary and one MySQL replica instance, 8.0 Reference

  • Replication is asynchronous by default, it could also be synchronous and semisynchronous.
    • synchronous replication is a characteristic of NDB Cluster.
    • semisynchronous replication is supported in addition to the built-in asynchronous replication.
      • With semisynchronous replication, a commit performed on the source blocks before returning to the session that performed the transaction until at least one replica acknowledges that it has received and logged the events for the transaction.
  • Replicas do not need to be connected permanently(replication can be paused) to receive updates from a source.
  • Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Goal

Here we focus on binary log file position based replication with MySQL version 8.0.42, Reference and MySQL Shell.

Vagrant

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.

Initial Data Seed

On primary, let’s create an animals DB and tables:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE DATABASE IF NOT EXISTS animals;

CREATE TABLE animals.animals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    species VARCHAR(50),
    age INT
);

INSERT INTO animals.animals (name, species, age) VALUES
('Bella', 'Dog', 3),
('Milo', 'Cat', 2);

SELECT * FROM animals.animals;

Create replication user

Reference

Go to the primary and run below commands to create a dedicated replication user repl with password ReplP2025!! for all hosts:

1
2
3
4
5
6
7
8
9
10
11
12
-- Go to the primary and run:
CREATE USER 'repl'@'%' IDENTIFIED BY 'ReplP2025!!';

-- Check the replication user
SELECT User, Host FROM mysql.user WHERE User='repl';

+------+------+
| User | Host |
+------+------+
| repl | %    |
+------+------+
1 row in set (0.00 sec)

At this point, you are able to connect to primary from replica using user repl:

1
2
# 172.20.21.30 is primary IP
mysql -h 172.20.21.30 -urepl -p

Grant privileges

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- for replication user to connect to the primary (master) and read the binary
-- log.
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
-- for allows a user to run SHOW MASTER STATUS, SHOW SLAVE STATUS, root user
-- on replica don't need this.
GRANT REPLICATION CLIENT ON *.* TO 'repl'@'%'; 

-- https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
-- The required set of privileges, you can exclude the unwanted ones.
GRANT RELOAD, SELECT, EVENT, SHOW VIEW, TRIGGER ON *.* TO 'repl'@'%';

-- Check the repl user privileges.
SHOW GRANTS FOR 'repl'@'%';

+-------------------------------------------------------------------------------------------------------------+
| Grants for repl@%                                                                                           |
+-------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO `repl`@`%` |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Data Dump

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
2
3
# Create a folder to host dump
sudo mkdir /etc/mysqlsh-dump
sudo chown vagrant:vagrant /etc/mysqlsh-dump

Run mysqlsh as below in replica:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
# The dump log file for debugging.
# --log-file=./dump_mysqlsh.log

# Doing a instance dump, you can choose schema or table dump if needed.
# Please note that the system DBs will be skipped.
# -- util dump-instance /etc/mysqlsh-dump

# When true is set, the utility sets a global read lock using the FLUSH TABLES
# WITH READ LOCK statement (if the user ID used to run the utility has the RELOAD
# privilege)
# Default it is true.
# --consistent=true

# I don't need to dump the events and triggers, so disable them here
# --events=false
# --triggers=false

mysqlsh \
--host='172.20.21.30' \
--port=3306 \
--user='repl' \
--password='ReplP2025!!' \
--log-file=./dump_mysqlsh.log \
-- util dump-instance /etc/mysqlsh-dump \
--consistent=true \
--events=false \
--triggers=false \
--threads=2 \
--dry-run=false

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
WARNING: Using a password on the command line interface can be insecure.
NOTE: Backup lock is not available to the account 'repl'@'%' and DDL changes will not be blocked. The dump may fail with an error if schema changes are made while dumping.

Acquiring global read lock
Global read lock acquired

Initializing - done
1 out of 5 schemas will be dumped and within them 1 table, 0 views.
2 out of 5 users will be dumped.

Gathering information - done
All transactions have been started
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 2 threads.

NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done

Starting data dump
100% (2 rows / ~2 rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 27 bytes
Compressed data size: 36 bytes
Compression ratio: 0.8
Rows written: 2
Bytes written: 36 bytes
Average uncompressed throughput: 27.00 B/s
Average compressed throughput: 36.00 B/s

Data Load

Use mysqlsh load utilities.

Go to the replica and run below commands:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# I am using Unix socket instead of TCP/IP to connect local MySQL. Since I don't
# configure auth_socket plugin, so password is needed.
# --socket=/var/lib/mysql/mysql.sock

# Ignore the major version cap.
# --ignoreVersion=true

mysqlsh \
--user=root \
--password='easyone' \
--socket=/var/lib/mysql/mysql.sock \
--log-file=./load_mysqlsh.log \
-- util load_dump /etc/mysqlsh-dump \
--threads=2 \
--ignoreVersion=true \
--waitDumpTimeout=300 \
--deferTableIndexes=all \
--dry-run=false

The output is like:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WARNING: Using a password on the command line interface can be insecure.
Loading DDL and Data from '/etc/mysqlsh-dump' using 2 threads.

Opening dump...
Target is MySQL 8.0.42. Dump was produced from MySQL 8.0.42

Scanning metadata - done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL - done
Executing view DDL - done
Starting data load
Executing common postamble SQL
100% (27 bytes / 27 bytes), 0.00 B/s, 1 / 1 tables done
Recreating indexes - done
1 chunks (2 rows, 27 bytes) for 1 tables in 1 schemas were loaded in 0 sec (avg throughput 27.00 B/s)
0 warnings were reported during the load.

If the load is good, you should find the animals.animals table presents:

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM animals.animals;

+----+-------+---------+------+
| id | name  | species | age  |
+----+-------+---------+------+
|  1 | Bella | Dog     |    3 |
|  2 | Milo  | Cat     |    2 |
+----+-------+---------+------+
2 rows in set (0.00 sec)

Obtain binlog coordinates

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
{
    "dumper": "mysqlsh Ver 8.0.42 for Linux on aarch64 - for MySQL 8.0.42 (MySQL Community Server (GPL))",
    "version": "2.0.1",
    "origin": "dumpInstance",
    "options": {
        "consistent": true,
        "dryRun": false,
        "events": false,
        "threads": 2,
        "triggers": false
    },
    "schemas": [
        "animals"
    ],
    "basenames": {
        "animals": "animals"
    },
    "users": [
        "'repl'@'%'",
        "'root'@'localhost'"
    ],
    "defaultCharacterSet": "utf8mb4",
    "tzUtc": true,
    "bytesPerChunk": 64000000,
    "user": "repl",
    "hostname": "mysql-replica1",
    "server": "mysql-primary",
    "serverVersion": "8.0.42",
    "binlogFile": "binlog.000002",
    "binlogPosition": 2201,
    "gtidExecuted": "",
    "gtidExecutedInconsistent": false,
    "consistent": true,
    "partialRevokes": false,
    "compatibilityOptions": [],
    "capabilities": [],
    "begin": "2025-07-04 00:07:37"
}

Start Replication

We need to kick off the replication within the expiration of the bin log in the primary after the data dump and load:

1
2
mysql> show replica status\G
Empty set (0.01 sec)

Run replication setup in replica:

1
2
3
4
5
6
7
8
9
10
-- You can set SSL if it is enabled, the fill options please see
-- https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html
CHANGE REPLICATION SOURCE TO
  SOURCE_HOST = '172.20.21.30',
  SOURCE_PORT = 3306,
  SOURCE_USER = 'repl',
  SOURCE_HEARTBEAT_PERIOD = 5,
  SOURCE_PASSWORD = 'ReplP2025!!',
  SOURCE_LOG_FILE = 'binlog.000002',
  SOURCE_LOG_POS = 2201;

Check replication status, the Replica_IO_Running, Replica_SQL_Running are No since we haven’t started it yet:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
mysql> show replica status\G

*************************** 1. row ***************************
             Replica_IO_State:
                  Source_Host: 172.20.21.30
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 2201
               Relay_Log_File: mysql-replica1-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File: binlog.000002
           Replica_IO_Running: No
          Replica_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 2201
              Relay_Log_Space: 157
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 0
                  Source_UUID:
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State:
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.01 sec)

Now start the replication:

1
2
mysql> START REPLICA;
Query OK, 0 rows affected (0.05 sec)

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> show replica status\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 172.20.21.30
                  Source_User: repl
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000002
          Read_Source_Log_Pos: 2201
               Relay_Log_File: mysql-replica1-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Source_Log_File: binlog.000002
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 2201
              Relay_Log_Space: 542
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: a8f99222-5863-11f0-9d43-08002731abd8
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.01 sec)

Check replication

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
2
3
4
5
6
7
8
9
-- 1001 is the replica1 server ID
mysql> show replicas;

+-----------+------+------+-----------+--------------------------------------+
| Server_Id | Host | Port | Source_Id | Replica_UUID                         |
+-----------+------+------+-----------+--------------------------------------+
|      1001 |      | 3306 |         1 | 51eea654-5864-11f0-9394-08002731abd8 |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)

Show all currently running threads, includes the replication:

1
2
3
4
5
6
7
8
9
10
11
mysql> show processlist\G

*************************** 2. row ***************************
     Id: 46
   User: repl
   Host: 172.20.21.31:52140
     db: NULL
Command: Binlog Dump
   Time: 7738
  State: Source has sent all binlog to replica; waiting for more updates
   Info: NULL

Verification

Now, let’s insert new row on animals.animals table on primary:

1
2
INSERT INTO animals.animals (name, species, age) VALUES
('Maria', 'Fish', 23);

It will be replicated to replica.

Posted on Edited on In

Decks

Course Decks.

Words and Sentences

To help recall:

  • Doodle sth on whiteboard
  • filler words count
  • constructive feedback: extends to everyday
  • vampire
  • blunder
  • Toastmasters Clubs
  • listen the question until it finishes
  • my addon is xxx
  • with that being said (more formal way of however, but)
  • constructural speaking
  • body swing, body stillness
  • shoulder shrug
  • stress on face
  • nit pick
  • grammar does not matter
  • long pause
  • making movement for topic transition
  • put hands in buckle if no idea
  • huge transfomration

Communication:

Books on persuasion

Crucial Conversations books:

Data Visualization books:

Posted on Edited on In

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
2
3
4
# list VMs
vboxmanage list vms
# show running VMs
vboxmanage list runningvms

Virtual Network Configuration

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.

    • The VM cannot be accessed from outside world.
    • VM no access from host, for example, ping VM IP won’t work.
    • VM no access from other VMs in the same host.
    • By setting up 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.

    • The VM will be assigned IP in the same level as the host.
    • The VM is just working as another host in your network.
    • Docker, Docker compose use bridged network by default, e.g 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.

    • VM no access from host.

  • 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.

    • Connection is allowed b/w host and VMs, but VMs cannot access outside world.

Posted on Edited on In

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
2
# Byte: 2097152 => 2MB by default
getconf ARG_MAX

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
2
# MAX_ARG_STRLEN for single argument limit, unit: Byte: 131072 => 128KB
echo "$(( $(getconf PAGE_SIZE) * 32 ))"

How to Reproduce The Error

It is simple to trigger the limit error, for example:

1
2
3
4
5
6
7
8
9
# create a random file with 129KB size
dd if=/dev/urandom of=/tmp/test bs=1K count=129

# run it
psql -h 127.0.0.1 -U cloudsqladmin -c "$(cat /tmp/test)"

# get error:
bash: warning: command substitution: ignored null byte in input
bash: /bin/psql: Argument list too long

How to Bypass

The possible solution to bypass the limit is to use pipe or filename as the input for the binary, for example:

1
2
3
4
5
6
7
8
9
10
11
# Create a 500KB file as fake query (OS limit 128KB):
postgres@a-878062546719-vm-a9bdb3589547ab41:/$ dd if=/dev/urandom of=/tmp/test bs=1K count=500
500+0 records in
500+0 records out
512000 bytes (512 kB, 500 KiB) copied, 0.00356667 s, 144 MB/s

# filename
postgres@a-878062546719-vm-a9bdb3589547ab41:/$ psql -h 127.0.0.1 -U cloudsqladmin -f /tmp/test

# pipe
postgres@a-878062546719-vm-a9bdb3589547ab41:/$ cat /tmp/test | psql -h 127.0.0.1 -U cloudsqladmin
0%