Mysql学习总结_20190307

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。