mysql查询用户下的存储_MySQL查看当前用户、存储引擎、日志

mysql> show variableslike '%char%';

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

| Variable_name            | Value                      |

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

| character_set_client     | latin1                     |

| character_set_connection | latin1                     |

| character_set_database   | utf8                       |

| character_set_filesystem |binary                     |

| character_set_results    | latin1                     |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

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

8rows in set (0.00 sec)

#查看MySQL的当前用户

1

2

3

4

5

6

7mysql>SELECT USER();

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

|USER()         |

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

| root@localhost |

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

1 rowin set (0.00 sec)

#查看所有用户

1

2

3

4

5

6

7

8

9

10mysql>SELECT user,host,password FROM mysql.user;

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

|user    | host        |password                                  |

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

| root    | localhost   | *7AE39BE5035D5C32361400FF7DEDD757AA76896A |

| root    | 10.4.5.9    | *7AE39BE5035D5C32361400FF7DEDD757AA76896A |

| root    | 127.0.0.1   | *7AE39BE5035D5C32361400FF7DEDD757AA76896A |

| gechong | 10.4.14.168 | *11B9ACA21786F766739D0EB1483C5F64212B81AC |

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

4rows in set (0.00 sec)

#查看当前数据库版本，版本5.5一下默认的存储引擎并非是InnoDB，如果需要可以手动修改。

MariaDB [(none)]> select version();

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

| version()       |

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

| 10.1.13-MariaDB |

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

1 row in set (0.02 sec)

#查看数据库是否启用了log_bin

1

2

3

4

5

6

7

8

9

10mysql> show variableslike '%log_bin%';

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

| Variable_name                   | Value |

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

| log_bin                         |ON    |

| log_bin_trust_function_creators |OFF   |

| log_bin_trust_routine_creators  |OFF   |

| sql_log_bin                     |ON    |

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

4rows in set (0.00 sec)

#查看当前写入的日志

1

2

3

4

5

6

7mysql> show master status ;

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

| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| log-bin.000002 |      231 | test         |                  |

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

1 rowin set (0.00 sec)

根据/etc/my.cnf 找到日志的配置路径

1log-bin = /var/lib/mysql/log-bin

查看日志内容

1[root@10-4-14-168 ~]# mysqlbinlog /var/lib/mysql/log-bin.000002

#查看MySQL支持的字符集

1mysql> showcharacter set ;

#查看默认字符集

1

2

3

4

5

6

7

8

9

10

11

12

13

14mysql> show variableslike '%char%';

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

| Variable_name            | Value                      |

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

| character_set_client     | latin1                     |

| character_set_connection | latin1                     |

| character_set_database   | utf8                       |

| character_set_filesystem |binary                     |

| character_set_results    | latin1                     |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

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

8rows in set (0.00 sec)

#查看某一库的字符集

1

2

3

4

5

6

7mysql> showcreate database test;

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

|Database |Create Database                                               |

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

| test     |CREATE DATABASE `test`/*!40100 DEFAULT CHARACTER SET utf8 */ |

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

1 rowin set (0.00 sec)

查看某一表的字符集

1

2

3

4

5

6

7

8

9mysql> showcreate table test.t1 \G

*************************** 1. row ***************************

Table: t1

Create Table:CREATE TABLE `t1` (

`user`char(16)CHARACTER SET utf8COLLATE utf8_binNOT NULL DEFAULT '',

`host`char(60)CHARACTER SET utf8COLLATE utf8_binNOT NULL DEFAULT '',

`password`char(41)CHARACTER SET latin1COLLATE latin1_binNOT NULL DEFAULT ''

) ENGINE=MyISAMDEFAULT CHARSET=utf8

1 rowin set (0.00 sec)

#修改字符集

1mysql>alter database mydbcharacter set utf-8;

#可以直接在配置文件中做添加，然后重启服务

1

2

3

4

5[client]

character_set_server = utf8

[mysqld]

character_set_server = utf8