MYSQL添加新用户 MYSQL为用户创建数据库 MYSQL为新用户分配权限 (二)
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 |