MySQL查看当前用户、存储引擎、日志
#查看MySQL的当前用户
|
1
2
3
4
5
6
7
|
mysql>
SELECT
USER
();
+
----------------+
|
USER
() |
+
----------------+
| root@localhost |
+
----------------+
1 row
in
set
(0.00 sec)
|
#查看所有用户
|
1
2
3
4
5
6
7
8
9
10
|
mysql>
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 |
+
---------+-------------+-------------------------------------------+
4
rows
in
set
(0.00 sec)
|
#查看当前数据库版本，版本5.5一下默认的存储引擎并非是InnoDB，如果需要可以手动修改。
|
1
2
3
4
5
6
7
|
mysql>
select
@@version ;
+
------------+
| @@version |
+
------------+
| 5.1.73-log |
+
------------+
1 row
in
set
(0.00 sec)
|
#查看数据库是否启用了log_bin
|
1
2
3
4
5
6
7
8
9
10
|
mysql> show variables
like
'%log_bin%'
;
+
---------------------------------+-------+
| Variable_name | Value |
+
---------------------------------+-------+
| log_bin |
ON
|
| log_bin_trust_function_creators |
OFF
|
| log_bin_trust_routine_creators |
OFF
|
| sql_log_bin |
ON
|
+
---------------------------------+-------+
4
rows
in
set
(0.00 sec)
|
#查看当前写入的日志
|
1
2
3
4
5
6
7
|
mysql> show master status ;
+
----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
----------------+----------+--------------+------------------+
| log-bin.000002 | 231 | test | |
+
----------------+----------+--------------+------------------+
1 row
in
set
(0.00 sec)
|
根据/etc/my.cnf 找到日志的配置路径
|
1
|
log-bin = /var/lib/mysql/log-bin
|
查看日志内容
|
1
|
[root@10-4-14-168 ~]# mysqlbinlog /var/lib/mysql/log-bin.000002
|
#查看MySQL支持的字符集
|
1
|
mysql> show
character
set
;
|
#查看默认字符集
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
mysql> show variables
like
'%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/ |
+
--------------------------+----------------------------+
8
rows
in
set
(0.00 sec)
|
#查看某一库的字符集
|
1
2
3
4
5
6
7
|
mysql> show
create
database
test;
+
----------+---------------------------------------------------------------+
|
Database
|
Create
Database
|
+
----------+---------------------------------------------------------------+
| test |
CREATE
DATABASE
`test`
/*!40100 DEFAULT CHARACTER SET utf8 */
|
+
----------+---------------------------------------------------------------+
1 row
in
set
(0.00 sec)
|
查看某一表的字符集
|
1
2
3
4
5
6
7
8
9
|
mysql> show
create
table
test.t1 \G
*************************** 1. row ***************************
Table
: t1
Create
Table
:
CREATE
TABLE
`t1` (
`
user
`
char
(16)
CHARACTER
SET
utf8
COLLATE
utf8_bin
NOT
NULL
DEFAULT
''
,
`host`
char
(60)
CHARACTER
SET
utf8
COLLATE
utf8_bin
NOT
NULL
DEFAULT
''
,
`
password
`
char
(41)
CHARACTER
SET
latin1
COLLATE
latin1_bin
NOT
NULL
DEFAULT
''
) ENGINE=MyISAM
DEFAULT
CHARSET=utf8
1 row
in
set
(0.00 sec)
|
#修改字符集
|
1
|
mysql>
alter
database
mydb
character
set
utf-8;
|
#可以直接在配置文件中做添加，然后重启服务
|
1
2
3
4
5
|
[client]
character_set_server = utf8
[mysqld]
character_set_server = utf8
|