linux+mysql基本命令大全_Linux系统常用MySql命令

初始化数据库

[root@localhost opt]# mysqld --initialize --console

目录授权，否则可能启动失败

[root@localhost opt]# chown -R mysql:mysql /var/lib/mysql

启动mysql服务

[root@localhost opt]# systemctl start mysqld

将mysql设置为开机启动

[root@localhost opt]# systemctl enable mysqld

[root@localhost opt]# systemctl daemon-reload

查看mysql服务状态

[root@localhost opt]# service mysqld statu

查看/var/log/mysqld.log下临时密码

[root@localhost opt]# cat /var/log/mysqld.log

2020-12-10T05:31:16.940900Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server in progress as process 97488

2020-12-10T05:31:19.154333Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: w9pMMHj1t!Og

2020-12-10T05:31:20.983866Z 0 [System] [MY-013170] [Server] /usr/sbin/mysqld (mysqld 8.0.13) initializing of server has completed

2020-12-10T05:36:36.359331Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 103690

2020-12-10T05:36:36.877462Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.

2020-12-10T05:36:36.905049Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.13' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.

2020-12-10T05:36:36.976593Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 3306

用临时密码登录下数据库

[root@localhost opt]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 8.0.13

Copyright (c) 2000, 2018, 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> ^C

mysql>

修改root的密码

mysql> alter USER 'root'@'localhost' IDENTIFIED BY 'test.123456'

授权远程链接数据库

查看mysql上的数据库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.01 sec

进入到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> select host,user,authentication_string,plugin from user;

+-----------+------------------+------------------------------------------------------------------------+-----------------------+

| host | user | authentication_string | plugin |

+-----------+------------------+------------------------------------------------------------------------+-----------------------+

| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |

| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |

| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |

| localhost | root | $A$005$A_a&fF#l,mBHDe8FvuOlkY6UaLgfKV.DWw4yfIIDIQ4jG2QWvZDqGTmC | caching_sha2_password |

+-----------+------------------+------------------------------------------------------------------------+-----------------------+

4 rows in set (0.00 sec)

host为localhost的说明只能本地连接mysql服务，修改root用户的host值，使root可以远程登录

mysql> update user set host='%' where user='root';

Query OK, 1 row affected (0.07 sec)

Rows matched: 1 Changed: 1 Warnings: 0

刷新

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

开启mysql远程访问权限

mysql> use mysql

Database changed

mysql> alter USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'test.123456';

Query OK, 0 rows affected (0.08 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)