Admin

CLI

mysql

CREATE DATABASE db;
CREATE USER 'dbuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'dbuser'@'%';
-- GRANT ALL PRIVILEGES ON db.* TO 'dbuser'@'%';

"%" 是 MySQL 监听的 IP / hostname，可以使用 localhost (仅限本机用户访问)。

root & remote access

MySQL 8+

CREATE USER 'root'@'%' IDENTIFIED BY 'password'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

Another

UPDATE mysql.user SET host='%' WHERE user='root';

-- or
update user set host='%' where host='localhost'

MySQL < 8

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

Debug

select * from mysql.user

my.cnf

# explicitly
#bind-address=*

Backup & Restore

Backup:

mysqldump -h 127.0.0.1 -u user -pPassword db > db_dump.sql

Restore

mysql -h 127.0.0.1 -u user -pPassword db  < db_dump.sql

• 如果不提供 -h 参数，默认通过 unix socket domain 连接本机的 MySQL /var/run/mysqld/mysqld.sock。
• -h localhost 可能有问题。报错 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'。建议用 IP 地址连接。

Lock & Transactions

Transaction / autocommit

开始事务：

• START TRANSACTION
• SET autocommit=0

结束事务：
COMMIT / ROLLBACK

autocommit

autocommit（默认为 1）

所有 SQL 都是在“事务” (transaction)里运行的。如果 autocommit = 1 (MySQL 默认的每次连接后初始值），那么每个SQL语句都是一个单独的“事务”，默认语句执行后自动 commit (如果语句执行不出错)。

如果 autocommit = 0，那么当前连接里所有执行的 SQL 语句需要手工 "COMMIT" / "ROLLBACK"。并且 COMMIT / ROLLBACK 后会自动立即开启一个新的事务。

START TRANSACTION

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.

MySQL 服务级别表锁(table lock)

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

标准流程：关闭 autocommit -> Lock tables -> work -> commit -> UNLOCK Tables

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables.

UNLOCK TABLES implicitly commits any active transaction, but only if LOCK TABLES has been used to acquire table locks.

When you call LOCK TABLES, InnoDB internally takes its own table lock, and MySQL takes its own table lock. InnoDB releases its internal table lock at the next commit, but for MySQL to release its table lock, you have to call UNLOCK TABLES. You should not have autocommit = 1, because then InnoDB releases its internal table lock immediately after the call of LOCK TABLES, and deadlocks can very easily happen. InnoDB does not acquire the internal table lock at all if autocommit = 1, to help old applications avoid unnecessary deadlocks.

InnoDB Locking and Transaction Model

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-transaction-model.html

基本概念:

S (shared) 共享锁（读锁）
X (exclusive) 独占锁（写锁）

• 多个事务可以同时持有同一个（对象的） S 锁。
• X 锁是独占的，与 S 或其他 X 都不兼容。任何时刻最多只能有一个事务持有 X。
• 写入时，需要获得 X。
• 普通 read ("plain" select) 不需要获得锁，但“Transaction Isolation Levels” 隔离等级影响读取到的数据内容（根据不同的隔离性等级，可能获取到数据库旧版本数据、尚未commit的数据等）。（除了 "READ UNCOMMITTED"隔离等级以外，所有 plain select 都是 consistent read）
• Locking reads (select ... for share/update)时，需要获得 S或X。由于需要加锁，这时要么获取到最新的数据，要么 block（或超时失败）。

InnoDB Locking

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

InnoDB 内部使用锁：

表级锁：S，X。
意向锁：IS，IX。 (Intention Locks）
行级锁：Record / Gap / Next-key / Insert Intention / AUTO-INC Locks。

表级锁通过 LOCK / UNLOCK TABLE 显式获取/释放（事务结束时也会自动释放）；意向锁/行级锁是 InnoDB 在执行 DML SQL (select/update/delete/insert) 过程中自己管理的。

InnoDB（以及 PostgreSQL）的所有行级锁都是在 index 上的（如果表没有定义索引，InnoDB 会定义一个隐藏的 clustered index）。作为对比，Oracle 的行级锁在 record 本身上。

Before a transaction can acquire a shared / exclusive lock on a row in a table, it must first acquire an IS / IX lock or stronger on the table.

Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

就是说意向锁本身是“表”级别的，但其目的却是指示之后事务可能加的“行”级锁。

两个锁（类型为 S，X，IS，IX 之间任一种）之间兼容性：(指两个事务T1 T2是否可以 同时 对同一个对象加指定的锁)

• X 不兼容所有。
• S 不兼容 X 和 IX。
• IX 兼容 IX，IS。
• IS 兼容 IX，IS，S。

A lock is granted to a requesting transaction if it is compatible with existing locks, but not if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released.

事务申请某个锁时，如果申请的锁与（其他事务持有的）当前锁不兼容，会 block。

事务结束时，InnoDB 会同时释放该事务拥有的所有锁（表级锁和行级锁）

Transaction Isolation Levels

https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html

仅影响 "select" 语句的行为！隔离级别按从高到低：

• SERIALIZABLE
• REPEATABLE READ (默认)
• READ COMMITTED
• READ UNCOMMITTED (这种模式下 plain select 不是 consistent reads)

REPEATABLE READ

事务里所有 Consistent reads (plain select) 获取到的都是事务第1次"select"时间点的数据库 "snapshot"。

locking reads (select ... for share/update), update, delete:

• For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

• For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.5.1, “InnoDB Locking”. （如果查询条件没有用到任何索引，那么实际上会遍历并依次锁住整个表每行记录！）

Note

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match.
DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match.
UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values.
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

READ COMMITTED

与 "REPEATABLE READ" 的区别之处：

• 事务内每个 locking reads 独立，各自获取到当时数据库最新(freshest)版本(已commited的)

• locking reads (select ... for share/update), update, delete 里仅获取实际查询或操作的 rows 的 lock，不会获取 "gap index lock" / "next key lock"。所以允许其它事务 "insert" (与本事务里上述 SQL 用到的 gap lock)相关的 record；并且即使没有使用唯一性索引，不同事务也可以同时 update（只要它们 update 的行不冲突）。

  • For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition. This greatly reduces the probability of deadlocks, but they can still happen.

  • For UPDATE statements, if a row is already locked, InnoDB performs a “semi-consistent” read, returning the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again and this time InnoDB either locks it or waits for a lock on it.

READ UNCOMMITTED

SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent. This is also called a dirty read. Otherwise, this isolation level works like READ COMMITTED.

SERIALIZABLE

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)

Row lock

select ... for share // shared row lock
select ... for update // exclusive row lock