ubuntu下mysql管理_Ubuntu 下安装MySQL 服务端管理操作

2020-01-08

直接安装

sudo apt install mysql-server-5.7

过程中出现确认root账户密码确认.

Screenshot from 2020-01-08 13-51-47.png

安装后使用ps -ef |grep mysql查看当前服务是否运行

查看服务端是否已经在运行

上图显示是已经启动状态.

如果MySQL没有启动,使用一下命令来启动服务器

启动mysql：

方式一：sudo /etc/init.d/mysql start

方式二：sudo service mysql start

停止mysql：

方式一：sudo /etc/init.d/mysql stop

方式二：sudo service mysql stop

重启mysql：

方式一：sudo /etc/init.d/mysql restart

方式二：sudo service mysql restart

启动服务之后使用mysql -u root -p 进入 数据库操作终端.

-u 为用户名

登录成功

创建新的用户,

新用户具有SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 权限,用户名chy,密码是123456

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> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP

-> ON TUTORIALS.*

-> TO 'chy'@'localhost'

-> IDENTIFIED BY '123456';

Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.00 sec)

查看一下刚刚创建的用户

mysql> SELECT user FROM user WHERE user = 'chy';

+------+

| user |

+------+

| chy |

+------+

1 row in set (0.00 sec)

mysql> SELECT host ,user , authentication_string FROM user WHERE user = 'chy';

+-----------+------+-------------------------------------------+

| host | user | authentication_string |

+-----------+------+-------------------------------------------+

| localhost | chy | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+-----------+------+-------------------------------------------+

1 row in set (0.00 sec)

mysql>

查看当前DATABASE用哪些用户

mysql> SELECT host ,user , authentication_string FROM user;

+-----------+------------------+-------------------------------------------+

| host | user | authentication_string |

+-----------+------------------+-------------------------------------------+

| localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |

| localhost | debian-sys-maint | *D4B2E079DF1EA59B7663C65AA75613331418DE18 |

| localhost | chy | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

+-----------+------------------+-------------------------------------------+

5 rows in set (0.00 sec)

管理MySQL的常用命令

use xxx(数据库名)

选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

mysql> use mysql

Database changed

SHOW DATABASES:

列表列出当前服务器中所有的DATABASE,然后可以上用上面的指令进行USE

sql的语句大小写并不敏感, show databases 也可以,

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

SHOW TABLES:

显示上面已经use 的数据库中的所有表.

mysql> SHOW TABLES;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

|servers |

| time_zone_transition_type |

| user |

+---------------------------+

31 rows in set (0.00 sec)

SHOW COLUMNS FROM 数据表:

显示数据表中的属性,属性类型,主键信息,和一些其他内容.

mysql> show COLUMNS FROM servers;

+-------------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------+----------+------+-----+---------+-------+

| Server_name | char(64) | NO | PRI | | |

| Host | char(64) | NO | | | |

| Db | char(64) | NO | | | |

| Username | char(64) | NO | | | |

| Password | char(64) | NO | | | |

| Port | int(4) | NO | | 0 | |

| Socket | char(64) | NO | | | |

| Wrapper | char(64) | NO | | | |

| Owner | char(64) | NO | | | |

+-------------+----------+------+-----+---------+-------+

9 rows in set (0.00 sec)

SHOW INDEX FROM 数据表:

显示数据表的详细索引信息，包括PRIMARY KEY。

mysql> SHOW INDEX FROM servers;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| servers | 0 | PRIMARY | 1 | Server_name | A | 0 | NULL | NULL | | BTREE | | |

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G:

该命令将输出Mysql数据库管理系统的性能及统计信息。

mysql> SHOW TABLE STATUS FROM RUNOOB; # 显示数据库 RUNOOB 中所有表的信息

mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'; # 表名以runoob开头的表的信息

mysql> SHOW TABLE STATUS from RUNOOB LIKE 'runoob%'\G; # 加上 \G，查询结果按列打印

2020-01-08 14:50:06