我的博客，欢迎阅读 https://blog.starmeow.cn
服务器上Ubuntu16.04上进行安装
安装mysql
:~$ sudo apt-get install mysql-server
配置root密码
image.png
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)
创建完成后测试正常连接就行
连接方式
image.png
>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 基准程序
网友评论