MySQL刷新权限，将某些权限从硬盘刷新到内存中(修改root密码自带隐式刷新权限操作) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql 查看服务端口 mysql show variables like 'port';+---------
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>
