ubuntu 安装 mysql

安装MySQL：

apt install mysql-server

安装完成之后用如下命令来检查是否安装成功：

netstat -tap | grep mysql

登录mysql数据库通过如下命令：

mysql -u root -p

-u 表示选择登陆的用户名， -p 表示登陆的用户密码，现在是mysql数据库是没有密码的，Enter password:处直接回车，就能够进入mysql数据库。

然后通过 show databases; 就可以查看当前的所有数据库。

在sql命令行输入以下命令回车，你就可以把密码改成mynewpassword

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword';

为了确保数据库的安全性和正常运转，对数据库进行初始化操作。

对于上述数据库初始化的操作步骤，在下面的输出信息旁边做了简单注释。

root@ubuntu-virtual-machine:~# mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin? #要安装验证密码插件吗?

Press y|Y for Yes, any other key for No: N # 这里我选择N
Please set the password for root here.

New password: #输入要为root管理员设置的数据库密码

Re-enter new password: #再次输入密码

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y #删除匿名账户
Success.

现在配置mysql允许远程访问

vim /etc/mysql/mysql.conf.d/mysqld.cnf

注释掉bind-address = 127.0.0.1

root@VM-8-2-ubuntu:~# mysql -u root -p
 

报错：grant all on *.* to hive@localhost identified by 'hive'; ERROR 1064 (42000): You have an error in yo

时用下面 建新用户 访问即可

mysql> create user 'admin'@'%' identified by '123456';
Query OK, 0 rows affected (0.82 sec)

mysql> grant all on *.* to 'admin'@'%';
Query OK, 0 rows affected (0.23 sec)

或者给某个库的权限

grant all on material_platform.* to 'admin'@'%';

mysql>grant all on material_platform.* to 'admin'@'%'; 

给普通用户授予查看所有用户线程/连接的权限

不然会报错

Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

mysql>flush privileges;
mysql>exit
systemctl restart mysql

修改密码：

ALTER USER 'admin'@'%' IDENTIFIED BY '12399';

mysql时间精确到毫秒

将 datetime 长度设置为3，即可精确到毫秒。(如果没有设置3，则默认是精确到秒)

CURRENT_TIMESTAMP(3) 表示 插入数据时默认当前数据库的时间，到毫秒。如果不加长度3，默认是到秒。

CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) 表示更新数据时自动设置为数据库的当前时间，精确到毫秒。