西维蜀黍

【MySQL】允许远程访问

Run the MySQL Server Process Properly

Make sure that the server is running. If it is not, clients cannot connect to it. For example, if an attempt to connect to the server fails with a message such as one of those following, one cause might be that the server is not running:

$> mysql
ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
$> mysql
ERROR 2002: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (111)
  ...


【MySQL】Install MySQL in Docker

Install

$ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 3306:3306 -d mysql:tag

… where some-mysql is the name you want to assign to your container, my-secret-pw is the password to be set for the MySQL root user and tag is the tag specifying the MySQL version you want. See the list above for relevant tags.

# install the latest mysql with root's passord being 1234 and allowing remote access
$ docker run --name sw-mysql --restart=unless-stopped -e MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=1234 -p 3306:3306 -d mysql

# enter the container
$ docker exec -it sw-mysql sh
  • if you want to access your mysql server remotely, you need mapping the port 3306 of the container to port 3306 of the host machine.
  • If you wanna run MySQL in another port, use -p 3307:3306, meaning map the container-port 3306 on the prefered TCP port (of your server), in this case it is 3307
  • Set the environment variable MYSQL_ROOT_HOST with wildcards to allow root connections from other hosts
# access mysql from another host
$ mysql -u root -h 192.168.18.134 -p1234
  ...


【MySQL】Logs - Binary Logs

Binary Logs

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data.

Purpose

The binary log has two important purposes:

  • For replication, the binary log on a replication source server provides a record of the data changes to be sent to replicas. The source sends the information contained in its binary log to its replicas, which reproduce those transactions to make the same data changes that were made on the source. See Section 17.2, “Replication Implementation”.
  • Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. See Section 7.5, “Point-in-Time (Incremental) Recovery”.

The binary log is not used for statements such as SELECT or SHOW that do not modify data.

  ...


【Engineering】Single Source of Truth

Single source of truth

Definition 1

In information systems design and theory, single source of truth (SSOT) is the practice of structuring information models and associated data schema such that every data element is mastered (or edited) in only one place. Any possible linkages to this data element (possibly in other areas of the relational schema or even in distant federated databases) are by reference only. Because all other locations of the data just refer back to the primary “source of truth” location, updates to the data element in the primary location propagate to the entire system without the possibility of a duplicate value somewhere being forgotten.

Deployment of an SSOT architecture is becoming increasingly important in enterprise settings where incorrectly linked duplicate or de-normalized data elements (a direct consequence of intentional or unintentional denormalization of any explicit data model) pose a risk for retrieval of outdated, and therefore incorrect, information. A common example would be the electronic health record, where it is imperative to accurately validate patient identity against a single referential repository, which serves as the SSOT. Duplicate representations of data within the enterprise would be implemented by the use of pointers rather than duplicate database tables, rows, or cells. This ensures that data updates to elements in the authoritative location are comprehensively distributed to all federated database constituencies in the larger overall enterprise architecture.

  ...


【Database】冷热存储数据分离

背景

在海量大数据场景下,一张表中的部分业务数据随着时间的推移仅作为归档数据或者访问频率很低,同时这部分历史数据体量非常大,比如订单数据或者监控数据,降低这部分数据的存储成本将会极大的节省企业的成本。如何以极简的运维配置成本就能为企业极大降低存储成本。

  ...


【Grafana】Advance

  ...


【Architectural Pattern】Publish/Subscribe Pattern

Definition

In software architecture, publish–subscribe pattern is a messaging pattern where senders of messages, called publishers, do not program the messages to be sent directly to specific receivers, called subscribers, but instead categorize published messages into classes without knowledge of which subscribers, if any, there may be. Similarly, subscribers express interest in one or more classes and only receive messages that are of interest, without knowledge of which publishers, if any, there are.

Publish–subscribe is a sibling of the message queue paradigm, and is typically one part of a larger message-oriented middleware system. Most messaging systems support both the pub/sub and message queue models in their API, e.g. Java Message Service (JMS).

This pattern provides greater network scalability and a more dynamic network topology, with a resulting decreased flexibility to modify the publisher and the structure of the published data.

  ...


【Distributed System】Message Oriented Middleware

Message Oriented Middleware (MOM)

Message Oriented Middleware is a concept that involves the passing of data between applications using a communication channel that carries self-contained units of information (messages).

Note that since the message broker is the most component in a Message Oriented Middleware, therefore, message broker sometimes is used with Message Oriented Middleware interchangeably.

  ...


【Database】事务(Transactions)

Basic Idea

A transaction is an executing program that forms a logical unit of database processing.

A transaction includes one or more database access operations—these can include insertion, deletion, modification (update), or retrieval operations.

The database operations that form a transaction can either be embedded within an application program or they can be specified interactively via a high-level query language such as SQL.

  ...


【MySQL】MySQL中有的锁(Lock)

MySQL 中的锁

  • 全局锁
  • 表级锁
    • 表锁 table locks
    • AUTO-INC 锁
    • 元数据锁 Metadata lock
    • 意向锁 Intention Locks
  • 行级锁
    • 记录锁 Record Lock:也就是仅仅把一条记录锁上
    • 间隙锁 Gap Lock:锁定一个范围,但是不包含记录本身
    • Next-Key Lock:Record Lock + Gap Lock 的组合,锁定一个范围,并且锁定记录本身
  ...