1.检查线程连接
mysql> show processlist;
+----+------+-----------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+----------+------------------+
| 2 | root | localhost | mysql | Query | 0 | starting | show processlist |
+----+------+-----------+-------+---------+------+----------+------------------+
1 row in set (0.00 sec)
2.用户基础操作
#添加用户
mysql> create user ceshi@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
#查看表中字段
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
#检查用户
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| ceshi | 10.0.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
#修改用户密码
mysql> alter user ceshi@'10.0.0.%' identified by '456';
Query OK, 0 rows affected (0.00 sec)
#删除用户
mysql> drop user ceshi@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
#创建app用户登录数据库，查看权限及回收权限
mysql> grant select,update on app.* to app@'10.0.0.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.01 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> select user,host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| app | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
mysql>
查看权限
mysql> show grants for app@'10.0.0.%';
+-----------------------------------------------------+
| Grants for app@10.0.0.% |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'10.0.0.%' |
| GRANT SELECT, UPDATE ON `app`.* TO 'app'@'10.0.0.%' |
+-----------------------------------------------------+
2 rows in set (0.00 sec)
回收权限
mysql> revoke update on app.* from app@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for app@'10.0.0.%';
+---------------------------------------------+
| Grants for app@10.0.0.% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'app'@'10.0.0.%' |
| GRANT SELECT ON `app`.* TO 'app'@'10.0.0.%' |
+---------------------------------------------+
2 rows in set (0.00 sec)
mysql>
3.查看权限
mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)
4.修改数据库密码
数据密码修改
测试
[root@localhost ~]# systemctl stop mysql
[root@localhost ~]# mysqld_safe --skip-grant-tables --skip-networking &
[1] 7629
[root@localhost ~]# 2024-11-25T09:02:00.281481Z mysqld_safe Logging to '/data/mysql/localhost.localdomain.err'.
2024-11-25T09:02:00.301422Z mysqld_safe Starting mysqld daemon with databases from /data/mysql
[root@localhost ~]#
[root@localhost ~]# mysql -uroot -p
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user root@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> exit;
Bye
[root@localhost ~]#
[root@localhost ~]# systemctl restart mysql
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql>
5.mysql参数
mysql参数
-u 用户
-p 密码
-h IP
-P 端口
-S socket文件
-e 免交互执行命令
< 导入SQL脚本
[root@localhost ~]# mysql -uroot -p -h 10.0.0.51 -P3306
Enter password:
mysql> select @@socket;
+-----------------+
| @@socket |
+-----------------+
| /tmp/mysql.sock |
[root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock
Enter password:
[root@localhost ~]# mysql -uroot -p -e "show processlist"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 5 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
[root@localhost ~]# mysql -uroot -p -e "select user,host from mysql.user"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-----------+
| user | host |
+---------------+-----------+
| app | 10.0.0.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# mysql -uroot -p <world.sql
Enter password:
[root@localhost ~]#
导入数据,需要提前登录数据库
mysql> source /root/ps.sql
