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