Home / server / Questions / 932075
In Process
wobbily_col
Asked: 2018-09-22 07:32:57 +0800 CST

SSH to server as one user, switch to root and create mysql dump - how to script this

  • 772

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

ssh

3 Answers

  1. 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
  mysql_db:
    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>
    • 2

  2. 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: 

    GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'backup'@'localhost';

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

    GRANT LOCK TABLES ON *.* TO 'backup'@'localhost';
    • 1

  3. 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.
    • 0