MySQLStudy之--MySQL关闭自动commit(autocommit)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQLStudy之--MySQL关闭自动commit(autocommit),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含7391字,纯文字阅读大概需要11分钟。
内容图文
![MySQLStudy之--MySQL关闭自动commit(autocommit)](/upload/InfoBanner/zyjiaocheng/566/afd441e0c74a4778b5ae031cac9438ac.jpg)
MySQL Study之--MySQL关闭自动commit(autocommit) 对于mysql来讲,在事务处理时,默认是在动提交的(autocommit),以下方法可以自动关闭autocommit; 案例分析: 1、在mysql登录环境下修改 [root@mysql2 soft]# mysql -u root -p Enter password: Welcome
MySQL Study之--MySQL关闭自动commit(autocommit)
对于mysql来讲,在事务处理时,默认是在动提交的(autocommit),以下方法可以自动关闭autocommit;
案例分析:
1、在mysql登录环境下修改
[root@mysql2 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) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be 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 | ;;默认autocommit是开启的
+---------------+-------+
1 row in set (0.03 sec)
在当前session关闭autocommit:
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)
在global级别关闭autocommit:
mysql> set @@global.autocommit=0;
Query OK, 0 rows affected (0.01 sec)
创建普通用户:
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)
普通用户登录:
[root@mysql2 ~]# 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) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be 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)
创建测试表:
mysql> create table t1(id int,name varchar(10));
Query OK, 0 rows affected (0.15 sec)
mysql> insert into t1 values (10,'tom');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 10 | tom
|
+------+------+
1 row in set (0.00 sec)
事务回滚:
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from t1;
Empty set (0.00 sec)
2、在mysql service重启后
mysql server 重启后:
[root@mysql2 ~]# service mysql stop
Shutting down MySQL (Percona Server)....[ OK ]
[root@mysql2 ~]# service mysql start
Starting MySQL (Percona Server).....[ OK ]
[root@mysql2 ~]# 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) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be 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仍然是开启状态
+-------------------------------------------+-------+
6 rows in set (0.01 sec)
编辑/etc/my.cnf文件:
[root@mysql2 ~]# 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' ;;用户登录时,关闭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
用户登录查看:
[root@mysql2 ~]# 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) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be 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用户不受影响(为安全起见)
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) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be 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 | ;;普通用户,autocommit已被关闭
+-------------------------------------------+-------+
内容总结
以上是互联网集市为您收集整理的MySQLStudy之--MySQL关闭自动commit(autocommit)全部内容,希望文章能够帮你解决MySQLStudy之--MySQL关闭自动commit(autocommit)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。