MySQL 刷新权限, 将某些权限从硬盘刷新到内存中 (修改 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>
来源: http://www.linuxidc.com/Linux/2019-08/160215.htm