本文索引：
- 设置更改root密码
- 连接MySQL
- MySQL常用命令
设置更改root密码
要进行mysql数据库操作，需要启动mysql服务，执行ps aux进行查看
[root@localhost ~]# ps aux | grep mysqlroot 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.pidmysql 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.pidroot 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 rootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> quitBye
- 设置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 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 8Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 10Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> quitBye
- 密码未知的情况下修改
- 修改mysql配置文件
# 这里是/usr/local/mysql/my.cnf[root@localhost etc]# vim /usr/local/mysql/my.cnf# 在mysqld块内添加一行代码skip-grant //忽略授权
- 直接登录成功
[root@localhost etc]# mysql -u rootWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase 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 restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!
- 成功登录
[root@localhost ~]# mysql -u root -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 5Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> quitBye
- 连接远程
# -h指定远程ip，-P指定端口[root@localhost ~]# mysql -u root -p'111' -h 127.0.0.1 -P3306Warning: 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 5Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> quitBye
- 使用sock连接
[root@localhost ~]# mysql -u root -p'111' -S/tmp/mysql.sockWarning: 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 5Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> quitBye
- 只执行命令不登录（适合脚本）
[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 namesYou can turn off this feature to get a quicker startup with -ADatabase 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: userCreate 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)
- 查看当前使用的数据库
# 刚登录时未选择数据库，查看的结果为nullmysql> 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 changedmysql> 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\GVariable_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: 0453 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)