SelectEtoile : Base de connaissance SGBD

Posts Tagged 'database'

Sauvegarde / Restauration

Sauvegarde / restauration d'une table : 

Bcp out :

  • mysql> SELECT a,b,a+b INTO OUTFILE '/tmp/jp1.txt'
  • -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  • -> LINES TERMINATED BY '\n'
  • -> FROM jp1;

 

Bcp in :

  • mysql> LOAD DATA INFILE "/tmp/jp1.txt" INTO TABLE jp1
  • -> FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\'
  • -> LINES TERMINATED BY '\n';
  • LOAD DATA INFILE 'ta_syb_aseconfig_total.out' INTO TABLE ta_syb_aseconfig
  • FIELDS TERMINATED BY ';' ENCLOSED BY '' ESCAPED BY '\\'
  • LINES TERMINATED BY '\n';

 

Sauvegarde d'une Base de données

mysqldump [options] db_name [tables]

Sauvegarde : mysqldump -u root -ppwd --opt mabase -h monServeur > backup-production.sql

Exemple : mysqldump --socket=mysql.sock --user=root --password=pwd testjp > /tmp/testjp.sql

La commande dump permet de générer le script de création des objets et de la base elle même. Ci-dessous, un exemple de résultat de la commande :

 

-- MySQL dump 10.9

--

-- Host: localhost Database: testjp

-- ------------------------------------------------------

-- Server version 4.1.11-max-log

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

 

--

-- Table structure for table `jp1`

--

 

DROP TABLE IF EXISTS `jp1`;

CREATE TABLE `jp1` (

`a` int(11) default NULL,

`b` int(11) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

--

-- Dumping data for table `jp1`

--

 

 

/*!40000 ALTER TABLE `jp1` DISABLE KEYS */;

LOCK TABLES `jp1` WRITE;

INSERT INTO `jp1` VALUES (3,8),(1,1),(10,10),(6,7),(5,5),(8,9);

UNLOCK TABLES;

/*!40000 ALTER TABLE `jp1` ENABLE KEYS */;

 

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

 

Il faut prévoir un flush des logs binaire avant la sauvegarde full :

Soit lancer un 'flush logs' dans mysql

Soit ajouter l'option --flush-logs dans la commande mysqldump

 

Autre exemple de mysqldump :

mysqldump --opt ${DATABASE} --databases --routines --triggers --single-transaction --master-data=2 --hex-blob --flush-logs

--master-data=2 permet de mettre une ligne de commentaire dans le fichier généré donnant le binlog courant ainsi que la position
--single-transaction : permet de rendre transactionnel la sauvegarde et donc consistant
--hex-blob : Sauvegarde les colonnes binaires utilisant La notation hexadecimale
 
 

 

 

Restauration d'une base de données :

Restauration d'un dump full :

mysql -u root -ppwd -h monServeur -D mabase2 < backup-production.sql

Exemple : ‘mysql testjp < /tmp/testjp.dump’

 

Restauration des log binaire :

mysqlbinlog mysql-bin.000062 mysql-bin.000063 | mysql


Sauvegarde / restauration table (vont disparaître)

  • BACKUP TABLE table [, table] ... TO '/repertoire/de/sauvegarde/';
  • RESTORE TABLE table [, table] ... FROM '/repertoire/de/sauvegarde/';

 

 

Sauvegarde a l’aide de mysqlhotcopy (Prérequis : Perl et packages DBI)

  • mysqlhotcopy db_name [/répertoire/de/sauvegarde]
  • mysqlhotcopy -u root -ppwd mabase /mstoto/sgbd/sauvegarde/

 

 

 

 

 

Petit guide des commandes

 

Les fonctions MySql :

  • select version(), current_date;
  • SELECT nom, naissance, CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(naissance)), (RIGHT(CURRENT_DATE,5)<RIGHT(naissance,5)) AS age FROM animal;

  • select user();

 

Les Commandes SHOW :

  • show databases;
  • show columns from monDataCache;
  • show create table monDataCache;
  • show create database monitor_db;
  • show databases;
  • SHOW ERRORS [LIMIT [offset,] row_count]
  • SHOW COUNT(*) ERRORS
  • show engines;
  • show engines\G;
  • show grant for monty;
  • show innodb status;
  • show logs;
  • show privileges;
  • show processlist (ou mysqladmin process-list) : permet de voir qui est connecté à la base
  • show status; (ou mysqladmin extended-status)
  • show tables;
  • show tables status;
  • show variables
  • show variables like 'max_error_count';
  • show warnings; (suite a une requete avec des warnings)
  • SHOW COUNT(*) WARNINGS;

 

Les options :
 

  • L'attribut AUTO_INCREMENT peut être utilisé pour générer un identifiant unique pour les nouvelles lignes
  • Set autocommit=1 : utilisé pour la recréation rapide de la table

Les scripts natifs :

  • mysqlcc (MySQL Control Center) est un client interactif graphique, pour exécuter des commandes SQL, et administrer le serveur
  • mysqladmin est un client d'administration
  • mysqlcheck effectue les opérations de maintenance sur les tables
  • mysqldump et mysqlhotcopy font les sauvegardes de bases
  • mysqlimport importe des fichiers de données
  • mysqlshow affiche des informations sur les bases et les tables
  • mysql_secure_installation : permet de securiser l'installation de mysql

ex  : mysqladmin extended-status  (-r diff; -i 1 intervalle)

Les variables :

  • SELECT @@warning_count;

 

Exemples de commandes :

mysql> show create database testjp;

+----------+-------------------------------------------------------------------+

| Database | Create Database |

+----------+-------------------------------------------------------------------+

| testjp | CREATE DATABASE `testjp` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+-------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> use testjp

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> show create table jp1;

+-------+--------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+--------------------------------------------------------------------------------------------------------------------+

| jp1 | CREATE TABLE `jp1` (

`a` int(11) default NULL,

`b` int(11) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+-------+--------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> SHOW GRANTS FOR fsarasy;

+-----------------------------------------------------------------------------------------------------------------+

| Grants for fsarasy@% |

+-----------------------------------------------------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'fsarasy'@'%' IDENTIFIED BY PASSWORD '*63D85DCA15EAFFC58C908FD2FAE50CCBC60C4EA2' |

+-----------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

 

 

 

 

 

 

 

 

Utilisation de quiesce sous SYBASE ASE

QUIESCE :  

Mécanisme permettant de suspendre les écritures sur une base donnée.

1) On suspend les écritures sur la base test_db :
test_db_quiesce = étiquette (tag) identifiant cette opération
hold = type d'opération
to manifeste_file = fichier binaire contenant les informations pour la suite (i.e : umount / mount)
SQL> quiesce database test_db_q1 hold test_db [ for external dump ] [ to "/chemin/vers/manifestFile" ]

Note : On peut vérifer les tags en cours en consultant monOpenDatabases.QuiesceTag ou avec is_quiesce()
exemple :  
SQL> select QuiesceTag from master..monOpenDatabases
C'est également montré par dbcc resource (Section 'QUIESCEDB_INFO')
 
2) Pour libérer la base :  
SQL> quiesce database test_db_quiesce release = libére le verrou étiqueté "test_db_quiesce"

Utilisation de Mount / Umount sous SYBASE ASE

MOUNT / UNMOUNT :

 

ATTENTION : la commande "unmount" efface les entrées de la base concernée et de ses devices dans le dataserver source !

C'est une méthode alternative pour copier / déplacer / migrer une base. Pratique et rapide.

0) vérifier la taille des pages des ASE et la compatibilité des OS.

1) on crée une base test_db dans le serveur source.

2) on démonte la base en indiquant le manifest_file :
RE-ATTENTION : j'insiste sur le fait que cela supprime toutes les informations du dataserver actuel !
SQL>unmount database test_db to "/chemin/vers/testdb_manifestFile"

2 bis) mettre la base en "quiesce" en générant le fichier "manifest" (cf : QUIESCE)

3) on copie les fichiers de device et le manifeste vers leur nouveau répertoire sur la destination.

4)remonter la base en indiquant le changement de chemin :
SQL>mount database all from "/chemin/vers/test_db_manifest.file"
using '/nouveau/chemin/vers/devices/test_data1' = 'test_data1',
'/nouveau/chemin/vers/devices/test_log1' = 'test_log1'

Note : pour retrouver les informations contenues dans le manifest file :
SQL> mount database all from '/nouveau"/chemin/vers/testdb_manifestFile" with listonly
... voir en shell : strings test_db_manifest.file

5) Remettre la base en ligne (avec migration automatique si besoin) :
SQL> online database test_db

Et voilà ! La base est disponible, avec ses devices.

Temps de recouvrement d'une base

 

Il faut d’abord bien comprendre que lorsqu’un utilisateur, insert, modifie ou supprime des données, seul le journal de transaction est écrit immédiatement sur le disque. Les données modifiées sont écrites dans le cache d’ASE. Pour des raisons de performance, leurs écritures sur disque sont différées. C’est le processus checkpoint ou la commande checkpoint qui provoque l’écriture des données modifiés en cache sur disque. C’est ce même processus qui déplace le point de troncature pour permettre la purge du journal de transactions.

 

Si le serveur est arrêté avec « un arrêt immédiat » (On fige le journal de transaction en l’état, il n’y a pas de checkpoint ni de purge) ou alors suite à un arrêt brutal, les données modifiées en cache qui ne sont pas écrites sur disque sont flashées. Lorsque le serveur redémarre, il va rechercher ces données en se basant sur le journal de transaction.

 

Pour réaliser cette tache le serveur se lance dans un processus de  recovery de la base qui se décompose en trois étapes :

  • Analyse : C’est la lecture du journal de transaction afin de construire les taches à effectuer dans le REDO PASS et le UNDO PASS.
  • Redo Pass : C’est l’écriture des données, des transactions validées, sur disque
  • Undo Pass : C’est l’annulation des transactions qui n’ont pas abouties ou bien qui étaient marquées comme à défaire.

 

 

Donc selon la périodicité des checkpoints et le taille du journal de transaction, l’étape de REDO peut-être très longue.

Visualisation de l'espace dispo FileGroup

Create table #AllDbSpace (
    DbName varchar(100),
    FgName varchar(100),
    NbFiles int,
    CurrentSizeMB int,
    FreeSpaceMB int,
    FreeSpacePct int
)

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SQLScript VARCHAR(6000)

DECLARE DatabaseCursor CURSOR FOR
                SELECT [name] FROM master..sysdatabases
                 where dbid > 4
                ORDER BY [name]

OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
        BEGIN

                  SET @SQLScript = 'USE ' + @DatabaseName + ';
                                                  With dbSpace'+ @DatabaseName +' as
                                                    (
                                                        SELECT DB_NAME() AS DbName,
                                                        fg.name AS FgName,
                                                        count(f.name) AS NbFiles,
                                                        sum(f.size/128) AS CurrentSizeMB,
                                                        sum(f.size/128-CAST(FILEPROPERTY(f.name,''SpaceUsed'') AS INT)/128) AS FreeSpaceMB
                                                        FROM sys.database_files f
                                                        inner join sys.filegroups fg on fg.data_space_id = f.data_space_id
                                                        group by fg.name
                                                    )
                                                    INSERT INTO #AllDbSpace
                                                    Select DbName,FgName,NbFiles,CurrentSizeMB,FreeSpaceMB,FreeSpaceMB*100/CurrentSizeMB as ''%FreeSpace''
                                                    FROM dbSpace'+ @DatabaseName +';
                                                    '

                EXEC (@SQLScript)
                FETCH NEXT FROM DatabaseCursor INTO @DatabaseName

        END

CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor

SELECT DbName,FgName,NbFiles,CurrentSizeMB,FreeSpaceMB,FreeSpacePct as '%FreeSpace' FROM #AllDbSpace
DROP TABLE #AllDbSpace

 

Rechargement base avec modification status

Cet article décrit la procédure pour recharger une base qui est soit suspect, soit dans un état "impossible à loader"

 

Sauvegarde du status de la base

select status from sysdatabase where name = "MABASE"

A garder bien soigneusement

 

Mise à jour du status de la base pour reload :

1> begin tran

2> go

1> update sysdatabase set status = RefStatus where name = "mabase"   -- Refstatus défini ci dessous

2> go

1> commit

2> go

1> shutdown -- ou shutdown with nowait si nécessaire

2> go

 

RefStatus :

  • -32768 en cas de base en état 'suspect' (après le reboot, il faudra supprimer la base et la reconstruire)
  • 32 met la base en état 'à loader' (après reboot, il suffira de recharger la base)

 

Si on a mis le status à -32768 :

Extraire l'ordre de création de la base (create database mabase for load ...)

1> dbcc dbrepair('mabase','dropdb')

2> go

1> create database mabase for load

2> go

1> load database mabase ....

2> go

1> online database mabase

2> go

-- Vérification du status de la base mabase


Si on a mis le status à 32 :

 

  • Après le redémarrage, bien vérifier que la base mabase est dans l'état 'à recharger'.
  • Sinon, il faut redémarrer jusqu'à ce que ce soit le cas
  • Ensuite, on peut recharger la base

 

 

 

 

Installation Mirroring SQL Server

Définition du Miroring

 

Le Miroring est un outil disponible à partir de la version SQL SERVER 2005 SP1. Cet outil permet de maintenir à jours une base de données distante (par exemple sur un serveur de secours).

Sa granularité est la base de données (impossible de ne maintenir que certains objets), et son fonctionnement est basé sur le transfert des blocs du journal de transaction.

·         PRINCIPES DE FONCTIONNEMENT / restriction

Nous appellerons ‘base primaire’ la base de données source et ‘base secondaire’ la base de données cible du miroring.

La base primaire est obligatoirement en mode de recovery full, il sera donc nécessaire de mettre en place des jobs de backup pour le journal de transaction (cf Jobs DBA). L’instance sql serveur va donc gérer un second point de troncature pour ce journal de transaction. Lors d’une modification de données la partie du journal de transaction relative à ce changement va être envoyée et écrite dans le journal de transaction de la base secondaire. Cette dernière est quand à elle en recovery mode.

Le transfert des blocs de journaux de transactions ce fait par un ‘endpoint miroring’. Ce dernier définit la connexion entre l’instance primaire et l’instance secondaire.  La création d’un endpoint miroring est donc nécessaire sur les 2 instances cible est source. Le même ‘endpoint’ peut être utilisé pour plusieurs bases.

La base primaire ne peut avoir qu’une seule base secondaire (contrairement au log shipping)

La base secondaire ne peut pas être accédée (même en lecture). Il est possible de mettre en place en fonction des besoins un snapshot de la base secondaire qui lui sera dispo en lecture uniquement mais avec certaines contraintes (nous ne traiterons pas des snapshot dans ce document)

 

·         MODE de miroring

Il existe plusieurs modes de miroring en fonction des besoins.

                Le mode synchrone avec bascule automatique

                               Ce mode permet de ne perdre aucune transaction et de pouvoir basculer sur la base secondaire sans aucune intervention manuelle. En contrepartie de la sécurité les performances peuvent être considérablement diminuées (50%) car les modifications ne seront appliquées sur la bases primaire qu’une fois commitées sur la base secondaire. (des tests applicatifs sont impératifs pour définir l’acceptabilité des pertes du à ce mode synchrone). La bascule automatique Elle nous coutera un serveur/instance supplémentaire pour y installer le ‘witness server ‘ ce dernier va contrôler en permanence la présence de la base primaire, en cas de défaillance il activera automatiquement la base secondaire. 

 

                Le mode synchrone avec bascule manuelle

Idem que si dessus mais sans la partie ‘witness serveur’ la bascule sera donc manuelle (très rapide quand même)

                Le mode asynchrone

Ce mode de miroring pas la perte  de transaction est possible, mais les performances accrues. La base primaire va vivre indépendamment de la base secondaire, à fréquence définis les block de journaux de transaction seront envoyés et exécutés sur la base secondaire. La bascule sera obligatoirement manuelle.

 

Mise en place du Mirroring entre 2 serveurs SQL-Server (SQLServer1 --> SQLServer2)

Prérequis

  • La version de SQLServer doit être au minimum 2005 SP1
  • La base doit exister sur les 2 environnements.
  • La base primaire doit être en mode de recovery full
  • Les logins / users doivent être synchronisés entre primaire et secondaire via un script de transfert login par exemple.

Verrouillage des logins

Use master ;

ALTER LOGIN [mon login] DISABLE ;

Passage de la base Primaire en mode full sur le SQLServer 1

ALTER DATABASE [ma base] SET RECOVERY FULL;

Lancement d'un dump database et dump transaction de la base primaire SQLServer 1

BACKUP DATABASE [ma base] TO DISK = 'chemin\fichier.bck' WITH FORMAT; 

BACKUP LOG [ma base] TO DISK = 'chemin\fichier.trn' with format;

 

Read more: Installation Mirroring SQL Server