mysql在阿里云ubuntu服务器的配置过程

本文首发地址：https://www.dawnsite.cn/archives/164.html

1. MySQL配置

1.1 安装MySQL

• apt-get install mysql-server

1.2 配置MySQL

• 初始化配置mysql_secure_installation

1. VALIDATE PASSWORD PLUGIN can be used to test passwords... Press y|Y for Yes, any other key for No: N (我的选项)
   

2. Please set the password for root here...
   New password: (输入密码)
   Re-enter new password: (重复输入)
   

3.  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...
    Remove anonymous users? (Press y|Y for Yes, any other key for No) : N (我的选项)
   

4.  Normally, root should only be allowed to connect from
    'localhost'. This ensures that someone cannot guess at
    the root password from the network...
    Disallow root login remotely? (Press y|Y for Yes, any other key for No) : Y (我的选项)
   

5.  By default, MySQL comes with a database named 'test' that
    anyone can access...
    Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N (我的选项)
   

6.  Reloading the privilege tables will ensure that all changes
    made so far will take effect immediately.
    Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y (我的选项)
   

• 检查mysql服务状态systemctl status mysql.service,显示如下图则表示没有问题

1.3 配置MySQL远程访问

• 首先编辑数据库文件vim /etc/mysql/mysql.conf.d/mysqld.cnf注释掉bind-address = 127.0.0.1

• 进入mysql数据库mysql -u root -p之后输入之前配置的密码即可

• 进入之后执行以下命令

  use mysql
  select user,host from user;                          # 查询user表中数据
  update user set host='%' where user='root';          # 修改root账号的host字段为：%
  
  # 给任意主机root账户连接mysql服务器权限：
  GRANT ALL PRIVILEGES ON *.*  TO 'root'@'%' IDENTIFIED BY 'root的密码' WITH GRANT OPTION;
  flush privileges;  # 刷新权限表，使配置生效                                  
  

• 重启mysql服务sudo /etc/init.d/mysql restart

如果出现1064 错误：

修改加密规则：alter user '用户名'@'%' identified with mysql_native_password by '密码';

刷新权限：flush privileges;

重启mysql