mysql相关命令

最新推荐文章于 2021-07-14 02:42:34 发布

奈小奈

最新推荐文章于 2021-07-14 02:42:34 发布

阅读量171

点赞数

分类专栏：数据库文章标签： mysql linux

本文链接：https://blog.csdn.net/weixin_45849657/article/details/113107969

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

一、创建、修改密码

1、mysql创建密码

[root@localhost ~]# mysqladmin -uroot password 'liu'
Warning: Using a password on the command line interface can be insecure.

验证：密码登录

[root@localhost ~]# mysql -uroot -pliu
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

2、mysql修改密码

修改配置文件，在[mysqld]下添加一行skip-grant使忽略授权

[root@localhost ~]# vi /etc/my.cnf
[mysqld]

skip-grant       //添加这一行
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

重启服务或重新加载配置文件，进入数据库修改密码

[root@localhost ~]# systemctl restart mysql
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
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 * from user;
mysql> update user set password=password('linux') where user='root';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

查看user表中root用户的密码（此处被加密）

mysql> select password from user where user='root';
+-------------------------------------------+
| password                                  |
+-------------------------------------------+
| *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
| *6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7 |
+-------------------------------------------+
4 rows in set (0.00 sec)

将配置文件改回，重启服务，验证使用新密码进行登录

[root@localhost ~]# vi /etc/my.cnf
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -plinux
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

二、连接数据库

1.连接数据库的四种方式：

本地进入

[root@localhost ~]# mysql -uroot -plinux

[root@localhost ~]# mysql -uroot -plinux -S/tmp/mysql.sock

无需进入数据库，直接访问

[root@localhost ~]# mysql -uroot -plinux -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

可远程连接（也可连接本地mysql）

[root@localhost ~]# mysql -uroot -plinux -h127.0.0.1 -P3306

三、mysql常用命令

命令后跟\G表示竖排显示！

1、查询库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

2、切换库

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

3、查看库里的表

mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| 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                      |
+---------------------------+
28 rows in set (0.00 sec)

4、查看表里的字段

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(16)                          | NO   | PRI |                       |       |
| Password               | char(41)                          | NO   |     |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |

5、查看建表语句

结果太长所以只截了一小段（仅供参考）

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

6、查看当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

7、查看当前使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| mysql      |
+------------+
1 row in set (0.00 sec)

8、创建库

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

9、创建表

mysql> use db1;
Database changed
mysql> use db1;create table t1(`id`int(4),`name`char(40));
Database changed
Query OK, 0 rows affected (0.02 sec)

10、查看当前数据库版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.43    |
+-----------+
1 row in set (0.00 sec)

11、查看数据库状态

mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name                                 | Value       |
+-----------------------------------------------+-------------+
| Aborted_clients                               | 0           |
| Aborted_connects                              | 2           |
| Binlog_cache_disk_use                         | 0           |
| Binlog_cache_use                              | 0           |
| Binlog_stmt_cache_disk_use                    | 0           |
| Binlog_stmt_cache_use                         | 0           |
| Bytes_received                                | 766         |
| Bytes_sent                                    | 1949        |

12、查看各参数

mysql> show variables;show variables like 'max_connect%';
454 rows in set (0.00 sec)

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 100   |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

13、修改参数

mysql> set global max_connect_errors=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables;show variables like 'max_connect%';
454 rows in set (0.00 sec)

+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 1000  |
| max_connections    | 151   |
+--------------------+-------+
2 rows in set (0.00 sec)

14、查看列表

mysql> show processlist;show full processlist;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  9 | root | localhost | db1  | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host      | db   | Command | Time | State | Info                  |
+----+------+-----------+------+---------+------+-------+-----------------------+
|  9 | root | localhost | db1  | Query   |    0 | init  | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

四、数据库备份及恢复

1、备份库

[root@localhost ~]# mysqldump -uroot -plinux mysql > /tmp/mysql.sql
Warning: Using a password on the command line interface can be insecure.

2、恢复库

[root@localhost ~]# mysql -uroot -plinux mysql < /tmp/mysql.sql 
Warning: Using a password on the command line interface can be insecure.

3、备份表

[root@localhost ~]# mysqldump -uroot -plinux mysql user > /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.

4、恢复表

[root@localhost ~]# mysql -uroot -plinux mysql < /tmp/user.sql
Warning: Using a password on the command line interface can be insecure.

5、备份所以库

[root@localhost ~]# mysqldump -uroot -plinux -A > /tmp/123.sql
Warning: Using a password on the command line interface can be insecure.

6、只备份表结构

[root@localhost ~]# mysqldump -uroot -plinux -d > /tmp/mysql.sql 
Warning: Using a password on the command line interface can be insecure.

奈小奈

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql相关命令

文章目录一、创建、修改密码1、mysql创建密码2、mysql修改密码二、连接数据库1.连接数据库的四种方式：三、mysql常用命令1、查询库2、切换库3、查看库里的表4、查看表里的字段5、查看建表语句6、查看当前用户7、查看当前使用的数据库8、创建库9、创建表10、查看当前数据库版本11、查看数据库状态12、查看各参数13、修改参数14、查看列表四、数据库备份及恢复1、备份库2、恢复库3、备份表4、恢复表5、备份所以库6、只备份表结构一、创建、修改密码1、mysql创建密码[root@local
复制链接

扫一扫