MARIADB-DUMP(1) MariaDB Database System MARIADB-DUMP(1) (NAME) mariadb-dump - (mariadb-dump mariadb-dump ) (SYNOPSIS) mariadb-dump [options] [db_name [tbl_name ...]] (DESCRIPTION) mariadb-dump Igor Romanenko . (dump) SQL ( MariaDB ) . SQL . mariadb-dump CSV XML . MyISAM mariadb-hotcopy . mariadb-hotcopy(1) . mariadb-dump : shell> mariadb-dump [options] db_name [tbl_name ...] shell> mariadb-dump [options] --databases db_name ... shell> mariadb-dump [options] --all-databases shell> mariadb-dump [options] --system={options} db_name --databases --all-databases . mariadb-dump INFORMATION_SCHEMA performance_schema . --skip-lock-tables . mariadb-dump mariadb-dump --help . mariadb-dump : o --opt --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset . --opt --opt . o --compact --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --skip-set-charset . --skip-xxx (--skip-opt --skip-compact) . . : o --opt --skip . (extended inserts) --opt --skip-extended-insert --skip-quick . ( --skip-extended-insert --skip-quick --opt .) o --opt --skip-opt --disable-keys --lock-tables . . --disable-keys --lock-tables --skip-opt --skip-opt . mariadb-dump . . --quick ( --opt --quick ) . --opt ( --quick) --skip-quick ..PP mariadb-dump (dump) MySQL --opt --extended-insert . --skip-opt . mariadb-dump [mariadb-dump] [client] . mariadb-dump . o --help, -? . o --add-drop-database DROP DATABASE CREATE DATABASE. --all-databases --databases CREATE DATABASE . o --add-drop-table DROP TABLE CREATE TABLE. o --add-drop-trigger DROP TRIGGER CREATE TRIGGER. o --add-locks (dump) LOCK TABLES UNLOCK TABLES. dump . o --all-databases, -A . --databases . o --all-tablespaces, -Y SQL (tablespace) NDBCLUSTER . mariadb-dump . MySQL Cluster . o --allow-keywords . . o --apply-slave-statements.sp 'STOP SLAVE' 'CHANGE MASTER' 'START SLAVE' dump . o --as-of=name dump (system-versioned) (timestamp) . o --character-sets-dir=path (character sets). o --comments, -i dump . . --skip-comments . o --compact . --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --skip-set-charset . o --compatible=name MySQL. name ansi mysql323 mysql40 postgresql oracle mssql db2 maxdb no_key_options no_table_options no_field_options . . SQL (SQL mode) . . SQL dump . --compatible=oracle Oracle (comments) Oracle . o --complete-insert, -c INSERT . o --compress, -C . o --copy-s3-tables S3 . CREATE Aria ALTER TABLE xxx ENGINE=S3 . o --create-options, -a MariaDB CREATE TABLE. --skip-create-options . o --databases, -B . mariadb-dump . . CREATE DATABASE USE . o --debug[=debug_options], -# [debug_options] . debug_options 'd:t:o,file_name'. 'd:t:o,/tmp/mariadb-dump.trace' . o --debug-check . o --debug-info CPU . o --default-auth . o --default-character-set=charset_name charset_name . mariadb-dump utf8 . o --defaults-extra-file=filename filename . . o --defaults-file=filename filename . . o --defaults-group-suffix=str, str. mariadb-dump [client] [mariadb-dump] --defaults-group-suffix=x [mariadb-dump_x] [client_x] . o --delayed-insert INSERT DELAYED INSERT. o --delete-master-logs (master replication server) PURGE BINARY LOGS . --master-data . o --dir . --tab ( sql --parallel). --databases --all-databases . --dir --dir . . --tab --dir --parallel. o --disable-keys, -K INSERT /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; . . MyISAM . o --dump-date --comments mariadb-dump : -- Dump completed on DATE . --dump-date --skip-dump-date . --dump-date ( ). --skip-dump-date . o --dump-history . mariadb-dump . o --dump-slave[=value] (slave) (master) . binary log . 1 () CHANGE MASTER 2 () . --lock-all-tables --single- transaction ( - --single-transaction ). . --lock-tables . mariadb-dump SQL . o --events, -E Event Scheduler . o --extended-insert, -e INSERT VALUES . . o --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=... --tab FIELDS LOAD DATA INFILE . o --first-slave MariaDB 5.5 . --lock-all-tables . o --flush-logs, -F (log) MariaDB (flush) . RELOAD . --all-databases . --lock-all-tables --master-data : . --flush-logs --lock-all-tables --master-data . o --flush-privileges FLUSH PRIVILEGES mysql . mysql mysql . o --force, -f SQL . mariadb-dump (view) (drop) . --force mariadb-dump . --force mariadb-dump SQL . o --gtid MariaDB 10.0.13 --master-data --dump-slave slave GTID . SQL slave (GTID) master / . --gtid --gtid GTID . o --header --tab . txt . o --hex-blob ( 'abc' 0x616263 ). BINARY VARBINARY BLOB BIT. o --host=host_name, -h host_name MariaDB . localhost . o --ignore-table=db_name.tbl_name . . . o --include-master-host-port MASTER_HOST MASTER_PORT CHANGE MASTER TO --dump-slave slave. o --insert-ignore INSERT IGNORE INSERT. o --lines-terminated-by=... --tab LINES LOAD DATA INFILE . o --lock-all-tables, -x . (global read lock) . --single-transaction --lock-tables . o --lock-tables, -l . READ LOCAL MyISAM . InnoDB --single-transaction --lock-tables . --lock-tables . . --skip-lock-tables . o --log-error=file_name . . o --log-queries (general log) (slow query log) . --skip-log-queries . o --master-data[=value] (master replication server) slave master . CHANGE MASTER TO ( ) . slave . 2 CHANGE MASTER TO SQL . 1 . 1 . RELOAD (binary log) . --master-data --lock-tables . --lock-all-tables --single-transaction . . slave slave master . slave : 1. SQL slave : mariadb> STOP SLAVE SQL_THREAD; mariadb> SHOW SLAVE STATUS; 2. SHOW SLAVE STATUS master slave Relay_Master_Log_File Exec_Master_Log_Pos . file_name file_pos . 3. slave (dump) : shell> mariadb-dump --master-data=2 --all-databases > dumpfile 4. slave : mariadb> START SLAVE; 5. slave dump : shell> mariadb < dumpfile 6. slave master : mariadb> CHANGE MASTER TO -> MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos; CHANGE MASTER TO MASTER_HOST slave master . . o --max-allowed-packet=length . o --max-statement-time=seconds (timeout) . ( 0 () ) o --net-buffer-length=length TCP/IP . o --no-autocommit INSERT SET autocommit = 0 COMMIT . o --no-create-db, -n CREATE DATABASE --databases --all-databases . o --no-create-info, -t CREATE TABLE . o --no-data, -d ( ). CREATE TABLE ( ). o --no-defaults . . o --no-set-names, -N --skip-set-charset . o --opt . --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset . MariaDB . --opt . --skip-opt . --opt . o --order-by-primary (unique index) . MyISAM InnoDB . o --order-by-size . --single-transaction (truncate) (alter) . --single-transaction 'Table definition has changed, please retry transaction' . o --parallel=#, -j ( --tab). ( ) . . o --password[=password], -p[password].sp . (-p) . password --password -p mariadb-dump . . (option file) . o --pipe, -W Windows (named pipe). named-pipe . o --plugin-dir . o --port=port_num, -P port_num TCP/IP . --protocol=tcp . o --protocol={TCP|SOCKET|PIPE|MEMORY} . . o --quick, -q . mariadb-dump . o --print-defaults . . o --quote-names, -Q ( ) "`" . SQL ANSI_QUOTES """ . . --skip-quote-names --compatible --quote-names . o --replace REPLACE INSERT. o --result-file=file_name, -r file_name . Windows "\n" / "\r\n" . dump . o --routines, -R ( ) . SELECT mysql.proc . --routines CREATE PROCEDURE CREATE FUNCTION . . . --routines . mysql.proc MariaDB mysql . o --set-charset SET NAMES default_character_set . . SET NAMES --skip-set-charset . o --single-transaction START TRANSACTION . InnoDB BEGIN . InnoDB . MyISAM MEMORY . --single-transaction ( ) : ALTER TABLE CREATE TABLE DROP TABLE RENAME TABLE TRUNCATE TABLE. (consistent read) SELECT mariadb-dump . --single-transaction --lock-tables ( ) LOCK TABLES (commit) . --single-transaction --quick . o --skip-add-drop-table --add-drop-table. o --skip-add-locks --add-locks. o --skip-comments --comments. o --skip-compact --compact. o --skip-disable-keys.sp --disable-keys . o --skip-extended-insert --extended-insert . o --skip-opt --opt . o --skip-quick --quick . o --skip-quote-names --quote-names . o --skip-set-charset --set-charset . o --skip-triggers --triggers . o --skip-tz-utc --tz-utc . o --socket=path, -S path localhost Windows (named pipe) . --protocol=socket Windows --protocol=pipe . o --ssl SSL ( ). --skip-ssl. o --ssl-ca=name CA PEM ( OpenSSL --ssl ). o --ssl-capath=name CA ( OpenSSL --ssl ). o --ssl-cert=name X509 PEM ( OpenSSL --ssl ). o --ssl-cipher=name SSL ( OpenSSL --ssl ). o --ssl-key=name X509 PEM ( OpenSSL --ssl ). o --ssl-crl=name ( OpenSSL --ssl ). o --ssl-crlpath=name ( OpenSSL --ssl ). o --ssl-verify-server-cert <> . . o --system={all, users, plugins, udfs, servers, stats, timezones} mysql . . . : o all - . o users - CREATE USER CREATE ROLE GRANT SET DEFAULT ROLE (ALTER USER +MySQL-8.0). o plugins - INSTALL PLUGIN . o udfs - CREATE FUNCTION . o servers - ( federated) CREATE SERVER. o stats - InnoDB (EITS) REPLACE INTO ( --insert-ignore INSERT IGNORE) () . o timezones - REPLACE INTO ( --insert-ignore INSERT IGNORE) () . --replace --insert-ignore . --replace CREATE OR REPLACE SQL CREATE OR REPLACE DROP IF EXISTS CREATE . --system=user ( all) --replace SQL . --insert-ignore SQL CREATE IF NOT EXIST . stats timezones --replace --insert-ignore . mysql --all-databases mysql . MySQL MariaDB MySQL-5.7 8.0 . SQL MySQL-5.7/8.0 . / MariaDB MySQL . o --tab=path, -T path (tab-separated). mariadb-dump tbl_name.sql CREATE TABLE tbl_name.txt . . mariadb-dump mariadbd . FILE . .txt . --fields-xxx --lines-terminated-by . (character set) --default-character-set . o --tables --databases -B. mariadb-dump . o --triggers () . --skip-triggers . o --tz-utc TIMESTAMP . mariadb-dump UTC SET TIME_ZONE='+00:00' . TIMESTAMP . --tz-utc . --tz-utc . --skip-tz-utc . o --user=user_name, -u user_name MariaDB . o --verbose, -v (). . o --version, -V . o --where='where_condition', -w 'where_condition' WHERE . . : --where="user='jimf'" -w"userid>1" -w"userid<1" o --xml, -X XML (well-formed). NULL 'NULL' : column_name NULL 'NULL' . +------------------------+-------------------------+ |: | XML: | +------------------------+-------------------------+ |NULL ( | ) | name="column_name" | | | xsi:nil="true" /> | +------------------------+-------------------------+ |'' ( | ) | name="column_name"> | +------------------------+-------------------------+ |'NULL' ( | ) | name="column_name">NULL | +------------------------+-------------------------+ mariadb --xml . ( "MARIADB OPTIONS" .) XML mariadb-dump (namespace) XML : shell> mariadb-dump --xml -u root world City 1 Kabul AFG Kabol 1780000 ... 4079 Rafah PSE Rafah 92020 --var_name=value : o max_allowed_packet /. 1GB . o max_statement_time max_statement_time . . 0 () . 31536000 . o net_buffer_length . INSERT ( --extended-insert --opt) mariadb-dump net_buffer_length . net_buffer_length MariaDB . mariadb-dump : shell> mariadb-dump db_name > backup-file.sql : shell> mariadb db_name < backup-file.sql : shell> mariadb -e "source /path-to-backup/backup-file.sql" db_name mariadb-dump MariaDB : shell> mariadb-dump --opt db_name | mariadb --host=remote_host -C db_name : shell> mariadb-dump --databases db_name1 [db_name2 ...] > my_databases.sql --all-databases : shell> mariadb-dump --all-databases > all_databases.sql InnoDB mariadb-dump (online) : shell> mariadb-dump --all-databases --single-transaction > all_databases.sql ( FLUSH TABLES WITH READ LOCK) . (binary log) . FLUSH MariaDB . . MariaDB ( ) . ( "roll-forward" ) : shell> mariadb-dump --all-databases --master-data=2 > all_databases.sql : shell> mariadb-dump --all-databases --flush-logs --master-data=2 > all_databases.sql --master-data --single-transaction InnoDB . (views) ..SH " (COPYRIGHT)" 2007-2008 MySQL AB 2008-2010 Sun Microsystems, Inc. 2010-2025 MariaDB Foundation (GNU General Public License) (Free Software Foundation) / . << >> << >> << >>. . Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA http://www.gnu.org/licenses . (SEE ALSO) MariaDB https://mariadb.com/docs . (AUTHORS) MariaDB Foundation (http://www.mariadb.org). MariaDB 11.8 5 March 2025 MARIADB-DUMP(1)