mysql远程访问权限

Mariadb 清理

sudo systemctl stop mariadb
sudo apt-get remove --purge mariadb-server mariadb-client mariadb-common
sudo rm -rf /etc/mysql/ /var/lib/mysql/ /var/log/mysql/
sudo apt-get autoremove && sudo apt-get autoclean

mysql clear

sudo apt-get remove mysql-*
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

mysql install
sudo apt-get install mysql-client mysql-server
 

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'new_password';

mysql> 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
mysql> 

所以如果想要用户root可以远程登录，则可通过修改user表中root用户对应的host字段值为“%”即可。我们用以下语句进行修改：

update user set host = '%' where user = 'root';

mysql> select host,user from user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | myuser        |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

mysql> update user set host='%' where user='root'
    -> ;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 

授予所有权限

当然，如果要让root对所有数据库操作权限，还需给root用户进行授权：

grant all privileges on *.* to 'root'@'%';

grant all privileges  on *.* to tesot@'%' identified by "pwd";

pwd为root用户的密码，请根据实际值进行修改。

mysql> flush privileges
    -> ;