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.
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 |