Ubuntu16.04安装mysql5.7及配置远程访问

2018-10-28 本文已影响12人吾星喵

服务器上Ubuntu16.04上进行安装

安装mysql

:~$ sudo apt-get install mysql-server

配置root密码

image.png

配置mysql

:~$ whereis mysql
mysql: /usr/bin/mysql /usr/lib/mysql /etc/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz

:~$ cd /etc/mysql/

:/etc/mysql$ ls -l
total 24
drwxr-xr-x 2 root root 4096 Oct 23 11:41 conf.d
-rw------- 1 root root  317 Oct 23 11:41 debian.cnf
-rwxr-xr-x 1 root root  120 Jul 28 01:58 debian-start
lrwxrwxrwx 1 root root   24 Oct 23 11:41 my.cnf -> /etc/alternatives/my.cnf
-rw-r--r-- 1 root root  839 Jan 22  2017 my.cnf.fallback
-rw-r--r-- 1 root root  682 Feb  4  2017 mysql.cnf
drwxr-xr-x 2 root root 4096 Oct 23 11:41 mysql.conf.d

:/etc/mysql$ cd mysql.conf.d/

:/etc/mysql/mysql.conf.d$ ls
mysqld.cnf  mysqld_safe_syslog.cnf

:/etc/mysql/mysql.conf.d$ sudo vim mysqld.cnf

修改mysql端口号为：30306（端口号的范围从0到65535，mysql默认端口号3306）

port            = 30306
# 设置字符集
character-set-server=utf8

# 禁止
# bind-address          = 127.0.0.1

重启服务

：/etc/mysql/mysql.conf.d$ service mysql restart

:/etc/mysql/mysql.conf.d$ netstat -an | grep 3306
:/etc/mysql/mysql.conf.d$ netstat -an | grep 30306
tcp6       0      0 :::30306                :::*                    LISTEN

开启防火墙（可不用配置）

sudo iptables -A INPUT -p tcp --dport 30306 -j ACCEPT

通过telnet xxx.xxx.xxx.xxx 30306测试端口是否正常访问

添加用户userflaskmovie访问mysql的flaskmovie数据库

如果只允许本地访问，需要将%替换为localhost或127.0.0.1

添加新用户

:~$ mysql -uroot -p
Enter password: 

mysql> create user 'userflaskmovie'@'%' identified by 'userflaskmovie';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

创建数据库用户新用户

mysql> create database flaskmovie DEFAULT CHARSET utf8 COLLATE utf8_general_ci;


mysql> create database flaskmovie;
Query OK, 1 row affected (0.00 sec)

为新用户分配该数据库权限

授予用户通过外网IP对于flaskmovie数据库的全部权限

mysql> grant all privileges on `flaskmovie`.* to 'userflaskmovie'@'%' identified by 'userflaskmovie';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for userflaskmovie;
+----------------------------------------------------------------+
| Grants for userflaskmovie@%                                    |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'userflaskmovie'@'%'                     |
| GRANT ALL PRIVILEGES ON `flaskmovie`.* TO 'userflaskmovie'@'%' |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)

新用户登录出错

:~$ mysql -uuserflaskmovie -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'userflaskmovie'@'localhost' (using password: YES)

解决办法：

在mysql5.7以下的版本如下：mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root' and host='127.0.0.1' or host='localhost'; 把空的用户密码都修改成非空的密码就行了

在mysql5.7版本如下：update mysql.user set authentication_string=password('newpassword') where user='root' and host='127.0.0.1' or host='localhost';

实际操作

mysql> select * from mysql.user where user='userflaskmovie' \G;
*************************** 1. row ***************************
                  Host: %
                  User: userflaskmovie
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *1F31DAC15FBB9CBB8993D317275DC22CE21D359E
      password_expired: N
 password_last_changed: 2018-10-23 13:19:54
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

mysql> update mysql.user set authentication_string=password('userflaskmovie') where user='userflaskmovie' and host='%';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 1
mysql> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1681 | 'PASSWORD' is deprecated and will be removed in a future release. |
+---------+------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

添加用户DjangoProjecDBtUser访问mysql的DB-DjangoProjec数据库

创建数据库DB-DjangoProjec

mysql> create database DB-DjangoProjec;

创建用户

mysql> create user 'DjangoProjecDBtUser'@'%' identified by 'DjangoProjecDBtUser';

分配数据库权限

mysql> grant all privileges on `DB-DjangoProjec`.* to 'DjangoProjecDBtUser'@'%' identified by 'DjangoProjecDBtUser';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)



mysql> show grants for DjangoProjecDBtUser;
+------------------------------------------------------------------------+
| Grants for DjangoProjecDBtUser@%                                        |
+------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'DjangoProjecDBtUser'@'%'                         |
| GRANT ALL PRIVILEGES ON `DB-DjangoProjec`.* TO 'DjangoProjecDBtUser'@'%' |
+------------------------------------------------------------------------+
2 rows in set (0.00 sec)

创建完成后测试正常连接就行

连接方式