Mariadb remote login configuration and problem solving

Preface:

The installation process will not be described in detail, let's talk about the problem directly. There are two problems that need to be solved for the remote connection of MySQL: 1. Allow the root user to connect remotely. 2. Allow any IP to remotely connect to the database. Of course, before testing and solving the problem, you must first ensure that there is no problem with the network communication between your database and the remote host. Simply put, they can ping each other. Secondly, to avoid interference from the firewall, turn off the firewall of both the local host and the database host. Of course, the firewall must be turned on in the production environment, and additional security configuration is required.

Problem Solved:

1. The newly installed database needs to be initialized by default. When the database service is started, use the following command to initialize it.

[root@localhost ~]# mysql_secure_installation
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] 
 ... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n #If you are configuring remote login for the root user, you need to select n here. If you do not select Disallow root user remote login, the other options are irrelevant.
 ... skipping.

By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] 
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] 
 ... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB
The installation should now be secure.

Thanks for using MariaDB![root@localhost ~]# systemctl restart mariadb #After initialization, restart the service.

2. Allowing the root user to connect remotely and allowing any IP to connect remotely to the database can be achieved by executing a command in the database.

There are two situations here:

1) Create a new admin user to remotely connect to the MySQL database (create any user, taking admin as an example)

grant all on *.* to admin@'%' identified by '123456' with grant option;
flush privileges;

Allow any computer with an IP address (% means any IP address is allowed) to access this MySQL server using the admin account and password (123456).
Note that the admin account does not have to exist.

2) Support root user to allow remote connection to MySQL database

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;

It should be noted that when configuring remote login for the root user, the password must be the same as the previously configured password.

3. Additional configuration of Ubuntu system.

The my.cnf file of the Ubuntu system is in vim /etc/mysql/mysql.conf.d/mysqld.cnf, comment out

bind-address = 127.0.0.1

By default, there is no such line in the centos system configuration file.

The method of checking is also very simple. On the premise that the database is started, use netstat -an | grep 3306 to view the connection information of the port. 0.0.0.0 means that any IP connection is allowed.

As shown in the figure, any IP connection is allowed.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.