Solved

Centos

Posted on 2013-11-26
3
226 Views
Last Modified: 2014-01-14
Hi i need to backup all my mysql database using ssh. How i can do it. I have 68 databases and i know how i can do it one by one. I need some solution to do it automatic
0
Comment
Question by:umaxim
3 Comments
 
LVL 40

Assisted Solution

by:omarfarid
omarfarid earned 167 total points
ID: 39679729
you can do this in two ways:

1- set trust (no passwords to login remotely, it will be based on keys generated and exchanged between the systems) between your account on your system and the account on the remote system.

please see

http://www.phys.ufl.edu/cms/emu/fast/FAST-DAQ/ssh_between_2.html

Then you can use

ssh username@hostname1 "command"

The above can be automated with a loop to go through different remote servers

2- If you don't want to set trust between systems, then you can use expect to automate login (response to password prompts etc.

Please see link below

http://www.admin-magazine.com/Articles/Automating-with-Expect-Scripts
0
 
LVL 19

Assisted Solution

by:xterm
xterm earned 166 total points
ID: 39679837
I've put a little script below that you can modify - it will do an incremental backup over rsync so it will only copy what has changed since last time you backed up.

To run it each night at 1AM, put the following in /etc/crontab:
0 1 * * * root /path/to/mybackup.sh

Currently, it will prompt you for your password each time, so before you cron it, you will need to set up passwordless ssh.  This is pretty simple.  Go into the .ssh folder in the home directory of the user who will be doing the backup (probably root, but perhaps mysql) and find the file called id_rsa.pub  Now copy that file over to the backup server, into the home directory of the user that you're logging in as.  Find the file .ssh/authorized_keys and append your public key to the end of that file with this command:

cat id_rsa.pub >> authorized_keys

The next time you ssh in, it should no longer prompt you for a password.  BTW, if the file authorized_keys did not exist previously, you can still use the command above to create it.  But you will need to do this command to give it the right permissions:

chmod 600 authorized_keys

Let us know if you get stuck, and we'll help you through it!

#!/bin/sh

# mybackup.sh (sample name for this script)

# PUT ALL THE DATABASE FOLDERS YOU WANT IN HERE
DBDIRS="/var/lib/mysql /other/mysql/directory"
KEEPARGS="--delete"

for DBDIR in `echo $DBDIRS`;
do
        rsync -az $KEEPARGS $DBDIR -e 'ssh' myusername@otherhostname:/backups/databases
done

Open in new window

0
 
LVL 15

Accepted Solution

by:
Insoftservice earned 167 total points
ID: 39686502
Why can't you use sqlyog or some gui tool.
I am using sqlyog as it has feature of schedule backup and making zip of it.
so, file size issue gets automatically resolved
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
LINUX, CPANEL & WHM 5 44
Python variable _ manually assigned 9 64
CentOS 7 Installation 7 59
VLC Video Player No Longer Works after installing HandBrake, Fedora 25 5 126
rdate is a Linux command and the network time protocol for immediate date and time setup from another machine. The clocks are synchronized by entering rdate with the -s switch (command without switch just checks the time but does not set anything). …
If you have a server on collocation with the super-fast CPU, that doesn't mean that you get it running at full power. Here is a preamble. When doing inventory of Linux servers, that I'm administering, I've found that some of them are running on l…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now