ubunt18 安装mysql8, 并开启远程登录_ubuntu18 mysql8.0允许远程连接

阿里云平台，ubuntu18 安装mysql8

1、预安装先学卸载， 随便说一句，ubuntu内置的python2.7不要瞎搞卸载，会死的

Ubuntu18.10卸载MySQL: https://blog.csdn.net/qq_42280544/article/details/83796832

安装mysql8，https://blog.csdn.net/qq_42468130/article/details/88595418(在Ubuntu 18.04中安装MySQL 8.0数据库: https://www.linuxidc.com/Linux/2018-11/155408.htm)

输入show databases;
如果出现Ignoring query to other database，

mysql -root -p // Faulty connection
Instead of ...
mysql -uroot -p // Correct connection
1
2
3

解决办法链接：https://stackoverflow.com/questions/36132063/ignoring-query-to-other-database-command-line

1、防火墙

linux系列_UFW的简单使用

ufw status, 查看防火墙状态

2、开启远程

参考：
MySql8.X设置允许远程登陆 https://blog.csdn.net/sinat_37167645/article/details/80301076

第二步遇到ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY 'pswd' WITH GRANT OPTION' at line 1

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root_passwd';
1
2
3

解决办法链接：https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0

3、设置密码：

执行 mysql， 进入mysql > 执行命令 set password for root@localhost = password('passwd');

win10修改mysql8密码: (有更好的链接会补充，目前下面的代码可以实现修改)

win10 修改mysql8密码：
mysql 使用无密码模式登录：
1、无密码模式进入sql： 'mysql -uroot'
2、带密码模式进入sql： 'mysql -uroot -p你的密码'
依次执行命令:
	ALTER USER 'root'@'localhost' IDENTIFIED BY '你的密码';
	FLUSH PRIVILEGES;
1
2
3
4
5
6
7

4、不能使用group by ：

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wanlsuzho
解决办法：https://www.cnblogs.com/jim2016/p/6322703.html

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
查询出来的值为：
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

去掉ONLY_FULL_GROUP_BY，重新设置值
set @@GLOBAL.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set @@SESSION.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

1
2
3
4
5
6
7
8
9
10

全库备份与恢复

908、备份

在cmd：cmd>
备份数据库：

mysqldump -uroot -p --all-databases > /root/backup.sql
1

909、恢复

在mysql：mysql>
恢复数据表：

source /root/backup.sql
1

---

指定IP地址和端口号登录MySQL数据库
命令格式为：mysql -h ip -u root -p -P 3306
例如：mysql -h 127.0.0.1 -u root -p -P 3306
-h 指定 IP地址
-P 指定端口