Создание БД и удаленного пользователя

[George]

my.cnf (my.ini on windows)
#Replace xxx with your IP Address
bind-address        = xxx.xxx.xxx.xxx
Then:
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypass';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'mypass';
Then:
GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';
FLUSH PRIVILEGES;
Depending on your OS, you may have to open port 3306 to allow remote connections.


firewall-cmd --zone=public --add-port=3000/tcp --permanent

Because of your password. You can see password validate configuration metrics using the following query in MySQL client:
SHOW VARIABLES LIKE 'validate_password%';
The output should be something like that :
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
Now that the rules for a valid password are clear, you could chose a valid password.
To check the strength of the password you chose, use the VALIDATE_PASSWORD_STRENGTH() function, for example:
SELECT VALIDATE_PASSWORD_STRENGTH('weak');
+------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('weak') |
+------------------------------------+
|                                 25 |
+------------------------------------+
SELECT VALIDATE_PASSWORD_STRENGTH('lessweak$_@123');
+----------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('lessweak$_@123') |
+----------------------------------------------+
|                                           50 |
+----------------------------------------------+
SELECT VALIDATE_PASSWORD_STRENGTH('N0Tweak$_@123!');
+----------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('N0Tweak$_@123!') |
+----------------------------------------------+
|                                          100 |
+----------------------------------------------+
Alternatively, you can lower the password policy level or change the validation rules, for example:
SET GLOBAL validate_password.length = 6;
SET GLOBAL validate_password.number_count = 0;


sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Copy
Search for a line that begins with bind-address and set its value to the IP address on which a MySQL server should listen.

By default, the value is set to 127.0.0.1 (listens only in localhost).

In this example, we'll set the MySQL server to listen on all IPv4 interfaces by changing the value to 0.0.0.0

mysqld.cnf
bind-address           = 0.0.0.0
# skip-networking
Copy
If there is a line containing skip-networking, delete it or comment it out by adding # at the beginning of the line.

In MySQL 8.0 and higher, the bind-address directive may not be present. In this case, add it under the [mysqld] section.

Once done, restart the MySQL service for changes to take effect. Only root or users with sudo privileges can restart services.

To restart the MySQL service on Debian or Ubuntu, type:

sudo systemctl restart mysql
Copy
On RedHat based distributions like CentOS to restart the service run:

sudo systemctl restart mysqld
Copy
Granting Access to a User from a Remote Machine
The next step is to allow access to the database to the remote user.

Log in to the MySQL server as the root user by typing:

sudo mysql
Copy
If you are using the old, native MySQL authentication plugin to log in as root, run the command below and enter the password when prompted:

mysql -uroot -p
Copy
From inside the MySQL shell, use the GRANT statement to grant access to the remote user.

GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password';
Copy
Where:

database_name is the name of the database that the user will connect to.
user_name is the name of the MySQL user.
ip_address is the IP address from which the user will connect. Use % to allow the user to connect from any IP address.
user_password is the user password.
For example, to grant access to a database dbname to a user named foo with password my_passwd from a client machine with IP 10.8.0.5, you would run:

GRANT ALL ON dbname.* TO foo@'10.8.0.5' IDENTIFIED BY 'my_passwd';
Copy
Configuring Firewall
The last step is to configure your firewall to allow traffic on port 3306 (MySQL default port) from the remote machines.

Iptables
If you are using iptables as your firewall, the command below will allow access from any IP address on the Internet to the MySQL port. This is very insecure.

sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT
Copy
Allow access from a specific IP address:

sudo iptables -A INPUT -s 10.8.0.5 -p tcp --destination-port 3306 -j ACCEPT
Copy
UFW
UFW is the default firewall tool in Ubuntu. To allow access from any IP address on the Internet (very insecure), run:

sudo ufw allow 3306/tcp
Copy
Allow access from a specific IP address:

sudo ufw allow from 10.8.0.5 to any port 3306
Copy
FirewallD
FirewallD is the default firewall management tool in CentOS. To allow access from any IP address on the Internet (very insecure) type:

sudo firewall-cmd --permanent --zone=public --add-port=3306/tcp
sudo firewall-cmd --reload
CopyCopy
To allow access from a specific IP address on a specific port, you can either create a new FirewallD zone or use a rich rule. Well create a new zone named mysqlzone:

sudo firewall-cmd --new-zone=mysqlzone --permanent
sudo firewall-cmd --reload
sudo firewall-cmd --permanent --zone=mysqlzone --add-source=10.8.0.5/32
sudo firewall-cmd --permanent --zone=mysqlzone --add-port=3306/tcp
sudo firewall-cmd --reload
CopyCopyCopyCopyCopy
Verifying the Changes
To verify that the remote user can connect to the MySQL server, run the following command:

mysql -u user_name -h mysql_server_ip -p
Copy
Where user_name is the name of the user you granted access to, and mysql_server_ip is the IP address of the host where the MySQL server runs.