mysql远程读取本地文件_配置MySQL以进行本地和远程访问

I'm using MySQL server version: 10.1.23-MariaDB-9+deb9u1 Raspbian 9.0 on a Raspberry Pi.

This is my /etc/mysql/my.cnf:

# The MariaDB configuration file

#

# The MariaDB/MySQL tools read configuration files in the following order:

# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,

# 2. "/etc/mysql/conf.d/*.cnf" to set global options.

# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.

# 4. "~/.my.cnf" to set user-specific options.

#

# If the same option is defined multiple times, the last one will apply.

#

# One can use all long options that the program supports.

# Run program with --help to get a list of available options and with

# --print-defaults to see which it would actually understand and use.

#

# This group is read both both by the client and the server

# use it for options that affect everything

#

[client-server]

# Import all .cnf files from configuration directory

!includedir /etc/mysql/conf.d/

!includedir /etc/mysql/mariadb.conf.d/

#bind-address = 0.0.0.0

I've tried this:

sudo mysql_secure_installation

Change root password: y

Password

Retyped password

Remove anonymous users: y

Disallow root login remotely: n

Remove test database: y

Reload priviledges: y

CREATE USER 'root'@'%.%.%.%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%.%.%.%' WITH GRANT OPTION;

FLUSH PRIVILEDGES;

service mysql restart

In my.cnf, with bind-address commented, I can access from localhost, but not from remote host using SQL Workbench.

With bind-address uncommented, I cannot access from localhost, but I can access from remote host using SQL Workbench, e.g.:

mysql -u root

mysql: unknown variable 'bind-address=0.0.0.0'

This is my users table:

MariaDB [(none)]> select user, host, password from mysql.user;

+------+-----------+-------------------------------------------+

| user | host | password |

+------+-----------+-------------------------------------------+

| root | localhost | *054D119DEAD56E226D8356557796BFA72E71BA40 |

| root | %.%.%.% | *054D119DEAD56E226D8356557796BFA72E71BA40 |

| root | % | *054D119DEAD56E226D8356557796BFA72E71BA40 |

+------+-----------+-------------------------------------------+

How do I configure the server to allow local and remote access for root from any IP?

解决方案

Add a line over bind-address = 0.0.0.0 with [mysqld] like:

[mysqld]

bind-address = 0.0.0.0

In your case both, the server and the local client, reads the bind address and the client want to connect to the IP 0.0.0.0