mysqldump example

Mysqldump Backup Recovery and mysqldump Import and Export Statement details mysqldump backup: mysqldump-u username-p password-h host database a-w "SQL condition" -- lock-all-tables> path case: mysqldump-uroot-p1234-hlocalhost db1 a-w "id in (select id from B)" -- lock-all-ta

), the log will be refreshed on a per-database basis. In addition to using –lock-all-tables or –master-data. In this case, the log will be refreshed once, so the table is locked at the same time. Therefore, if you intend to export and refresh the logs at the same time, you should use –lock-all-tables or –master-data and –flush-logs. Mysqldump-uroot-p–all-databases–flush-logs–flush-privileges after exporting the MySQL database, issue a flush privileges

using mysqldump to MySQLof full and incremental backupspurpose of Backup: When data is an important asset, we need to back it up frequently to prevent data corruption , it can be restored to its damaged state in time. Backup content: Data, configuration files, binary logs, transaction logsBackup Category:Backup Type:hot Backup, warm backup and cold backupHot backup: Read, write not affected;Warm backup: Only read operation can be performed;Cold backup

Mysqldump export does not contain stored procedures, mysqldump stored procedures Mysqldump export does not contain stored procedures Mysqldump-u database username-p-n-t-d-R -- triggers = false Database Name> file name In this way, the stored procedure and function are exported separately. Before importing the

and the table name. For example:--ignore-table=database.table1--ignore-table=database.table2 ... mysqldump-uroot-p--host=localhost-- All-databases--ignore-table=mysql.user--include-master-host-port the ' Change master ' generated in--dump-slave. Add ' master_host=SummarizeThe article lists some commonly used export operations, there are many other parameters are often used, including "--add-drop-database",

. This option is enabled by default, but can be disabled with -- skip-opt. To disable only the option enabled with-opt, use the -- skip format. for example, -- skip-add-drop-tables or -- skip-quick. -- Password [= password],-p [password] The password used to connect to the server. If you use the short option format (-p), there cannot be a space between the option and password. If the password value after the -- password or-p option is ignored in t

MySQL itself provides command line export tool mysqldump and MySQL source Import command for SQL data import and export work, through the MySQL command line export tool mysqldump command to export MySQL data into text Format (TXT) SQL file, The MySQL source command is able to import SQL files into the MySQL database, and the following is an example of using MySQL

Code Gunzip Transferring a database to a new serverMysqldump–u{username}–p{password} {DatabaseName} | mysql–host=*.*.*.*–c {DatabaseName}II. Other:1, if the port is the default 3306, you can omit the-p {port number}.2, the contents of the command line format {} are variablesHost name: {hostname}Port: {port} (general default 3306, can default)User name: {user} {username} (for example, root)Password: {password}Database name: {dat

; Db_name.sql It simply requests the lock table at the very first moment, then refreshes the binlog, and then adds the change MASTER statement to the exported file to specify the Binlog location of the current backup, which can be done if you want to restore the file to slave. 1.2 Restore A file backed up with Mysqldump is a SQL script that can be poured directly, and there are two ways to import the data. Directly with MySQL client For

specified SQL file.–add-drop-table: As the name implies, add the drop-table IF exists statement before each create Tabel command to prevent data table names.–add-locks: Represents locking and unlocking a specific data table before and after the insert data, you can open the mysqldump exported SQL file, and the lock tables and unlock tables statements appear before insert.–extended-insert (-E): This parameter indicates that multiple rows can be insert

, No_key_options, no_tables_options, or no_field_options. To use several values, separate them with commas. These values have the same meaning as the corresponding options for setting the server SQL mode.This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently able to make the dump output more compatible. For example,--compatible=oracle does not map Oracle types or data types that use Or

default. Use -- skip-triggers to disable it. For details about other parameters, see the manual. I usually use the following SQL to back up the MyISAM table: The Code is as follows: /Usr/local/mysql/bin/mysqldump-uyejr-pyejr-- Default-character-set = utf8 -- opt -- extended-insert = false-- Triggers-R -- hex-blob-x db_name> db_name. SQL Use the following SQL to back up the Innodb table: The Code is as follow

enables--skip-add-drop-tables 、--no-set-names 、--skip-disable-keys and--skip-add-locking options.· --compatible=nameProduce output that is more compatible with other database systems or older MySQL servers. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, or no_field_options. To use several values, separate them with commas. These values have the same meaning as the corresponding options for setting the server SQL mode.This option

-tables, -- no-set-names, -- skip-disable-keys, and -- skip-add-locking options. -- Compatible = name Generate more compatible output with other database systems or old mysql servers. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, or no_field_options. Use commas to separate the values. These values have the same meaning as the corresponding options for setting the server SQL mode. This option does not guarantee compatibility w

--fields-terminated-by=......,--fields-enclosed-by=......,--fields-optionally-enclosed-by=......,--fields-escaped-by= ... ...,--lines-terminated-by= ... To change the default delimiter, line break, and so on, such as: mysqldump--single-transaction--add-drop-database--tab= "/usr/local/mysql/data/test" test Most DBAs prefer to use SELECT ... into outfile the way to export a table, but you can do the work through mys