Come fare il backup di una base dati MySql.

mysqldump 

Vediamo come utilizzare il comando mysqldump nella gestione dati MySql in Linux.

MySQL e MariaDB includono l’utilità mysqldump per semplificare il processo di creazione di un backup di un database o di un sistema di database. L’utilizzo di mysqldump crea un backup logico. È possibile utilizzare questo strumento solo se il processo del database è accessibile e funzionante.

Se il database non è accessibile per qualsiasi motivo, si può invece creare un backup fisico, che è una copia della struttura del filesystem che contiene i dati.

Le istruzioni di questa guida si applicano sia a MySQL che a MariaDB. Per semplificare, il nome MySQL sarà usato per essere applicato a entrambi.

MySQL
mysqldump – backup mysql

 

Utilizziamo mysqldump

Classico comando per effettuare un dump completo di un database è il seguente

mysqldump -u [username] –p[password] [nome_database] > [file_dump.sql]

dove:

[username] : Username
[password] : Password
[nome_database] : Nome del Database di cui vogliamo generare il dump.
[file_dump.sql] : Nome del file che vogliamo generare

Se volessimo fare un dump di più database possiamo farlo così

sudo mysqldump -u [user] -p [database_01] [database_02] [database_03] [database_xx] > [file_dump].sql

Infine se vogliamo effettuare un Backup totale dell’intero Database Management System possiamo farlo con

mysqldump –all-databases –single-transaction –quick –lock-tables=false > full-backup-$(date +%F).sql -u root -p



 

Non spaventatevi

Queste sono le opzioni che avete a disposizione!

Option Name Description
–add-drop-database Add DROP DATABASE statement before each CREATE DATABASE statement
–add-drop-table Add DROP TABLE statement before each CREATE TABLE statement
–add-drop-trigger Add DROP TRIGGER statement before each CREATE TRIGGER statement
–add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements
–all-databases Dump all tables in all databases
–allow-keywords Allow creation of column names that are keywords
–apply-slave-statements Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output
–bind-address Use specified network interface to connect to MySQL Server
–character-sets-dir Directory where character sets are installed
–column-statistics Write ANALYZE TABLE statements to generate statistics histograms
–comments Add comments to dump file
–compact Produce more compact output
–compatible Produce output that is more compatible with other database systems or with older MySQL servers
–complete-insert Use complete INSERT statements that include column names
–compress Compress all information sent between client and server
–compression-algorithms Permitted compression algorithms for connections to server
–create-options Include all MySQL-specific table options in CREATE TABLE statements
–databases Interpret all name arguments as database names
–debug Write debugging log
–debug-check Print debugging information when program exits
–debug-info Print debugging information, memory, and CPU statistics when program exits
–default-auth Authentication plugin to use
–default-character-set Specify default character set
–defaults-extra-file Read named option file in addition to usual option files
–defaults-file Read only named option file
–defaults-group-suffix Option group suffix value
–delete-master-logs On a master replication server, delete the binary logs after performing the dump operation
–disable-keys For each table, surround INSERT statements with statements to disable and enable keys
–dump-date Include dump date as “Dump completed on” comment if –comments is given
–dump-slave Include CHANGE MASTER statement that lists binary log coordinates of slave’s master
–enable-cleartext-plugin Enable cleartext authentication plugin
–events Dump events from dumped databases
–extended-insert Use multiple-row INSERT syntax
–fields-enclosed-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA
–fields-escaped-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA
–fields-optionally-enclosed-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA
–fields-terminated-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA
–flush-logs Flush MySQL server log files before starting dump
–flush-privileges Emit a FLUSH PRIVILEGES statement after dumping mysql database
–force Continue even if an SQL error occurs during a table dump
–get-server-public-key Request RSA public key from server
–help Display help message and exit
–hex-blob Dump binary columns using hexadecimal notation
–host Host on which MySQL server is located
–ignore-error Ignore specified errors
–ignore-table Do not dump given table
–include-master-host-port Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with –dump-slave
–insert-ignore Write INSERT IGNORE rather than INSERT statements
–lines-terminated-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA
–lock-all-tables Lock all tables across all databases
–lock-tables Lock all tables before dumping them
–log-error Append warnings and errors to named file
–login-path Read login path options from .mylogin.cnf
–master-data Write the binary log file name and position to the output
–max-allowed-packet Maximum packet length to send to or receive from server
–net-buffer-length Buffer size for TCP/IP and socket communication
–network-timeout Increase network timeouts to permit larger table dumps
–no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements
–no-create-db Do not write CREATE DATABASE statements
–no-create-info Do not write CREATE TABLE statements that re-create each dumped table
–no-data Do not dump table contents
–no-defaults Read no option files
–no-set-names Same as –skip-set-charset
–no-tablespaces Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output
–opt Shorthand for –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset.
–order-by-primary Dump each table’s rows sorted by its primary key, or by its first unique index
–password Password to use when connecting to server
–pipe Connect to server using named pipe (Windows only)
–plugin-dir Directory where plugins are installed
–port TCP/IP port number for connection
–print-defaults Print default options
–protocol Connection protocol to use
–quick Retrieve rows for a table from the server a row at a time
–quote-names Quote identifiers within backtick characters
–replace Write REPLACE statements rather than INSERT statements
–result-file Direct output to a given file
–routines Dump stored routines (procedures and functions) from dumped databases
–secure-auth Do not send passwords to server in old (pre-4.1) format
–server-public-key-path Path name to file containing RSA public key
–set-charset Add SET NAMES default_character_set to output
–set-gtid-purged Whether to add SET @@GLOBAL.GTID_PURGED to output
–shared-memory-base-name Name of shared memory to use for shared-memory connections
–show-create-skip-secondary-engine Exclude SECONDARY ENGINE clause from CREATE TABLE statements
–single-transaction Issue a BEGIN SQL statement before dumping data from server
–skip-add-drop-table Do not add a DROP TABLE statement before each CREATE TABLE statement
–skip-add-locks Do not add locks
–skip-comments Do not add comments to dump file
–skip-compact Do not produce more compact output
–skip-disable-keys Do not disable keys
–skip-extended-insert Turn off extended-insert
–skip-opt Turn off options set by –opt
–skip-quick Do not retrieve rows for a table from the server a row at a time
–skip-quote-names Do not quote identifiers
–skip-set-charset Do not write SET NAMES statement
–skip-triggers Do not dump triggers
–skip-tz-utc Turn off tz-utc
–socket Unix socket file or Windows named pipe to use
–ssl-ca File that contains list of trusted SSL Certificate Authorities
–ssl-capath Directory that contains trusted SSL Certificate Authority certificate files
–ssl-cert File that contains X.509 certificate
–ssl-cipher Permissible ciphers for connection encryption
–ssl-crl File that contains certificate revocation lists
–ssl-crlpath Directory that contains certificate revocation-list files
–ssl-fips-mode Whether to enable FIPS mode on client side
–ssl-key File that contains X.509 key
–ssl-mode Desired security state of connection to server
–tab Produce tab-separated data files
–tables Override –databases or -B option
–tls-ciphersuites Permissible TLSv1.3 ciphersuites for encrypted connections
–tls-version Permissible TLS protocols for encrypted connections
–triggers Dump triggers for each dumped table
–tz-utc Add SET TIME_ZONE=’+00:00′ to dump file
–user MySQL user name to use when connecting to server
–verbose Verbose mode
–version Display version information and exit
–where Dump only rows selected by given WHERE condition
–xml Produce XML output
–zstd-compression-level Compression level for connections to server that use zstd compression