mysql+配置文件+事务_MySQL 事务配置命令行操作和持久化

MySQL 事务配置命令行操作和持久化

参考

MySQL 官方参考手册 提供 5.5 5.6 5.7 8.0 版本的参考手册

https://dev.mysql.com/doc/refman/5.5/en/set-transaction.html

# MySQL 8.0 版本参考手册

4.2.4 Specifying Program Options # 事务持久化

5.1.1 Configuring the Server # MySQL 的配置

5.1.7 Server Command Options # mysqld 的选项信息，事务持久化。

15.7.2.1 Transaction Isolation Levels

13.3.7 SET TRANSACTION Syntax

Appendix D Indexes System Variable Index # 事务持久化

4个帮助相关的表

mysql.help_category

mysql.help_keyword

mysql.relation

mysql.help_topic 存放语法格式和示例

mysqld reads options from the [mysqld] and [server] groups. mysqld_safe reads options from

the [mysqld], [server], [mysqld_safe], and [safe_mysqld] groups. mysql.server reads

options from the [mysqld] and [mysql.server] groups.

There are several ways to specify options for MySQL programs:

? List the options on the command line following the program name. This is common for options that

apply to a specific invocation of the program.

? List the options in an option file that the program reads when it starts. This is common for options

that you want the program to use each time it runs.

? List the options in environment variables (see Section 4.2.11, “Setting Environment Variables”).

This method is useful for options that you want to apply each time the program runs. In practice,

option files are used more commonly for this purpose, but Section 5.8.3, “Running Multiple MySQL

Instances on Unix”, discusses one situation in which environment variables can be very helpful. It

describes a handy technique that uses such variables to specify the TCP/IP port number and Unix

socket file for the server and for client programs.

Options are processed in order, so if an option is specified multiple times, the last occurrence takes

precedence.

帮助信息，本质上是查询4个帮助表获取帮助信息

隔离级别设置

mysql> help isolation

Name: 'ISOLATION'

Description:

Syntax:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL

{

REPEATABLE READ

| READ COMMITTED

| READ UNCOMMITTED

| SERIALIZABLE

}

其他事务相关的帮助

mysql> help transactions

You asked for help about help category: "Transactions"

For more information, type 'help ', where is one of the following

topics:

CHANGE MASTER TO

DEALLOCATE PREPARE

EXECUTE STATEMENT

ISOLATION

LOCK

PREPARE

PURGE BINARY LOGS

RESET MASTER

RESET SLAVE

SAVEPOINT

SET GLOBAL SQL_SLAVE_SKIP_COUNTER

SET SQL_LOG_BIN

START SLAVE

START TRANSACTION

STOP SLAVE

XA

查看会话和全局变量

mysql> help show variables;

Name: 'SHOW VARIABLES'

Description:

Syntax:

SHOW [GLOBAL | SESSION] VARIABLES

[LIKE 'pattern' | WHERE expr]

set 命令

mysql> help set

Name: 'SET'

Description:

Syntax:

SET variable_assignment [, variable_assignment] ...

variable_assignment:

user_var_name = expr

| [GLOBAL | SESSION] system_var_name = expr

| [@@global. | @@session. | @@]system_var_name = expr

事务操作实战 5.5版本

mysql> select @@version;

+-----------+

| @@version |

+-----------+

| 5.5.40 |

+-----------+

自动提交查询和修改 autocommit 当前会话和全局

# 修改当前会话变量 autocommit ，可以使用 on/off 0/1 true/false

mysql> set autocommit=off;

Query OK, 0 rows affected (0.03 sec)

# 查看当前会话变量 autocommit

mysql> show variables like '%autoco%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.00 sec)

# 查看全局变量 autocommit

mysql> show global variables like '%autoco%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.00 sec)

# 使用 0/1

mysql> set autocommit=1;

Query OK, 0 rows affected (0.05 sec)

mysql> show variables like '%autoco%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set (0.00 sec)

事务隔离级别查询和修改 当前会话和全局

注意：必须加上 session/global 不然不起作用

# 设置当前会话的事务隔离级别。 注意：必须写 session/global ，不然不起作用。

mysql> set session TRANSACTION ISOLATION LEVEL READ COMMITTED;

Query OK, 0 rows affected (0.00 sec)

# 查询当前会话的事务隔离级别

mysql> show variables like '%iso%';

+---------------+----------------+

| Variable_name | Value |

+---------------+----------------+

| tx_isolation | READ-COMMITTED |

+---------------+----------------+

1 row in set (0.00 sec)

# 查询全局的事务隔离级别

mysql> show global variables like '%iso%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

1 row in set (0.00 sec)

事务配置持久化

将 MySQL 配置选项帮助信息保存到文件

C:\Users\jie>mysqld --verbose --help > d:\00\a.txt

transaction-isolation REPEATABLE-READ

autocommit TRUE

键 transaction-isolation 键可以用连字符或者下划线

取值 READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE

To set the global default isolation level at server startup, use the

--transaction-isolation=level option to mysqld on the command line or

in an option file.

Values of level for this option use dashes rather than spaces, so the permissible values are READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE.

For example, to set the default isolation level to REPEATABLE READ, use these lines in the [mysqld] section of an option file:

[mysqld]

transaction-isolation = REPEATABLE-READ

持久化实战

# D:\chengxu\MySQL\mysql-5.5\my.ini 文件中添加如下配置。

# D:\chengxu\MySQL\mysql-5.5\ 是我的 MySQL 5.5 安装目录。

# 事务隔离级别 键可以用连字符或下划线分隔。值是固定的，使用连字符相连而不是空格。

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, or SERIALIZABLE

transaction_isolation=READ-COMMITTED

# 配置自动提交 on/off 0/1 true/false

autocommit=on