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

服务器上Ubuntu16.04上进行安装 # 安装mysql ```bash :~$ sudo apt-get install mysql-server ``` 配置root密码   # 配置mysql ```bash :~$ 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） ```cnf port = 30306 # 设置字符集 character-set-server=utf8 # 禁止 # bind-address = 127.0.0.1 ``` # 重启服务 ```bash ：/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 ``` 开启防火墙（可不用配置） ```bash sudo iptables -A INPUT -p tcp --dport 30306 -j ACCEPT ``` 通过`telnet xxx.xxx.xxx.xxx 30306`测试端口是否正常访问 # 添加用户userflaskmovie访问mysql的flaskmovie数据库 如果只允许本地访问，需要将`%`替换为`localhost`或`127.0.0.1` ## 添加新用户 ```sql :~$ mysql -uroot -p Enter password: mysql> create user 'userflaskmovie'@'%' identified by 'userflaskmovie'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; ``` ## 创建数据库用户新用户 ```sql 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**数据库的全部权限 ```sql 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) ``` ## 新用户登录出错 ```bash :~$ 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';` 实际操作 ```sql 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 ```sql mysql> create database DB-DjangoProjec; ``` ## 创建用户 ```sql mysql> create user 'DjangoProjecDBtUser'@'%' identified by 'DjangoProjecDBtUser'; ``` ## 分配数据库权限 ```sql 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) ``` 创建完成后测试正常连接就行 # 连接方式  ```bash >mysql -h xxx.xxx.xxx.xxx -P 30306 -u userflaskmovie -p Enter password: ************** mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | flaskmovie | +--------------------+ 2 rows in set (0.01 sec) ``` # 数据默认存储 ``` /usr/bin 客户端程序和脚本 /usr/sbin mysqld 服务器 /var/lib/mysql 日志文件，数据库 ［重点要知道这个］ /usr/share/doc/packages 文档 /usr/include/mysql 包含( 头) 文件 /usr/lib/mysql 库 /usr/share/mysql 错误消息和字符集文件 /usr/share/sql-bench 基准程序 ```

明人不说暗话 领红包就是支持！！！ 来都来了，再到页面底部看下呗~ ↓ ↓ ↓

本文地址： http://blog.starmeow.cn/detail/17e62166fc8586dfa4d1bc0e1742c08b/

很赞哦！ (0)