Quote from: mason64 on February 22, 2025, 09:52:02 AM
Hi
is it possible to access mysql database with out going in the control panel for the user. i have a user who wants to backup his mysql database to his home linux server using a bash script. we dont need help with the script but wondering if i can access myphpadmin without logging in to the users control panel
Hope this makes sense.
Thanks
Dave
Yes, it is possible do it with a script.
But not without a little added complexity.
1) Open the 3306 port remotely to your user.
If your user have a fixed IP address, open just for that IP in CSF firewall.
If your user haven't a fixed IP address, you can use a portknocking scheme, in CSF firewall.
2) Allow the MariaDB user to remotely access directly the database:
in file /etc/my.cnf.d/server.cnf, change the line (just uncomment it):
#bind-address=0.0.0.0
to
bind-address=0.0.0.0
3) May be you prefer to create a new user do just remote backups (a readonly user, with just SELECT permission):
You can create a new user account that will only connect from the remote host (fixed IP remote_server_ip) with the following command:
MariaDB [(none)]> CREATE USER 'new_user'@'remote_server_ip' IDENTIFIED BY 'password';
Then grant the new user the appropriate privileges for your particular needs. I recomment a readonly user, with:
MariaDB [(none)]> GRANT SELECT on `Database_name`.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;
Replace Database_Name with the backuped database name, and remote_server_ip with a FIX IP address.
But, if it have a dynamic IP (CAUTION: using ONLY with portknocking scheme) use:
MariaDB [(none)]> CREATE USER 'new_user'@'%' IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT SELECT on `Database_name`.* TO 'sammy'@'%' WITH GRANT OPTION;
After changes of privileges in MariaDB, run:
MariaDB [(none)]> FLUSH PRIVILEGES;
One more word of caution: open your port 3306 to the internet only to someone with a fixed IP address, or don't open it at all without using the portknocking scheme.
To your user access with portknocking, your customer can install 'nmap' program, and use:
#!/bin/bash
remotehost=12.23.34.45
for x in 555 111 444 333; do sudo nmap -p $x $remotehost > /dev/null; done
where 12.23.34.45 is the IP of the your main server, and 555, 111, 444 and 333 are the ports numbers used in your portknocking scheme.
CAUTION: Do not use these ports numbers, change it to your taste.
After the port are opened, your user can install and use mariadb-dump, mariadb-backup or mysqldump programs to make their backups.
Regards,
Netino
Don't do following:
Quote
MariaDB [(none)]> CREATE USER 'new_user'@'remote_server_ip' IDENTIFIED BY 'password';
Then grant the new user the appropriate privileges for your particular needs. I recomment a readonly user, with:
Code: [Select]
MariaDB [(none)]> GRANT SELECT on `Database_name`.* TO 'sammy'@'remote_server_ip' WITH GRANT OPTION;
Replace Database_Name with the backuped database name, and remote_server_ip with a FIX IP address.
But, if it have a dynamic IP (CAUTION: using ONLY with portknocking scheme) use:
Code: [Select]
MariaDB [(none)]> CREATE USER 'new_user'@'%' IDENTIFIED BY 'password';
MariaDB [(none)]> GRANT SELECT on `Database_name`.* TO 'sammy'@'%' WITH GRANT OPTION;
After changes of privileges in MariaDB, run:
Code: [Select]
MariaDB [(none)]> FLUSH PRIVILEGES;
Instead, Manage all SQL users using the CWP panel. You can add remote SQL users using CWP panel (See options offered by "MySQL manager").
Sorry, I'm going to chime in and say DON'T DO THIS:
Quote from: Netino on February 24, 2025, 02:56:21 AM
1) Open the 3306 port remotely to your user.
If your user have a fixed IP address, open just for that IP in CSF firewall.
If your user haven't a fixed IP address, you can use a portknocking scheme, in CSF firewall.
2) Allow the MariaDB user to remotely access directly the database:
in file /etc/my.cnf.d/server.cnf, change the line (just uncomment it):
#bind-address=0.0.0.0
to
bind-address=0.0.0.0
Best practice is to bind to a local socket and only allow local connections. Do not allow remote TCP connections to your MySQL/MariaDB server as is standard security recommendation.
If the user has static IP address then the port 3306 can be firewalled and opened for specific IP only. However, it is a point of attention.
If the user's IP is dynamic then VPN can be used.