MYSQLDUMP COMMAND : If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:
Syntax :
If you logged in into your database then :
mysqldump [options] [database name] > [BackupFilename]
Example :
mysqldump --routines --no-create-info --no-data --no-create-db mydatabaseName > /var/www/backupDatabase.sql
If you are not logged in into your database then :
mysqldump -u[username] -p[password] [options] [database name] > [BackupFilename]
Example :
mysqldump -u cg_famcom -p --routines --no-create-info --no-create-db mydatabaseName > /var/www/backupDatabase.sql
Example explain : Below is the explanation of above examples-
[username] : The username of your database.
[password] : This is the password of your database.
--routines : If you are using routines option then it will export all stored procedure and functions which you are creted in your database.
--no-create-info : If you are using no-create-info option then it will not be write CREATE TABLE statements that re-create each dumped table.
--no-data : If you are using no-data option then it will not be export the table content of your database.
--no-create-db : If you are using no-create-db option then it will compress the CREATE DATABASE statements.
mydatabaseName : This is the name of my database which i want to export.
/var/www/backupDatabase.sql : This is the path of my backup file name.
Below is the complete options list with there discreptions :
If you like this post don't forgot to leave a comment.
Chears :)
Happy Coding
Syntax :
If you logged in into your database then :
mysqldump [options] [database name] > [BackupFilename]
Example :
mysqldump --routines --no-create-info --no-data --no-create-db mydatabaseName > /var/www/backupDatabase.sql
If you are not logged in into your database then :
mysqldump -u[username] -p[password] [options] [database name] > [BackupFilename]
Example :
mysqldump -u cg_famcom -p --routines --no-create-info --no-create-db mydatabaseName > /var/www/backupDatabase.sql
Example explain : Below is the explanation of above examples-
[username] : The username of your database.
[password] : This is the password of your database.
--routines : If you are using routines option then it will export all stored procedure and functions which you are creted in your database.
--no-create-info : If you are using no-create-info option then it will not be write CREATE TABLE statements that re-create each dumped table.
--no-data : If you are using no-data option then it will not be export the table content of your database.
--no-create-db : If you are using no-create-db option then it will compress the CREATE DATABASE statements.
mydatabaseName : This is the name of my database which i want to export.
/var/www/backupDatabase.sql : This is the path of my backup file name.
Below is the complete options list with there discreptions :
| Format | Description | Introduced |
|---|---|---|
| --add-drop-database | Add a DROP DATABASE statement before each CREATE DATABASE statement | |
| --add-drop-table | Add a DROP TABLE statement before each CREATE TABLE statement | |
| --add-drop-trigger | Add a 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=ip_address | Use specified network interface to connect to MySQL Server | 5.6.1 |
| --comments | Add comments to the dump file | |
| --compact | Produce more compact output | |
| --compatible=name[,name,...] | 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 | |
| --create-options | Include all MySQL-specific table options in CREATE TABLE statements | |
| --databases | Dump several databases | |
| --debug[=debug_options] | Write a debugging log | |
| --debug-check | Print debugging information when program exits | |
| --debug-info | Print debugging information, memory, and CPU statistics when program exits | |
| --default-auth=plugin | Authentication plugin to use | |
| --default-character-set=charset_name | Specify default character set | |
| --defaults-extra-file=file_name | Read option file in addition to usual option files | |
| --defaults-file=file_name | Read only named option file | |
| --defaults-group-suffix=str | Option group suffix value | |
| --delayed-insert | Write INSERT DELAYED statements rather than INSERT statements | |
| --delete-master-logs | On a master replication server, delete the binary logs after performing the dump operation | |
| --disable-keys | For each table, surround the 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[=value] | Include CHANGE MASTER statement that lists binary log coordinates of slave's master | |
| --events | Dump events from the dumped databases | |
| --extended-insert | Use multiple-row INSERT syntax that include several VALUES lists | |
| --fields-enclosed-by=string | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --fields-escaped-by | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --fields-optionally-enclosed-by=string | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --fields-terminated-by=string | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --flush-logs | Flush the MySQL server log files before starting the dump | |
| --flush-privileges | Emit a FLUSH PRIVILEGES statement after dumping the mysql database | |
| --help | Display help message and exit | |
| --hex-blob | Dump binary columns using hexadecimal notation (for example, 'abc' becomes 0x616263) | |
| --host | Host to connect to (IP address or hostname) | |
| --ignore-table=db_name.tbl_name | Do not dump the 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 statements rather than INSERT statements | |
| --lines-terminated-by=string | This option is used with the --tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE | |
| --lock-all-tables | Lock all tables across all databases | |
| --lock-tables | Lock all tables before dumping them | |
| --log-error=file_name | Append warnings and errors to the named file | |
| --login-path=name | Read login path options from .mylogin.cnf | 5.6.6 |
| --master-data[=value] | Write the binary log file name and position to the output | |
| --max_allowed_packet=value | Maximum packet length to send to or receive from server | |
| --net_buffer_length=value | Buffer size for TCP/IP and socket communication | |
| --no-autocommit | Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements | |
| --no-create-db | This option suppresses the 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] | Password to use when connecting to server | |
| --pipe | On Windows, connect to server using named pipe | |
| --plugin-dir=path | Directory where plugins are installed | |
| --port=port_num | TCP/IP port number to use for connection | |
| --print-defaults | Print defaults | |
| --protocol=type | 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=file | Direct output to a given file | |
| --routines | Dump stored routines (procedures and functions) from the dumped databases | |
| --secure-auth | Do not send passwords to the server in old (pre-4.1.1) format | 5.6.17 |
| --set-charset | Add SET NAMES default_character_set to output | |
| --set-gtid-purged=value | Whether to add SET @@GLOBAL.GTID_PURGED to output | 5.6.9 |
| --shared-memory-base-name=name | The name of shared memory to use for shared-memory connections | |
| --single-transaction | This option issues a BEGIN SQL statement before dumping data from the 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 the 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 the 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 | Suppress the SET NAMES statement | |
| --skip-triggers | Do not dump triggers | |
| --skip-tz-utc | Turn off tz-utc | |
| --socket=path | For connections to localhost, the Unix socket file to use | |
| --ssl | Enable SSL for connection | |
| --ssl-ca=file_name | Path of file that contains list of trusted SSL CAs | |
| --ssl-capath=dir_name | Path of directory that contains trusted SSL CA certificates in PEM format | |
| --ssl-cert=file_name | Path of file that contains X509 certificate in PEM format | |
| --ssl-cipher=cipher_list | List of permitted ciphers to use for SSL encryption | |
| --ssl-crl=file_name | Path of file that contains certificate revocation lists | 5.6.3 |
| --ssl-crlpath=dir_name | Path of directory that contains certificate revocation list files | 5.6.3 |
| --ssl-key=file_name | Path of file that contains X509 key in PEM format | |
| --ssl-verify-server-cert | Verify server Common Name value in its certificate against host name used when connecting to server | |
| --tab=path | Produce tab-separated data files | |
| --tables | Override the --databases or -B option | |
| --triggers | Dump triggers for each dumped table | |
| --tz-utc | Add SET TIME_ZONE='+00:00' to the dump file | |
| --user=user_name | MySQL user name to use when connecting to server | |
| --verbose | Verbose mode | |
| --version | Display version information and exit | |
| --where='where_condition' | Dump only rows selected by the given WHERE condition | |
| --xml | Produce XML output |
If you like this post don't forgot to leave a comment.
Chears :)
Happy Coding
Tags:
Command