Telnet MYSQL/MARIADB Database Configuration tutorial

If you want to separate the WEB server from the database server, you need to configure the database server so that MYSQL/MARIADB can log on remotely.

Assume:

Web Server IP Address: 192.168.33.61
Database server IP Address: 192.168.33.63
Server operating system: CentOS 7

First step: Edit MY.CNF

MY.CNF is the MYSQL/MARIADB configuration file, the name is generally MY.CNF:

Vi/etc/my.cnf

Under [Mysqld], find bind-address, and if not, add a line:

[Mysqld]
bind-address=192.168.33.63

The value behind the bind-address is set to the IP address of the MYSQL/MARIADB database server, preferably with a private IP address so that the database server is not made public and is known to everyone. Save the configuration, and then restart the service.

Systemctl Restart MARIADB

Step Two: Add remote users

Now that the database service can accept a remote connection, but no user has permission to connect remotely, you will need to manually add a remote user and log on to the database service with the root user:

Mysql-u root-p

Then go to create a database:

Create Database Drupal;

To create a new user, note that this user is a user that can be used on a Web server, so you need to specify the IP address of a Web server:

Create user ' Drupal ' @ ' 192.168.33.61 ' identified by ' Drupal ';

That is, Drupal, the user, can only connect to the database server on the server that 192.168.33.61 this IP address. To assign permissions to this user:

Grant all privileges in drupal.* to ' Drupal ' @ ' 192.168.33.61 ';

Take effect immediately and then execute:

Flush privileges;

Step Three: Remote connection

On the WEB server, try to log on to the database system on the database server:

Mysql-u drupal-h 192.168.33.63-p

Here we use the-H option to specify the IP address of the server to log on to. This IP address is the IP address of the database server.

If you see an error similar to the following:

ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.33.63 ' (113 "No route to host")

It is possible that the firewall on the database server is configured for the reason that the CentOS 7 is configured with a firewall FIREWALLD:

Firewall-cmd--zone=public--add-port=3306/tcp

When you are done, try connecting to the database server again on the WEB server.

Add: # # MARIADB Create user

> I assume you've done the initialization of the database (disable root remote access), and then connect the database locally with the root account.

Mysql-u root-p MySQL

You can view the current user below:

Select User,host,password from user;

[Mysql]>drop user ' @ ' localhost '; --Delete unsafe accounts

[mysql]>drop user root@ ':: 1 ';

[Mysql]>drop user root@127.0.0.1;

Mariadb I never know how to create a user, but I think, should also like PostgreSQL, have their own create the user's command is. But Google did not, had to be honest to see the official document.
MARIADB The syntax for creating a user is:

CREATE User User

[identified by [PASSWORD] ' PASSWORD ']

[, USER [identified by [PASSWORD] ' PASSWORD ']]

...