刷新权限,将某些权限从硬盘刷新到内存中(修改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>