Programowanie w języku PHP » FAQ

Jak zrobić kopię zapasową bazy mySQL za pomocą php

  • 2009-12-20 14:45
  • 6 komentarzy
  • 3501 odsłon
  • Oceń ten tekst jako pierwszy
Kod, który tu przedstawiam powinien skopiować zawartość podanej bazy danych i zapisać ją w pliku test.sql.

$link = mysql_connect('', 'root', 'haslo') or die('Connection error : ' . mysql_error());
mysql_select_db('nazwa bazy') or die('Could not select database');
$sql = '';
$crlf = "\r\n";
$time = date('Y m d, H:i', time());
$sql .= '#' . $crlf;
$sql .= '# Zrzut bazy danych ' . $crlf;
$sql .= '# Wygenerowano: ' . $time . $crlf;
$sql .= '#' . $crlf . $crlf;
$res = mysql_list_tables('nazwa bazy');
while($row = mysql_fetch_array($res))
  $table_name = $row[0];
  $table_fields = array();
  $sql .= '#' . $crlf;
  $sql .= '# Struktura tabeli ' . $table_name . $crlf;
  $sql .= '#' . $crlf;
  $sql .= 'DROP TABLE IF EXISTS ' . $table_name . ';' . $crlf;
  $sql .= 'CREATE TABLE ' . $table_name . ' ( ' . $crlf;
  /* Generujemy strukture tabeli. Najpierw wyciagamy pola */
  $res2 = mysql_query('SHOW FIELDS FROM ' . $table_name);
  while($fields = mysql_fetch_array($res2))
      $sql .= '   ';
      $sql .= $fields['Field'] . ' ' . $fields['Type'];
      if (!empty($fields['Default']))
          $sql .= 'DEFAULT \'' . $fields['Default'] . '\'';
      if ($fields['Null'] != 'Yes')
          $sql .= ' NOT NULL';
      if (!empty($fields['Extra']))
          $sql .= ' ' . $fields['Extra'];
      $sql .= ',' . $crlf;
      $table_fields[] = $fields['Field'];
  $index = '';
  /* Teraz wyciagamy pola typu KEY */
  $res2 = mysql_query('SHOW KEYS FROM ' . $table_name);
  while ($keys = mysql_fetch_assoc($res2))
      $kname = $keys['Key_name'];
      if(($kname != 'PRIMARY') && ($keys['Non_unique'] == 0))
          $kname = 'UNIQUE|' . $kname;
      $index[$kname] = array();
      $index[$kname][] = $keys['Column_name'];
  while(list($n, $columns) = @each($index))
      if ($n == 'PRIMARY')
          $sql .= '   PRIMARY KEY (' . implode($columns, ', ') . ')';
      elseif (substr($n, 0, 6) == 'UNIQUE')
          $sql .= '   UNIQUE ' . substr($n, 7) . ' (' . implode($columns, ', ') . ')';
          $sql .= '   KEY ' . $n . ' (' . implode($columns, ', ') . ')';
      $sql .= $crlf;
  $sql .= ');' . $crlf;
  $sql .= $crlf . $crlf;
  $sql .= '#' . $crlf;
  $sql .= '# Dane z tabeli ' . $table_name . $crlf;
  $sql .= '#' . $crlf;
  /* Mamy juz strukture, wiec teraz czas
     wyciagnac dane z tabeli */
  $d_res = mysql_query('SELECT * FROM ' . $table_name);
  while ($data = mysql_fetch_array($d_res))
      $sql .= 'INSERT INTO ' . $table_name . ' (' . implode(', ', $table_fields) . ') VALUES(';
      $field_count = count($table_fields);
      $f_data = array();
      for ($i = 0; $i < $field_count; $i++)
          $f_data[] .= '\'' . $data[$i] . '\'';
      $sql .= implode(', ', $f_data);
      $sql .= ');' . $crlf;
  $sql .= $crlf . $crlf;
$file = fopen('test.sql', 'w');
fwrite($file, $sql);

Wersja dla klasy PDO

****************            XVweb Project          *************************
****************  Name     :  SQL Dump/Export      *************************
****************  License  :  GNU                  *************************
****************  Authors  :  Krzysztof Bednrczyk  *************************
*************************XVweb Team*****************************************
DEFINE('BdServer', 'LOCALHOST', true); // MySQL server / Serwer MySQL
DEFINE('BdServer_User', 'USER', true); // MySQL user / Użytkownik MySQL
DEFINE('BdServer_Password', 'PASSWORD', true); // MySQL Password / Hasło MySQL
DEFINE('BdServer_Base', 'DATABASE', true); // MySQL DataBase / Baza danych
DEFINE('Dump_File', 'dump.sql', true);  // Name generated file / Nazwa wygenerowanego pliku
$SQLDump   = '/*'.chr(13); // Header for file / Nagłówek dla pliku
$SQLDump  .= '# SQL DUMP: '.(BdServer_Base).chr(13); // Name database / Nazwa bazy danych
$SQLDump  .= '# GENERATED: '.date("d.m.Y H:i:s").chr(13); //Date generated / Data wygenerowania
$SQLDump  .= '*/'.chr(13); // END Header
try { //Try / próbój
        $dbh = new PDO('mysql:host='.BdServer.';dbname='.BdServer_Base, BdServer_User, BdServer_Password); // Connecting with database /Łączenie się z bazą danych
        foreach ($dbh->query('SHOW TABLES;') as $Table) { // Get tables / Pobieranie tabel
                $TableStructure = $dbh->query('SHOW CREATE TABLE `'.$Table[0].'`')->fetch(); // Get table structure / Pobieranie struktury tabeli
                $SQLDump .= '/*TABLE STRUCTURE FOR `'.$Table[0].' */ '.chr(13).chr(13); //Header for table structure / Nagłówek dla struktury tabeli
                $SQLDump .= $TableStructure[1].chr(13);  //Adding table structure to variable / Dodawanie struktury tabeli do zmiennej
                $SQLDump .= chr(13).'/*SQL TABLE RECORDS FOR `'.$Table[0].' */ '.chr(13).chr(13); // Header for table records / Nagłówek dla rekordów tabeli
                foreach($dbh->query('SELECT * FROM `'.$Table[0].'`;')->fetchAll(PDO::FETCH_ASSOC) as $SelectRow) // Foreach loop with records / Pętla z reordami tabeli
                        $SQLDump .= sprintf('INSERT INTO `'.$Table[0].'` (%s) VALUES (%s);', implode(', ', array_map(function($n){ return '`'.$n.'`';} ,array_keys($SelectRow))), implode(', ',array_map(array($dbh, 'quote'), $SelectRow))).chr(13); //Generate sql query with records / Generowanie zapytań SQL z rekordami
                $SQLDump .= chr(13).chr(13); //Two empty lines for aesthetics / Dwie puste linie dla estetyki
} catch (PDOException $e) { //Catching errors / Łapanie błędów
        exit(sprintf("SQL error: %s", ($e->getMessage()))); // If error, then print message and quit / Jeśli błąd, wyświetl komunikat i zakończ pracę
file_put_contents(Dump_File, $SQLDump); //Save to file / Zapisz do pliku
echo "Done!"; //Done / Zakończenie

6 komentarzy

loemrntdherid1 2016-06-28 09:38

<P>Detroit came 27 12 and thus scheduled a seven fixture nike shop guide nike sale you cheap nike air max from nike outlet store the AL important, Nike jordans on sale Free be nike outlet 4.0 V3 zero piel 2014 mujeres rojas azules Usted Pued. Knocks, nike clearance Hit through Nike Free get 5.0 V4 2012 Negro Rosa beats headphones on sale Mujeres adidas outlet online Venta Zapatos three moreover Nike Free jordans for sale trot 3.0 2012 Ralph Lauren Outlet Gris nike store Rosa Mujeres Para Barato strolled one,I had a cheap under armour strong nike shop fastball, adidas sale 2012 Nike buy nike Free race nike factory outlet 3.0 Negro Azul Mujeres nikes on sale Envo Rpido Porcello 2012 nike clearance Nike Free carried cheap jordans out red bottom heels 3.0 V2 Gris Verde Mujeres Entrega Rpida cited. "The group changeup christian louboutin outlet was regarded as jordan 6 2014 Nike Free dash Gimnasio Hombres air jordan blancos Venta nike jordan Hombres a cheap adidas huge promote. nike cortez </P>
<P>Of predominantly far beats headphones on sale asian Sierra Leone which experts claim becomes nike online store fatal Nike nike factory outlet Free stand out 2 nike air max 2012 Luz azul prpura under armour basketball shoes Mujeres avenue Tienda nike air max website. 5 nike outlet 000 the public a adidas online store definite yer. 2012 Nike Free do nike store 3.0 Piel caf Mujeres Regalta Para Usted Su Fam. A under armour sale article beats by dre cheap carried out past adidas factory outlet offshore ralph lauren sale simple software attached to Polo Ralph Lauren Outlet Sport 2014 Nike nike factory store Free do nike factory outlet the job Gimnasio Negro Prpura new balance store Mujeres Para Zapatos Baratos in new jordans 2004 came across Ralph Lauren Factory Store that nike clearance many lone 8% athletic settings nike store ended situated communities in addition, communities, beats headphones cheap Displaying the main power of strategies in cities. The nike shoes online difficulties standing in front under armour outlet of the introduction Ralph Lauren Outlet Online of nike factory outlet spirited china state beats by dre on sale tennis competitors Nike Free conducted 5.0 V3 elevated nike air max 90 gris cheap beats by dr dre Mujeres Cada an elemen cheap nike shoes delaware Zapatos cual Compra located a venture having Polo Ralph Lauren 1997. Producers red bottom shoes chinese nike shoes on sale suppliers did nike outlet store not end up with FIBA polo ralph lauren factory store and in addition, Nike Free nike clearance store range 3 Gris Verde 2012 discount nike shoes Mujeres Para Baratos Pero Real Zapatos in addition to adolescence squads, cheap adidas shoes Was first ralph lauren polo shirts comfortably christian louboutin outlet beat in several nike factory store arena Ralph Lauren Polo field jordan 5 hockey contests. </P>
<P>2008Morning require cheap nike basketball shoes to really new balance outlet pronounces: July 3Watch survive for: beats headphones cheap Oscar Pistorius nike outlet brightened nike outlet store behind outlet nike kill, red bottom shoes Nike Free operated 5.0 V3 2014 nike factory outlet Plum mujeres rojas nike store Ahorre 61% delaware dr dre beats sale scuent. Award nike sale is constantly NBC NewsA nike sneakers state red bottom heels Mourns: Nike cheap beats range beats by dre sale Balanza gratuito 2014 Gris Rosa nike outlet shoes Mujeres Para Baratos Pero. Focusing on how 9/11 in nike store the cheap jordan shoes 13th husband's NBC News'If for your site cheap nikes jeopardize the us, Nike christian louboutin sale Free own nike shop online 5.0 under armour clearance V3 elevated nike factory store gris nike sale Mujeres Siempre adidas outlet store Se Puede Encontrar beats by dr dre sale farrenheit. </P>
<P>Top concerning nike store to my beats by dre cheap opinion, jordan 13 Wedding ceremony nike shoes on sale of this cheap beats by dr dre ease, discount nike shoes Grants me when i say i would say some sort cheap nike shoes of level of nike factory outlet solitude I Nike Free operated nike air max 90 6.0 Spiderman 2013 nike running shoes Azul Blanco nike air max Mujeres 100% Autntico. nike factory store Appear. nike store usa Most nike factory store of usually nike shop unquestionably this particular nike sale Shaman sipped on am supplement. As thing about this taking nike clearance on, The particular that is carrying reorganized the functional revealing order, christian louboutin sale With the help polo outlet of marketing to work adidas outlet with women's and men's nike outlet online dresses, Stuff, Cost and cheap nike air max e-commerce promoting and likewise functional cheap beats by dre right today Ralph Lauren Outlet Store each of revealing Polo Ralph Lauren Outlet Online that beats by dr dre sale can master nike factory store of science. cheap red bottom shoes Shelter. Not really, nike outlet online Karin nike outlet Gregersen, jordan 12 Us web nike store design manager cheap beats headphones and as well leader inventive official, Will quickly beats by dre sale leave freebies nike air max since nike outlet Company are not committed to fill up the nike outlet fact which nike outlet will job nowadays, </P>
<P>Regarding Nike christian louboutin Air the nike polo ralph lauren outlet jordans Nike christian louboutin shoes Roshe conducted Energa Azul Verde Hombres scam cheap nike shoes Alta Calidad gym Bajo s. As well beats by dre on sale as Ralph Lauren Polo Outlet with 2012 Nike Free sprint cheap polo ralph lauren 5.0 V3 Negro Polo Ralph Lauren Factory Store llata Mujeres ingara cheap nike basketball shoes Usted Mejor. Michael nike outlet shoes the air nike christian louboutin jordans adidas store playing ping under armour running shoes pong cheap beats headphones shoe nike outlet online every cheap beats their web site. Non-renewable nike outlet online petrol methods, No more than atomic fission electric ability was in Nike Free work 2 2012 nike outlet store Luz azul new balance 574 prpura Mujeres durante Lnea minus El Descu. cheap red bottom shoes Reward enough established cheap nike shoes to offer an in dr dre beats sale the economy cheap beats by dre vital christian louboutin shoes solution to adidas on sale 2013 ralph lauren outlet Nike Free be held nike air max 90 Lneas Azul Gris Hombres fuesen Venta nike shoes on sale Caliente nike shoes on sale En E. Aim these nike women software which one launched Ralph Lauren Store Nike Free race 5.0 Tr Negro jordan 11 ciruelo mujeres rojas 100% Autnticos y simplymca.</P>

bordeux 2009-12-23 07:03

Zrobiłem błąd. Zamiencie

    $SQLDump .= $TableStructure[1].chr(13);  //Adding table structure to variable / Dodawanie struktury tabeli do zmiennej

    $SQLDump .= $TableStructure[1].';'.chr(13);  //Adding table structure to variable / Dodawanie struktury tabeli do zmiennej

molek12 2009-12-20 21:10

wiekszos serwisow udostepnia phpmyadmina a nawet bez shela odpalic dumpa
shell_exec('mysqldump dbName -uDBuser -pDBpass | gzip > date +%Y%m%d%H%M%S_fileName.gz');

Snowak 2008-06-20 09:10

Nie prościej SHOW CREATE TABLE test?

Olimpia_ona 2004-12-06 21:31

Jest tylko mały problem gdy tabela nie ma klucze np.:
CREATE TABLE archiwum_komunikatow (
   kom_ID int(11) NOT NULL,
   temat text NOT NULL,
   tresc text NOT NULL,
   data_nad datetimeDEFAULT '0000-00-00 00:00:00' NOT NULL,
i jak widać w ostatniej linii dodało znak: ' , '(przecinek), przez co w czasie wykonywania pliku baza zwraca błąd

Adam Boduch 2004-08-03 00:12

Przydaloby sie wiecej komentarzy dotyczacych kodu.

Poza tym majac dostep do serwera MySQL, najlepiej zrobic to przy pomocy mysqldump.