记录曲折的ElesticSearch+ES环境安装过程程(五)——Mysql安装篇

下载安装：

#命令1
sudo apt-get update
#命令2
sudo apt-get install mysql-server

初始化：

sudo 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 (我的选项)

查看运行状态：

systemctl status mysql.service

配置远程访问

在Ubuntu下MySQL默认仅允许本地访问，需要配置远程访问：

sudo mysql -uroot -p

输入本机root的密码后进入mysql命令行，查看下当前用户：

SELECT CURRENT_USER();

修改root密码：

CREATE USER 'root'@'%' IDENTIFIED BY '你的密码';
ALTER USER 'root'@'localhost' IDENTIFIED BY '320320';

或者：SET PASSWORD FOR ‘root’@’%’ = ‘320320’;

重新登陆下

然后设置远程访问，使用授权法

# 授权法 
# 允许192.168.10.105连接访问所有库的所有表(*.*)，连接密码为320320
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.2.151' IDENTIFIED BY '320320' WITH GRANT OPTION;
# 允许所有连接访问所有库的所有表(*.*)，连接密码为320320
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '320320' WITH GRANT  OPTION; 
# 刷新权限 
FLUSH PRIVILEGES; 

报错：

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 ‘320320’ WITH GRANT OPTION’ at line 1

提示意思是不能用grant创建用户，mysql8.0以前的版本可以使用grant在授权的时候隐式的创建用户，mysql8.0以后已经不支持，所以必须先创建用户，然后再授权，命令如下

mysql> CREATE USER 'root'@'localhost' IDENTIFIED BY '320320';
Query OK, 0 rows affected (0.48 sec)

mysql> grant all privileges on *.* to 'root'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.48 sec)

mysql> CREATE USER 'root'@'%' IDENTIFIED BY '320320';
Query OK, 0 rows affected (0.48 sec)

mysql> grant all privileges on *.* to 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.48 sec)

FLUSH PRIVILEGES; 

然后发现远程链接不上：

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2

经过大量时间排查发现，是因为文件/etc/mysql/mysql.conf.d/mysqld.cnf中 默认参数的bind_address问题：

注释掉这两行后，使用ps -ef|grep mysql 查到mysql的进程pid，然后kill掉，接着运行指令重启musql服务：

sudo service mysql start

这下远程链接数据库，成功了

mysql其他操作

• 显示某用户权限

要显示特定用户的权限，您可以在MySQL命令行中执行以下查询：

SHOW GRANTS FOR 'username'@'hostname';

• linux测试mysql是否能连通

要测试Linux上的MySQL是否能够连通，您可以使用以下命令来尝试连接到MySQL服务器：

mysql -h 192.168.2.229 -u username -p

• 重启

sudo service mysql restart

查询所有用户

SELECT User, Host FROM mysql.user;