external backup of mysql database to linux server using bash

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

To access PHPMyAdmin without accessing CWP use:
https://cpanel.DOMAIN.COM/pma/

use the db_user assigned to the database you want to access as the username and db_user_password to access the database.

Additionally, you can access the databases from shell or using remote SQL management tools like HeidiSQL. To access the databases remotely, make sure the db_user assigned to the database is allowed to connect to the database remotely (see "host" in MySQL manager when edit/add user).

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):

Code: [Select]

#bind-address=0.0.0.0
to

Code: [Select]

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:

Code: [Select]

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;

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:

Code: [Select]

#!/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:

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").

mysqldump and scp if you don't want to open you mysql to the world.

Sorry, I'm going to chime in and say DON'T DO THIS:

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):

Code: [Select]

#bind-address=0.0.0.0
to

Code: [Select]

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.