Mysql学习总结_20190307
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql学习总结_20190307，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5562字，纯文字阅读大概需要8分钟。
内容图文
mysql查看版本的方法：
[root@rhel6 ~]# mysql --version
mysql Ver 14.14 Distrib 5.7.19, for linux-glibc2.12 (x86_64) using EditLine wrapper
mysql> status
--------------
mysql Ver 14.14 Distrib 5.7.19, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 9
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.19-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 hour 35 min 45 sec
Threads: 5 Questions: 80 Slow queries: 0 Opens: 146 Flush tables: 1 Open tables: 139 Queries per second avg: 0.013
--------------
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.00 sec)
mysql查看server-id：
mysql> show variables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 10241 |
| server_id_bits | 32 |
+----------------+-------+
2 rows in set (0.01 sec)
一个mysql连接问题：
用Navicat连接Mysql的时候，提示我 1130 - Host is not allowed to connect to this MySql server;
解决方法：
1) 修改user表的host字段的值
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.01 sec)
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql commit的问题：
在解决mysql连接的问题的时候，我执行update user set host = '%' where user = 'root'的时候 执行完我执行了下commit，但是提示我 Query OK, 0 rows affected (0.00 sec)，于是对mysql的commit机制产生了疑问。
上网搜索了一下，原来mysql的commit由搜索引擎和一个参数共同决定：
查看mysql搜索引擎：
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
Transactions列表明引擎是否支持事务，不支持事务的引擎都是自动提交的。
查看当前使用的引擎：
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set (0.00 sec)
如果使用的是支持事务的引擎，查看参数确认是否开启自动提交：
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
一个mysql 登录权限的问题：
在解决mysql连接的问题的时候，我试了下执行update user set host = '2' where user = 'root' 来试试autocommit的问题，但是当我退出mysql想重新登的时候，无法登陆了。
[root@rhel6 ~]# mysql -uroot -pCqmyg14dss
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
我立刻意识到是我刚才update的问题。
解决方法：
修改my.cnf文件 在里面添加参数skip-grant-tables,然后重启Mysql服务，登陆后再use mysql，update user set host = '%' where user = 'root'，然后再flush privileges。
内容总结
以上是互联网集市为您收集整理的Mysql学习总结_20190307全部内容，希望文章能够帮你解决Mysql学习总结_20190307所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。