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

本文索引：

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

设置更改root密码

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

[root@localhost ~]# ps aux | grep mysql
root       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
mysql      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
root       8402  0.0  0.0 112684   976 pts/0    S+   14:45   0:00 grep --color=auto mysql

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

# 临时生效
[root@localhost ~]# export PATH=$PATH:/usr/local/mysql/bin

# 永久生效
[root@localhost ~]# vim /etc/profile
在最后一行追加
PATH=$PATH:/usr/local/mysql/bin
保存退出

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

修改密码

密码已知的情况下修改

[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
密码未知的情况下修改

修改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的命令

连接本机

[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
连接远程

-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
使用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
只执行命令不登录（适合脚本）

[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)