mysql查看保存账号_MySQL查看当前用户、存储引擎、日志

#查看MySQL的当前用户

mysql> SELECT USER();

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

| USER() |

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

| root@localhost |

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

1 row in set (0.00 sec)

#查看所有用户

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，如果需要可以手动修改。

mysql> select @@version ;

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

| @@version |

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

| 5.1.73-log |

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

1 row in set (0.00 sec)

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

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)

#查看当前写入的日志

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 找到日志的配置路径

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

查看日志内容

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

#查看MySQL支持的字符集

mysql> show character set ;

#查看默认字符集

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)

#查看某一库的字符集

mysql> show create database test;

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

| Database | Create Database |

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

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

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

1 row in set (0.00 sec)

查看某一表的字符集

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)

#修改字符集

mysql>alter database mydb character set utf-8;

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

[client]

character_set_server = utf8

[mysqld]

character_set_server = utf8