设置更改 root 密码
1.mysql 命令无法使用:
[root@weixing01 mysql]# mysql -uroot
-bash: mysql: 未找到命令
需要将 mysql 添加到环境变量: 临时生效
- [root@weixing01 mysql]# export PATH=$PATH:/usr/local/mysql/bin/
- [root@weixing01 mysql]# 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)
- 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@weixing01 mysql]# vi /etc/profile
添加
export PATH=$PATH:/usr/local/mysql/bin/
然后执行
[root@weixing01 mysql]# source /etc/profile
2. 设置 root 密码:
- [root@weixing01 mysql]# mysqladmin -uroot password we914
- Warning: Using a password on the command line interface can be insecure.
3. 再次尝试登陆:
- [root@weixing01 mysql]# mysql -uroot
- ERROR 1045 (28000): Access denied for user root@localhost (using password: NO)
- [root@weixing01 mysql]# mysql -uroot -p
- Enter password:
- 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>
4. 修改密码:
- [root@weixing01 mysql]# mysqladmin -uroot -pwei914 password wei927
- Warning: Using a password on the command line interface can be insecure.
5. 忘记 root 密码情况下, 重置密码:
- [root@weixing01 mysql]# vim /etc/my.cnf
- [mysqld]
- skip-grant
- datadir=/data/mysql
- socket=/tmp/mysql.sock
- # Disabling symbolic-links is recommended to prevent assorted security risks
- symbolic-links=0
6. 重启服务, 测试:
- [root@weixing01 mysql]# /etc/init.d/mysqld restart
- Shutting down MySQL.. SUCCESS!
- Starting MySQL. SUCCESS!
- [root@weixing01 mysql]# 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)
- 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>
7. 更改密码表:
切换库
- 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
查看 user 表:
- mysql> select * from user;
- +-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+
- | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired |
- +-----------+------+------------------------
- mysql> update user set password=password(wei914) where user=root;
- Query OK, 4 rows affected (0.00 sec)
- Rows matched: 4 Changed: 4 Warnings: 0
改完后需要把 / etc/my.cnf 改回原状
再次登陆:
- [root@weixing01 mysql]# mysql -uroot -pwei914
- 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 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
1. 普通密码登陆
2. 远程登录
- [root@weixing01 mysql]# mysql -uroot -pwei914 -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 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>
3. 使用 socket 连接: 只适合在本机
- [root@weixing01 mysql]# mysql -uroot -pwei914 -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 3
- 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>
4. 展示所有库:
- [root@weixing01 mysql]# mysql -uroot -pwei914 -e "show databases"
- Warning: Using a password on the command line interface can be insecure.
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | test |
- +--------------------+
MySQL 常用命令
1. 查看库:
- mysql> show databases
- -> ;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | test |
- +--------------------+
- 4 rows in set (0.00 sec)
2. 切换库:
- 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
3. 查看库里的表:
- 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)
4. 查看表里的字段:
- 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)
5. 查看表是怎么创建的:
- 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)
- ERROR:
- No query specified
6. 查看当前用户:
- mysql> select user();
- +----------------+
- | user() |
- +----------------+
- | root@localhost |
- +----------------+
- 1 row in set (0.00 sec)
7. 记录历史文件:
- [root@weixing01 ~]# less .mysql_history
- _HiStOrY_V2_
- use\040mysql;
- select\040*\040from\040user;
- ;
- use\040mysql
- show\040databases
- ;
- use\040mysql
- show\040tables
- use\040mysql
- show\040tables;
- desc\040user;
- show\040create\040table\040user\134G;
- select\040user();
- .mysql_history (END)
8. 创建库:
- 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;
- Database changed
9. 创建表:
- mysql> create table t1(`id` int(4), `name` char(40));
- Query OK, 0 rows affected (0.02 sec)
- mysql> show create table t1\G
- *************************** 1. row ***************************
- Table: t1
- Create Table: CREATE TABLE `t1` (
- `id` int(4) DEFAULT NULL,
- `name` char(40) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
- 1 row in set (0.01 sec)
10. 删除表:
- mysql> drop table t1;
- Query OK, 0 rows affected (0.01 sec)
重新定义
- mysql> create table t1(`id` int(4), `name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- Query OK, 0 rows affected (2.05 sec)
- mysql> show create table t1\G
- *************************** 1. row ***************************
- Table: t1
- Create Table: CREATE TABLE `t1` (
- `id` int(4) DEFAULT NULL,
- `name` char(40) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
11. 查看数据版本:
- mysql> select version();
- +-----------+
- | version() |
- +-----------+
- | 5.6.36 |
- +-----------+
- 1 row in set (0.00 sec)
12. 查看数据库状态:
- mysql> show status
- -> ;
- +-----------------------------------------------+-------------+
- | Variable_name | Value |
- +-----------------------------------------------+-------------+
- | Aborted_clients | 0 |
- | Aborted_connects | 0 |
- | Binlog_cache_disk_use | 0 |
- | Binlog_cache_use | 0 |
- | Binlog_stmt_cache_disk_use | 0 |
- | Binlog_stmt_cache_use | 0 |
- | Bytes_received | 574 |
- | Bytes_sent | 1026 |
- | 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 |
13. 修改参数:
- mysql> set global max_connect_errors=1000;
- Query OK, 0 rows affected (0.00 sec)
- mysql> show variables like max_connect%;
- +--------------------+-------+
- | Variable_name | Value |
- +--------------------+-------+
- | max_connect_errors | 1000 |
- | max_connections | 151 |
- +--------------------+-------+
- 2 rows in set (0.00 sec)
14. 查看队列: full 会更加完整
- mysql> show processlist;
- +----+------+-----------+------+---------+------+-------+------------------+
- | Id | User | Host | db | Command | Time | State | Info |
- +----+------+-----------+------+---------+------+-------+------------------+
- | 8 | 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 |
- +----+------+-----------+------+---------+------+-------+-----------------------+
- | 8 | root | localhost | db1 | Query | 0 | init | show full processlist |
- +----+------+-----------+------+---------+------+-------+-----------------------+
- 1 row in set (0.00 sec)
来源: http://www.bubuko.com/infodetail-2535992.html