blib/lib/Mail/Toaster/Mysql.pm untested

filter: Statementstmt 0.0% 0 / 362 Branchbran 0.0% 0 / 174 Conditioncond 0.0% 0 / 67 MC/DCmcdc n/a Subroutinesub 0.0% 0 / 34 Podpod 0.0% 0 / 25 totaltotal 0.0% 0 / 662 SCAR 89.7
CC
88
Cov
0%
install_freebsd
272.0
parse_dot_file
156.0
phpmyadmin_install
156.0
CRAP 7832.0
1 package Mail::Toaster::Mysql;
2 use strict;
3 use warnings;
4  
5 our $VERSION = '5.50';
6  
7 use Carp;
8 #use DBI; # eval'ed in connect
9 use Params::Validate ':all';
10 use English '-no_match_vars';
11  
12 use lib 'lib';
13 use parent 'Mail::Toaster::Base';
14  
15 sub autocommit {
16 my ($self,$dot) = @_;
17 return 1 if ! $dot->{autocommit};
18 return 1 if $dot->{autocommit} ne '';
19 return $dot->{'autocommit'};
20 }
21  
22 sub backup {
23 my ( $self, $dot ) = @_;
24  
25 unless ( ref $dot eq 'HASH' ) {
26 print "FATAL, you passed backup a bad argument!\n";
27 return 0;
28 }
29  
30 my $verbose = $dot->{'verbose'};
31 my $backupfile = $dot->{'backupfile'} || "mysql_full_dump";
32 my $backupdir = $dot->{'backup_dir'} || "/var/backups/mysql";
33  
34 $self->audit( "backup: beginning mysql_backup.\n" );
35  
36 my $cronolog = $self->util->find_bin( "cronolog" );
37 my $mysqldump = $self->util->find_bin( "mysqldump" );
38  
39 my $mysqlopts = "--all-databases --opt --password=" . $dot->{'pass'};
40 my ( $dd, $mm, $yy ) = $self->util->get_the_date( verbose => $verbose );
41  
42 print "backup: backup root is $backupdir.\n" if $verbose;
43  
44 $self->util->cwd_source_dir( "$backupdir/$yy/$mm/$dd" );
45  
46 $self->audit( "backup: backup file is $backupfile.\n" );
47  
48 if ( -e "$backupdir/$yy/$mm/$dd/$backupfile"
49 || -e "$backupdir/$yy/$mm/$dd/$backupfile.gz" )
50 {
51 $self->audit( "backup: backup for today is already done, ok (skipped)" );
52 }
53  
54 # dump the databases
55 my $cmd =
56 "$mysqldump $mysqlopts | $cronolog $backupdir/%Y/%m/%d/$backupfile";
57 $self->audit("backup: running $cmd");
58 $self->util->syscmd( $cmd );
59  
60 # gzip the backup to greatly reduce its size
61 my $gzip = $self->util->find_bin( "gzip" );
62 $cmd = "$gzip $backupdir/$yy/$mm/$dd/$backupfile";
63 $self->audit("backup: running $cmd");
64 $self->util->syscmd( $cmd );
65 }
66  
67 sub binlog_on {
68  
69 my ( $self, $db_mv ) = @_;
70  
71 if ( $db_mv->{log_bin} ne "ON" ) {
72 print <<EOBINLOG;
73  
74 Hey there! In order for this server to act as a master, binary logging
75 must be enabled! Please edit /etc/my.cnf or $db_mv->{datadir}/my.cnf and
76 add "log-bin". You must also set server-id as documented at mysql.com.
77  
78 EOBINLOG
79 return 0;
80 }
81  
82 return 1;
83 }
84  
85 sub connect {
86 my ( $self, $dot, $warn, $verbose ) = @_;
87  
88 $self->util->install_module( "DBI", verbose => $verbose );
89 $self->util->install_module( "DBD::mysql", verbose => $verbose );
90  
91 my $ac = $self->autocommit($dot);
92 my %v = $self->db_vars($dot);
93 my $dsn = "DBI:$v{driver}:database=$v{db};host=$v{host};port=$v{port}";
94  
95 eval 'use DBI'; ## no critic ( ProhibitStringyEval )
96 return $self->error($@) if $@;
97  
98 my $dbh = DBI->connect( $dsn, $v{user}, $v{pass},
99 { RaiseError => 0, AutoCommit => $ac } ) or do {
100  
101 carp "db connect failed: $!\n" if $verbose;
102 croak unless $warn;
103 return;
104 };
105  
106 my $drh = DBI->install_driver( $v{driver} );
107  
108 return ( $dbh, $dsn, $drh );
109 }
110  
111 sub db_vars {
112 my ( $self, $val ) = @_;
113  
114 return (
115 driver => $val->{driver} || 'mysql',
116 db => $val->{database} || $val->{db} || 'mysql',
117 host => $val->{host} || 'localhost',
118 port => $val->{port} || '3306',
119 user => $val->{user} || 'root',
120 pass => $val->{password} || $val->{pass} || '',
121 dir => $val->{dir_m} || '/var/db/mysql',
122 );
123 }
124  
125 sub dbs_list {
126 my ( $self, $dbh ) = @_;
127  
128 if ( my $sth = $self->query( $dbh, "SHOW DATABASES" ) ) {
129 while ( my ($db_name) = $sth->fetchrow_array ) { print "$db_name "; }
130  
131 if ( $sth->err ) { print "FAILED!\n"; }
132 else { $sth->finish; print "\n"; }
133 }
134  
135 ### Documented (but non-working methods for listing databases ###
136 # my @databases = $drh->func($db_mv->{'host'}, $db_mv->{'port'}, '_ListDBs');
137 # print "mysql_info->databases:\t@databases\n";
138 #
139 # my @databases2 = DBI->data_sources("mysql");
140 # print "mysql_info->databases2:\t@databases2\n";
141 }
142  
143 sub defaults {
144 my $self = shift;
145  
146 if ( -e "/etc/my.cnf" ) {
147 $self->audit( "mysql->defaults: checking my.cnf, ok (exists)" );
148 return 1;
149 }
150  
151 $self->audit( "mysql->defaults: checking my.cnf, MISSING" );
152  
153 if ( -e "/usr/local/share/mysql/my-large.cnf" ) {
154 use File::Copy;
155 copy( "/usr/local/share/mysql/my-large.cnf", "/etc/my.cnf" );
156  
157 if ( -e "/etc/my.cnf" ) {
158 $self->audit( "mysql->defaults: installing my.cnf, ok" );
159 print "\n\n\tI just installed a default /etc/my.cnf\n";
160 print "\n\tPlease review it for sanity in your environment!\n\n";
161 sleep 3;
162 }
163 else {
164 $self->audit( "mysql->defaults: installing my.cnf, FAILED" );
165 }
166 }
167 }
168  
169 sub flush_logs {
170 my ( $self, $dbh, $verbose ) = @_;
171  
172 my $query = "FLUSH LOGS";
173 my $sth = $self->query( $dbh, $query );
174 $sth->finish;
175  
176 return { error_code => 200, error_desc => "logs flushed successfully" };
177 }
178  
179 sub get_hashes {
180 my ( $self, $dbh, $sql ) = @_;
181 my @records;
182  
183 if ( my $sth = $self->query( $dbh, $sql ) ) {
184 while ( my $ref = $sth->fetchrow_hashref ) {
185 push @records, $ref;
186 }
187 $sth->finish;
188 }
189 return @records;
190 }
191  
192 sub install {
193 my $self = shift;
194  
195 # only install if install_mysql is set to a value we recognize
196 my $ver = $self->conf->{install_mysql} or do {
197 $self->audit( "skipping MySQL install, not selected.");
198 return;
199 };
200  
201 if ( lc($OSNAME) eq "darwin" ) {
202 $self->audit( "detected OS " . $OSNAME . ", installing for Darwin.");
203 return $self->install_darwin;
204 };
205  
206 if ( lc($OSNAME) eq "freebsd" ) {
207 return $self->install_freebsd;
208 };
209  
210 print "\nskipping MySQL, build support on $OSNAME is not available."
211 . "Please install MySQL manually.\n";
212 return;
213 };
214  
215 sub install_darwin {
216 my $self = shift;
217  
218 return if $OSNAME ne "darwin";
219  
220 if ( $self->util->find_bin( "port", verbose=>0) ) {
221 $self->darwin->install_port( "mysql5" );
222 $self->darwin->install_port( "p5-dbi" );
223 $self->darwin->install_port( "p5-dbd-mysql" );
224 return 1;
225 }
226  
227 croak "DarwinPorts is not installed.\n";
228 }
229  
230 sub install_freebsd_extras {
231 my $self = shift;
232  
233 if ( $self->conf->{install_mysqld} ) {
234  
235 $self->freebsd->conf_check(
236 check=>"mysql_enable",
237 line=>"mysql_enable=\"YES\"",
238 );
239  
240 $self->defaults();
241 $self->startup;
242 };
243  
244 $self->freebsd->install_port( "p5-DBI" );
245 $self->freebsd->install_port( "p5-DBD-mysql" );
246  
247 return 1;
248 }
249  
250 sub install_freebsd {
251 my ($self) = @_;
252  
253 my @ports = qw/ mysql-client /;
254 push @ports, 'mysql-server' if $self->conf->{install_mysqld};
255  
256 my $installed = 0;
257 foreach ( @ports ) {
258 $installed++ if $self->freebsd->is_port_installed( $_, verbose => 0 );
259 };
260  
261 if ($installed == scalar @ports ) {
262 $self->audit( "mysql->install: MySQL is installed" );
263 return $self->install_freebsd_extras;
264 };
265  
266 # MySQL is not installed, lets do it!
267 my $flags = "SKIP_DNS_CHECK";
268 $flags .= ",BUILD_OPTIMIZED" if $self->conf->{'install_mysql_optimized'};
269  
270 my $dir = $self->conf->{'install_mysql_dir'};
271 if ( $dir && $dir ne "/var/db/mysql" ) { $flags .= ",DB_DIR=$dir"; };
272  
273 my $check;
274  
275 my $ver = $self->conf->{install_mysql};
276  
277 if ( $ver =~ /^3|323$/ ) { $dir = "323"; $check = "3.23"; }
278 elsif ( $ver =~ /^4|40$/ ) { $dir = "40"; $check = "4.0"; }
279 elsif ( $ver =~ /^41|4.1$/ ) { $dir = "41"; $check = "4.1"; }
280 elsif ( $ver =~ /^50|5.0$/ ) { $dir = "50"; $check = "5"; }
281 elsif ( $ver =~ /^51|5.1$/ ) { $dir = "51"; $check = "5"; }
282 elsif ( $ver =~ /^55|5.5$/ ) { $dir = "55"; $check = "5"; }
283 else { $dir = "51"; $check = "5"; }
284  
285 @ports = 'client';
286 push @ports, 'server' if $self->conf->{install_mysqld};
287  
288 foreach ( @ports ) {
289 $self->freebsd->install_port( "mysql$dir-$_",
290 check => "mysql-$_-$check",
291 flags => $flags,
292 );
293 };
294  
295 return $self->error( "MySQL install FAILED" )
296 if !$self->freebsd->is_port_installed( "mysql-client" );
297  
298 if ( ! $self->conf->{install_mysqld} ) {
299 $self->audit( "installing MySQL client, ok" );
300 return $self->install_freebsd_extras;
301 };
302  
303 return $self->error( "MySQL install FAILED" )
304 if !$self->freebsd->is_port_installed( "mysql-server" );
305  
306 $self->audit( "installing MySQL client and server, ok" );
307 return $self->install_freebsd_extras;
308 };
309  
310 sub is_newer {
311  
312 my ( $self, $min, $cur ) = @_;
313  
314 $min =~ /^([0-9]+)\.([0-9]{1,})\.([0-9]{1,})$/;
315 my @mins = ( $1, $2, $3 );
316 $cur =~ /^([0-9]+)\.([0-9]{1,})\.([0-9]{1,})$/;
317 my @curs = ( $1, $2, $3 );
318  
319 if ( $curs[0] > $mins[0] ) { return 1; }
320 if ( $curs[1] > $mins[1] ) { return 1; }
321 if ( $curs[2] > $mins[2] ) { return 1; }
322  
323 return 0;
324 }
325  
326 sub parse_dot_file {
327  
328 my ( $self, $file, $start, $verbose ) = @_;
329  
330 my ($homedir) = ( getpwuid($REAL_USER_ID) )[7];
331 my $dotfile = "$homedir/$file";
332  
333 return if ! -e $dotfile;
334  
335 if ( !-r $dotfile ) {
336 carp "WARNING: parse_dot_file: can't read $dotfile!\n";
337 return 0;
338 }
339  
340 my %array;
341 my $gotit = 0;
342  
343 print "parse_dot_file: $dotfile\n" if $verbose;
344 foreach ( $self->util->file_read( $dotfile, verbose=>$verbose ) ) {
345  
346 next if /^#/;
347 my $line = $_;
348 chomp $line;
349 if ($gotit) {
350 if ( $line =~ /^\[/ ) { last }
351 print "2. $line\n" if $verbose;
352 $line =~ /(\w+)\s*=\s*(.*)\s*$/;
353 $array{$1} = $2 if $1;
354 }
355 else {
356 print "1. $line\n" if $verbose;
357 if ( $line eq $start ) {
358 $gotit = 1;
359 next;
360 }
361 }
362 }
363  
364 if ($verbose) {
365 foreach my $key ( keys %array ) {
366 print "hash: $key\t=$array{$key}\n";
367 }
368 }
369  
370 return \%array;
371 }
372  
373 sub phpmyadmin_install {
374 my ( $self ) = @_;
375  
376 if ( ! $self->conf->{'install_phpmyadmin'} ) {
377 print "phpmyadmin: install is disabled. Enable install_phpmyadmin in "
378 . "toaster-watcher.conf and try again.\n";
379 return;
380 }
381  
382 my $dir;
383  
384 if ( $OSNAME eq "freebsd" ) {
385  
386 $self->freebsd->install_port( "phpmyadmin", check => "phpMyAdmin");
387 $dir = "/usr/local/www/data/phpMyAdmin";
388  
389 # the port moved the install location
390 unless ( -d $dir ) { $dir = "/usr/local/www/phpMyAdmin"; }
391 }
392 elsif ( $OSNAME eq "darwin" ) {
393  
394 print
395 "NOTICE: the port install of phpmyadmin requires that Apache be installed in ports!\n";
396 $self->darwin->install_port( "phpmyadmin" );
397 $dir = "/Library/Webserver/Documents/phpmyadmin";
398 }
399  
400 if ( !-e $dir ) {
401 print "FAILURE: phpMyAdmin installation failed.\n";
402 return 0;
403 }
404  
405 print "installed successfully. Now configuring....";
406 unless ( -e "$dir/config.inc.php" ) {
407  
408 my $user = $self->conf->{'phpMyAdmin_user'} || "pma";
409 my $pass = $self->conf->{'phpMyAdmin_pass'} || "pmapass";
410 my $auth = $self->conf->{'phpMyAdmin_auth_type'} || "cookie";
411  
412 $self->util->syscmd( "cp $dir/config.inc.php.sample $dir/config.inc.php" );
413  
414 my @lines = $self->util->file_read( "$dir/config.inc.php" );
415 foreach (@lines) {
416  
417 chomp;
418 if (/(\$cfg\['blowfish_secret'\] =) ''/) {
419 $_ = "$1 'babble, babble, babble blowy fish';";
420 }
421 elsif (/(\$cfg\['Servers'\]\[\$i\]\['controluser'\])/) {
422 $_ = "$1 = '$user';";
423 }
424 elsif (/(\$cfg\['Servers'\]\[\$i\]\['controlpass'\])/) {
425 $_ = "$1 = '$pass';";
426 }
427 elsif (/(\$cfg\['Servers'\]\[\$i\]\['auth_type'\])/) {
428 $_ = "$1 = '$auth';";
429 }
430 }
431 $self->util->file_write( "$dir/config.inc.php", lines => \@lines );
432  
433 my $dot = { user => 'root', pass => '' };
434 if ( $self->connect( $dot, 1 ) ) {
435  
436 my ( $dbh, $dsn, $drh ) = $self->connect( $dot, 1 );
437  
438 my $query =
439 "GRANT USAGE ON mysql.* TO '$user'\@'localhost' IDENTIFIED BY '$pass'";
440 my $sth = $self->query( $dbh, $query );
441 $query =
442 "GRANT SELECT ( Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
443 Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv) ON mysql.user TO '$user'\@'localhost'";
444 $sth = $self->query( $dbh, $query );
445 $query = "GRANT SELECT ON mysql.db TO '$user'\@'localhost'";
446 $sth = $self->query( $dbh, $query );
447 $query = "GRANT SELECT ON mysql.host TO '$user'\@'localhost'";
448 $sth = $self->query( $dbh, $query );
449 $query =
450 "GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv) ON mysql.tables_priv TO '$user'\@'localhost'";
451 $sth = $self->query( $dbh, $query );
452 $sth->finish;
453  
454 #$dbh->close;
455 }
456 else {
457 print <<EOGRANT;
458  
459 NOTICE: You need to log into MySQL and run the following comands:
460  
461 GRANT USAGE ON mysql.* TO '$user'\@'localhost' IDENTIFIED BY '$pass';
462 GRANT SELECT (
463 Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
464 Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
465 File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
466 Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
467 Execute_priv, Repl_slave_priv, Repl_client_priv
468 ) ON mysql.user TO '$user'\@'localhost';
469 GRANT SELECT ON mysql.db TO '$user'\@'localhost';
470 GRANT SELECT ON mysql.host TO '$user'\@'localhost';
471 GRANT SELECT (Host, Db, User, Table_name, Table_priv, Column_priv)
472 ON mysql.tables_priv TO '$user'\@'localhost';
473  
474 EOGRANT
475 }
476 }
477 return 1;
478 }
479  
480 sub query {
481 my ( $self, $dbh, $query, $warn ) = @_;
482  
483 my $sth;
484 if ( $sth = $dbh->prepare($query) ) {
485 $sth->execute or carp "couldn't execute: $sth->errstr\n";
486  
487 #$dbh->commit or carp "couldn't commit: $sth->errstr\n";
488 return $sth;
489 }
490  
491 no warnings;
492 return $self->error( "couldn't prepare: $dbh::errstr", fatal => 0);
493 return $sth;
494 }
495  
496 sub query_confirm {
497  
498 my ( $self, $dbh, $query, $verbose ) = @_;
499  
500 if ( $self->util->yes_or_no("\n\t$query \n\n Does this query look correct? ") )
501 {
502 my $sth;
503 if ( $sth = $self->query( $dbh, $query ) ) {
504 $sth->finish;
505 print "\nQuery executed successfully.\n" if $verbose;
506 }
507 print "\nQuery execute FAILED.\n";
508 return 0;
509 }
510 }
511  
512 sub sanity {
513  
514 my ( $self, $dot ) = @_;
515  
516 if ( !$dot->{'user'} ) {
517 croak
518 "\n\nYou have not configured ~/.my.cnf. Read the FAQ before proceeding.\n\n";
519 }
520  
521 if ( length( $dot->{'user'} ) > 16 ) {
522 croak
523 "\n\nUsername cannot exceed 16 characters. Edit user in ~/.my.cnf\n\n";
524 }
525  
526 if ( !$dot->{'pass'} ) {
527 croak
528 "\nYou have not configured ~/.my.cnf properly. Read the FAQ before proceeding.\n\n";
529 }
530  
531 if ( length( $dot->{'pass'} ) > 32 ) {
532 croak
533 "\nPassword cannot exceed 16 characters. Edit pass in ~/.my.cnf\n\n";
534 }
535 }
536  
537 sub shutdown_mysqld {
538  
539 my ( $self, $db_v, $drh, $verbose ) = @_;
540 my $rc;
541  
542 print "shutdown: shutting down mysqld $db_v->{'host'}..." if $verbose;
543  
544 if ($drh) {
545 $rc = $drh->func(
546 'shutdown', $db_v->{'host'},
547 $db_v->{'user'}, $db_v->{'pass'},
548 'admin'
549 );
550 }
551 else {
552 ( my $dbh, my $dsn, $drh ) = $self->connect( $db_v, 1 );
553 unless ($drh) {
554 print "shutdown_mysqld: FAILED: couldn't connect.\n";
555 return 0;
556 }
557 $rc = $drh->func(
558 'shutdown', $db_v->{'host'},
559 $db_v->{'user'}, $db_v->{'pass'},
560 'admin'
561 );
562 }
563  
564 if ($verbose) {
565 print "shutdown->rc: $rc\n";
566 $rc ? print "success.\n" : print "failed.\n";
567 }
568  
569 if ($rc) {
570 return {
571 error_code => 200,
572 error_desc => "$db_v->{'host'} shutdown successful"
573 };
574 }
575 else {
576 return { error_code => 500, error_desc => "$drh->err, $drh->errstr" };
577 }
578 }
579  
580 sub startup {
581 my $self = shift;
582  
583 if ( -e "/tmp/mysql.sock" || -e "/opt/local/var/run/mysqld/mysqld.sock" ) {
584 $self->audit( "mysql->startup: starting MySQL, ok (already started)" );
585 return 1;
586 }
587  
588 my $etc = $self->conf->{'system_config_dir'} || "/usr/local/etc";
589  
590 my $start = "$etc/rc.d/mysql-server";
591 if ( !-e $start && -e "$etc/rc.d/mysql-server.sh" ) {
592 $start = "$etc/rc.d/mysql-server.sh";
593 }
594 if ( !-e $start && -e "$etc/init.d/mysql" ) { $start = "$etc/init.d/mysql" }
595 if ( !-e $start && -e "$etc/init.d/mysql-server" ) {
596 $start = "$etc/init.d/mysql-server";
597 }
598 if ( !-e $start && -e "$etc/rc.d/mysql" ) { $start = "$etc/rc.d/mysql" }
599 if ( !-e $start && -e "$etc/rc.d/mysql.sh" ) {
600 $start = "$etc/rc.d/mysql.sh";
601 }
602  
603 if ( -x $start ) {
604 $self->util->syscmd( "sh $start start", verbose=>0 );
605 $self->audit( "mysql->startup: starting MySQL, ok" );
606 }
607 else {
608 $self->audit( "mysql->startup: starting MySQL, FAILED" );
609 print "\t\tcould not find startup file.\n";
610 return 0;
611 }
612  
613 return 1;
614 }
615  
616 sub status {
617 my ( $self, $dbh ) = @_;
618  
619 unless ($dbh) {
620 print "FAILED: no database handle passed to status()!\n";
621 return 0;
622 }
623  
624 if ( my $sth = $self->query( $dbh, "SHOW STATUS" ) ) {
625 while ( my $r = $sth->fetchrow_arrayref ) {
626 print "\t\t\t $r->[0] \t $r->[1]\n";
627 }
628 $sth->finish;
629 }
630 }
631  
632 sub tables_lock {
633  
634 my ( $self, $dbh, $verbose ) = @_;
635  
636 # Table locking is done at the per-thread level. If we did a $sth->finish
637 # the thread would end and we'd lose our lock. So, instead we pass the $sth
638 # handle back and close it after we've done our deeds.
639  
640 print "lock_tables: locking tables.\n" if $verbose;
641  
642 if ( my $sth = $self->query( $dbh, "FLUSH TABLES WITH READ LOCK" ) ) {
643 return $sth;
644 }
645 }
646  
647 sub tables_unlock {
648  
649 my ( $self, $dbh, $sth, $verbose ) = @_;
650  
651 print "tables_unlock: unlocking mysql tables.\n" if $verbose;
652  
653 my $query = "UNLOCK TABLES"; # unnecessary, simply calling finish does this
654  
655 $sth = $self->query( $dbh, $query )
656 or croak "FATAL: couldn't unlock tables: $sth->errstr\n";
657  
658 $sth->finish;
659 }
660  
661 sub version {
662  
663 my ( $self, $dbh ) = @_;
664 my ( $sth, $minor );
665  
666 if ( $sth = $self->query( $dbh, "SELECT VERSION()" ) ) {
667 my $r = $sth->fetchrow_arrayref;
668 ($minor) = split( /-/, $r->[0] );
669 $sth->finish;
670 }
671  
672 return $minor;
673 }
674  
675 1;
676 __END__
677  
678  
679 =head1 NAME
680  
681 Mail::Toaster::Mysql - so much more than just installing mysql
682  
683 =head1 SYNOPSIS
684  
685 Functions for installing, starting, stopping, querying, and otherwise interacting with MySQL.
686  
687  
688 =head1 DESCRIPTION
689  
690 I find myself using MySQL for a lot of things. Geographically distributed dns systems (MySQL replication), mail servers, and all the other fun stuff you'd use a RDBMS for. As such, I've got a growing pile of scripts that have lots of duplicated code in them. As such, the need for this perl module grew.
691  
692 Currently used in:
693 mysql_replicate_manager v1.5+
694 uron.net user_*.pl
695 polls.pl
696 nt_export_djb_update.pl
697 toaster_setup.pl
698  
699  
700 =head1 SUBROUTINES
701  
702 =over
703  
704 =item new
705  
706 use Mail::Toaster::Mysql;
707 my $mysql = Mail::Toaster::Mysql->new();
708  
709  
710 =item autocommit
711  
712  
713 =item backup
714  
715 Back up your mysql databases
716  
717 $mysql->backup( $dot );
718  
719 The default location for backups is /var/backups/mysql. If you want them stored elsewhere, set backupdir = /path/to/backups in your .my.cnf (as shown in the FAQ) or pass it via -d on the command line.
720  
721 You will need to have cronolog, gzip, and mysqldump installed in a "normal" location. Your backups will be stored in a directory based on the date, such as /var/backups/mysql/2003/09/11/mysql_full_dump.gz. Make sure that path is configured to be backed up by your backup software.
722  
723 arguments required:
724 dot - a hashref of values from a .my.cnf file
725  
726  
727 =item connect
728  
729 my ($dbh, $dsn, $drh) = $mysql->connect($dot, $warn, $verbose);
730  
731 $dot is a hashref of key/value pairs in the same format you'd find in ~/.my.cnf. Not coincidentally, that's where it expects you'll be getting them from.
732  
733 $warn allows you to determine whether to die or warn on failure or error. To warn, set $warn to a non-zero value.
734  
735 $verbose will print out helpful messages should you be having problems.
736  
737  
738 =item db_vars
739  
740 This sub is called internally by $mysql->connect and is used principally to set some reasonable defaults should you not pass along enough connection parameters in $dot.
741  
742  
743 =item flush_logs
744  
745 $mysql->flush_logs($dbh, $verbose)
746  
747 runs the mysql "FLUSH LOGS" query on the server. This commits any pending (memory cached writes) to disk.
748  
749  
750 =item get_hashes
751  
752 Gets results from a mysql query as an array of hashes
753  
754 my @r = $mysql->get_hashes($dbh, $sql);
755  
756 $dbh is a database handle
757  
758 $sql is query
759  
760  
761 =item install
762  
763 Installs MySQL
764  
765  
766 =item is_newer
767  
768 my $ver = $mysql->version($dbh);
769 my $newer = $mysql->is_newer("4.1.0", $ver);
770  
771 if ($newer) { print "you are brave!" };
772  
773 As you can see, is_newer can be very useful, especially when you need to execute queries with syntax differences between versions of Mysql.
774  
775  
776 =item parse_dot_file
777  
778 $mysql->parse_dot_file ($file, $start, $verbose)
779  
780 Example:
781  
782 my $dot = $mysql->parse_dot_file(".my.cnf", "[mysql_replicate_manager]", 0);
783  
784 $file is the file to be parsed.
785  
786 $start is the [identifier] where we begin looking for settings. This expects the format used in .my.cnf MySQL configuration files.
787  
788 A hashref is returned wih key value pairs
789  
790  
791 =item phpmyadmin_install
792  
793 Install PhpMyAdmin from FreeBSD ports.
794  
795 $mysql->phpmyadmin_install;
796  
797  
798 =item query
799  
800 my $sth = $mysql->query ($dbh, $query, $warn)
801  
802 $dbh is the database handle you've already acquired via $mysql->connect.
803  
804 $query is the SQL statement to execute.
805  
806 If $warn is set, we don't die if the query fails. This way you can decide when you call the sub whether you want it to die or return a failed $sth (and likely an error message).
807  
808 execute performs whats necessary to execute a statement
809 Always returns true regardless of # of rows affected.
810 For non-Select, returns # of rows affected: No rows = 0E0
811 For Select, simply starts query. Follow with fetch_*
812  
813  
814 =item query_confirm
815  
816 $mysql->query_confirm($dbh, $query );
817  
818 Use this if you want to interactively get user confirmation before executing a query.
819  
820  
821 =item sanity
822  
823 A place to do validation tests on values to make sure they are reasonable
824  
825 Currently we only check to assure the password is less than 32 characters and the username is less than 16. More tests will come.
826  
827  
828 =item shutdown_mysqld
829  
830 Shuts down mysql using a $drh handle.
831  
832 my $rc = $mysql->shutdown_mysqld($dbvs, $drh);
833  
834 $dbvs is a hashref containing: host, user, pass
835  
836 returns error_code 200 on success, 500 on error. See error_desc for details.
837  
838  
839 =item tables_lock
840  
841 my $sth = $mysql->tables_lock($dbh );
842 # do some mysql stuff
843 $mysql->tables_unlock($dbh, $sth);
844  
845 Takes a statement handle and does a global lock on all tables. Quite useful when you want do do things like make a tarball of the database directory, back up the server, etc.
846  
847  
848 =item tables_unlock
849  
850 $mysql->tables_unlock($dbh, $sth );
851  
852 Takes a statement handle and does a global unlock on all tables. Quite useful after you've used $mysql->tables_lock, done your deeds and wish to release your lock.
853  
854  
855 =item status
856  
857  
858 =item version
859  
860 my $ver = $mysql->version($dbh);
861  
862 Returns a string representing the version of MySQL running.
863  
864  
865 =back
866  
867 =head1 DEPENDENCIES
868  
869 DBI.pm - /usr/ports/databases/p5-DBI
870 DBD::mysql - /usr/ports/databases/p5-DBD-mysql
871  
872 In order to use this module, you must have DBI.pm and DBD::Mysql installed. If they are not installed and you attempt to use this module, you should get some helpful error messages telling you how to install them.
873  
874 =head1 AUTHOR
875  
876 Matt Simerson <matt@tnpi.net>
877  
878 =head1 BUGS
879  
880 None known. Report any to author.
881  
882 =head1 TODO
883  
884 =head1 SEE ALSO
885  
886 The following are all man/perldoc pages:
887  
888 Mail::Toaster
889 Mail::Toaster::Conf
890 toaster.conf
891 toaster-watcher.conf
892  
893 http://www.mail-toaster.com/
894  
895  
896 =head1 COPYRIGHT
897  
898  
899 Copyright (c) 2003-2012, The Network People, Inc. All Rights Reserved.
900  
901 Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
902  
903 Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
904  
905 Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
906  
907 Neither the name of the The Network People, Inc. nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
908  
909 THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
910  
911  
912 =cut