Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.