HelloWorld
登录

Mysql密码设置、数据库连接及常用命令

Wesley13

2021-10-11

1219 0 0

本文索引：

  • 设置更改root密码
  • 连接MySQL
  • MySQL常用命令

设置更改root密码

要进行mysql数据库操作，需要启动mysql服务，执行ps aux进行查看

1[root@localhost ~]# ps aux | grep mysql
2root       1908  0.0  0.1 115392  1696 ?        S    13:20   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysql/my.cnf --datadir=/data/mysql --pid-file=/data/mysql/localhost.localdomain.pid
3mysql      2156  0.1 30.3 1301048 460788 ?      Sl   13:20   0:06 /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/localhost.localdomain.err --pid-file=/data/mysql/localhost.localdomain.pid
4root       8402  0.0  0.0 112684   976 pts/0    S+   14:45   0:00 grep --color=auto mysql

要想直接执行mysql命令，将/usr/local/mysql/bin加入到环境变量PATH；

1# 临时生效
2[root@localhost ~]# export PATH=$PATH:/usr/local/mysql/bin
3
4# 永久生效
5[root@localhost ~]# vim /etc/profile
6在最后一行追加
7PATH=$PATH:/usr/local/mysql/bin
8保存退出
9
10[root@localhost ~]# source /etc/profile

  • 初始状态，无密码即可登录

    [root@localhost etc]# mysql -u root 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)

    Copyright (c) 2000, 2017, 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> quit Bye

  • 设置mysql数据库的用户密码

    [root@localhost etc]# mysqladmin -u root password "123456" Warning: Using a password on the command line interface can be insecure.

    这里的warning不用关心

    使用新设置的密码登录

    [root@localhost etc]# mysql -u root -p Enter password: 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)

    Copyright (c) 2000, 2017, 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 etc]# mysqladmin -u root -p"123456" password "654321" Warning: Using a password on the command line interface can be insecure.

    使用修改后的密码登录

    [root@localhost etc]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.6.36 MySQL Community Server (GPL)

    Copyright (c) 2000, 2017, 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> quit Bye

  2. 密码未知的情况下修改

  • 修改mysql配置文件

    这里是/usr/local/mysql/my.cnf

    [root@localhost etc]# vim /usr/local/mysql/my.cnf

    在mysqld块内添加一行代码

    skip-grant //忽略授权

  • 直接登录成功

    [root@localhost etc]# mysql -u root 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)

    Copyright (c) 2000, 2017, 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数据库内

    mysql> update user set password=password('123456') where user='root'; Query OK, 4 rows affected (0.03 sec) Rows matched: 4 Changed: 4 Warnings: 0

  • 删除配置文件内的skip-grant，

    [root@localhost etc]# vim /usr/local/mysql/my.cnf

    删除skip-grant，保存退出

  • 最后重启服务

    [root@localhost etc]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!

  • 成功登录

    [root@localhost ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.6.36 MySQL Community Server (GPL)

    Copyright (c) 2000, 2017, 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

常用的连接mysql的命令

  1. 连接本机

    [root@localhost ~]# mysql -u root -p'111' 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)

    Copyright (c) 2000, 2017, 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> quit Bye

  2. 连接远程

    -h指定远程ip，-P指定端口

    [root@localhost ~]# mysql -u root -p'111' -h 127.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 5 Server version: 5.6.36 MySQL Community Server (GPL)

    Copyright (c) 2000, 2017, 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> quit Bye

  3. 使用sock连接

    [root@localhost ~]# mysql -u root -p'111' -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 5 Server version: 5.6.36 MySQL Community Server (GPL)

    Copyright (c) 2000, 2017, 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> quit Bye

  4. 只执行命令不登录（适合脚本）

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

MySQL常用命令

  • 查询库

    mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 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> 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 | | | 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 | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_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 | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_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 | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | mysql_native_password | | | authentication_string | text | YES | | NULL | | | password_expired | enum('N','Y') | NO | | N | | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ 43 rows in set (0.00 sec)

  • 查看建表语句

    \G将显示的凌乱内容规整，加了\G就不需要加;了

    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', 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', Show_db_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Super_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', Execute_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Repl_slave_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', Repl_client_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', Create_user_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', Create_tablespace_priv enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', ssl_type enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '', ssl_cipher blob NOT NULL, x509_issuer blob NOT NULL, x509_subject blob NOT NULL, max_questions int(11) unsigned NOT NULL DEFAULT '0', max_updates int(11) unsigned NOT NULL DEFAULT '0', max_connections int(11) unsigned NOT NULL DEFAULT '0', max_user_connections int(11) unsigned NOT NULL DEFAULT '0', plugin char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password', authentication_string text COLLATE utf8_bin, password_expired enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N', PRIMARY KEY (Host,User) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges' 1 row in set (0.00 sec)

  • 查看当前用户

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

  • 查看当前使用的数据库

    刚登录时未选择数据库，查看的结果为null

    mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 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指令前加#，该条指令将不会执行

  • 创建表

    mysql> use db1; Database changed

    mysql> create table t1(id int(4), name char(40)); Query OK, 0 rows affected (0.03 sec)

    还可以在创建时指定搜索引擎和子符编码

    先要删除t1表

    mysql> drop tables t1; mysql> create table t1(id int(4), name char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.03 sec)

    mysql> show create table t1; +-------+---------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE t1 ( id int(4) DEFAULT NULL, name char(40) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+---------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)

  • 查看当前数据库版本

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

  • 查看数据库状态

    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 | 466 | | Bytes_sent | 45143 | | Com_admin_commands | 0 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 0 | | Com_alter_tablespace | 0 | | Com_alter_user | 0 | | Com_analyze | 0 | | Com_begin | 0 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 1 | | Com_change_master | 0 | | Com_check | 0 | | Com_checksum | 0 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_event | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_procedure | 0 | | Com_create_server | 0 | | Com_create_table | 0 | | Com_create_trigger | 0 | | Com_create_udf | 0 | | Com_create_user | 0 | | Com_create_view | 0 | | Com_dealloc_sql | 0 | | Com_delete | 0 | | Com_delete_multi | 0 | | Com_do | 0 | | Com_drop_db | 0 | | Com_drop_event | 0 | | Com_drop_function | 0 | ...

  • 查看各参数

    mysql> show variables\G Variable_name: updatable_views_with_limit Value: YES *************************** 448. row *************************** Variable_name: version Value: 5.6.36 *************************** 449. row *************************** Variable_name: version_comment Value: MySQL Community Server (GPL) *************************** 450. row *************************** Variable_name: version_compile_machine Value: x86_64 *************************** 451. row *************************** Variable_name: version_compile_os Value: linux-glibc2.5 *************************** 452. row *************************** Variable_name: wait_timeout Value: 28800 *************************** 453. row *************************** Variable_name: warning_count Value: 0 453 rows in set (0.01 sec)

    按指定名称来匹配

    mysql> show variables like "max_connection%"; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec)

  • 修改参数

    mysql> set global max_connect_errors=1000; Query OK, 0 rows affected (0.00 sec)

    %表示匹配任意字符

    这里可以看到参数已经变为修改后的1000了。

    mysql> show variables like "max_connect%"; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | max_connect_errors | 1000 | | max_connections | 151 | +--------------------+-------+ 2 rows in set (0.00 sec)

  • 查看队列

    可以查看当前使用用户、数据库，进行的操作等

    mysql> show processlist; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 6 | root | localhost | db1 | Query | 0 | init | show processlist | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 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)

点赞
收藏

评论区

加载中...

相关推荐

MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1

文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s

Oracle 分组与拼接字符串同时使用

SELECTT.,ROWNUMIDFROM(SELECTT.EMPLID,T.NAME,T.BU,T.REALDEPART,T.FORMATDATE,SUM(T.S0)S0,MAX(UPDATETIME)CREATETIME,LISTAGG(TOCHAR(

MySQL部分从库上面因为大量的临时表tmp_table造成慢查询

背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_

皕杰报表之UUID

​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为

手写Java HashMap源码

HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22

2020年前端实用代码段，为你的工作保驾护航

有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )