时间:2021-07-01 10:21:17 帮助过:4人阅读
首次使用mysql会提示‘该命令不在’,原因是还没有将该命令加入环境变量,如果要使用该命令,需要使用其绝对路径:/usr/local/mysql/bin/mysql,为了方便,先将其加入系统环境变量。
- <code>[root@localhost ~]# export PATH=$PATH:/usr/local/mysql/bin/mysql</code>
重启系统后该变量会失效,若要永久生效,需要将其加入环境变量配置文件:
- <code>[root@localhost ~]# vim /etc/profile
- ......
- export PATH=$PATH:/usr/local/mysql/bin/
- 刷新配置:
- [root@localhost ~]# source /etc/profile</code>
设置密码
首次登陆mysql,root用户没有密码,直接登陆
[root@localhost ~]# mysql -uroot
//-u指定用户登录
Welcome to the MySQL monitor. Commands end with ; or \g.
......
mysql>quit
Bye
// quit命令可以退出mysql。
设置密码:
[root@localhost ~]# mysqladmin -uroot password ‘123456‘
Warning: Using a password on the command line interface can be insecure.
// 这里并没有报错,只是提示说密码在命令行显示出来了不×××全。
不使用密码登录:
[root@localhost ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)
// 提示登录被拒绝,需要密码。
使用密码登录:
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
......
// -p参数,使用密码登录。可以将密码接在-p后。也可以不在-p后输入密码,根据后面的提示信息输入,这个方法不会暴露用户密码,更安全。
注意: 在没设置root密码时使用-p参数登录mysql,会提示输入密码,这是直接回车就行。
更改密码
当知道用户密码时,进行密码更改:
[root@localhost ~]# mysqladmin -uroot -p‘123456‘ password ‘654321‘
Warning: Using a password on the command line interface can be insecure.
// 警告密码在命令行输入,不安全。但是密码已经修改成功!
使用旧密码登录:
[root@localhost ~]# mysql -uroot -p123456
Warning: Using a password on the command line interface can be insecure.
// 警告密码在命令行输入,不安全。
ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)
// 提示登录信息验证失败,密码错误!
使用新密码登录
[root@localhost ~]# mysql -uroot -p654321
Warning: Using a password on the command line interface can be insecure.
// 警告密码在命令行输入,不安全。
Welcome to the MySQL monitor. Commands end with ; or \g.
......
mysql>
// 使用新密码登录成功!
密码重置
在不记得root密码时使用,重置密码。
编辑配置文件:
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
skip-grant // 忽略授权!
......
// 在mysqld模块下加入代码:skip-grant
重启mysql服务:
[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!
注意: 完成上面操作之后登录mysql就不需要密码了。
- <code>登录mysql:
- [root@localhost ~]# mysql -uroot
- Welcome to the MySQL monitor. Commands end with ; or \g.
- ......
- mysql>
- // 不使用-p参数直接登录。
- 切换到mysql库:
- mysql> use mysql; // 切换到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\G;
- // 查看用户的表信息,该表中存放的是用户相关信息(密码、授权…)
- // G选项的作用是使输出信息有序显示,不加该选项,显示内容会很乱
- mysql> select password from user;
- // 查看用户密码,显示结果Wie加密字符串!
- 重置密码:
- mysql> update user set password=password(‘112233‘) where user=‘root‘;
- Query OK, 4 rows affected (0.11 sec)
- Rows matched: 4 Changed: 4 Warnings: 0
- // 将密码更改为‘112233’
- 恢复配置文件:
- [root@localhost ~]# vim /etc/my.cnf
- // 将之前加入skip-grant那行注释掉
- 重启mysql服务:
- [root@localhost ~]# /etc/init.d/mysqld restart
- Shutting down MySQL.. SUCCESS!
- Starting MySQL. SUCCESS!
- 登录:
- [root@localhost ~]# mysql -uroot -p‘112233‘
- Warning: Using a password on the command line interface can be insecure.
- Welcome to the MySQL monitor. Commands end with ; or \g.
- ......
- mysql> </code>
重置密码步骤: vim /etc/my.cnf-->添加skip-grant-->mysql restart-->登录-->use mysql-->update user set password=...-->vim /etc/my.cnf-->删除skip-grant-->mysql restart。
远程连接
使用IP和端口号连接
[root@localhost ~]# mysql -uroot -p‘112233‘ -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.
......
mysql>
// -h=host,指定IP,-P=port,指定端口号
本地连接
直接可以直接连接或使用socket连接。
使用socket链接:
[root@localhost ~]# mysql -uroot -p‘112233‘ -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.
......
mysql>
// -S=socket,指定socket。此方法只适用于本地连接。和直接mysql连接一样。
连接数据后显示所有数据库
- <code>[root@localhost ~]# mysql -uroot -p‘112233‘ -e "show databases"</code>
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
-e 参数后可以跟一条mysql语句。
// 该方法常用于shell脚本中。
- <code>查看有哪些数据库:
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | test |
- +--------------------+
- 4 rows in set (0.00 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>
- 查看库里的表:
- 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)
- 查看表里面的字段:
- 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 | |
- ......
- +------------------------+-----------------------------------+------+-----+-----------------------+-------+
- 43 rows in set (0.00 sec)
- 查看表是怎么创建的
- 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‘,
- ......
- // \G 是让结果竖排显示;
- 查看当前登录的用户:
- mysql> select user();
- +----------------+
- | user() |
- +----------------+
- | root@localhost |
- +----------------+
- 1 row in set (0.00 sec)
- 查看当前所在的库:
- mysql> select database();
- +------------+
- | database() |
- +------------+
- | mysql |
- +------------+
- 1 row in set (0.00 sec)
- 创建库:
- 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)
- mysql> use db1 //切换到db1库
- Database changed
- 创建表:
- mysql> use db1;
- // 先切换到指定库下
- Database changed
- mysql> create table t1(`id` int(4),`name` char(40));
- // 括号中是定义字段及字段格式,使用反引号引起来
- Query OK, 0 rows affected (1.51 sec)
- // drop table t1,可以删除表。
- 查看当前数据库的版本:
- mysql> select version();
- +-----------+
- | version() |
- +-----------+
- | 5.6.35 |
- +-----------+
- 1 row in set (0.00 sec)
- // 数据库版本:5.6.35
- 查看数据库状态:
- mysql> show status;
- +-----------------------------------------------+-------------+
- | Variable_name | Value |
- +-----------------------------------------------+-------------+
- | Aborted_clients | 0 |
- | Aborted_connects | 0 |
- +-----------------------------------------------+-------------+
- 查看所有参数:
- mysql> show variables\G; //查看所有参数
- 查看指定参数
- mysql> show variables like ‘max_connect%‘\G;
- // like表示匹配;%是通配符
- 更改参数:
- mysql> set global max_connect_errors=110;
- Query OK, 0 rows affected (0.04 sec)
- #在此只是临时更改,如果要永久更改,需要编辑配置文件/etc/my.cnf
- 查看队列:
- mysql> show processlist;
- +----+------+-----------+------+---------+------+-------+------------------+
- | Id | User | Host | db | Command | Time | State | Info |
- +----+------+-----------+------+---------+------+-------+------------------+
- | 5 | root | localhost | db1 | Query | 0 | init | show processlist |
- +----+------+-----------+------+---------+------+-------+------------------+
- 1 row in set (0.01 sec)
- 完整显示:
- mysql> show full processlist;
- +----+------+-----------+------+---------+------+-------+-----------------------+
- | Id | User | Host | db | Command | Time | State | Info |
- +----+------+-----------+------+---------+------+-------+-----------------------+
- | 6 | root | localhost | db1 | Query | 0 | init | show full processlist |
- +----+------+-----------+------+---------+------+-------+-----------------------+
- 1 row in set (0.00 sec)</code>
在mysql中 drop 后跟库或者表名,可以删除库或者表。
可以使用 ctrl+l 清屏
mysql的历史命令在.mysql_history 文件中。
mysql设置更改root密码、连接mysql、常用命令
标签:创建 names 操作 stat 用户 format success mes col