MYSQL设置更改root密码

  • export PATH=$PATH:/usr/local/mysql/bin/ 把mysql加入环境变量可以直接使用mysql命令,永久生效要把这条命令放到/etc/profile,并source /etc/profile
  • 设置密码
    [root@aminglinux-02 ~]# mysqladmin -uroot password 's5381561'
    Warning: Using a password on the command line interface can be insecure.
    [root@aminglinux-02 ~]# mysql -uroot 
    ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
  • 更改密码
    [root@aminglinux-02 ~]# mysqladmin -uroot -p's5381561' password '123456'
    Warning: Using a password on the command line interface can be insecure.
    [root@aminglinux-02 ~]# mysql -uroot -p'123456'
    mysql> 
  • 忘记密码时,进行密码更改
    • 改配置文件
      [root@aminglinux-02 ~]# vim /etc/my.cnf
      [mysqld]
      skip-grant  #忽略授权
      [root@aminglinux-02 ~]# vim /etc/my.cnf
      [root@aminglinux-02 ~]# /etc/init.d/mysqld restart
      Shutting down MySQL.. SUCCESS! 
      Starting MySQL.. SUCCESS! 
      [root@aminglinux-02 ~]# mysql -uroot
      Welcome to the MySQL monitor.  Commands end with ; or \g.
    • 更改密码
      [root@aminglinux-02 ~]# mysql -uroot
      Welcome to the MySQL monitor.  Commands end with ; or \g.
      mysql> use mysql;
      #切换mysql库
      Database changed
      mysql> select * from user\G;
      #查看用户的表信息,该表中存放的是用户相关信息(密码、授权…)
      #G选项的作用是使输出信息有序显示,不加该选项,显示内容会很乱  
      mysql> select password from user;
      #查看用户密码,显示结果Wie加密字符串!  
      mysql> update user set password=password('1234567') where user='root';
      Query OK, 4 rows affected (0.11 sec)
      Rows matched: 4  Changed: 4  Warnings: 0
      #将密码更改为‘1234567’
      mysql> quit
      Bye
    • 恢复配置文件并重启
      [root@aminglinux-02 ~]# vim /etc/my.cnf
      [root@aminglinux-02 ~]# /etc/init.d/mysqld restart
      Shutting down MySQL.. SUCCESS! 
      Starting MySQL. SUCCESS! 
      [root@aminglinux-02 ~]# mysql -uroot -p1234567
      Warning: Using a password on the command line interface can be insecure.

连接mysql

  • 远程连接
    [root@aminglinux-02 ~]# mysql -uroot -p1234567 -h127.0.0.1 -P3306
    Warning: Using a password on the command line interface can be insecure.
  • 本地连接
    [root@aminglinux-02 ~]# mysql -uroot -p1234567 -S/tmp/mysql.sock
    Warning: Using a password on the command line interface can be insecure.
  • 显示所有数据库,该方法使用于shell脚本中
    [root@aminglinux-02 ~]# mysql -uroot -p'1234567' -e "show databases"
    Warning: Using a password on the command line interface can be insecure.
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+

    mysql常用命令

  • 查看库信息
    • show databases; | 查询所有数据库
    • use db_name; | 切换库
    • show tables; | 查看库中的表
    • desc tb_name | 查看表里的字段
    • show create table tb_name\G; | 查看建表语句
    • select user(); | 查看当前用户
    • select database(); | 查看当前使用的数据库
    • select * from user\G; | 查看所有用户
  • 编辑库
    • create database db_name; | 创建库
    • use db_name;create table tb_name | 在某库下创建表
    • select version(); | 查看当前数据库版本
    • show status; | 查看数据库状态
    • show variables; | 查看所有参数
    • show variables like 'max_connet%' | 查看某参数,%为通配符
    • set global max_connect_errors=100; | 修改某参数 可以在my.cnf里永久修改
    • show processlist; | 查看mysql进程队列
    • show full processlist | 查看队列详细信息
    • drop database db_name | 删除库
  • 代码
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                      |
| time_zone                 |
| time_zone_leap_second     |
+---------------------------+
28 rows in set (0.00 sec)

mysql> desc time_zone;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| Time_zone_id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Use_leap_seconds | enum('Y','N')    | NO   |     | N       |                |
+------------------+------------------+------+-----+---------+----------------+
2 rows in set (0.11 sec)

mysql> show create table time_zone\G;
#G=grep筛选文字内容,规律显示出来
*************************** 1. row ***************************
       Table: time_zone
Create Table: CREATE TABLE `time_zone` (
  `Time_zone_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Time zones'
1 row in set (0.03 sec)

ERROR: 
No query specified

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

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

mysql> select * from user\G;
创建库:
mysql> create database db1;
Query OK, 1 row affected (0.02 sec)

创建表:
mysql> use db1;  
#先切换到指定库下
Database changed
mysql> create table t1(`id` int(4),`name` char(40)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
#括号中是定义字段及字段格式,使用反引号引起来
Query OK, 0 rows affected (1.51 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.35    |
+-----------+
1 row in set (0.06 sec)

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)
#在此只是临时更改,如果要永久更改,需要编辑配置文件

查看队列:
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> drop table t1;
Query OK, 0 rows affected (0.32 sec)

mysql> drop database db1;
Query OK, 0 rows affected (0.10 sec)

扩展