刷新权限，将某些权限从硬盘刷新到内存中(修改root密码自带隐式刷新权限操作)
我要评论mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
mysql>
查看服务端口
mysql> show variables like 'port';
+---------------+-------+
| variable_name | value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
查看数据库字符集
mysql> show variables like '%char%';
+--------------------------+----------------------------------+
| variable_name | value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)
切数据库
mysql> select database();
+------------+
| database() |
+------------+
| test |
+------------+
1 row in set (0.01 sec)
mysql> show databases;
+--------------------+
| database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> use mysql
reading table information for completion of table and column names
you can turn off this feature to get a quicker startup with -a
database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
mysql> status
--------------
mysql ver 14.14 distrib 5.7.25, for linux-glibc2.12 (x86_64) using editline wrapper
connection id: 6
current database: mysql
current user: root@localhost
ssl: not in use
current pager: stdout
using outfile: ''
using delimiter: ;
server version: 5.7.25 mysql community server (gpl)
protocol version: 10
connection: localhost via unix socket
server characterset: latin1
db characterset: latin1
client characterset: utf8
conn. characterset: utf8
unix socket: /tmp/mysql.sock
uptime: 2 days 16 hours 47 min 41 sec
threads: 1 questions: 58 slow queries: 0 opens: 138 flush tables: 1 open tables: 131 queries per second avg: 0.000
--------------
mysql> show tables;
+---------------------------+
| tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
mysql>
创建允许远程登陆的用户
mysql> select host,user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)
mysql> grant all privileges on *.* to scott@'%' identified by 'tiger';
query ok, 0 rows affected, 1 warning (0.00 sec)
mysql>
data目录中每个数据库都创建了一个文件夹，lbdata1、ib_logfile0、ib_logfile1三个是专门为innodb存放数据和日志的共享文件
mysql> create database test;
query ok, 1 row affected (0.01 sec)
mysql> use test;
database changed
mysql> create table tmp(id int);
query ok, 0 rows affected (0.03 sec)
mysql> desc tmp;
+-------+---------+------+-----+---------+-------+
| field | type | null | key | default | extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | yes | | null | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)
mysql> insert into tmp value ('2')
-> ;
query ok, 1 row affected (0.01 sec)
mysql> commit;
query ok, 0 rows affected (0.00 sec)
mysql> select * from tmp;
+------+
| id |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> \q
bye
[root@localhost /usr/local/mysql/data]$ ls
auto.cnf ib_buffer_pool ibdata1 ib_logfile0 ib_logfile1 ibtmp1 localhost.localdomain.err localhost.localdomain.pid mysql performance_schema sys test
[root@localhost /usr/local/mysql/data]$ cd test
[root@localhost /usr/local/mysql/data/test]$ ls
db.opt tmp.frm tmp.ibd
每个数据库目录中的db.opt是数据库的信息,表名.frm是表的元信息,表名.ibd是数据信息,其中innodb_file_per_table参数来控制是否单独存储，5.7默认on，之前版本默认off
mysql> show variables like '%per_table%';
+-----------------------+-------+
| variable_name | value |
+-----------------------+-------+
| innodb_file_per_table | on |
+-----------------------+-------+
1 row in set (0.01 sec)
mysql>
.myd .myi是老的myisam存储引擎,myi是索引信息
mysql> create table tmp2(id int);
query ok, 0 rows affected (0.02 sec)
mysql> show create table tmp;
+-------+-----------------------------------------------------------------------------------------+
| table | create table |
+-------+-----------------------------------------------------------------------------------------+
| tmp | create table `tmp` (
`id` int(11) default null
) engine=innodb default charset=latin1 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
如对本文有疑问， 点击进行留言回复！！
一步步教你用Prometheus搭建实时监控系统系列(一)——上帝之火，普罗米修斯的崛起
网友评论