{"id":701,"date":"2011-07-03T12:58:15","date_gmt":"2011-07-03T10:58:15","guid":{"rendered":"https:\/\/bob-team.de\/wordpress\/?p=701"},"modified":"2024-03-28T21:30:18","modified_gmt":"2024-03-28T20:30:18","slug":"backup-prozedur-fur-mysql","status":"publish","type":"post","link":"https:\/\/bob-team.de\/wordpress\/2011\/07\/03\/backup-prozedur-fur-mysql\/","title":{"rendered":"Backup-Prozedur f\u00fcr MySQL"},"content":{"rendered":"<p>Die Aufgabe war, eine gespeicherte Prozedur zu schreiben, mit deren Hilfe kritische Tabellen automatisiert mit einem Suffix gesichert werden k\u00f6nnen. Entstanden ist folgendes Konstrukt. Ein Cursor durchwandert eine Liste mit Tabellenname. Mit diesen und dem Suffix-Parameter werden <a href=\"http:\/\/dev.mysql.com\/doc\/refman\/5.1\/de\/sqlps.html\">vorbereitete Anweisungen<\/a> erstellt und ausgef\u00fchrt.<br \/>\n<!--more--><br \/>\n<code lang=\"sql\">drop procedure if exists pro_backup_create;<br \/>\ndelimiter $$<br \/>\ncreate procedure pro_backup_create (table_suffix varchar(50))<br \/>\nbegin<br \/>\n  declare done int default 0;<br \/>\n  declare table_name varchar(50);<br \/>\n  declare debug_id varchar(50) default 'pro_backup_create';<br \/>\n  declare cur1 cursor for select 'tab1' as tablename<br \/>\n     union all select 'tab2'<br \/>\n     union all select 'tab3';<br \/>\n  declare continue handler for sqlstate '02000' set done = 1;<br \/>\n  call dbmc.debug_on(debug_id);<br \/>\n  open cur1;<br \/>\n  repeat<br \/>\n     fetch cur1 into table_name;<br \/>\n     if not done then<br \/>\n        -- DROP TABLE<br \/>\n        set @cmd = concat(<br \/>\n           'DROP TABLE IF EXISTS zp.', table_name, '_', table_suffix<br \/>\n        );<br \/>\n        prepare backup_table_drop from @cmd;<br \/>\n        execute backup_table_drop;<br \/>\n        deallocate prepare backup_table_drop;<br \/>\n        call dbmc.debug_insert(debug_id, @cmd);<\/p>\n<p>        -- CREATE TABLE<br \/>\n        set @cmd = concat(<br \/>\n           'CREATE TABLE zp.', table_name, '_', table_suffix,<br \/>\n           ' LIKE zp.', table_name<br \/>\n        );<br \/>\n        prepare backup_table_create from @cmd;<br \/>\n        execute backup_table_create;<br \/>\n        deallocate prepare backup_table_create;<br \/>\n        call dbmc.debug_insert(debug_id, @cmd);<\/p>\n<p>        -- INSERT INTO<br \/>\n        set @cmd = concat(<br \/>\n           'INSERT INTO zp.', table_name, '_', table_suffix,<br \/>\n           ' SELECT * FROM zp.', table_name<br \/>\n        );<br \/>\n        prepare backup_table_copy from @cmd;<br \/>\n        execute backup_table_copy;<br \/>\n        deallocate prepare backup_table_copy;<br \/>\n        call dbmc.debug_insert(debug_id, @cmd);<br \/>\n     end if;<br \/>\n   until done end repeat;<br \/>\n   close cur1;<br \/>\nend $$<br \/>\ndelimiter ;<\/code><\/p>\n<p>Die Protokollierung der Statusmeldungen <tt>call dbmc...<\/tt> wird <a href=\"https:\/\/bob-team.de\/wordpress\/2009\/08\/01\/mysql-debugging\/\">hier<\/a> beschrieben.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Die Aufgabe war, eine gespeicherte Prozedur zu schreiben, mit deren Hilfe kritische Tabellen automatisiert mit einem Suffix gesichert werden k\u00f6nnen. Entstanden ist folgendes Konstrukt. Ein Cursor durchwandert eine Liste mit Tabellenname. Mit diesen und dem Suffix-Parameter werden vorbereitete Anweisungen erstellt und ausgef\u00fchrt.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[30],"class_list":["post-701","post","type-post","status-publish","format-standard","hentry","category-skripte","tag-mysql","entry"],"_links":{"self":[{"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/posts\/701","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/comments?post=701"}],"version-history":[{"count":11,"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/posts\/701\/revisions"}],"predecessor-version":[{"id":3449,"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/posts\/701\/revisions\/3449"}],"wp:attachment":[{"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/media?parent=701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/categories?post=701"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bob-team.de\/wordpress\/wp-json\/wp\/v2\/tags?post=701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}