Extract SQL files.

English Programming/Scripting
1

Hello to @ll who reads this.

I’m a little-server administrator (learning) and was, untill some weeks ago, always manual creating backups and so on, after that the backups are rsync’ed to the backup server.

All was well, untill I got many databases on one server.

with this script you can extract all databases to one directory.


#!/usr/bin/perl
use Getopt::Long;
use Term::ANSIColor qw(:constants);

sub MyPrint {
  my ($txt)  = shift; my ($bld)  = shift;
  if($bld eq 1) { print BOLD, $txt, RESET; }
  else { print($txt); }
  }
sub MyExec {
  my ($exe) = shift; my ($dbg) = shift;
  if($dbg eq 1) { MyPrint("Execute : ", 1); print qq($exe 
); }
  else { qx($exe); }
  }


my $dbnm  = "test";

my $user  = "root";
my $pass  = "";
my $hlp   = "";
my $debug = 0;
my $dir   = ".";

$result = GetOptions ("user|u=s" => \$user, "password|p=s" => \$pass, "help|h" => \$hlp, "test|t" => \$debug, "dir|d=s" => \$dir);

if ($hlp eq 1) {
  MyPrint("
Usage:

", 1);
  MyPrint("	sqlbkup -u user -p password -t -d directory 

", 0);
  MyPrint("Explanation:

", 1);
  MyPrint("	-u user to login
", 0);
  MyPrint("	-p MySQL root password
", 0);
  MyPrint("	-d Directory to store sql files. Default is the directory where you are.
", 0);
  MyPrint("	-t Test / Debug mode

", 0);
  exit;
  };

  
use DBI;
my $dbh = DBI->connect("dbi:mysql:database=$dbnm;host=localhost", $user, $pass, {'RaiseError' => 1});
@databases = $dbh->func('_ListDBs');

foreach $db (@databases) {
  unless (($db eq 'mysql') or ($db eq 'information_schema') or ($db eq 'performance_schema') or ($db eq 'test')) { 
    MyPrint("
Copying DB: ", 1); MyPrint($db."
", 0);
    
    MyExec("mysqldump ".$db." > ".$dir."/".$db.".sql", $debug);
    }
  }
  
MyPrint("

", 0);

Save all code to a file named: sqlbkup, and copy this file to /usr/local/bin

Do not forget to change the userrights with: chmod +x /usr/local/bin/sqlbkup

cd to the directory, where you wonna store your sql files. then invoke sqlbkup with:

sqlbkup -p root_pw_of_sqlserver

That’s it. :slight_smile:

A complete backup script will follow.

Best regards,

Marjon

2

Nice, thanks for sharing

3

Marjon wrote:
> Hello to @ll who reads this.
>
> I’m a little-server administrator (learning) and was, untill some
> weeks ago, always manual creating backups and so on, after that the
> backups are rsync’ed to the backup server.
>
> All was well, untill I got many databases on one server.
>
> with this script you can extract all databases to one directory.
>
>
> Code:
> --------------------
>
> #!/usr/bin/perl

use strict;
use warnings;

or use Modern::Perl;

> use Getopt::Long;
> use Term::ANSIColor qw(:constants);

4

Nice script; as a general note, it would probably be best to make sure
this works (if it does not already) with no password since while this is
running any user on the system can see the ‘root’ DB user’s password via
the ‘ps’ command:

Code:

ps aux | grep sqlbkup

Most systems have ways around this; in MySQL’s case that way is usually
creating a ‘root’ user, only allows from 127.0.0.1, which has read rights
to all of the to-be-backed-up databases. As a result you can
authenticate, if you’re on the DB server itself, as root for the sole
purpose of reading the database. Anybody else who happens to be on the
system at the time of the backup, or any user who happens to find a cron
job where all of this is stored, doesn’t get a more-powerful ‘root’ user’s
(or any other user’s) password.

Good luck.

5

SQL extraction without the need of typing the password over and over, when you create a backup, is actually no problem at all.

Create a file named: .my.cnf
in the directory: /root

You can do this by: touch /root/.my.cnf

After that, put the following in this file:


[client]
password  = your_pw_here
socket    = /var/run/mysql/mysql.sock

that should do the trick.