I want to be able to copy a mysqdump from a remote server to my local machine.

I had a script to do this, but our operations guys have now changed the server permissions and it doesn't work.

So before I could SSH from my machine login as root and supply a password and connect to mysql, so I had a command in a Perl script like so :

sshpass -p root_password ssh root@$servername  mysqldump $database_name > $mysqldump_location

Anyway, permissions have changed and I can't login using root from my local machine.

Now I need to

  • login to the remote server with a deploy user.
  • Once I am logged in, I need to use su - to get the root user
  • Now I can execute the mysqldump command (the deploy user gets an access denied message)

I want to have a script or command that I can execute from my local machine and have it go through the steps - log in as deploy, switch to root, create a mysql dump and copy it to my local machine.

What is the best way to go about that? Is it possible with SSH tunnels? Or is a scripted option better (how do you script multiple logins)?

  • Any reason for the downvotes? – wobbily_col Sep 25 '18 at 9:14

You're probably best to use ansible for this: https://docs.ansible.com/ansible/2.5/modules/mysql_db_module.html

...which will take care of sudo'ing and 'how' the dump is done.

Example, a mysqldump.yml playbook contains:

- hosts: all
  gather_facts: false
  serial: 1
- name: Dump all databases to hostname.sql
    state: dump
    name: all
    target: /tmp/{{ inventory_hostname }}.sql

...then target your mysql box, sudo'ing (-kK) like so:

$ ansible-playbook -i ${servername}, mysqldump.yml -kK -v
SSH password: <your non-root password on target>
SUDO password[defaults to SSH password]: <usually hit return>
  • That was one of my options. Last time I tried Ansible it was a bit of a hassle to get ssh set up, but if its the best way maybe I'll try again. – wobbily_col Sep 25 '18 at 7:34

You don’t need to be root on the Linux OS to be able to run MySQL commands as root , you can simply log in to the MySQL root account from your personal Linux accountby adding the root username and the root password to your script (and maybe change the file system location where you write the database dump).

Even better create a dedicated user for MySQL backups so you won’t need excessive DBA privileges for a simple backup ..

CREATE USER 'backup'@'localhost' IDENTIFIED BY 's3cr3t-p@ssw0rd';

mysqldump --single-transaction which can be used with INNODB tables requires the following privileges:


mysqldump --lock-all-tables for MyISAMB tables additionally requires the LOCK TABLES role:

GRANT LOCK TABLES ON *.* TO 'backup'@'localhost';
  • I have tried this, but I don't think its set up to use anyone but root@localhost to access the database. I can't even use -u root, I need to actua;lly be logged in as root for this to work. – wobbily_col Sep 25 '18 at 7:33
  • 1
    MySQL is probably configured to use socket authentication for the root account, which indeed requires one to be root on the Linux server to access the database root account, but that doesn't prevent you from creating a backup account with read-only privileges to facilitate backups – HBruijn Sep 25 '18 at 8:07
  • The problem is that teh environments are designed to to be reproducible via puppet (I don't have access to the puppet code). They regularly get broken and resinstalled, which would loose any extra accounts I have set up. – wobbily_col Sep 25 '18 at 8:55

You could use a RSA Key to login directly to root, if that's an option.

To allow login directly to root with RSA key:

  • Edit the file /etc/sshd/sshd_config and set PermitRootLogin without-password (If you use AllowUsers, put root in there, too)
  • Restart SSH: service sshd restart (it may be service ssh restart, onerestart, it depends)

After that, generate your RSA key (local machine) and put it into authorized_keys (remote server):

  • In your local machine, run ssh-keygen and you'll be prompted for where to put the file, set password, etc; just hit enter until it's done
  • Copy the content of id_rsa.pub inside /root/.ssh (or /home/youruser/.ssh if not root)
  • Go to your remote server and su to root
  • Edit the file /root/.ssh/authorized_keys (create it if it doesn't exist)
  • Paste your key at the end of the file and save it
  • Go back to your machine and try to login directly to root@remoteserver.
  • 1
    One should never login as root directly if at all possible. In the event of a security breach you want as much of an audit trail as possible. Allowing ssh as root removes a lot of that audit trail. Please use sudo. – toppledwagon Sep 21 '18 at 18:59
  • Yes, you are right. I only set RSA keys between servers that are closed to external access (firewall allowing only trusted IPs to SSH on them). – Asdra Sep 21 '18 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.