MySQL查看当前用户、存储引擎、日志
#查看MySQL的当前用户|
1
2
3
4
5
6
7
|
mysql> SELECT
USER();
+----------------+
| USER() |
+----------------+
+----------------+
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
|
[[email protected] ~]# 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 |