mysqldump parameters explain

2011-06-23  来源:本站原创  分类:Database  人气:111 

1, mysqldump
1.1 Backup
mysqldump is the use of SQL-level backup mechanism, which will lead into a data table SQL script file between different versions of MySQL upgrade compared to the

More appropriate, and this is the most common backup method.
Mysqldump now speaking about some of the key parameters:

- Compatible = name
It tells mysqldump, export the data and what kind of database or an older version of MySQL server compatible. Value can be ansi,

mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options,

no_field_options, etc., to use several values, separate them with commas. Of course, it does not guarantee full compatibility, but as far as possible compatible.

- Complete-insert,-c
Exported data contains field names using a complete INSERT mode, that is, all values ​​are written in one line. Do to improve the insertion efficiency, but

Max_allowed_packet parameter may be the impact of causing insertion. Therefore, careful use of the parameters, at least I do not recommend.

- Default-character-set = charset
Export data which specifies the character set used, if the data table instead of using the default character set latin1, then this option must be specified when exporting

Otherwise, import the data again, the problem will be garbled.

- Disable-keys
In the INSERT statement to tell mysqldump the beginning and end of increase / *! 40000 ALTER TABLE table DISABLE KEYS * /; and / *!

40000 ALTER TABLE table ENABLE KEYS * /; statement, which can greatly increase the speed of insert statement, because it is inserted all the data in

Reconstruction after the index. This option is only for MyISAM tables.

- Extended-insert = true | false
By default, mysqldump open the - complete-insert mode, so do not want it, then, use this option, set its value

false can be.

- Hex-blob
Export hexadecimal format using the binary string field. If binary data must use this option. Affect the field types are BINARY,


- Lock-all-tables,-x
Export at the beginning, before the request lock all tables in all databases to ensure data consistency. This is a global read lock, and auto-off

Close - single-transaction and - lock-tables option.

- Lock-tables
It and - lock-all-tables similar, but is currently locked to export the data table, instead of locking all the libraries at once under the table. This option is only available

In MyISAM tables, Innodb tables can be used if it is - single-transaction option.

- No-create-info,-t
Export data only, without adding the CREATE TABLE statement.

- No-data,-d
Does not export any data, just export the database table structure.

- Opt
This is just a shortcut option, equivalent to the same time add - add-drop-tables - add-locking - create-option - disable-keys

- Extended-insert - lock-tables - quick - set-charset option. This option allows mysqldump to export data quickly and

And can quickly lead to export the data back. This option is enabled by default, but you can use - skip-opt to disable. Note that if you run mysqldump not specified

- Quick or - opt option, then the entire result set in memory. If you are exporting a large database, then problems may arise.

- Quick,-q
This option is useful when exporting a large table, it forces mysqldump query to obtain records directly from the server output, not all the records after they made slow

Saved to memory.

- Routines,-R
Exporting stored procedures and custom functions.

- Single-transaction
This option before exporting the data presented in a BEGIN SQL statement, BEGIN does not block any applications and can export the database to ensure consistency

State. It applies only to the transaction table, such as InnoDB and BDB.
This option and - lock-tables option are mutually exclusive, because LOCK TABLES causes any pending transaction implicitly to submit.
To export a large table, then, in combination with the - quick option.

- Triggers
At the same time exporting triggers. This option is enabled by default, use - skip-triggers disable it.

Other parameters, please refer to manual, I usually use the following SQL to back up MyISAM tables:

/ 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 backup Innodb tables:

/ Usr / local / mysql / bin / mysqldump-uyejr-pyejr
- Default-character-set = utf8 - opt - extended-insert = false
- Triggers-R - hex-blob - single-transaction db_name> db_name.sql
In addition, if you want online backup, you can also use the - master-data parameter to achieve, as follows:

/ Usr / local / mysql / bin / mysqldump-uyejr-pyejr
- Default-character-set = utf8 - opt - master-data = 1
- Single-transaction - flush-logs db_name> db_name.sql
It is only at the beginning of the moment request the lock table, then refresh the binlog, and then add the exported file in the CHANGE MASTER statement to specify when

Binlog position before the backup, if we want this file to go back to the slave, you can use this method to do.

1.2 out of restore using mysqldump backup file is a SQL script directly into, there are two ways data can be imported.

For example, the direct use of mysql client:

/ Usr / local / mysql / bin / mysql-uyejr-pyejr db_name <db_name.sql

SOURCE syntax with the fact this is not standard SQL syntax, but the mysql client to provide features such as:

SOURCE / tmp / db_name.sql;
There need to specify the absolute path, and must be run mysqld user (such as nobody) has read permissions to the file.

2, mysqlhotcopy
2.1 Backup
mysqlhotcopy is a PERL program, written originally by Tim Bunce. It uses LOCK TABLES, FLUSH TABLES and cp or scp

To quickly back up the database. It is a backup database or the fastest way to a single table, but it only runs on the database files (including data table definition file

, Data files, index files) on the same machine. mysqlhotcopy only be used for backup MyISAM, and can only run on Unix and

NetWare systems.

mysqlhotcopy support a one-time copy of multiple databases, and also supports regular expressions. Here are a few examples:

root # / usr / local / mysql / bin / mysqlhotcopy-h = localhost-u = yejr-p = yejr
db_name / tmp (copied to the db_name database directory under / tmp)
root # / usr / local / mysql / bin / mysqlhotcopy-h = localhost-u = yejr-p = yejr
db_name_1 ... db_name_n / tmp
root # / usr / local / mysql / bin / mysqlhotcopy-h = localhost-u = yejr-p = yejr
db_name. / regex / / tmp
More details see the manual use, or invoke the following command to view mysqlhotcopy help:

perldoc / usr / local / mysql / bin / mysqlhotcopy
Note that you want to use mysqlhotcopy, must have SELECT, RELOAD (to execute FLUSH TABLES) rights, and also must be able to have

Read datadir / db_name directory.

2.2 Reduction
mysqlhotcopy back out of the entire database directory, use the copy to mysqld can be directly specified datadir (here

/ Usr / local / mysql / data /) directory, you can, keeping in mind the question of powers, the following example:

root # cp-rf db_name / usr / local / mysql / data /
root # chown-R nobody: nobody / usr / local / mysql / data / (db_name directory owner will change mysqld to run user)

3, SQL syntax backup
3.1 Backup
BACKUP TABLE Syntax fact and mysqlhotcopy works are similar with the lock table, then copy the data files. It can realize online backup

, But the result is not satisfactory, so not recommended. Only copy of the table structure and data files, not the same copy of the index file, so the recovery more


BACK TABLE tbl_name TO '/ tmp / db_name /';
Note that you must have the FILE privilege can execute this SQL, and the directory / tmp / db_name / mysqld user must be written, the exported file

Can not overwrite the existing file to avoid security problems.

SELECT INTO OUTFILE is guided out of the data as plain text file, you can customize the way the field interval, facilitate the processing of these data


SELECT * INTO OUTFILE '/ tmp / db_name / tbl_name.txt' FROM tbl_name;
Note that you must have the FILE privilege can execute this SQL, and file / tmp / db_name / tbl_name.txt mysqld must be user-writable

, The exported file can not overwrite the existing file to avoid security problems.

3.2 to restore a backup with BACKUP TABLE way out of the file, you can run the RESTORE TABLE statement to restore the data table.

RESTORE TABLE FROM '/ tmp / db_name /';
Requirements similar to the above permission.

Method using SELECT INTO OUTFILE back out of the file, you can run the LOAD DATA INFILE statement to restore the data table.

LOAD DATA INFILE '/ tmp / db_name / tbl_name.txt' INTO TABLE tbl_name;
Requirements similar to the above permission. Into the data before the data table should already exist Caixing. If you worry about duplicate data can increase the REPLACE

Keyword to replace the existing record or with the IGNORE keyword to ignore them.

4, enabled the binary log (binlog)
Method using binlog relatively more flexible, worry and effort, but also supports incremental backups.

Binlog enabled must be restarted mysqld. First, close the mysqld, open my.cnf, add the following lines:

server-id = 1
log-bin = binlog
log-bin-index = binlog.index
Then start mysqld on it. Operation will produce binlog.000001 and binlog.index, recorded in front of the file is mysqld

All updates to the data recording operation, the latter file is all binlog index, are not easily removed. See information on the binlog


Need to back up, you can execute your SQL statements first, so the termination of the current binlog mysqld write, you can back up the files directly, so

It can achieve the purpose of incremental backups:

FLUSH LOGS; If the backup copy from the server system, should also back up and files.

Binlog file can be backed up out of the tools provided with MySQL mysqlbinlog to see, such as:

/ Usr / local / mysql / bin / mysqlbinlog / tmp/binlog.000001
This tool allows you to display all of the specified database under the SQL statement, and also limit the time frame, very convenience, please see the manual for detailed


Recovery can be done similar to the following statement:

/ Usr / local / mysql / bin / mysqlbinlog / tmp/binlog.000001 | mysql-uyejr-pyejr db_name
Mysqlbinlog output of the SQL statement to execute it directly as input.

If you have a spare machine, may wish to adopt this approach to backup. As the performance requirements of the machine as a slave is relatively not so high, so low cost

, Incremental backups can be achieved with low cost but also share part of the pressure data query, why not?

5, directly compared to the previous backup data files in several ways, the most direct backup data files, fast and convenient, the disadvantage is basically incremental backup can not be achieved. To ensure consistent data

Of the need to back the file before performing the following SQL statement:

FLUSH TABLES WITH READ LOCK; that is, the data in memory are flushed to the disk, and lock the table to ensure that the copy process does not

There will be new data is written. This method to back up out of the data recovery is very simple, direct copy back to the original database directory.

Note that for Innodb type table, the need to back up the log files, ib_logfile * file. Because when Innodb tables damaged, it

You can rely on these log files to recover.

6, medium-level backup strategy for the volume of business systems, so a backup strategy can be: the amount of the first full backup, incremental backup once a day, do a full week

The amount of backup, so repeated. As for the important and busy system, you may need to once a day full volume backup, incremental backup once an hour

Even more frequently. In order not to affect the online business, online backup, incremental backup and can be the best way is to use master-slave replication

(Replication), the slave machine to do backup.

7, data maintenance and disaster recovery as a DBA (I am not now, huh, huh), one of the most important job is to ensure that data tables can be safe, stable, high-speed use. Therefore,

Regular maintenance of your data table. The following SQL statement is useful:

CHECK TABLE or REPAIR TABLE, inspection or maintenance of MyISAM tables
OPTIMIZE TABLE, MyISAM table optimization
ANALYZE TABLE, analyze MyISAM tables, of course, the above commands are available through the tool myisamchk start to finish, no detail here.

Innodb tables you can execute the following statement to defragment, to improve indexing speed:

ALTER TABLE tbl_name ENGINE = Innodb;
This is actually a NULL action, ostensibly to do nothing, in fact, re-finishing pieces.

MyISAM tables can normally use the recovery method used above to complete. If the index is broken, you can rebuild the indexes with myisamchk tool

. For Innodb tables, the not so straightforward, because it put all the tables are stored in a table space. However, there is a check Innodb

Search mechanism called the fuzzy checkpoint, just to save a log file, you can fix the error from the log file. In the my.cnf file, add the following

Parameters, so that mysqld at boot time automatically check the log file:

innodb_force_recovery = 4
Information about the parameters, see the manual.

  • mysqldump parameters explain 2011-06-23

    1, mysqldump 1.1 Backup mysqldump is the use of SQL-level backup mechanism, which will lead into a data table SQL script file between different versions of MySQL upgrade compared to the More appropriate, and this is the most common backup method. Mys

  • shutdown parameters explain 2010-12-29

    August 24, 2008 Unit one for Rising Antivirus 2008 software upgrade online computer at work when the auto-shutdown, the Internet search program that comes with the system, the task can be completed. Command (in the "Run" field type): At 18:00 /

  • The MySQL my.ini configuration parameters explain 2011-08-03

    1. Back_log MySQL may specify the number of connections. When MySQL is the main thread in a very short period of time to get a lot of connection requests, the parameters work, the main thread after some time (although very short) check the connection

  • oracle memory tuning parameters explain 2011-06-12

    Transfer: Instance = instance structure oracle memory architecture + oracle instance startup process structure process, in fact, the value of oracle memory parameters loaded into memory and start the approp

  • dbcp parameters explain 2011-10-19

    dbcp connection pool configuration official website address: username: User name to connect password : password to connect url: connection url (if you connect mysql, the form jdbc: mysql: / / ip: port

  • redis.conf configuration parameters explain 2011-07-15

    redis.conf configuration options are as follows daemonize if a background process running, the default is no pidfile is running as a background process, you need to specify a pid, the default is / var / run / bind bind host IP, the default

  • mysqldump carry the user names and passwords, export data parameters 2010-09-03

    Online a lot, but no, his practice a little, groping out a command line interface to enter: in the command line interface access to the mysql installation directory bin directory. Enter the command: mysqldump - default-character-set utf8-u root - pas

  • Vmx file of the virtual machine configuration file parameters to explain 2011-03-26

    From Thank tsunami_liao Share VMware parameters explain: Do not indiscriminate modify vmx file. Modify the vmx file before backup

  • oracle of the working mechanism 2010-03-15

    Quote The working mechanism of -1 ORACLE We started talking from a user request, ORACLE summary of the work mechanism is, first of all a user process sends a connection request, if using a host name or host name of local service hit using a machine n

  • Backup and restore MySQL DataBase 2010-11-14

    back windows_system mysql database: C: \> mysqldump - opt-u root - password = "123456" mydb>; backup.sql C: \> mysql-u root - password = "123456" mydb <; backup.sql ================================================== =======

  • RedHat Linux Grub repair 2010-11-20

    Think of the most practical way to restore grub directly to install redhat CD. first redhat installation CD can start rescue mode, you can perform with a basic shell to install grub and recovery. For this experiment, using rpm-e grub to grub uninstal

  • Notes the development process Linux command 2010-11-09

    1, find the file contents find-type f-name "*. java" | xargs grep "/" 2,. / Configure-prefix = / usr / libevent configuer command parameters explain: 3, make install; Install

  • DOS cycle: bat / command batch for Detailed bis (although a detailed summary of the history and description ~ ~) 2011-06-15

    IV. Parameters /F ( To deal with file parsing command output strings and file content .) --- This parameter is the most difficult, the addition of parameters , Explain briefly the :for Command files can be analyzed with the contents of this parameter

  • YourKit Java Profiler 9.5.1 and analytical thinking a 2010-12-10

    The following is my analysis of the program on the use of YoutKit some ideas! Idea of program analysis Analysis of the application CPU to get a class method is called the number and cost of CPU processing time. Operation • Start or Stop CPU assessmen

  • Modify sql server 2008 ports 2010-10-16

    View SQL Server 2005 port number, we can go to install the SQL Server service on the machine, through the configuration tool SQL Server Configuration Manager (SSCM) in come. If the SQL Server on a remote server, this approach will not work. However,

  • LINUX under the mysql case-sensitivity setting 2010-11-03

    1, linux after the default mysql installation under: distinguish between the case table names, column names are not case sensitive; 2, with the root account, in the / etc / my.cnf in the [mysqld], add add lower_case_table_names = 1, restart the MYSQL

  • MySQL built-in structure, the use of hash tables 2010-11-30

    Have recently implemented two patch to MySQL using the built-in hash structure. This structure is in the MySQL layer multiple use framework, it is easy to understand the code read. 1, the overall InnoDB also has built-HASH table, described in this ar

  • SQL Server with the SQL statement to view the port number 2011-09-01

    SQL statement used to view the port number to view SQL Server 2005 SQL Server 2005, port number, we can go to install the SQL Server service on the machine, through the configuration tool SQL Server Configuration Manager (SSCM) in come. If the SQL Se

  • explain the eclipse profile eclipse.ini parameters (jsp files can not include) 2010-12-07

    Encountered a strange problem today, in running the system, the page jumps jsp files that can not always include, solution is to actually set that as the eclipse -Vmargs -Xms256m -Xmx512m Originally 40m --- 256m, he looked a bit eclipse.ini instructi

  • explain fully analytical parameters 2011-02-15

    explain the parameters that appear in the full analysis: MySQL Explain to us in the show feature a variety of information to explain: ◆ ID: Query Optimizer Plan of Implementation of the selected query the serial number; ◆ Select_type: the types of qu