MySQL study--mysql off automatic commit (autocommit)

MySQL study--mysql off automatic commit (autocommit)

For MySQL, in the transaction process, the default is the dynamic commit (AUTOCOMMIT), the following methods can automatically close the autocommit;

Case Analysis:

1. Modify in MySQL login environment

[email protected] soft]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 4
Server version:5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
affiliates. Other names trademarks of their respective
owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in Set (0.02 sec)

mysql> Select version ();
+-------------+
| version () |
+-------------+
| 5.6.25-73.1 |
+-------------+
1 row in Set (0.00 sec)

mysql> Show variables like '%autocommit% ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| autocommit |                On | ;; default autocommit is turned on
+---------------+-------+
1 row in Set (0.03 sec)

Close Autocommit at the current session:
mysql> SET @ @session. autocommit=0;
Query OK, 0 rows Affected (0.00 sec)

mysql> Show variables like '%autocommit% ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in Set (0.00 sec)

to turn off autocommit at the global level:
Mysql> SET @ @global. autocommit=0;
Query OK, 0 rows affected (0.01 sec)

To create a normal user:
Mysql> create user Tom identified by ' Tom ';
Query OK, 0 rows Affected (0.00 sec)

mysql> Grant all on prod.* to ' Tom ' @ ' localhost ' identified by ' Tom ';
Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

Normal User login:
[Email protected] ~]# mysql-u tom-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 6
Server version:5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
affiliates. Other names trademarks of their respective
owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> use MySQL;
ERROR 1044 (42000): Access denied for user ' tom ' @ ' localhost ' to database ' MySQL '
mysql> use prod;
Database changed
mysql> show tables;
Empty Set (0.00 sec)

mysql> Show variables like '%commit% ';
+-------------------------------------------+-------+
| variable_name | Value |
+-------------------------------------------+-------+
| autocommit | OFF |
| binlog_order_commits | On |
| innodb_api_bk_commit_interval | 5 |
| innodb_commit_concurrency | 0 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_use_global_flush_log_at_trx_commit | On |
+-------------------------------------------+-------+
6 rows in Set (0.00 sec)

To create a test table:
mysql> CREATE TABLE t1 (ID int,name varchar);
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO T1 values (' Tom ');
Query OK, 1 row Affected (0.00 sec)

mysql> SELECT * from T1;
+------+------+
| id | name |
+------+------+
| 10 | Tom
 |
+------+------+
1 row in Set (0.00 sec)

Transaction rollback:
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * from T1;
Empty Set (0.00 sec)

2. After the MySQL service restarts
after MySQL server restarts:
[[Email protected] ~]# service MySQL stop
shutting down MySQL (Percona Server) .... [OK]
[[Email protected] ~]# service MySQL start
starting MySQL (Percona Server) ..... [OK]
[email protected] ~]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server version:5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
affiliates. Other names trademarks of their respective
owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Show variables like '%commit% ';
+-------------------------------------------+-------+
| variable_name | Value |
+-------------------------------------------+-------+
| autocommit | On | ;; autocommit is still open
+-------------------------------------------+-------+
6 rows in Set (0.01 sec)

edit/etc/my.cnf File:

[email protected] ~]# vi/etc/my.cnf
[Mysqld]
Datadir=/var/lib/mysql
Socket=/var/lib/mysql/mysql.sock
User=mysql
# Disabling Symbolic-links is recommended to prevent assorted security risks
Symbolic-links=0
init_connect= ' Set autocommit=0 '; When the user logs on, close autocommit
[Mysqld_safe]
Log-error=/var/log/mysqld.log
Pid-file=/var/run/mysqld/mysqld.pid
Explicit_defaults_for_timestamp=true
Innodb_buffer_pool_size = 128M
Join_buffer_size = 128M
Sort_buffer_size = 2M
Read_rnd_buffer_size = 2M

User Login View:
[email protected] ~]# mysql-u root-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server version:5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
affiliates. Other names trademarks of their respective
owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Show variables like '%commit% ';
+-------------------------------------------+-------+
| variable_name | Value |
+-------------------------------------------+-------+
| autocommit |                On | ;; Root user is unaffected (for security reasons)

mysql> system Mysql-u tom-p
Enter Password:
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server version:5.6.25-73.1 Percona Server (GPL), Release 73.1, Revision 07b797f
Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c), Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of the Oracle Corporation and/or its
affiliates. Other names trademarks of their respective
owners.
Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Show variables like '%commit% ';
+-------------------------------------------+-------+
| variable_name | Value |
+-------------------------------------------+-------+
| autocommit |            OFF | ;; Ordinary user, autocommit has been closed
+-------------------------------------------+-------+

This article is from the "Tianya blog," Please make sure to keep this source http://tiany.blog.51cto.com/513694/1683722

MySQL study--mysql off automatic commit (autocommit)