What is the configuration of MySQL MariaDB password and remote access permissions
This article to share with you is about MySQL MariaDB password and remote access permissions configuration is how, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some gains, not much to say, follow Xiaobian to see it.
environment
MySQL Version Server version: 5.5.64-MariaDB MariaDB Server
Linux version CentOS Linux release 7.6.1810 (AltArch)
operation
1) root By default, login without password, enter directly, enter password directly
root@host-172-16-61-102 bin]# mysql -uroot -p
PASSWORD:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 5.5.64-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
2)No database selected
MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
3) Specify that the password of root user is root
MariaDB [mysql]> update user set password=password("root")where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 5 Changed: 0 Warnings: 0
Note:
If the execution is invalid, it may be a version problem. Check whether the password field or the authentication_string field stores the password.
update user set password = password("root"),authentication_string=password("root") where user=root;
The desc user command allows you to view the current user table structure.
4) Grant access
root uses password to connect to mysql server from any host
MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
where BY is followed by the root password:root
Only 192.168.10.168 is allowed to connect to mysql server via root
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.10.168' IDENTIFIED BY 'root' WITH GRANT OPTION;
5) Refresh permissions
MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Note:
1)Navicat remote login failed
1045 - Access denied for user 'root@192.168.63.184' (using password: YES)
Grant access permission is not executed. Go to Step 4.
Select host,user from user; to see if you have remote access.
2)turn off the firewall
systemctl stop firewall.service #Stop firewall
systemctl disable firewall d.service #disable firewall boot
opening port
firewall-cmd --zone=public --add-port=3306/tcp --permanent
mysql service starts, you can check whether to listen through port lsof -i:3306
Restart firewall
firewall-cmd --reload
The above is how MySQL MariaDB password and remote access permission configuration are. Xiaobian believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.
