mysql中set rn_MySQL 日常简易操作

YoshiSukiMatsu

于 2021-01-28 00:27:46 发布

阅读量196

点赞数

文章标签： mysql中set rn

本文链接：https://blog.csdn.net/weixin_42471237/article/details/113442982

版权

常用MySQL操作

修改MySQL用户的密码

把mysql加入到环境变量中

[root[@abc](https://my.oschina.net/aaaaaa) ~]# mysql -uroot

-bash: mysql: 未找到命令

[root[@abc](https://my.oschina.net/aaaaaa) ~]# ls /usr/local/mysql/bin/mysql

/usr/local/mysql/bin/mysql

[root[@abc](https://my.oschina.net/aaaaaa) ~]# echo $PATH

/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

[root[@abc](https://my.oschina.net/aaaaaa) ~]# export PATH=$PATH:/usr/local/mysql/bin/

[root[@abc](https://my.oschina.net/aaaaaa) ~]# mysql -uroot

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.36 MySQL Community Server (GPL)

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>

但这样只是临时生效，重启机器后变会失效，如想开机加载，则需要在 /etc/profile 文件最后面加入上面的命令，然后 source /etc/profile 即可生效

-u 指定登录的用户

root用户是MySQL自带的管理员账户，默认没有密码，如需设置密码，则需如下操作：

[root@abc ~]# mysqladmin -uroot password 'lantern'

Warning: Using a password on the command line interface can be insecure.

[root@abc ~]#

这样给MySQL的root用户设置了密码，密码为 lantern

在执行过程中它会返回一条警告信息，意思是在命令行下面暴露了密码，这样不安全

再次使用root用户进入MySQL

[root@abc ~]# mysql -uroot -p'lantern'

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 5

Server version: 5.6.36 MySQL Community Server (GPL)

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>

也可以用如下登录：

[root@abc ~]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.36 MySQL Community Server (GPL)

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>

输入密码时需要加入-p选项后面可以直接跟密码。也可以不跟，以用户交互的方式输入密码

修改mysql的密码

[root@abc ~]# mysqladmin -uroot -p'lantern' password 'asdjkl'

这是在你知道原密码的情况下修改密码

不知道密码的情况下修改用户密码

1. 首先要去更改 /etc/my.cnf 下配置文件中加入skip-grant(skip-grant ，表示忽略授权，也就是说操作mysql的时候不需要用户名和密码了，能直接登录)

2. 更改配置文件后，重启服务 service mysqld restart

这样在进入MySQL ，即可直接进入

use 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>

mysql> select password from user where user='root';

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

| password |

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

| *2A032F7C5BA932872F0F045E0CF6B53CF702F2C5 |

| |

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

4 rows in set (0.05 sec)

password表，密码是加密的

mysql> update user set password=password('qwerty') where user='root';

输入上方的命令后即可成功修改密码，然后去退出数据库，

去 /etc/my.cnf配置文件中删除这一行 skip-grant

然后重启服务，输入新密码测试

连接MySQL

连接方式：使用ip端口连接机器，或者sock方式连接，但sock只支持本机使用

[root@abc ~]# mysql -uroot -p'lantern' -h127.0.0.1 -P3306

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 7

Server version: 5.6.36 MySQL Community Server (GPL)

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>

-h 指定连接的IP。 -P 指定端口默认3306

sock方式连接

[root@abc ~]# mysql -uroot -p'lantern' -S/tmp/mysql.sock

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 8

Server version: 5.6.36 MySQL Community Server (GPL)

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>

show databases 列出所有数据库 -e 连接MySQL后执行命令

[root@abc ~]# mysql -uroot -p'lantern' -e 'show databases'

Warning: Using a password on the command line interface can be insecure.

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

[root@abc ~]#

MySQL常用命令

查询库

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.01 sec)

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> 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.04 sec)

mysql>

查看表的字段

mysql> desc db;

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

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

| Host | char(60) | NO | PRI | | |

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

| User | char(16) | NO | PRI | | |

| Select_priv | enum('N','Y') | NO | | N | |

| Insert_priv | enum('N','Y') | NO | | N | |

| Update_priv | enum('N','Y') | NO | | N | |

| Delete_priv | enum('N','Y') | NO | | N | |

| Create_priv | enum('N','Y') | NO | | N | |

| Drop_priv | enum('N','Y') | NO | | N | |

| Grant_priv | enum('N','Y') | NO | | N | |

| References_priv | enum('N','Y') | NO | | N | |

| Index_priv | enum('N','Y') | NO | | N | |

| Alter_priv | enum('N','Y') | NO | | N | |

| Create_tmp_table_priv | enum('N','Y') | NO | | N | |

| Lock_tables_priv | enum('N','Y') | NO | | N | |

| Create_view_priv | enum('N','Y') | NO | | N | |

| Show_view_priv | enum('N','Y') | NO | | N | |

| Create_routine_priv | enum('N','Y') | NO | | N | |

| Alter_routine_priv | enum('N','Y') | NO | | N | |

| Execute_priv | enum('N','Y') | NO | | N | |

| Event_priv | enum('N','Y') | NO | | N | |

| Trigger_priv | enum('N','Y') | NO | | N | |

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

22 rows in set (0.07 sec)

mysql>

查看建表语句

mysql> show create table db\G

*************************** 1. row ***************************

Table: db

Create Table: CREATE TABLE `db` (

`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',

`User` char(16) COLLATE utf8_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',

`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

PRIMARY KEY (`Host`,`Db`,`User`),

KEY `User` (`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileges'

1 row in set (0.01 sec)

mysql>

查看当前用户

mysql> select user();

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

| user() |

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

| root@localhost |

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

1 row in set (0.00 sec)

mysql>

查看当前使用的数据库

mysql> select database();

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

| database() |

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

| mysql |

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

1 row in set (0.00 sec)

mysql>

创建新库

mysql> create database db1;

Query OK, 1 row affected (0.05 sec)

mysql>

mysql> show databases;

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

| Database |

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

| information_schema |

| db1 |

| mysql |

| performance_schema |

| test |

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

5 rows in set (0.00 sec)

mysql>

创建表

mysql> create table t1(`id` int(4), `name` char(40));

Query OK, 0 rows affected (0.39 sec)

mysql> show tables;

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

| Tables_in_db1 |

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

| t1 |

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

1 row in set (0.01 sec)

mysql>

id和name字段需要使用反引号

查看数据库版本号

mysql> select version();

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

| version() |

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

| 5.6.36 |

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

1 row in set (0.00 sec)

mysql>

查看MySQL当前状态

mysql> show status;

查看mysql参数

mysql> show variables;

修改mysql参数

mysql> show variables like 'max_connect%';

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

| Variable_name | Value |

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

| max_connect_errors | 100 |

| max_connections | 151 |

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

2 rows in set (0.01 sec)

mysql> set global max_connect_errors = 1000;

Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connect_errors';

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

| Variable_name | Value |

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

| max_connect_errors | 1000 |

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

1 row in set (0.00 sec)

mysql>

MySQL命令行中符号% 类似于 * ，表示通配。

set global 可以临时修改某些参数，但重启后会失效，想永久生效，需在/etc/my.cnf中定义

查看MySQL服务器的队列

mysql> show processlist;

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

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

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

1 row in set (0.00 sec)

mysql>

查看到的队列最后一个会非常完成的显示出来

mysql> show full processlist;

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

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

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

1 row in set (0.00 sec)

mysql>

MySQL用户管理

授权命令

mysql> grant all on *.* to 'user1'@'127.0.0.1' identified by '123456';

Query OK, 0 rows affected (0.08 sec)

mysql>

all表示所有的权限(读，写，查询，删除等操作)

*.* 第一个*表示所有数据库第二个*表示所有表；

identified by 后面跟密码，

user1 表示用户

用户和主机之间有一个符号@

网络上其他机器的某个用户授权

mysql> grant all on db1.* to 'user1'@'192.168.1.150' identified by '123456';

连接测试

[root@abc ~]# mysql -uuser1 -p'123456'

Warning: Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

[root@abc ~]# mysql -uuser1 -p'123456' -h127.0.0.1

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 17

Server version: 5.6.36 MySQL Community Server (GPL)

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>

第一次登录失败是因为默认是sock 需要加上-h参数和指定IP 才可以登录

把的IP改为 localhost 则可直接登录成功，不需要加-h指定IP

授权就是针对localhost，localhost就是针对的sock

mysql> grant all on *.* to 'user1'@'localhost' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> quit

Bye

[root@abc ~]# mysql -uuser1 -p'123456'

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 19

Server version: 5.6.36 MySQL Community Server (GPL)

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> show grants;

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

| Grants for user1@localhost |

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

| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

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

1 row in set (0.00 sec)

mysql>

上面是查询自己用户的权限

下面我登录了root用户来查询user1用户的权限

mysql> show grants for user1@'localhost';

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

| Grants for user1@localhost |

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

| GRANT ALL PRIVILEGES ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

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

1 row in set (0.01 sec)

mysql>

常用sql语句

查询某个表有多少行

mysql> select count(*) from mysql.user;

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

| count(*) |

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

| 9 |

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

1 row in set (0.07 sec)

mysql>

count(*)表示有多少行

mysql.user表示mysql数据库中的user表

查询表里所有数据

mysql> select * from mysql.db;

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

| % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y |

| % | test\_% | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N | Y | Y |

2 rows in set (0.00 sec)

查看表里的单个字段或者多个字段

mysql> select db,user from mysql.db;

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

| db | user |

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

| test | |

| test\_% | |

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

2 rows in set (0.00 sec)

mysql> select db from mysql.db;

+---------+

| db |

+---------+

| test |

| test\_% |

+---------+

2 rows in set (0.00 sec)

插入一行

mysql> insert into db1.t1 values (97,'lan');

Query OK, 1 row affected (0.11 sec)

mysql> select * from db1.t1;

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

| id | name |

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

| 97 | lan |

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

1 row in set (0.00 sec)

mysql>

更改表中的一行

mysql> update db1.t1 set name='Rn' where id=97;

Query OK, 1 row affected (0.32 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from db1.t1;

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

| id | name |

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

| 97 | Rn |

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

1 row in set (0.00 sec)

mysql>

清空表数据操作

mysql> truncate table db1.t1;

Query OK, 0 rows affected (0.17 sec)

mysql> select * from db1.t1;

Empty set (0.01 sec)

mysql>

删除表

mysql> drop table db1.t1;

Query OK, 0 rows affected (0.11 sec)

mysql>

删除数据库

mysql> drop database db1;

Query OK, 0 rows affected (0.06 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

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

4 rows in set (0.00 sec)

mysql>

MySQL数据库的备份及恢复

备份

[root@abc ~]# mysqldump -uroot -p'lantern' test > /tmp/test.sql

Warning: Using a password on the command line interface can be insecure.

[root@abc ~]#

/tmp/test.sql 就是你的备份文件了 test是数据库名称

备份其中一个表操作如下

[root@abc ~]# mysql -uroot -p'lantern' test user < /tmp/test.sql

test数据库后面跟表名

查看备份文件内容

[root@abc ~]# cat /etc/my.cnf

[mysqld]

datadir=/data/mysql

socket=/tmp/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

#log-error=/var/log/mariadb/mariadb.log

#pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory

#!includedir /etc/my.cnf.d

[root@abc ~]# mysqldump -uroot -p'lantern' test > /tmp/test.sql

Warning: Using a password on the command line interface can be insecure.

[root@abc ~]# cat /tmp/test.sql

-- MySQL dump 10.13 Distrib 5.6.36, for linux-glibc2.5 (x86_64)

-- Host: localhost Database: test

-- ------------------------------------------------------

-- Server version5.6.36

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-07-09 5:01:16

[root@abc ~]#

备份所有的库

[root@abc ~]# mysqldump -uroot -p'lantern' -A > /tmp/test_bak.sql

-A 表示所有的意思

备份表结构

[root@abc ~]# mysqldump -uroot -p'lantern' -d test > /tmp/test.sql

不备份数据，只备份表结构

恢复

[root@abc ~]# mysql -uroot -p'lantern' test < /tmp/test.sql

Warning: Using a password on the command line interface can be insecure.

[root@abc ~]#

恢复操作与备份操作相反

扩展

YoshiSukiMatsu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫