Backup Strategies and MySQL Management in IMT3003

Backup

Backup er enkelt, viktig, men kan bli dyrt, med lite fokus på restore.

Backup media

Typer media som brukes for backup inkluderer Tape, disk, og Optisk.

Hjemmelaget / kjøpt

Refererer til om backup-løsningen er utviklet internt eller kjøpt fra en leverandør.

Rutiner / Backup plan

Planlegging og prosedyrer for hvordan backup skal utføres.

Mengde data

Refererer til volumet av data som skal tas backup av.

Båndbredde

Kapasiteten for dataoverføring som påvirker hastigheten på backup.

Applikasjoner med egne dataformater

Programmer som har spesifikke formater for lagring av data som kan påvirke backup.

Corpus

Datamengden som skal bli tatt backup av.

Endringsrate (change rate)

Hvor mye av corpus har endret seg mellom hver gang backup tas.

Backup vindu

Det tidsrommet vi kan ta backup.

Snapshot

En lokal kopi tas for backup, slik at corpus kan endre seg underveis.

Lokal / Remote

Refererer til hvor backup skal være plassert.

Medium

Hvor dataene lagres.

Partiell backup

Backup av deler av corpus, for eksempel en del hver dag.

Inkrementell backup

Backup av endringene siden sist, forutsetter en full backup tidligere.

Full backup

En komplett kopi av hele corpus som må tas regelmessig.

Linux verktøy for backup

Gode løsninger for backup inkluderer tar + gz, rsync, scp, og cp -al.

tar + gz

Lag en gzip av en hel mappe.

rsync

Synkroniserer to mapper, gjerne over SSH.

scp

Bruker SSH til å flytte filer.

cp -al

Tar kopi, men lager bare ny inode og bruker ikke mer plass før den originale filen endrer seg.

Error-log

Inneholder feilsituasjoner og om mysqld serveren har blitt startet/stoppet. Var egen logfil, men mange automatiske installasjoner lar den peke rett mot /var/log/syslog.

Query log

Inneholder en lang log over ALLE spørringer, gruppert på forbindelser til databasen. Inneholder også tidspunkt for forbindelsens start og stopp, men ikke hvor lang tid hver enkelt spørring tok. Er ikke skrudd på til vanlig.

Binær-log

En viktig bestanddel av MySQL sin mulighet til å ta backup / restore. Kun operasjoner som forandrer data blir lagret og først etter at de er fullført. Filen er på et binært format for raskest mulig skriving og lesing /var/log/mysql/mysql-bin.xxxxxx. Filen mysql-bin.index holder orden. Bruk kommandoen mysqlbinlog til å lese den.

Slow (langsom) query log

Inneholder alle queries som varer lengre enn det som er spesifisert i long_query_time = X.

Logfil vedlikehold

Linux vil automatisk rotere log-filene for oss hver natt, maks syv netter. Binær-logfiler roteres, men ikke på samme ukentlige sirkel. Man kan fremprovosere en ny binær-log fil med kommandoen mysqladmin flush-logs.

MySQL Backup

Når man lager en backup-strategi, er det viktigst å spørre seg om hvordan man vil kunne gjenopprette data. Backup i MySQL består av det å dumpe data og å ta vare på logfiler.

Situasjoner hvor ekstra backup er nødvendig

Før man skal oppgradere / gjøre store endringer. Etter feil som er blitt gjort av sysadmin. Etter ondsinnede kommandoer har blitt kjørt, f.eks delete * from posts. Etter tap av maskinvare eller planlagt migrering til ny server.

mysqldump

Vanligste verktøyet til å ta backup av en database og alt dets innhold. Skriver ut alt som SQL-kode med tanke på at du en gang kanskje vil bruke den til å gjenopprette databasen. Backup skrives til skjerm, må omdirigeres til fil.

mysqldump

A command-line utility for creating backups of MySQL databases.

Selective backup of databases

Backing up only specific databases or tables instead of all.

mysqldump [options] database [tables]

Command format for backing up a single database.

mysqldump [options] --databases db1 db2 db3

Command format for backing up multiple specified databases.

mysqldump [options] --all-databases

Command format for backing up all databases.

--add-drop-table

Option to include commands that drop tables before recreating them.

--add-locks

Option to add locks around each table restoration.

--create-options

Option to include all MySQL options when creating tables.

--disable-keys

Option to not optimize the index after each tuple is added.

--extended-insert

Option for optimizing insert operations.

--lock-tables

Option to lock all tables before the dump.

--quick

Option to not load everything into memory before writing it out.

--set-charset

Option to include a command to set the same character set.

--opt

Option that enables all the options discussed so far.

--master-data=2

Includes the last position and file number in the binary log file.

--flush-logs

Switches to a new binary log file right after the lock is taken.

mysqldump example

Typical backup command: mysqldump --opt --master-data=2 --flush-logs --all-databases > backup.sql.

backup.sql

File containing everything needed to restore the database.

Restore command

Command to restore the database from backup.sql: cat backup.sql | mysql.

mysqlbinlog

Utility to read binary log files and output SQL code.

Restore from mysqlbinlog

To restore, output must be sent to the MySQL server: mysqlbinlog mysql-bin.* | mysql.

--stop-datetime="date"

Option to stop processing the log before a specific date.

--start-datetime="date"

Option to start processing the log from a specific date.

--database=db

Option to restrict the output to a specific database.

--start-position=n

Option to start processing the log from a specific position.

--stop-position=n

Option to stop processing the log before a specific position.

mysqlbinlog

A command-line utility for processing MySQL binary log files.

stop-position

A parameter used with mysqlbinlog to stop processing at a specified position in the binary log.

start-position

A parameter used with mysqlbinlog to start processing from a specified position in the binary log.

restore from mysqldump

The process of restoring a database from a dump file created by mysqldump, which also writes changes to the binary log.

SET sql_log_bin=0

A command that can be added to the top of a backup.sql file to prevent logging of the restore operation in the binary log.

mysqladmin flush-logs

A command used to flush the MySQL logs, ensuring that all logs are written and can be rotated.

full backup

A complete copy of the database at a specific point in time, typically performed at regular intervals.

incremental backup

A backup that includes only the changes made since the last backup, allowing for more frequent backups.

binlog.sql

A file that contains the output of mysqlbinlog, which can be edited to remove unwanted commands before being executed on the database.

logrotate

A system utility that automatically manages the rotation of log files, including MySQL binary logs.

mysqldump

A command-line utility for creating backups of MySQL databases by exporting them to a dump file.

server-id

A unique identifier for a MySQL server instance, required for replication and remote access.

remote server

A server that is accessed over a network, allowing for operations like mysqldump and mysqlbinlog to be executed from a different machine.

binary log

A log file that records all changes to the database, which can be used for recovery and replication.

backup strategy

A plan outlining how and when backups are performed, including frequency and methods of backup.

flush logs

The action of clearing or writing out the current logs to disk, ensuring all data is saved.

restore process

The series of steps taken to recover a database from backups and binary logs.

bin-log files

Files that contain the binary log data, which can be processed to recover changes made to the database.

edit binlog.sql

The action of modifying the binlog.sql file to remove specific commands before executing it on the database.

data retrieval

The process of obtaining data from a database, which can be done remotely using mysqldump.

disk protection

Measures taken to safeguard against data loss due to disk failure, especially when backups and logs are stored on the same machine.

Bootstrapping images

The process of using a bootstrap script to pass configuration parameters to container images.

Environment variables

Variables that processes can check in a Linux system, which can be passed to a Docker container when starting it.

Checking current environment variables

Use the command 'set' to check the current environment variables in a Linux system.

Example: Alternative config.php

A PHP script that retrieves environment variables using getenv() for database configuration.

Running a container with environment variables

Use the command 'docker run -e BF_DB_HOST=10.10.0.X -e BF_DB=bf -e BF_USER=bfuser ... mybookface_image:tag' to start a container with specified environment variables.

Docker Swarm

A mode in Docker where multiple servers are connected to collaborate in running containers.

Docker Swarm Mode

A configuration where a swarm consists of one or more managers and multiple nodes.

Creating a swarm

Install Docker on all involved servers and run 'docker swarm init' on the master server.

Node join command

A command that allows other nodes to join the Docker swarm after obtaining the token from the master server.

Checking status of nodes

Use 'docker node ls' to see if all nodes are connected in the swarm.

Changing node availability

Use 'docker node update --availability active ' to change a node's availability.

Removing containers from a node

Use 'docker node update --availability drain node-2' to remove all containers from node-2.

Creating services from containers

A service in Docker consists of one or more instances from the same image, providing benefits like load balancing and fail-over.

Creating a service

Run 'docker service create [options] [image]' to create a service in Docker.

Service options

Options like '--replicas=X' and '--name=xyz' can be used when creating a service.

Example of creating a service

Use 'docker service create -d --replicas=3 --name=hello -p 3000:80 tutum/hello-world' to create a service.

Listing all services

Use 'docker service ls' to list all services in the Docker swarm.

Checking service status

Check the status of a service to determine how it is performing.

docker service ps service-name

Command to list tasks of a service in Docker.

docker service scale name=replicas

Command to increase or decrease the number of replicas (instances) of a service.

Docker Compose

A tool for defining and running multi-container Docker applications.

docker-compose.yml

File where Docker Compose configurations are defined.

docker-compose up [-d]

Command to start the containers defined in a docker-compose.yml file.

docker ps

Command to check the status of running containers.