MySQL常用操作

MySQL常用操作

一．更改root密码

1.更改环境变量
修改/etc/profile 文件，文件尾添加 mysql 的绝对路径

[root@localhost mysql]# tail -1 /etc/profile
export PATH=$PATH:/usr/local/mysql/bin/
[root@localhost mysql]# source /etc/profile

或者直接添加

[root@localhost mysql]# mysql -uroot
-bash: mysql: command not found
[root@localhost mysql]# export PATH=$PATH:/usr/local/mysql/bin/
[root@localhost mysql]# mysql -uroot
mysql> 

2.创建密码

[root@localhost mysql]# mysqladmin -uroot password '000000'
Warning: Using a password on the command line interface can be insecure.
注释：可以忽略 warning 内容，指的是明码输入屏幕不安全。
[root@localhost mysql]# mysql -uroot -p000000

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

3.密码重置
修改配置文件/etc/my.cnf，在 mysqld 配置段，增加字段 skip-grant

[mysqld]

skip-grant    //忽略授权
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

修改完成后，重启 mysql 服务
之后可以不适用密码直接登录

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

[root@localhost ~]# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

切换库，更改表：

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('linux') where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

修改完成后，把/etc/my.cnf 改回原有状态，并重启 mysql服务。

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

[root@localhost ~]# mysql -uroot -plinux
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

二、连接数据库

[root@localhost ~]# mysql -uroot -plinux
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.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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 ~]# mysql -uroot -plinux -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 3
Server version: 5.6.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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 ~]# mysql -uroot -plinux -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 4
Server version: 5.6.43 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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 ~]# mysql -uroot -plinux -e "show databases"
Warning: Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
[root@localhost ~]# 

三、MySQL创建用户及授权
1.进行授权：

mysql> grant all on *.* to 'user1' identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT,UPDATE,INSERT on db1.* to 'user2'@'192.168.200.10' identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on db1.* to 'user3'@'%' identified by 'passwd';
Query OK, 0 rows affected (0.00 sec)

2.查看授权表

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*6F3CAE7C3BBB2A5B5D933738682953BC21AEBEE7' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


mysql> show grants for user2@192.168.200.10;
+-------------------------------------------------------------------------------------------------------------------+
| Grants for user2@192.168.200.10                                                                                   |
+-------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user2'@'192.168.200.10' IDENTIFIED BY PASSWORD '*59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0' |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO 'user2'@'192.168.200.10'                                               |
+-------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)