<< mysqlhotcopy ¸¦ ÀÌ¿ëÇÑ DB ¹é¾÷ >> ÀÛ¼ºÀÚ : ÀåÇüÈ­ (hhjang97@venus.uos.ac.kr) ÀÛ¼ºÀÏ : 2001. 12. 24 ¼öÁ¤ÀÏ : 2002. 02. 01 : ¼Ò½º·Î *.pm ¼³Ä¡Çϱâ Ãß°¡ 2002. 02. 24 : cron ÀÌ¿ëÇϱâ Ãß°¡ ȯ°æ : Redhat 7.2, MySQL 3.23.38 mysqlhotcopy ¸¦ ÀÌ¿ëÇϸé DB¸¦ ½±°í ¾ÈÀüÇÏ°Ô ¹é¾÷ÇÒ ¼ö ÀÖ½À´Ï´Ù. Usage: ./mysqlhotcopy db_name [new_db_name | directory] -?, --help display this helpscreen and exit -u, --user=# user for database login if not current user -p, --password=# password to use when connecting to server -P, --port=# port to use when connecting to local server -S, --socket=# socket to use when connecting to local server --allowold don't abort if target already exists (rename it _old) --keepold don't delete previous (now renamed) target when done --noindices don't include full index files in copy --method=# method for copy (only "cp" currently supported) -q, --quiet be silent except for errors --debug enable debug -n, --dryrun report actions without doing them --regexp=# copy all databases with names matching regexp --suffix=# suffix for names of copied databases --checkpoint=# insert checkpoint entry into specified db.table --flushlog flush logs once all tables are locked --tmpdir=# temporary directory (instead of /oracle/tmp) Try 'perldoc ./mysqlhotcopy for more complete documentation' ## mysql DB ¹é¾÷Çϱâ backup ÇÒ µð·ºÅ丮°¡ /data/mysql_backup ÀÌ°í, ¹é¾÷ÇÒ DB À̸§ÀÌ php ÀÌ¸é ´ÙÀ½°ú °°´Ù. # /usr/local/mysql/bin/mysqlhotcopy php /data/mysql_backup # ls /data/mysql_backup php/ ¿Í °°ÀÌ ¹é¾÷µÈ µ¥ÀÌÅ͸¦ º¼ ¼ö ÀÖ´Ù. º¹±¸°¡ ÇÊ¿äÇÒ¶§ ¹é¾÷µÈ µ¥ÀÌÅ͸¦ ¹Ù·Î ÀÌ¿ëÇÒ ¼ö ÀÖ´Ù. #### ÇüÈ­°¡ ¸¸µç ¹é¾÷ ÇÁ·Î±×·¥ ÀÌ ÇÁ·Î±×·¥Àº Àüü ¹é¾÷À» À§ÇØ ¸¸µé¾î Á³½À´Ï´Ù. mysqlhotcopy ¸¦ »ç¿ëÇϱâ À§Çؼ­´Â MySQL ÀÌ rpm À¸·Î ¼³Ä¡ µÇ¾î ÀÖ¾î¾ß ÇÏ°í¿ä. perl-DBD-MySQL-1.2216-4 ¿Í perl-DBI-1.18-1 °¡ ¼³Ä¡ µÇ¾î ÀÖÀ¸¾ß ÇÕ´Ï´Ù. MySQL À» ¼Ò½º·Î ¼³Ä¡ÇϽŠºÐÀº perl »çÀÌÆ®¿¡¼­ ÇÊ¿äÇÑ *.pm À» ¹Þ¾Æ¼­ ¼³Ä¡ÇÏ½Ã¸é µË´Ï´Ù. ¼Ò½º·Î *.pm À» ¼³Ä¡ÇÏ´Â ¹æ¹ýÀº ¹Ø¿¡ ÀÖ½À´Ï´Ù. ############################################################################# ¹é¾÷¿¡ »ç¿ëÇÒ db_list ¸¸µå´Â ¹æ¹ý : # $mysql_path/mysqlshow -u root $passwd $host > $db_list »ç¿ë ¹æ¹ý : perl mysql_backup.cgi µð·ºÅ丮À̸§ ============================= mysql_backup.cgi ============================== #!/usr/bin/perl # Linux¿¡ ÀÖ´Â ¸ðµç DB¸¦ DB_name.dump ÆÄÀÏ¿¡ dumpÇÑ´Ù. # 1999,12,2 # $mysql_path = "/usr/local/bin"; # FreeBSD $mysql_path = "/usr/bin"; $passwd = ""; # root ºñ¹øÀ» ÀÔ·ÂÇÑ´Ù. $host = ""; # Á¢¼ÓÇÒ È£½ºÆ® (¿ÜºÎ¿¡ DB°¡ ÀÖ´Â °æ¿ì¸¸ »ç¿ëÇÑ´Ù.) $db_list = "/backup/mysql_backup/db_list"; # db_list ÀÇ À§Ä¡ $backup_path = "/backup/mysql_backup"; # backupÇÒ µð·ºÅ丮 if( $passwd ne ''){ $passwd = " -p$passwd"; } if( $host ne ''){ $host = " -h $host"; } if( @ARGV < 1 ) { print "Usage:perl mysql_backup.cgi [name] \n\n"; exit; } print "Arg : ". @ARGV[0]. "\n\n"; ## ÀÌÀü db_list ¸¦ ÀÌ¿ëÇÏ·Á¸é ÁÖ¼®À» Ä¡¸é µÈ´Ù. `$mysql_path/mysqlshow -u root $passwd $host > $db_list`; open( IN, $db_list ); $start = 0; while( ) { if( $start ne 3 ) { $start ++; # print "start:$_\n"; next; } elsif ( $_ !~ / / ) { # Blank next; } chop(); ( $a, $db, $c ) = split( /\|/, $_); $db =~ s/ //g; $directory = $backup_path. "/". @ARGV[0]; # Backup Directory ¸¦ ¸¸µç´Ù. `mkdir -p $directory`; # °¢ DBÀÇ dumpÆÄÀÏÀ» »ý¼ÍÇÑ´Ù. `$mysql_path/mysqlhotcopy -u root $passwd $host $db $directory`; # print "$mysql_path/mysqlhotcopy -uroot $passwd $host $db $directory\n"; print "$db Backup\n"; } close( IN ); ============================= mysql_backup.cgi ============================== ## Cron À» ÀÌ¿ëÇÒ·Á¸é ´ÙÀ½°ú °°ÀÌÇϸé ÆíÇÏ´Ù. ´ÙÀ½Àº ÀÏÁÖÀÏÀ» ÁÖ±â·Î ¹é¾÷À» ÇÏ´Â °ÍÀÌ´Ù. cron ¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ¼³¸íÀº www.kldp.org ¿¡¼­ °Ë»öÀ» ÇغÁ¶ó.. ============================ crontab -e ================================= # ¸ÅÀÏ 3½Ã 2ºÐ¿¡ hotcopy ¸¦ ÇÑ´Ù. 02 03 * * * perl /home/server/script/mysql_fullbackup.pl $(date | awk '{print $1 }') ============================ crontab -e ================================= ######################### # ¼Ò½º·Î ¼³Ä¡Çϱâ ######################### ## Âü°í ¹®¼­ MySQL DBD¶õ? http://database.sarang.net/database/mysql/dbd/mysql-dbd-1.html ## Download - http://dbi.symbolstone.org/ - http://www.perl.org/CPAN/modules/by-module DBI/DBI-1.201.tar.gz Data/Data-ShowTable-3.3.tar.gz : ÀÌ°Ô ¿Ü ÇÊ¿äÇÑÁö ¸ð¸£°Ú´Ù. -.-;; DBD/DBD-mysql-2.1010.tar.gz ############ # DBI.pm ############ ## Download http://www.perl.org/CPAN/modules/by-module ftp://ftp.funet.fi/pub/languages/perl/CPAN/modules/by-module ¿¡¼­ DBI-1.201.tar.gz ¸¦ ¹Þ´Â´Ù. ## ¼³Ä¡ perl Makefile.PL make make test make install ##################### # DBD/mysql.pm ##################### perl-DBD-MySQL ¸ðµâÀÌ´Ù. DBD-mysql-2.1010.tar.gz ## ¼³Ä¡ perl Makefile.PL make make test make install ################## # ¿¡·¯ ################## ## Error 1 [root@demo mysql_var]# mysqlhotcopy -u root mysql /backup/mysql_var/ Can't locate DBI.pm in @INC (@INC contains: /usr/lib/perl5/5.6.0/i386-linux /usr/lib/perl5/5.6.0 /usr/lib/perl5/site_perl/5.6.0/i386-linux /usr/lib/perl5/site_perl/5.6.0 /usr/lib/perl5/site_perl .) at /usr/local/mysql/bin/mysqlhotcopy line 8. BEGIN failed--compilation aborted at /usr/local/mysql/bin/mysqlhotcopy line 8. ## Ans 1 DBI.pm À» ¼³Ä¡ÇÑ´Ù. ## Error 2 [root@demo root]# mysqlhotcopy mysql /backup/mysql_var/ install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib/perl5/5.6.0/i386-linux /usr/lib/perl5/5.6.0 /usr/lib/perl5/site_perl/5.6.0/i386-linux /usr/lib/perl5/site_perl/5.6.0 /usr/lib/perl5/site_perl .) at (eval 1) line 3. Perhaps the DBD::mysql perl module hasn't been fully installed, or perhaps the capitalisation of 'mysql' isn't right. Available drivers: ExampleP, Proxy. at /usr/local/mysql/bin/mysqlhotcopy line 157 ## Ans 1 DBD/mysql.pm À» ¼³Ä¡ÇÑ´Ù.