复制的使用方案,MySQL备份苏醒之mysqldump

 

mysqldump备份:

16.2. Replication Solutions

Replication can be used in many different environments for a range of
purposes. In this section you will find general notes and advice on
using replication for specific solution types.

For information on using replication in a backup environment, including
notes on the setup, backup procedure, and files to back up, see
Section 16.2.1, “Using Replication for Backups”.

For advice and tips on using different storage engines on the master and
slaves, see Section 16.2.2, “Using Replication with Different Master and
Slave Storage Engines”.

Using replication as a scale-out solution requires some changes in the
logic and operation of applications that use the solution. See
Section 16.2.3, “Using Replication for Scale-Out”.

For performance or data distribution reasons you may want to replicate
different databases to different replication slaves. See Section 16.2.4,
“Replicating Different Databases to Different Slaves”

As the number of replication slaves increases, the load on the master
can increase (because of the need to replicate the binary log to each
slave) and lead to a reduction in performance of the master. For tips on
improving your replication performance, including using a single
secondary server as an replication master, see Section 16.2.5,
“Improving Replication Performance”.

For guidance on switching masters, or converting slaves into masters as
part of an emergency failover solution, see Section 16.2.6, “Switching
Masters During Failover”.

To secure your replication communication you can encrypt the
communication channel by using SSL to exchange data. Step-by-step
instructions can be found in Section 16.2.7, “Setting Up Replication
Using SSL”.

mysqldump备份:

Preface

复制代码 代码如下:

16.2.1. Using Replication for Backups

You can use replication as a backup solution by replicating data from
the master to a slave, and then backing up the data slave. Because the
slave can be paused and shut down without affecting the running
operation of the master you can produce an effective snapshot of ‘live’
data that would otherwise require a shutdown of the master database.

How you back up the database will depend on the size of the database and
whether you are backing up only the data, or the data and the
replication slave state so that you can rebuild the slave in the event
of failure. There are therefore two choices:

If you are using replication as a solution to enable you to back up the
data on the master, and the size of your database is not too large, then
the mysqldump tool may be suitable. See Section 16.2.1.1, “Backing
Up a Slave Using mysqldump”.

For larger databases, where mysqldump would be impractical or
inefficient, you can back up the raw data files instead. Using the raw
data files option also means that you can back up the binary and relay
logs that will enable you to recreate the slave in the event of a slave
failure. For more information, see Section 16.2.1.2, “Backing Up Raw
Data from a Slave”.

Another backup strategy, which can be used for either master or slave
servers, is to put the server in a read-only state. The backup is
performed against the read-only server, which then is changed back to
its usual read/write operational status. See Section 16.2.1.3, “Backing
Up a Master or Slave by Making It Read Only”.

复制代码 代码如下:

 

mysqldump -u用户名 -p密码 -h主机 数据库 a -w “sql条件” –lock-all-tables
> 路径

16.2.1.1. Backing Up a Slave Using mysqldump

Using mysqldump to create a copy of the database enables you to
capture all of the data in the database in a format that allows the
information to be imported into another instance of MySQL. Because the
format of the information is SQL statements the file can easily be
distributed and applied to running servers in the event that you need
access to the data in an emergency. However, if the size of your data
set is very large then mysqldump may be impractical.

When using mysqldump you should stop the slave before starting the
dump process to ensure that the dump contains a consistent set of data:

  1. Stop the slave from processing requests. You can either stop the
    slave completely using mysqladmin:

    shell> mysqladmin stop-slave
    

    Alternatively, you can stop processing the relay log files by
    stopping the replication SQL thread. Using this method will allow
    the binary log data to be transferred. Within busy replication
    environments this may speed up the catch-up process when you start
    the slave processing again:

    shell> mysql -e 'STOP SLAVE SQL_THREAD;'
    
  2. Run mysqldump to dump your databases. You may either select
    databases to be dumped, or dump all databases. For more information,
    see Section 4.5.4, “mysqldump — A Database Backup Program”. For
    example, to dump all databases:

    shell> mysqldump --all-databases >fulldb.dump
    
  3. Once the dump has completed, start slave operations again:

    shell> mysqladmin start-slave
    

In the preceding example you may want to add login credentials (user
name, password) to the commands, and bundle the process up into a script
that you can run automatically each day.

If you use this approach, make sure you monitor the slave replication
process to ensure that the time taken to run the backup in this way is
not affecting the slave’s ability to keep up with events from the
master. See Section 16.1.4.1, “Checking Replication Status”. If the
slave is unable to keep up you may want to add another server and
distribute the backup process. For an example of how to configure this
scenario, see Section 16.2.4, “Replicating Different Databases to
Different Slaves”.

mysqldump -u用户名 -p密码 -h主机 数据库 a -w “sql条件” –lock-all-tables
> 路径

    The day before yesterday,there’s a
motif about the lock procedure when backing up MySQL using mysqldump or
Xtrabackup in “Ask Ye” which is like a kind of Q&A originated by Mr.
Ye.In my essay today,I’m gonna use
the tool mysqlsump to do some tests then observe procedures of backing
up MySQL database,here we go.

mysqldump还原:

16.2.1.2. Backing Up Raw Data from a Slave

To guarantee the integrity of the files that are copied, backing up the
raw data files on your MySQL replication slave should take place while
your slave server is shut down. If the MySQL server is still running
then background tasks, particularly with storage engines with background
processes such as InnoDB, may still be updating the database files. With
InnoDB, these problems should be resolved during crash recovery, but
since the slave server can be shut down during the backup process
without affecting the execution of the master it makes sense to take
advantage of this facility.

To shut down the server and back up the files:

  1. Shut down the slave MySQL server:

    shell> mysqladmin shutdown
    
  2. Copy the data files. You can use any suitable copying or archive
    utility, including cp, tar or WinZip:

    shell> tar cf /tmp/dbbackup.tar ./data
    
  3. Start up the mysqld process again:

    shell> mysqld_safe &
    

    Under Windows:

    C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld"
    

Normally you should back up the entire data folder for the slave MySQL
server. If you want to be able to restore the data and operate as a
slave (for example, in the event of failure of the slave), then when you
back up the slave’s data, you should back up the slave status files,
master.info and relay-log.info, along with the relay log files.
These files are needed to resume replication after you restore the
slave’s data.

If you lose the relay logs but still have the relay-log.info file, you
can check it to determine how far the SQL thread has executed in the
master binary logs. Then you can use CHANGE MASTER TO with the
MASTER_LOG_FILE and MASTER_LOG_POS options to tell the slave to
re-read the binary logs from that point. Of course, this requires that
the binary logs still exist on the master server.

If your slave is subject to replicating LOAD DATA INFILE statements,
you should also back up any SQL_LOAD-* files that exist in the
directory that the slave uses for this purpose. The slave needs these
files to resume replication of any interrupted LOAD DATA INFILE
operations. The directory location is specified using the
--slave-load-tmpdir option. If this option is not specified, the
directory location is the value of the tmpdir system variable.

mysqldump还原:

 

复制代码 代码如下:

16.2.1.3. Backing Up a Master or Slave by Making It Read Only

It is possible to back up either master or slave servers in a
replication setup by acquiring a global read lock and manipulating the
read_only system variable to change the read-only state of the server
to be backed up:

  1. Make the server read-only, so that it processes only retrievals and
    blocks updates

  2. Perform the backup

  3. Change the server back to its normal read/write state

The following instructions describe how to do this for a master server
and for a slave server.

These instructions require MySQL 5.1.15 or higher. For earlier versions,
setting read_only did not block while table locks or outstanding
transactions were pending, so that some data changes could still occur
during the backup.

复制代码 代码如下:

Introduction

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

Note

The instructions in this section place the server to be backed up in a
state that is safe for backup methods that get the data from the server,
such as mysqldump (see Section 4.5.4, “mysqldump — A Database
Backup Program”). You should not attempt to use these instructions to
make a binary backup by copying files directly because the server may
still have modified data cached in memory and not flushed to disk.

For both scenarios discussed here, suppose that you have the following
replication setup:

  • A master server M1

  • A slave server S1 that has M1 as its master

  • A client C1 connected to M1

  • A client C2 connected to S1

Scenario 1: Backup with a Read-Only Master

Put the master M1 in a read-only state by executing these statements on
it:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

While M1 is in a read-only state, the following properties are true:

  • Requests for updates sent by C1 to M1 will fail because the server
    is in read-only mode

  • Requests for retrievals sent by C1 to M1 will succeed

  • Making a backup on M1 is safe

  • Making a backup on S1 is not safe: this server is still running, and
    might be processing the binary log or update requests coming from
    client C2 (S1 might not be in a read-only state)

While M1 is read only, perform the backup. For example, you can use
mysqldump.

After the backup on M1 has been done, restore M1 to its normal
operational state by executing these statements:

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

Although performing the backup on M1 is safe (as far as the backup is
concerned), it is not optimal because clients of M1 are blocked from
executing updates.

This strategy also applies to backing up a single server in a
non-replication setting.

Scenario 2: Backup with a Read-Only Slave

Put the slave S1 in a read-only state by executing these statements on
it:

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

While S1 is in a read-only state, the following properties are true:

  • The master M1 will continue to operate

  • Making a backup on the master is not safe

  • The slave S1 is stopped

  • Making a backup on the slave S1 is safe

These properties provide the basis for a popular backup scenario: Having
one slave busy performing a backup for a while is not a problem because
it does not affect the entire network, and the system is still running
during the backup. (For example, clients can still perform updates on
the master server.)

While S1 is read only, perform the backup.

After the backup on S1 has been done, restore S1 to its normal
operational state by executing these statements:

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

After the slave is restored to normal operation, it again synchronizes
to the master by catching up with any outstanding updates in the binary
log from the master.

In either scenario, the statements to acquire the global read lock and
manipulate the read_only variable are performed on the server to be
backed up and do not propagate to any slaves of that server.

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

 

mysqldump按原则导出:

16.2.2. Using Replication with Different Master and Slave Storage Engines

The replication process does not care if the source table on the master
and the replicated table on the slave use different engine types. In
fact, the system variables storage_engine and table_type are not
replicated.

This provides a number of advantages in the replication process in that
you can take advantage of different engine types for different
replication scenarios. For example, in a typical scaleout scenario (see
Section 16.2.3, “Using Replication for Scale-Out”), you want to use
InnoDB tables on the master to take advantage of the transactional
functionality, but use MyISAM on the slaves where transaction support
is not required because the data is only read. When using replication in
a data logging environment you may want to use the Archive storage
engine on the slave.

Setting up different engines on the master and slave depends on how you
set up the initial replication process:

  • If you used mysqldump to create the database snapshot on your
    master then you could edit the dump text to change the engine type
    used on each table.

    Another alternative for mysqldump is to disable engine types
    that you do not want to use on the slave before using the dump to
    build the data on the slave. For example, you can add the
    --skip-innodb option on your slave to disable the InnoDB engine.
    If a specific engine does not exist, MySQL will use the default
    engine type, usually MyISAM. If you want to disable further
    engines in this way, you may want to consider building a special
    binary to be used on the slave that only supports the engines you
    want.

  • If you are using raw data files for the population of the slave, you
    will be unable to change the initial table format. Instead, use
    ALTER TABLE to change the table types after the slave has been
    started.

  • For new master/slave replication setups where there are currently no
    tables on the master, avoid specifying the engine type when creating
    new tables.

If you are already running a replication solution and want to convert
your existing tables to another engine type, follow these steps:

  1. Stop the slave from running replication updates:

    mysql> STOP SLAVE;
    

    This will enable you to change engine types without interruptions.

  2. Execute an ALTER TABLE ... Engine='enginetype‘ for each table
    where you want to change the engine type.

  3. Start the slave replication process again:

    mysql> START SLAVE;
    

Although the storage_engine and table_type variables are not
replicated, be aware that CREATE TABLE and ALTER TABLE statements
that include the engine specification will be correctly replicated to
the slave. For example, if you have a CSV table and you execute:

mysql> ALTER TABLE csvtable Engine='MyISAM';

The above statement will be replicated to the slave and the engine type
on the slave will be converted to MyISAM, even if you have previously
changed the table type on the slave to an engine other than CSV. If you
want to retain engine differences on the master and slave, you should be
careful to use the storage_engine variable on the master when creating
a new table. For example, instead of:

mysql> CREATE TABLE tablea (columna int) Engine=MyISAM;

Use this format:

mysql> SET storage_engine=MyISAM;
mysql> CREATE TABLE tablea (columna int);

When replicated, the storage_engine variable will be ignored, and the
CREATE TABLE statement will be executed with the slave’s default
engine type.

mysqldump按规则导出:

    mysqldump is a protogenic logical
backup tool of MySQL.As soon as you’ve installed MySQL server,it can be
used in command line directly.

复制代码 代码如下:

16.2.3. Using Replication for Scale-Out

You can use replication as a scale-out solution, i.e. where you want to
split up the load of database queries across multiple database servers,
within some reasonable limitations.

Because replication works from the distribution of one master to one or
more slaves, using replication for scaleout works best in an environment
where you have a high number of reads and low number of writes/updates.
Most websites fit into this category, where users are browsing the
website, reading articles, posts, or viewing products. Updates only
occur during session management, or when making a purchase or adding a
comment/message to a forum.

Replication in this situation enables you to distribute the reads over
the replication slaves, while still allowing your web servers to
communicate with the replication master when a write is required. You
can see a sample replication layout for this scenario in Figure 16.1,
“Using replication to improve the performance during scaleout”.

Figure 16.1. Using replication to improve the performance during
scaleout

997755.com澳门葡京 1

If the part of your code that is responsible for database access has
been properly abstracted/modularized, converting it to run with a
replicated setup should be very smooth and easy. Change the
implementation of your database access to send all writes to the master,
and to send reads to either the master or a slave. If your code does not
have this level of abstraction, setting up a replicated system gives you
the opportunity and motivation to clean it up. Start by creating a
wrapper library or module that implements the following functions:

  • safe_writer_connect()

  • safe_reader_connect()

  • safe_reader_statement()

  • safe_writer_statement()

safe_ in each function name means that the function takes care of
handling all error conditions. You can use different names for the
functions. The important thing is to have a unified interface for
connecting for reads, connecting for writes, doing a read, and doing a
write.

Then convert your client code to use the wrapper library. This may be a
painful and scary process at first, but it pays off in the long run. All
applications that use the approach just described are able to take
advantage of a master/slave configuration, even one involving multiple
slaves. The code is much easier to maintain, and adding troubleshooting
options is trivial. You need modify only one or two functions; for
example, to log how long each statement took, or which statement among
those issued gave you an error.

If you have written a lot of code, you may want to automate the
conversion task by using the replace utility that comes with
standard MySQL distributions, or write your own conversion script.
Ideally, your code uses consistent programming style conventions. If
not, then you are probably better off rewriting it anyway, or at least
going through and manually regularizing it to use a consistent style.

复制代码 代码如下:

    mysqldump logically dumps data in the
target database then gernerate a sql file which can be used to restore
the the very database later.it’s rather convenient to backup a single or
multiple databases online(innodb only) but the side-effect is
that it can hold lock when backing up.

mysqldump -u用户名 -p密码 -h主机 数据库 a –where “条件语句” –no-建表>
路线
mysqldump -uroot -p1234 dbname a –where “tag=’88′” –no-create-info>
c:\a.sql

16.2.4. Replicating Different Databases to Different Slaves

There may be situations where you have a single master and want to
replicate different databases to different slaves. For example, you may
want to distribute different sales data to different departments to help
spread the load during data analysis. A sample of this layout is shown
in Figure 16.2, “Using replication to replicate databases to separate
replication slaves”.

Figure 16.2. Using replication to replicate databases to separate
replication slaves

997755.com澳门葡京 2

You can achieve this separation by configuring the master and slaves as
normal, and then limiting the binary log statements that each slave
processes by using the --replicate-wild-do-table configuration option
on each slave.

mysqldump -u用户名 -p密码 -h主机 数据库 a –where “条件语句” –no-建表>
路线
mysqldump -uroot -p1234 dbname a –where “tag=’88′” –no-create-info>
c:\a.sql

 

mysqldump按规范导入:

Important

You should not use --replicate-do-db
for this purpose when using statement-based replication, since
statement-based replication causes this option’s affects to vary
according to the database that is currently selected. This applies to
mixed-format replication as well, since this allows some updates to be
replicated using the statement-based format.

However, it should be to use --replicate-do-db for this purpose if you
are using row-based replication only, since in this case the
currently-selected database has no effect on the option’s operation.

For example, to support the separation as shown in Figure 16.2, “Using
replication to replicate databases to separate replication slaves”, you
should configure each replication slave as follows, before executing
START SLAVE:

  • Replication slave 1 should use
    --replicate-wild-do-table=databaseA.%.

  • Replication slave 2 should use
    --replicate-wild-do-table=databaseB.%.

  • Replication slave 3 should use
    --replicate-wild-do-table=databaseC.%.

If you have data that needs to be synchronized to the slaves before
replication starts, you have a number of choices:

  • Synchronize all the data to each slave, and delete the databases,
    tables, or both that you do not want to keep.

  • Use mysqldump to create a separate dump file for each database
    and load the appropriate dump file on each slave.

  • Use a raw data file dump and include only the specific files and
    databases that you need for each slave.

    ### Note

    This does not work with InnoDB databases unless you use
    innodb_file_per_table.

Each slave in this configuration receives the entire binary log from the
master, but executes only those events from the binary log that apply to
the databases and tables included by the --replicate-wild-do-table
option in effect on that slave.

mysqldump按标准导入:

Procedure

复制代码 代码如下:

16.2.5. Improving Replication Performance

As the number of slaves connecting to a master increases, the load,
although minimal, also increases, as each slave uses up a client
connection to the master. Also, as each slave must receive a full copy
of the master binary log, the network load on the master may also
increase and start to create a bottleneck.

If you are using a large number of slaves connected to one master, and
that master is also busy processing requests (for example, as part of a
scaleout solution), then you may want to improve the performance of the
replication process.

One way to improve the performance of the replication process is to
create a deeper replication structure that enables the master to
replicate to only one slave, and for the remaining slaves to connect to
this primary slave for their individual replication requirements. A
sample of this structure is shown in Figure 16.3, “Using an additional
replication host to improve performance”.

Figure 16.3. Using an additional replication host to improve
performance

997755.com澳门葡京 3

For this to work, you must configure the MySQL instances as follows:

  • Master 1 is the primary master where all changes and updates are
    written to the database. Binary logging should be enabled on this
    machine.

  • Master 2 is the slave to the Master 1 that provides the replication
    functionality to the remainder of the slaves in the replication
    structure. Master 2 is the only machine allowed to connect to
    Master 1. Master 2 also has binary logging enabled, and the
    --log-slave-updates option so that replication instructions from
    Master 1 are also written to Master 2’s binary log so that they can
    then be replicated to the true slaves.

  • Slave 1, Slave 2, and Slave 3 act as slaves to Master 2, and
    replicate the information from Master 2, which is really the data
    logged on Master 1.

The above solution reduces the client load and the network interface
load on the primary master, which should improve the overall performance
of the primary master when used as a direct database solution.

If your slaves are having trouble keeping up with the replication
process on the master then there are a number of options available:

  • If possible, you should put the relay logs and the data files on
    different physical drives. To do this, use the --relay-log option
    to specify the location of the relay log.

  • If the slaves are significantly slower than the master, then you may
    want to divide up the responsibility for replicating different
    databases to different slaves. See Section 16.2.4, “Replicating
    Different Databases to Different Slaves”.

  • If your master makes use of transactions and you are not concerned
    about transaction support on your slaves, then use MyISAM or
    another non-transactional engine. See Section 16.2.2, “Using
    Replication with Different Master and Slave Storage Engines”.

  • If your slaves are not acting as masters, and you have a potential
    solution in place to ensure that you can bring up a master in the
    event of failure, then you can switch off --log-slave-updates.
    This prevents ‘dumb’ slaves from also logging events they have
    executed into their own binary log.

复制代码 代码如下:

 

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

16.2.6. Switching Masters During Failover

There is currently no official solution for providing failover between
master and slaves in the event of a failure. With the currently
available features, you would have to set up a master and a slave (or
several slaves), and to write a script that monitors the master to check
whether it is up. Then instruct your applications and the slaves to
change master in case of failure.

Remember that you can tell a slave to change its master at any time,
using the CHANGE MASTER TO复制的使用方案,MySQL备份苏醒之mysqldump。 statement. The slave will not check whether
the databases on the master are compatible with the slave, it will just
start executing events from the specified log and position on the new
master. In a failover situation all the servers in the group are
probably executing the same events from the same binary log, so changing
the source of the events should not affect the database structure or
integrity providing you are careful.

Run your slaves with the --log-bin option and without
--log-slave-updates. In this way, the slave is ready to become a
master as soon as you issue STOP SLAVE; RESET MASTER, and
CHANGE MASTER TO statement on the other slaves. For example, assume
that you have the structure shown in Figure 16.4, “Redundancy using
replication, initial structure”.

Figure 16.4. Redundancy using replication, initial structure

997755.com澳门葡京 4

In this diagram, the MySQL Master holds the master database, the
MySQL Slave computers are replication slaves, and the Web Client
machines are issuing database reads and writes. Web clients that issue
only reads (and would normally be connected to the slaves) are not
shown, as they do not need to switch to a new server in the event of
failure. For a more detailed example of a read/write scaleout
replication structure, see Section 16.2.3, “Using Replication for
Scale-Out”.

Each MySQL Slave (Slave 1, Slave 2, and Slave 3) are slaves
running with --log-bin and without --log-slave-updates. Because
updates received by a slave from the master are not logged in the binary
log unless --log-slave-updates is specified, the binary log on each
slave is empty initially. If for some reason MySQL Master becomes
unavailable, you can pick one of the slaves to become the new master.
For example, if you pick Slave 1, all Web Clients should be
redirected to Slave 1, which will log updates to its binary log.
Slave 2 and Slave 3 should then replicate from Slave 1.

The reason for running the slave without --log-slave-updates is to
prevent slaves from receiving updates twice in case you cause one of the
slaves to become the new master. Suppose that Slave 1 has
--log-slave-updates enabled. Then it will write updates that it
receives from Master to its own binary log. When Slave 2 changes
from Master to Slave 1 as its master, it may receive updates from
Slave 1 that it has already received from Master

Make sure that all slaves have processed any statements in their relay
log. On each slave, issue STOP SLAVE IO_THREAD, then check the output
of SHOW PROCESSLIST until you see Has read all relay log. When this
is true for all slaves, they can be reconfigured to the new setup. On
the slave Slave 1 being promoted to become the master, issue
STOP SLAVE and RESET MASTER.

On the other slaves Slave 2 and Slave 3, use STOP SLAVE and
CHANGE MASTER TO MASTER_HOST='Slave1' (where 'Slave1' represents the
real host name of Slave 1). To use CHANGE MASTER TO, add all
information about how to connect to Slave 1 from Slave 2 or
Slave 3 (user, password, port). In CHANGE MASTER TO,
there is no need to specify the name of Slave 1‘s binary log or binary
log position to read from: We know it is the first binary log and
position 4, which are the defaults for CHANGE MASTER TO. Finally, use
START SLAVE on Slave 2 and Slave 3.

Once the new replication is in place, you will then need to instruct
each Web Client to direct their statements to Slave 1. From that
point on, all updates statements sent by Web Client to Slave 1 are
written to the binary log of Slave 1, which then contains every update
statement sent to Slave 1 since Master died.

The resulting server structure is shown in Figure 16.5, “Redundancy
using replication, after master failure”.

Figure 16.5. Redundancy using replication, after master failure

997755.com澳门葡京 5

When Master is up again, you must issue on it the same
CHANGE MASTER TO as that issued on Slave 2 and Slave 3, so that
Master becomes a slave of S1 and picks up each Web Client writes
that it missed while it was down.

To make Master a master again (because it is the most powerful
machine, for example), use the preceding procedure as if Slave 1 was
unavailable and Master was to be the new master. During this
procedure, do not forget to run RESET MASTER on Master before making
Slave 1, Slave 2, and Slave 3 slaves of Master. Otherwise, they
may pick up old Web Client writes from before the point at which
Master became unavailable.

Note that there is no synchronization between the different slaves to a
master. Some slaves might be ahead of others. This means that the
concept outlined in the previous example might not work. In practice,
however, the relay logs of different slaves will most likely not be far
behind the master, so it would work, anyway (but there is no guarantee).

A good way to keep your applications informed as to the location of the
master is by having a dynamic DNS entry for the master. With bind you
can use nsupdate to dynamically update your DNS.

mysqldump -u用户名 -p密码 -h主机 数据库 < 路径

    In
order to see the intact procedure of backup,we open the general log by
setting “general_log=on”  in runtime first.

案例:

16.2.7. Setting Up Replication Using SSL

Setting up replication using an SSL connection is similar to setting up
a server and client using SSL. You will need to obtain (or create) a
suitable security certificate that you can use on the master, and a
similar certificate (from the same certificate authority) on each slave.

To use SSL for encrypting the transfer of the binary log required during
replication you must first set up the master to support SSL network
connections. If the master does not support SSL connections (because it
has not been compiled or configured for SSL), then replication through
an SSL connection will not be possible.

For more information on setting up a server and client for SSL
connectivity, see Section 5.5.7.2, “Using SSL Connections”.

To enable SSL on the master you will need to create or obtain suitable
certificates and then add the following configuration options to the
master’s configuration within the mysqld section:

ssl-ca=cacert.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

案例:

 

复制代码 代码如下:

Note

You should use full path to specify the location of your certificate
files.

The options are as follows:

  • ssl-ca identifies the Certificate Authority (CA) certificate.

  • ssl-cert identifies the server public key. This can be sent to the
    client and authenticated against the CA certificate that it has.

  • ssl-key identifies the server private key.

On the slave, you have two options available for setting the SSL
information. You can either add the slaves certificates to the client
section of the slave configuration file, or you can explicitly specify
the SSL information using the CHANGE MASTER TO statement.

Using the former option, add the following lines to the client section
of the slave configuration file:

[client]
ssl-ca=cacert.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

Restart the slave server, using the --skip-slave to prevent the slave
from connecting to the master. Use CHANGE MASTER TO to specify the
master configuration, using the master_ssl option to enable SSL
connectivity:

mysql> CHANGE MASTER TO \
    MASTER_HOST='master_hostname', \
    MASTER_USER='replicate', \
    MASTER_PASSWORD='password', \
    MASTER_SSL=1;

To specify the SSL certificate options during the CHANGE MASTER TO
command, append the SSL options:

CHANGE MASTER TO \
      MASTER_HOST='master_hostname', \
      MASTER_USER='replicate', \
      MASTER_PASSWORD='password', \
      MASTER_SSL=1, \
      MASTER_SSL_CA = 'ca_file_name', \
      MASTER_SSL_CAPATH = 'ca_directory_name', \
      MASTER_SSL_CERT = 'cert_file_name', \
      MASTER_SSL_KEY = 'key_file_name';

Once the master information has been updated, start the slave
replication process:

mysql> START SLAVE;

You can use the SHOW SLAVE STATUS to confirm that SSL connection has
been completed.

For more information on the CHANGE MASTER TO syntax, see
Section 12.6.2.1, “CHANGE MASTER TO Syntax”.

If you want to enforce SSL connections to be used during replication,
then create a user with the REPLICATION SLAVE privilege and use the
REQUIRE_SSL option for that user. For example:

mysql> GRANT REPLICATION SLAVE ON *.*
    -> TO 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass' REQUIRE SSL;

复制代码 代码如下:

 1 ###Open General Log###
 2 (root@localhost mysql3306.sock)[performance_schema]09:29:17>show variables like '%gener%';
 3 +------------------+-------------------------------------+
 4 | Variable_name    | Value                               |
 5 +------------------+-------------------------------------+
 6 | general_log      | OFF                                 |
 7 | general_log_file | /data/mysql/mysql3306/data/zlm2.log |
 8 +------------------+-------------------------------------+
 9 2 rows in set (0.00 sec)
10 
11 (root@localhost mysql3306.sock)[performance_schema]09:29:23>set general_log=on;
12 ERROR 1229 (HY000): Variable 'general_log' is a GLOBAL variable and should be set with SET GLOBAL
13 (root@localhost mysql3306.sock)[performance_schema]09:29:39>set global general_log=on;
14 Query OK, 0 rows affected (0.14 sec)
15 
16 ###Create innodb table and myisam table in database zlm.###
17 (root@localhost mysql3306.sock)[zlm]09:36:56>create table test_innodb(id int primary key) engine=innodb;
18 Query OK, 0 rows affected (0.02 sec)
19 
20 (root@localhost mysql3306.sock)[zlm]09:37:17>create table test_myisam(id int primary key) engine=myisam;
21 Query OK, 0 rows affected (0.01 sec)
22 
23 (root@localhost mysql3306.sock)[zlm]09:37:42>show tables;
24 +---------------+
25 | Tables_in_zlm |
26 +---------------+
27 | test_innodb   |
28 | test_myisam   |
29 +---------------+
30 2 rows in set (0.00 sec)
31 
32 (root@localhost mysql3306.sock)[zlm]09:37:45>
33 
34 ###Gnerate First Backup(disable triggers)###
35 [root@zlm2 09:50:08 ~]
36 #mysqldump --triggers=false -B zlm > /data/backup/first_3306-`date +%Y%m%d`.sql
37 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
38 
39 [root@zlm2 09:53:41 ~]
40 #ls -l /data/backup
41 total 8
42 -rw-r--r-- 1 root root 2822 Jun 15 09:53 first_3306-20180615.sql
43 
44 ###Check General Log###
45 [root@zlm2 09:52:57 /data/mysql/mysql3306/data]
46 #cat zlm2.log
47 
48 2018-06-15T07:53:41.345850Z       32 Connect    root@localhost on  using Socket
49 2018-06-15T07:53:41.346193Z       32 Query    /*!40100 SET @@SQL_MODE='' */
50 2018-06-15T07:53:41.346252Z       32 Query    /*!40103 SET TIME_ZONE='+00:00' */
51 2018-06-15T07:53:41.346332Z       32 Query    SHOW VARIABLES LIKE 'gtid\_mode'
52 2018-06-15T07:53:41.348422Z       32 Query    SELECT @@GLOBAL.GTID_EXECUTED
53 2018-06-15T07:53:41.350309Z       32 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
54 2018-06-15T07:53:41.351603Z       32 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
55 2018-06-15T07:53:41.352151Z       32 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
56 2018-06-15T07:53:41.354634Z       32 Init DB    zlm
57 2018-06-15T07:53:41.354659Z       32 Query    SHOW CREATE DATABASE IF NOT EXISTS `zlm`
58 2018-06-15T07:53:41.354660Z       32 Query    show tables
59 2018-06-15T07:53:41.354747Z       32 Query    LOCK TABLES `test_innodb` READ /*!32311 LOCAL */,`test_myisam` READ /*!32311 LOCAL */
60 2018-06-15T07:53:41.354815Z       32 Query    show table status like 'test\_innodb'
61 2018-06-15T07:53:41.355067Z       32 Query    SET SQL_QUOTE_SHOW_CREATE=1
62 2018-06-15T07:53:41.355110Z       32 Query    SET SESSION character_set_results = 'binary'
63 2018-06-15T07:53:41.355144Z       32 Query    show create table `test_innodb`
64 2018-06-15T07:53:41.355188Z       32 Query    SET SESSION character_set_results = 'utf8'
65 2018-06-15T07:53:41.355227Z       32 Query    show fields from `test_innodb`
66 2018-06-15T07:53:41.355412Z       32 Query    show fields from `test_innodb`
67 2018-06-15T07:53:41.355631Z       32 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
68 2018-06-15T07:53:41.356449Z       32 Query    show table status like 'test\_myisam'
69 2018-06-15T07:53:41.356723Z       32 Query    SET SQL_QUOTE_SHOW_CREATE=1
70 2018-06-15T07:53:41.356759Z       32 Query    SET SESSION character_set_results = 'binary'
71 2018-06-15T07:53:41.356819Z       32 Query    show create table `test_myisam`
72 2018-06-15T07:53:41.356863Z       32 Query    SET SESSION character_set_results = 'utf8'
73 2018-06-15T07:53:41.356900Z       32 Query    show fields from `test_myisam`
74 2018-06-15T07:53:41.357109Z       32 Query    show fields from `test_myisam`
75 2018-06-15T07:53:41.357349Z       32 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
76 2018-06-15T07:53:41.357420Z       32 Query    UNLOCK TABLES --Release the lock only after all tables have finished backup.
77 2018-06-15T07:53:41.361654Z       32 Quit    

mysql -uroot -p1234 db1 < c:\a.txt

mysql -uroot -p1234 db1 < c:\a.txt

 

mysqldump导出表:

mysqldump导出表:

   
I’ve make the output be simplest by disable triggers’ backup.We can see
from general log is that “LOCK TABLES test_innodb READ” then the
“UNLOCK TABLES” statment.Let’s see the difference with parameter
“single-transaction”.

复制代码 代码如下:

复制代码 代码如下:

 

mysqldump -u用户名 -p密码 -h主机 数据库 表

mysqldump -u用户名 -p密码 -h主机 数据库 表

 1 ###Gnerate Sceond Backup(enable transaction consistent backup)###
 2 [root@zlm2 10:00:41 ~]
 3 #mysqldump --triggers=false --single-transaction -B zlm > /data/backup/second_3306-`date +%Y%m%d`.sql
 4 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 5 
 6 [root@zlm2 10:02:16 ~]
 7 #ls -l /data/backup
 8 total 8
 9 -rw-r--r-- 1 root root 2822 Jun 15 09:53 first_3306-20180615.sql
10 -rw-r--r-- 1 root root 2822 Jun 15 10:00 second_3306-20180615.sql
11 
12 ###Check General Log###
13 [root@zlm2 10:00:56 /data/mysql/mysql3306/data]
14 #cat zlm2.log
15 
16 2018-06-15T08:00:52.911566Z       34 Connect    root@localhost on  using Socket
17 2018-06-15T08:00:52.911733Z       34 Query    /*!40100 SET @@SQL_MODE='' */
18 2018-06-15T08:00:52.911848Z       34 Query    /*!40103 SET TIME_ZONE='+00:00' */
19 2018-06-15T08:00:52.912749Z       34 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
20 2018-06-15T08:00:52.912839Z       34 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
21 2018-06-15T08:00:52.912919Z       34 Query    SHOW VARIABLES LIKE 'gtid\_mode'
22 2018-06-15T08:00:52.915228Z       34 Query    SELECT @@GLOBAL.GTID_EXECUTED
23 2018-06-15T08:00:52.915371Z       34 Query    UNLOCK TABLES --Release lock here untill end,there're no more locks.
24 2018-06-15T08:00:52.915568Z       34 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
25 2018-06-15T08:00:52.916737Z       34 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
26 2018-06-15T08:00:52.918498Z       34 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
27 2018-06-15T08:00:52.920665Z       34 Init DB    zlm
28 2018-06-15T08:00:52.920742Z       34 Query    SHOW CREATE DATABASE IF NOT EXISTS `zlm`
29 2018-06-15T08:00:52.920787Z       34 Query    SAVEPOINT sp --Notice,there's a save opoint here.
30 2018-06-15T08:00:52.920837Z       34 Query    show tables
31 2018-06-15T08:00:52.921068Z       34 Query    show table status like 'test\_innodb'
32 2018-06-15T08:00:52.921242Z       34 Query    SET SQL_QUOTE_SHOW_CREATE=1
33 2018-06-15T08:00:52.921290Z       34 Query    SET SESSION character_set_results = 'binary'
34 2018-06-15T08:00:52.921334Z       34 Query    show create table `test_innodb`
35 2018-06-15T08:00:52.921397Z       34 Query    SET SESSION character_set_results = 'utf8'
36 2018-06-15T08:00:52.921444Z       34 Query    show fields from `test_innodb`
37 2018-06-15T08:00:52.921833Z       34 Query    show fields from `test_innodb`
38 2018-06-15T08:00:52.922279Z       34 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
39 2018-06-15T08:00:52.922380Z       34 Query    ROLLBACK TO SAVEPOINT sp --Rollback to savepoint sp
40 2018-06-15T08:00:52.922487Z       34 Query    show table status like 'test\_myisam'
41 2018-06-15T08:00:52.922694Z       34 Query    SET SQL_QUOTE_SHOW_CREATE=1
42 2018-06-15T08:00:52.922730Z       34 Query    SET SESSION character_set_results = 'binary'
43 2018-06-15T08:00:52.922763Z       34 Query    show create table `test_myisam`
44 2018-06-15T08:00:52.922872Z       34 Query    SET SESSION character_set_results = 'utf8'
45 2018-06-15T08:00:52.922929Z       34 Query    show fields from `test_myisam`
46 2018-06-15T08:00:52.923140Z       34 Query    show fields from `test_myisam`
47 2018-06-15T08:00:52.923395Z       34 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
48 2018-06-15T08:00:52.923459Z       34 Query    ROLLBACK TO SAVEPOINT sp --Rollback to savepoint sp.
49 2018-06-15T08:00:52.923487Z       34 Query    RELEASE SAVEPOINT sp --Release it where backup finish.
50 2018-06-15T08:00:52.928411Z       34 Quit    

案例:mysqldump -uroot -p sqlhk9 a –no-data

案例:mysqldump -uroot -p sqlhk9 a –no-data

 

参数详解:

参数详解:

   
There is only one piece of “UNLOCK TABLES”  which is related with lock
can be found this time.At the very beginning of the general log,it shows
“START TRANSACTION WITH CONSISTENT SNAPSHOT”.As soon as it gets the
gtid_executed variable,then it will realse the lock by execute statment
“UNLOCK TABLES”.Obviously,it’s a very short time.

使用mysqldump
mysqldump -u root -p your-new-password databasename [tablename] >
db.sql

使用mysqldump
mysqldump -u root -p your-new-password databasename [tablename] >
db.sql

   
After get the statment of creating database,there’s a savepoint
created.The rollback operation of the savepoint hapens after it gets the
full table backup.Let’s see another parameter “master-data” which can
make something different,too.

正如大的表须求用优化的dump以节本省存:
mysqldump –opt database > backup-file.sql

异常的大的表须要用优化的dump以节省外部存款和储蓄器:
mysqldump –opt database > backup-file.sql

 

mysqldump工具备大量的选项,部分选项如下表:

mysqldump工具备大批量的选项,部分选项如下表:

 1 ###Gnerate Third Backup(add replication information)###
 2 [root@zlm2 10:37:55 ~]
 3 #mysqldump --triggers=false --single-transaction --master-data=2 -B zlm > /data/backup/third_3306-`date +%Y%m%d`.sql
 4 Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 
 5 
 6 [root@zlm2 10:37:29 ~]
 7 #ls -l /data/backup
 8 total 12
 9 -rw-r--r-- 1 root root 2822 Jun 15 09:53 first_3306-20180615.sql
10 -rw-r--r-- 1 root root 2822 Jun 15 10:00 second_3306-20180615.sql
11 -rw-r--r-- 1 root root 2971 Jun 15 10:37 third_3306-20180615.sql
12 
13 ###Check General Log###
14 [root@zlm2 10:37:39 /data/mysql/mysql3306/data]
15 #cat zlm2.log
16 
17 2018-06-15T08:37:29.848849Z       35 Connect    root@localhost on  using Socket
18 2018-06-15T08:37:29.849052Z       35 Query    /*!40100 SET @@SQL_MODE='' */
19 2018-06-15T08:37:29.849153Z       35 Query    /*!40103 SET TIME_ZONE='+00:00' */
20 2018-06-15T08:37:29.849290Z       35 Query    FLUSH /*!40101 LOCAL */ TABLES -- Difference 1.
21 2018-06-15T08:37:29.855139Z       35 Query    FLUSH TABLES WITH READ LOCK -- Difference 2.
22 2018-06-15T08:37:29.855196Z       35 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
23 2018-06-15T08:37:29.855225Z       35 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
24 2018-06-15T08:37:29.855272Z       35 Query    SHOW VARIABLES LIKE 'gtid\_mode'
25 2018-06-15T08:37:29.857074Z       35 Query    SELECT @@GLOBAL.GTID_EXECUTED
26 2018-06-15T08:37:29.857406Z       35 Query    SHOW MASTER STATUS
27 2018-06-15T08:37:29.857498Z       35 Query    UNLOCK TABLES
28 2018-06-15T08:37:29.857622Z       35 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
29 2018-06-15T08:37:29.858662Z       35 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('zlm')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
30 2018-06-15T08:37:29.859309Z       35 Query    SHOW VARIABLES LIKE 'ndbinfo\_version'
31 2018-06-15T08:37:29.861396Z       35 Init DB    zlm
32 2018-06-15T08:37:29.862152Z       35 Query    SHOW CREATE DATABASE IF NOT EXISTS `zlm`
33 2018-06-15T08:37:29.862255Z       35 Query    SAVEPOINT sp
34 2018-06-15T08:37:29.862322Z       35 Query    show tables
35 2018-06-15T08:37:29.862485Z       35 Query    show table status like 'test\_innodb'
36 2018-06-15T08:37:29.862665Z       35 Query    SET SQL_QUOTE_SHOW_CREATE=1
37 2018-06-15T08:37:29.862724Z       35 Query    SET SESSION character_set_results = 'binary'
38 2018-06-15T08:37:29.862777Z       35 Query    show create table `test_innodb`
39 2018-06-15T08:37:29.862827Z       35 Query    SET SESSION character_set_results = 'utf8'
40 2018-06-15T08:37:29.862880Z       35 Query    show fields from `test_innodb`
41 2018-06-15T08:37:29.863198Z       35 Query    show fields from `test_innodb`
42 2018-06-15T08:37:29.863476Z       35 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_innodb`
43 2018-06-15T08:37:29.863597Z       35 Query    ROLLBACK TO SAVEPOINT sp
44 2018-06-15T08:37:29.863668Z       35 Query    show table status like 'test\_myisam'
45 2018-06-15T08:37:29.865590Z       35 Query    SET SQL_QUOTE_SHOW_CREATE=1
46 2018-06-15T08:37:29.865833Z       35 Query    SET SESSION character_set_results = 'binary'
47 2018-06-15T08:37:29.865853Z       35 Query    show create table `test_myisam`
48 2018-06-15T08:37:29.865853Z       35 Query    SET SESSION character_set_results = 'utf8'
49 2018-06-15T08:37:29.865854Z       35 Query    show fields from `test_myisam`
50 2018-06-15T08:37:29.866059Z       35 Query    show fields from `test_myisam`
51 2018-06-15T08:37:29.867277Z       35 Query    SELECT /*!40001 SQL_NO_CACHE */ * FROM `test_myisam`
52 2018-06-15T08:37:29.867367Z       35 Query    ROLLBACK TO SAVEPOINT sp
53 2018-06-15T08:37:29.867398Z       35 Query    RELEASE SAVEPOINT sp
54 2018-06-15T08:37:29.869643Z       35 Quit    

  选项/Option 作用/Action Performed

  选项/Option 作用/Action Performed

 

  –add-drop-table

  –add-drop-table

   
There’re two differences above,one is “FLUSH TABLES” another one is
“FLUSH TABLE WITH READ LOCK”.In case of other transactoins hold the
table lock related with the table to be backed up,the backup operation
will be blocked until those transactions release the lock or waiting
until time out(according to value of “lock_wait_timeout”)
occurs.

  这么些选项将会在每二个表的先头加上DROP TABLE IF
EXISTS语句,那样可以确认保证导回MySQL数据库的时候不会出错,因为每一趟导回的时候,都会率先检查表是还是不是存在,存在就删除

  那么些选项将会在每三个表的后面加上DROP TABLE IF
EXISTS语句,那样能够确认保障导回MySQL数据库的时候不会出错,因为老是导回的时候,都会首先检查表是还是不是留存,存在就删除

*    As
a matter of fact,we usually use “master-data” to backup our databases,we
shoudn’t make it happen in the case of there’re massive dml operations
continueously executed in rush hour.This will lead to failure of your
backup.

*

  –add-locks

  –add-locks

 

  那几个选项会在INSERT语句中捆上三个LOCK TABLE和UNLOCK
TABLE语句。那就幸免在那么些记录被另行导入数据库时其余用户对表实行的操作
  
  -c or – complete_insert

  这几个选项会在INSERT语句中捆上2个LOCK TABLE和UNLOCK
TABLE语句。那就制止在这么些记录被再次导入数据库时其余用户对表实行的操作
  
  -c or – complete_insert

Summary

  这几个选项使得mysqldump命令给每四个产生INSERT语句加上列(田野(field))的名字。当把数据导出导其余3个数据库时这一个选项很有用。

  那一个选项使得mysqldump命令给每一个发生INSERT语句加上列(田野同志)的名字。当把数据导出导其余二个数据库时这几个选项很有用。

  •  
      You’ve been clear about the whole procedure of backup using
    mysqldump now.But,there’s still one thing make me confused is that
    why I used “master-data” in mysqldump but not got the change master
    statement
    .Is it due to my Group Replication environment?**
  •  
      Look out,this tool doesnot support backing up in parallel mode.If
    your database is huge enough,do think twice or use another backup
    tool in stead of it. 

  –delayed-insert 在INSERT命令中参加DELAY选项

  –delayed-insert 在INSERT命令中投入DELAY选项

 

  -F or -flush-logs
使用那么些选项,在实行导出此前将会刷新MySQL服务器的log.

  -F or -flush-logs
使用那么些选项,在施行导出之前将会刷新MySQL服务器的log.

  -f or -force 使用这一个选项,即便有荒唐爆发,依旧继续导出

  -f or -force 使用那么些选项,尽管有错误产生,如故持续导出

  –full 那一个选项把附加音信也加到CREATE TABLE的言语中

  –full 那些选项把附加消息也加到CREATE TABLE的话语中

  -l or -lock-tables 使用那么些选项,导出表的时候服务器将会给表加锁。

  -l or -lock-tables 使用这么些选项,导出表的时候服务器将会给表加锁。

  -t or -no-create- info

  -t or -no-create- info

  这一个选项使的mysqldump命令不创设CREATE
TABLE语句,那么些选项在你只要求多少而不供给DDL(数据库定义语句)时很有利。
  
  -d or -no-data 这么些选项使的mysqldump命令不创制INSERT语句。

  这些选项使的mysqldump命令不成立CREATE
TABLE语句,那些选项在你只供给多少而不须要DDL(数据库定义语句)时很便宜。
  
  -d or -no-data 这些选项使的mysqldump命令不成立INSERT语句。

在你只须要DDL语句时,能够应用那个选项。

在您只供给DDL语句时,可以运用那些选项。

  –opt
此选项将开采全体会进步文书导出速度和创设一个能够越来越快导入的文书的选项。

  –opt
此选项将开垦全部会提升公文导出速度和创制2个足以更加快导入的公文的选项。

  -q or -quick
这些选项使得MySQL不会把全副导出的始末读入内部存款和储蓄器再实践导出,而是在读到的时候就写入导文件中。

  -q or -quick
那一个选项使得MySQL不会把任何导出的剧情读入内部存款和储蓄器再施行导出,而是在读到的时候就写入导文件中。

  -T path or -tab = path
那个选项将会创制多少个公文,贰个文本包蕴DDL语句或然表创设语句,另贰个文书包含数据。DDL文件被取名叫table_name.sql,数据文件被命名叫table_name.txt.路线名是存放在那五个文本的目录。目录必须已经存在,并且命令的使用者有对文件的特权。
  
  -w “WHERE Clause” or -where = “Where clause “

  -T path or -tab = path
这几个选项将会创制多少个文件,叁个文书包含DDL语句可能表创造语句,另四个文件包罗数据。DDL文件被命名称叫table_name.sql,数据文件被命名叫table_name.txt.路线名是存放在那多个文件的目录。目录必须已经存在,并且命令的使用者有对文件的特权。
  
  -w “WHERE Clause” or -where = “Where clause “

参考国外网址

参考国外网址

NAME
       mysqldump – a database backup program

NAME
       mysqldump – a database backup program

SYNOPSIS
       mysqldump [options] [db_name [tbl_name …]]

SYNOPSIS
       mysqldump [options] [db_name [tbl_name …]]

DESCRIPTION
       The mysqldump client can be used to dump a database or a
collection of
       databases for backup or for transferring the data to another SQL
server
       (not necessarily a MySQL server). The dump contains SQL
statements to
       create the table and/or populate the table.

DESCRIPTION
       The mysqldump client can be used to dump a database or a
collection of
       databases for backup or for transferring the data to another SQL
server
       (not necessarily a MySQL server). The dump contains SQL
statements to
       create the table and/or populate the table.

       If you are doing a backup on the server, and your tables all are
MyISAM
       tables, you could consider using the mysqlhotcopy instead since
faster
       backups and faster restores can be accomplished with the latter.
See
       mysqlhotcopy(1).

       If you are doing a backup on the server, and your tables all are
MyISAM
       tables, you could consider using the mysqlhotcopy instead since
faster
       backups and faster restores can be accomplished with the latter.
See
       mysqlhotcopy(1).

       There are three general ways to invoke mysqldump:

       There are three general ways to invoke mysqldump:

       shell> mysqldump [options] db_name [tables]
       shell> mysqldump [options] –databases DB1 [DB2 DB3…]
       shell> mysqldump [options] –all-databases

       shell> mysqldump [options] db_name [tables]
       shell> mysqldump [options] –databases DB1 [DB2 DB3…]
       shell> mysqldump [options] –all-databases

       If you do not name any tables or use the –databases or
–all-databases
       option, entire databases are dumped.

       If you do not name any tables or use the –databases or
–all-databases
       option, entire databases are dumped.

       To get a list of the options your version of mysqldump
supports,
       execute mysqldump –help.

       To get a list of the options your version of mysqldump
supports,
       execute mysqldump –help.

       If you run mysqldump without the –quick or –opt option,
mysqldump
       loads the whole result set into memory before dumping the result.
This
       probably is a problem if you are dumping a big database. As of
MySQL
       4.1, –opt is enabled by default, but can be disabled with
–skip-opt.

       If you run mysqldump without the –quick or –opt option,
mysqldump
       loads the whole result set into memory before dumping the result.
This
       probably is a problem if you are dumping a big database. As of
MySQL
       4.1, –opt is enabled by default, but can be disabled with
–skip-opt.

       If you are using a recent copy of the mysqldump program to
generate a
       dump to be reloaded into a very old MySQL server, you should not
use
       the –opt or -e options.

       If you are using a recent copy of the mysqldump program to
generate a
       dump to be reloaded into a very old MySQL server, you should not
use
       the –opt or -e options.

       Before MySQL 4.1.2, out-of-range numeric values such as -inf and
inf,
       as well as NaN (not-a-number) values are dumped by mysqldump as
NULL.
       You can see this using the following sample table:

       Before MySQL 4.1.2, out-of-range numeric values such as -inf and
inf,
       as well as NaN (not-a-number) values are dumped by mysqldump as
NULL.
       You can see this using the following sample table:

       mysql> CREATE TABLE t (f DOUBLE);
       mysql> INSERT INTO t VALUES(1e+111111111111111111111);
       mysql> INSERT INTO t VALUES(-1e111111111111111111111);
       mysql> SELECT f FROM t;
       +——+
       | f    |
       +——+
       |  inf |
       | -inf |
       +——+

       mysql> CREATE TABLE t (f DOUBLE);
       mysql> INSERT INTO t VALUES(1e+111111111111111111111);
       mysql> INSERT INTO t VALUES(-1e111111111111111111111);
       mysql> SELECT f FROM t;
       +——+
       | f    |
       +——+
       |  inf |
       | -inf |
       +——+

       For this table, mysqldump produces the following data output:

       For this table, mysqldump produces the following data output:

       —
       — Dumping data for table ‘t‘
       —
       INSERT INTO t VALUES (NULL);
       INSERT INTO t VALUES (NULL);

       —
       — Dumping data for table ‘t‘
       —
       INSERT INTO t VALUES (NULL);
       INSERT INTO t VALUES (NULL);

       The significance of this behavior is that if you dump and restore
the
       table, the new table has contents that differ from the original
       contents. This problem is fixed as of MySQL 4.1.2; you cannot
insert
       inf in the table, so this mysqldump behavior is only relevant
when you
       deal with old servers.

       The significance of this behavior is that if you dump and restore
the
       table, the new table has contents that differ from the original
       contents. This problem is fixed as of MySQL 4.1.2; you cannot
insert
       inf in the table, so this mysqldump behavior is only relevant
when you
       deal with old servers.

       mysqldump supports the following options:

       mysqldump supports the following options:

       ·  –help, -?

       ·  –help, -?

          Display a help message and exit.

          Display a help message and exit.

       ·  –add-drop-database

       ·  –add-drop-database

          Add a DROP DATABASE statement before each CREATE DATABASE
statement.
          Added in MySQL 4.1.13.

          Add a DROP DATABASE statement before each CREATE DATABASE
statement.
          Added in MySQL 4.1.13.

       ·  –add-drop-table

       ·  –add-drop-table

          Add a DROP TABLE statement before each CREATE TABLE statement.

          Add a DROP TABLE statement before each CREATE TABLE statement.

       ·  –add-locks

       ·  –add-locks

          Surround each table dump with LOCK TABLES and UNLOCK TABLES
          statements. This results in faster inserts when the dump file
is
          reloaded. See Section 2.13, “Speed of INSERT Statements”.

          Surround each table dump with LOCK TABLES and UNLOCK TABLES
          statements. This results in faster inserts when the dump file
is
          reloaded. See Section 2.13, “Speed of INSERT Statements”.

       ·  –all-databases, -A

       ·  –all-databases, -A

          Dump all tables in all databases. This is the same as using
the
          –databases option and naming all the databases on the command
line.

          Dump all tables in all databases. This is the same as using
the
          –databases option and naming all the databases on the command
line.

       ·  –allow-keywords

       ·  –allow-keywords

          Allow creation of column names that are keywords. This works
by
          prefixing each column name with the table name.

          Allow creation of column names that are keywords. This works
by
          prefixing each column name with the table name.

       ·  –comments[={0|1}]

       ·  –comments[={0|1}]

          If set to 0, suppresses additional information in the dump
file such
          as program version, server version, and host.  –skip-comments
has
          the same effect as –comments=0. The default value is 1,
which
          includes the extra information. Added in MySQL 4.0.17.

          If set to 0, suppresses additional information in the dump
file such
          as program version, server version, and host.  –skip-comments
has
          the same effect as –comments=0. The default value is 1,
which
          includes the extra information. Added in MySQL 4.0.17.

       ·  –compact

       ·  –compact

          Produce less verbose output. This option suppresses comments
and
          enables the –skip-add-drop-table, –no-set-names,
          –skip-disable-keys, and –skip-add-locks options. Added in
MySQL
          4.1.2.

          Produce less verbose output. This option suppresses comments
and
          enables the –skip-add-drop-table, –no-set-names,
          –skip-disable-keys, and –skip-add-locks options. Added in
MySQL
          4.1.2.

       ·  –compatible=name

       ·  –compatible=name

          Produce output that is more compatible with other database
systems
          or with older MySQL servers. The value of name can be ansi,
          mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb,
          no_key_options, no_table_options, or no_field_options.
To use
          several values, separate them by commas. These values have the
same
          meaning as the corresponding options for setting the server
SQL
          mode. See the section called “THE SERVER SQL MODE”.

          Produce output that is more compatible with other database
systems
          or with older MySQL servers. The value of name can be ansi,
          mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb,
          no_key_options, no_table_options, or no_field_options.
To use
          several values, separate them by commas. These values have the
same
          meaning as the corresponding options for setting the server
SQL
          mode. See the section called “THE SERVER SQL MODE”.

          This option does not guarantee compatibility with other
servers. It
          only enables those SQL mode values that are currently
available for
          making dump output more compatible. For example,
–compatible=oracle
          does not map data types to Oracle types or use Oracle
comment
          syntax.

          This option does not guarantee compatibility with other
servers. It
          only enables those SQL mode values that are currently
available for
          making dump output more compatible. For example,
–compatible=oracle
          does not map data types to Oracle types or use Oracle
comment
          syntax.

          This option requires a server version of 4.1.0 or higher. With
older
          servers, it does nothing.

          This option requires a server version of 4.1.0 or higher. With
older
          servers, it does nothing.

       ·  –complete-insert, -c

       ·  –complete-insert, -c

          Use complete INSERT statements that include column names.

          Use complete INSERT statements that include column names.

       ·  –compress, -C

       ·  –compress, -C

          Compress all information sent between the client and the
server if
          both support compression.

          Compress all information sent between the client and the
server if
          both support compression.

       ·  –create-options

       ·  –create-options

          Include all MySQL-specific table options in the CREATE TABLE
          statements. Before MySQL 4.1.2, use –all instead.

          Include all MySQL-specific table options in the CREATE TABLE
          statements. Before MySQL 4.1.2, use –all instead.

       ·  –databases, -B

       ·  –databases, -B

          Dump several databases. Normally, mysqldump treats the first
name
          argument on the command line as a database name and following
names
          as table names. With this option, it treats all name arguments
as
          database names.  CREATE DATABASE IF NOT EXISTS db_name and
USE
          db_name statements are included in the output before each
new
          database.

          Dump several databases. Normally, mysqldump treats the first
name
          argument on the command line as a database name and following
names
          as table names. With this option, it treats all name arguments
as
          database names.  CREATE DATABASE IF NOT EXISTS db_name and
USE
          db_name statements are included in the output before each
new
          database.

       ·  –debug[=debug_options], -# [debug_options]

       ·  –debug[=debug_options], -# [debug_options]

          Write a debugging log. The debug_options string is often
          ´d:t:o,file_name’.

          Write a debugging log. The debug_options string is often
          ´d:t:o,file_name’.

       ·  –default-character-set=charset

       ·  –default-character-set=charset

          Use charset as the default character set. See Section 7.1,
“The
          Character Set Used for Data and Sorting”. If not specified,
          mysqldump from MySQL 4.1.2 or later uses utf8, and earlier
versions
          use latin1.

          Use charset as the default character set. See Section 7.1,
“The
          Character Set Used for Data and Sorting”. If not specified,
          mysqldump from MySQL 4.1.2 or later uses utf8, and earlier
versions
          use latin1.

       ·  –delayed-insert

       ·  –delayed-insert

          Insert rows using INSERT DELAYED statements.

          Insert rows using INSERT DELAYED statements.

       ·  –delete-master-logs

       ·  –delete-master-logs

          On a master replication server, delete the binary logs after
          performing the dump operation. This option automatically
enables
          –first-slave before MySQL 4.1.8 and enables –master-data
          thereafter. It was added in MySQL 3.23.57 (for MySQL 3.23) and
MySQL
          4.0.13 (for MySQL 4.0).

          On a master replication server, delete the binary logs after
          performing the dump operation. This option automatically
enables
          –first-slave before MySQL 4.1.8 and enables –master-data
          thereafter. It was added in MySQL 3.23.57 (for MySQL 3.23) and
MySQL
          4.0.13 (for MySQL 4.0).

       ·  –disable-keys, -K

       ·  –disable-keys, -K

          For each table, surround the INSERT statements with /*!40000
ALTER
          TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE
tbl_name
          ENABLE KEYS */; statements. This makes loading the dump file
into a
          MySQL 4.0 or newer server faster because the indexes are
created
          after all rows are inserted. This option is effective for
MyISAM
          tables only.

          For each table, surround the INSERT statements with /*!40000
ALTER
          TABLE tbl_name DISABLE KEYS */; and /*!40000 ALTER TABLE
tbl_name
          ENABLE KEYS */; statements. This makes loading the dump file
into a
          MySQL 4.0 or newer server faster because the indexes are
created
          after all rows are inserted. This option is effective for
MyISAM
          tables only.

       ·  –extended-insert, -e

       ·  –extended-insert, -e

          Use multiple-row INSERT syntax that include several VALUES
lists.
          This results in a smaller dump file and speeds up inserts when
the
          file is reloaded.

          Use multiple-row INSERT syntax that include several VALUES
lists.
          This results in a smaller dump file and speeds up inserts when
the
          file is reloaded.

       ·  –fields-terminated-by=…, –fields-enclosed-by=…,
          –fields-optionally-enclosed-by=…,
–fields-escaped-by=…,
          –lines-terminated-by=…

       ·  –fields-terminated-by=…, –fields-enclosed-by=…,
          –fields-optionally-enclosed-by=…,
–fields-escaped-by=…,
          –lines-terminated-by=…

          These options are used with the -T option and have the same
meaning
          as the corresponding clauses for LOAD DATA INFILE. See Section
2.5,
          “LOAD DATA INFILE Syntax”.

          These options are used with the -T option and have the same
meaning
          as the corresponding clauses for LOAD DATA INFILE. See Section
2.5,
          “LOAD DATA INFILE Syntax”.

       ·  –first-slave, -x

       ·  –first-slave, -x

          Deprecated, renamed to –lock-all-tables in MySQL 4.1.8.

          Deprecated, renamed to –lock-all-tables in MySQL 4.1.8.

       ·  –flush-logs, -F

       ·  –flush-logs, -F

          Flush the MySQL server log files before starting the dump.
This
          option requires the RELOAD privilege. Note that if you use
this
          option in combination with the –all-databases (or -A) option,
the
          logs are flushed for each database dumped. The exception is
when
          using –lock-all-tables or –master-data: In this case, the
logs are
          flushed only once, corresponding to the moment that all tables
are
          locked. If you want your dump and the log flush to happen at
exactly
          the same moment, you should use –flush-logs together with
either
          –lock-all-tables or –master-data.

          Flush the MySQL server log files before starting the dump.
This
          option requires the RELOAD privilege. Note that if you use
this
          option in combination with the –all-databases (or -A) option,
the
          logs are flushed for each database dumped. The exception is
when
          using –lock-all-tables or –master-data: In this case, the
logs are
          flushed only once, corresponding to the moment that all tables
are
          locked. If you want your dump and the log flush to happen at
exactly
          the same moment, you should use –flush-logs together with
either
          –lock-all-tables or –master-data.

       ·  –force, -f

       ·  –force, -f

          Continue even if an SQL error occurs during a table dump.

          Continue even if an SQL error occurs during a table dump.

       ·  –host=host_name, -h host_name

       ·  –host=host_name, -h host_name

          Dump data from the MySQL server on the given host. The default
host
          is localhost.

          Dump data from the MySQL server on the given host. The default
host
          is localhost.

       ·  –hex-blob

       ·  –hex-blob

          Dump binary string columns using hexadecimal notation (for
example,
          ´abc’ becomes 0x616263). The affected columns are BINARY,
VARBINARY,
          and BLOB in MySQL 4.1 and up, and CHAR BINARY, VARCHAR BINARY,
and
          BLOB in MySQL 4.0. This option was added in MySQL 4.0.23 and
4.1.8.

          Dump binary string columns using hexadecimal notation (for
example,
          ´abc’ becomes 0x616263). The affected columns are BINARY,
VARBINARY,
          and BLOB in MySQL 4.1 and up, and CHAR BINARY, VARCHAR BINARY,
and
          BLOB in MySQL 4.0. This option was added in MySQL 4.0.23 and
4.1.8.

       ·  –lock-all-tables, -x

       ·  –lock-all-tables, -x

          Lock all tables across all databases. This is achieved by
acquiring
          a global read lock for the duration of the whole dump. This
option
          automatically turns off –single-transaction and
–lock-tables.
          Added in MySQL 4.1.8.

          Lock all tables across all databases. This is achieved by
acquiring
          a global read lock for the duration of the whole dump. This
option
          automatically turns off –single-transaction and
–lock-tables.
          Added in MySQL 4.1.8.

       ·  –lock-tables, -l

       ·  –lock-tables, -l

          Lock all tables before starting the dump. The tables are
locked with
          READ LOCAL to allow concurrent inserts in the case of MyISAM
tables.
          For transactional tables such as InnoDB and BDB,
          –single-transaction is a much better option, because it does
not
          need to lock the tables at all.

          Lock all tables before starting the dump. The tables are
locked with
          READ LOCAL to allow concurrent inserts in the case of MyISAM
tables.
          For transactional tables such as InnoDB and BDB,
          –single-transaction is a much better option, because it does
not
          need to lock the tables at all.

          Please note that when dumping multiple databases,
–lock-tables
          locks tables for each database separately. So, this option
does not
          guarantee that the tables in the dump file are logically
consistent
          between databases. Tables in different databases may be dumped
in
          completely different states.

          Please note that when dumping multiple databases,
–lock-tables
          locks tables for each database separately. So, this option
does not
          guarantee that the tables in the dump file are logically
consistent
          between databases. Tables in different databases may be dumped
in
          completely different states.

       ·  –master-data[=value]

       ·  –master-data[=value]

          This option causes the binary log position and filename to
be
          written to the output. This option requires the RELOAD
privilege and
          the binary log must be enabled. If the option value is equal
to 1,
          the position and filename are written to the dump output in
the form
          of a CHANGE MASTER statement that makes a slave server start
from
          the correct position in the master’s binary logs if you use
this SQL
          dump of the master to set up a slave. If the option value is
equal
          to 2, the CHANGE MASTER statement is written as an SQL
comment. This
          is the default action if value is omitted.  value may be given
as of
          MySQL 4.1.8; before that, do not specify an option value.

          This option causes the binary log position and filename to
be
          written to the output. This option requires the RELOAD
privilege and
          the binary log must be enabled. If the option value is equal
to 1,
          the position and filename are written to the dump output in
the form
          of a CHANGE MASTER statement that makes a slave server start
from
          the correct position in the master’s binary logs if you use
this SQL
          dump of the master to set up a slave. If the option value is
equal
          to 2, the CHANGE MASTER statement is written as an SQL
comment. This
          is the default action if value is omitted.  value may be given
as of
          MySQL 4.1.8; before that, do not specify an option value.

          The –master-data option turns on –lock-all-tables, unless
          –single-transaction also is specified (in which case, a
global read
          lock is only acquired a short time at the beginning of the
dump. See
          also the description for –single-transaction. In all cases,
any
          action on logs happens at the exact moment of the dump. This
option
          automatically turns off –lock-tables.

          The –master-data option turns on –lock-all-tables, unless
          –single-transaction also is specified (in which case, a
global read
          lock is only acquired a short time at the beginning of the
dump. See
          also the description for –single-transaction. In all cases,
any
          action on logs happens at the exact moment of the dump. This
option
          automatically turns off –lock-tables.

       ·  –no-create-db, -n

       ·  –no-create-db, -n

          This option suppresses the CREATE DATABASE /*!32312 IF NOT
EXISTS*/
          db_name statements that are otherwise included in the output
if the
          –databases or –all-databases option is given.

          This option suppresses the CREATE DATABASE /*!32312 IF NOT
EXISTS*/
          db_name statements that are otherwise included in the output
if the
          –databases or –all-databases option is given.

       ·  –no-create-info, -t

       ·  –no-create-info, -t

          Do not write CREATE TABLE statements that re-create each
dumped
          table.

          Do not write CREATE TABLE statements that re-create each
dumped
          table.

       ·  –no-data, -d

       ·  –no-data, -d

          Do not write any row information for the table. This is very
useful
          if you want to get a dump of only the structure for a table.

          Do not write any row information for the table. This is very
useful
          if you want to get a dump of only the structure for a table.

       ·  –opt

       ·  –opt

          This option is shorthand; it is the same as specifying
          –add-drop-table –add-locks –create-options –disable-keys
          –extended-insert –lock-tables –quick –set-charset. It
should
          give you a fast dump operation and produce a dump file that
can be
          reloaded into a MySQL server quickly.  As of MySQL 4.1, –opt
is on
          by default, but can be disabled with –skip-opt. To disable
only
          certain of the options enabled by –opt, use their –skip
forms; for
          example, –skip-add-drop-table or –skip-quick.

          This option is shorthand; it is the same as specifying
          –add-drop-table –add-locks –create-options –disable-keys
          –extended-insert –lock-tables –quick –set-charset. It
should
          give you a fast dump operation and produce a dump file that
can be
          reloaded into a MySQL server quickly.  As of MySQL 4.1, –opt
is on
          by default, but can be disabled with –skip-opt. To disable
only
          certain of the options enabled by –opt, use their –skip
forms; for
          example, –skip-add-drop-table or –skip-quick.

       ·  –password[=password], -p[password]

       ·  –password[=password], -p[password]

          The password to use when connecting to the server. If you use
the
          short option form (-p), you cannot have a space between the
option
          and the password. If you omit the password value following
the
          –password or -p option on the command line, you are prompted
for
          one.

          The password to use when connecting to the server. If you use
the
          short option form (-p), you cannot have a space between the
option
          and the password. If you omit the password value following
the
          –password or -p option on the command line, you are prompted
for
          one.

       ·  –port=port_num, -P port_num

       ·  –port=port_num, -P port_num

          The TCP/IP port number to use for the connection.

          The TCP/IP port number to use for the connection.

       ·  –protocol={TCP | SOCKET | PIPE | MEMORY}

       ·  –protocol={TCP | SOCKET | PIPE | MEMORY}

          The connection protocol to use. Added in MySQL 4.1.

          The connection protocol to use. Added in MySQL 4.1.

       ·  –quick, -q

       ·  –quick, -q

          This option is useful for dumping large tables. It forces
mysqldump
          to retrieve rows for a table from the server a row at a time
rather
          than retrieving the entire row set and buffering it in memory
before
          writing it out.

          This option is useful for dumping large tables. It forces
mysqldump
          to retrieve rows for a table from the server a row at a time
rather
          than retrieving the entire row set and buffering it in memory
before
          writing it out.

       ·  –quote-names, -Q

       ·  –quote-names, -Q

          Quote database, table, and column names within ‘‘’ characters.
If
          the server SQL mode includes the ANSI_QUOTES option, names
are
          quoted within ‘”‘ characters. As of MySQL 4.1.1, –quote-names
is on
          by default. It can be disabled with –skip-quote-names, but
this
          option should be given after any option such as –compatible
that
          may enable –quote-names.

          Quote database, table, and column names within ‘‘’ characters.
If
          the server SQL mode includes the ANSI_QUOTES option, names
are
          quoted within ‘”‘ characters. As of MySQL 4.1.1, –quote-names
is on
          by default. It can be disabled with –skip-quote-names, but
this
          option should be given after any option such as –compatible
that
          may enable –quote-names.

       ·  –result-file=file, -r file

       ·  –result-file=file, -r file

          Direct output to a given file. This option should be used on
          Windows, because it prevents newline ‘n’ characters from
being
          converted to ‘rn’ carriage return/newline sequences.

          Direct output to a given file. This option should be used on
          Windows, because it prevents newline ‘n’ characters from
being
          converted to ‘rn’ carriage return/newline sequences.

       ·  –set-charset

       ·  –set-charset

          Add SET NAMES default_character_set to the output. This
option is
          enabled by default. To suppress the SET NAMES statement, use
          –skip-set-charset. This option was added in MySQL 4.1.2.

          Add SET NAMES default_character_set to the output. This
option is
          enabled by default. To suppress the SET NAMES statement, use
          –skip-set-charset. This option was added in MySQL 4.1.2.

       ·  –single-transaction

       ·  –single-transaction

          This option issues a BEGIN SQL statement before dumping data
from
          the server. It is useful only with transactional tables such
as
          InnoDB and BDB, because then it dumps the consistent state of
the
          database at the time when BEGIN was issued without blocking
any
          applications.

          This option issues a BEGIN SQL statement before dumping data
from
          the server. It is useful only with transactional tables such
as
          InnoDB and BDB, because then it dumps the consistent state of
the
          database at the time when BEGIN was issued without blocking
any
          applications.

          When using this option, you should keep in mind that only
InnoDB
          tables are dumped in a consistent state. For example, any
MyISAM or
          HEAP tables dumped while using this option may still change
state.

          When using this option, you should keep in mind that only
InnoDB
          tables are dumped in a consistent state. For example, any
MyISAM or
          HEAP tables dumped while using this option may still change
state.

          The –single-transaction option was added in MySQL 4.0.2.
This
          option is mutually exclusive with the –lock-tables option,
because
          LOCK TABLES causes any pending transactions to be committed
          implicitly.

          The –single-transaction option was added in MySQL 4.0.2.
This
          option is mutually exclusive with the –lock-tables option,
because
          LOCK TABLES causes any pending transactions to be committed
          implicitly.

          To dump big tables, you should combine this option with
–quick.

          To dump big tables, you should combine this option with
–quick.

       ·  –socket=path, -S path

       ·  –socket=path, -S path

          The socket file to use when connecting to localhost (which is
the
          default host).

          The socket file to use when connecting to localhost (which is
the
          default host).

       ·  –skip-comments

       ·  –skip-comments

          See the description for the –comments option.

          See the description for the –comments option.

       ·  –tab=path, -T path

       ·  –tab=path, -T path

          Produce tab-separated data files. For each dumped table,
mysqldump
          creates a tbl_name.sql file that contains the CREATE TABLE
statement
          that creates the table, and a tbl_name.txt file that contains
its
          data. The option value is the directory in which to write the
files.

          Produce tab-separated data files. For each dumped table,
mysqldump
          creates a tbl_name.sql file that contains the CREATE TABLE
statement
          that creates the table, and a tbl_name.txt file that contains
its
          data. The option value is the directory in which to write the
files.

          By default, the .txt data files are formatted using tab
characters
          between column values and a newline at the end of each line.
The
          format can be specified explicitly using the –fields-xxx
and
          –lines–xxx options.

          By default, the .txt data files are formatted using tab
characters
          between column values and a newline at the end of each line.
The
          format can be specified explicitly using the –fields-xxx
and
          –lines–xxx options.

          Note: This option should be used only when mysqldump is run on
the
          same machine as the mysqld server. You must have the FILE
privilege,
          and the server must have permission to write files in the
directory
          that you specify.

          Note: This option should be used only when mysqldump is run on
the
          same machine as the mysqld server. You must have the FILE
privilege,
          and the server must have permission to write files in the
directory
          that you specify.

       ·  –tables

       ·  –tables

          Override the –databases or -B option. All arguments following
the
          option are regarded as table names.

          Override the –databases or -B option. All arguments following
the
          option are regarded as table names.

       ·  –user=user_name, -u user_name

       ·  –user=user_name, -u user_name

          The MySQL username to use when connecting to the server.

          The MySQL username to use when connecting to the server.

       ·  –verbose, -v

       ·  –verbose, -v

          Verbose mode. Print out more information on what the program
does.

          Verbose mode. Print out more information on what the program
does.

       ·  –version, -V

       ·  –version, -V

          Display version information and exit.

          Display version information and exit.

       ·  –where=�����where-condition�����, -w
�����where-condition�����

       ·  –where=�����where-condition�����, -w
�����where-condition�����

          Dump only records php/select”>selected by the given WHERE
condition. Note that
          quotes around the condition are mandatory if it contains
spaces or
          characters that are special to your command interpreter.

          Dump only records php/select”>selected by the given WHERE
condition. Note that
          quotes around the condition are mandatory if it contains
spaces or
          characters that are special to your command interpreter.

          Examples:

          Examples:

          “–where=user=’jimf'”
          “-wuserid>1”
          “-wuserid<1”

          “–where=user=’jimf'”
          “-wuserid>1”
          “-wuserid<1”

       ·  –xml, -X

       ·  –xml, -X

          Write dump output as well-formed XML.

          Write dump output as well-formed XML.

       You can also set the following variables by using
–var_name=value
       options:

       You can also set the following variables by using
–var_name=value
       options:

       ·  max_allowed_packet

       ·  max_allowed_packet

          The maximum size of the buffer for client/server
communication. The
          value of the variable can be up to 16MB before MySQL 4.0, and
up to
          1GB from MySQL 4.0 on.

          The maximum size of the buffer for client/server
communication. The
          value of the variable can be up to 16MB before MySQL 4.0, and
up to
          1GB from MySQL 4.0 on.

       ·  net_buffer_length

       ·  net_buffer_length

          The initial size of the buffer for client/server
communication. When
          creating multiple-row-insert statements (as with option
          –extended-insert or –opt), mysqldump creates rows up to
          net_buffer_length length. If you increase this variable, you
should
          also ensure that the net_buffer_length variable in the MySQL
server
          is at least this large.

          The initial size of the buffer for client/server
communication. When
          creating multiple-row-insert statements (as with option
          –extended-insert or –opt), mysqldump creates rows up to
          net_buffer_length length. If you increase this variable, you
should
          also ensure that the net_buffer_length variable in the MySQL
server
997755.com澳门葡京 ,          is at least this large.

       It is also possible to set variables by using
       –set-variable=var_name=value or -O var_name=value syntax.
However,
       this syntax is deprecated as of MySQL 4.0.

       It is also possible to set variables by using
       –set-variable=var_name=value or -O var_name=value syntax.
However,
       this syntax is deprecated as of MySQL 4.0.

       The most common use of mysqldump is probably for making a backup
of an
       entire database:

       The most common use of mysqldump is probably for making a backup
of an
       entire database:

       shell> mysqldump –opt db_name > backup-file.sql

       shell> mysqldump –opt db_name > backup-file.sql

       You can read the dump file back into the server like this:

       You can read the dump file back into the server like this:

       shell> mysql db_name < backup-file.sql

       shell> mysql db_name < backup-file.sql

       Or like this:

       Or like this:

       shell> mysql -e “source /path-to-backup/backup-file.sql”
db_name

       shell> mysql -e “source /path-to-backup/backup-file.sql”
db_name

       mysqldump is also very useful for populating databases by copying
data
       from one MySQL server to another:

       mysqldump is also very useful for populating databases by copying
data
       from one MySQL server to another:

       shell> mysqldump –opt db_name | mysql –host=remote_host -C
db_name

       shell> mysqldump –opt db_name | mysql –host=remote_host -C
db_name

       It is possible to dump several databases with one command:

       It is possible to dump several databases with one command:

       shell> mysqldump –databases db_name1 [db_name2 …] >
my_databases.sql

       shell> mysqldump –databases db_name1 [db_name2 …] >
my_databases.sql

       If you want to dump all databases, use the –all-databases
option:

       If you want to dump all databases, use the –all-databases
option:

       shell> mysqldump –all-databases > all_databases.sql

       shell> mysqldump –all-databases > all_databases.sql

       If tables are stored in the InnoDB storage engine, mysqldump
provides a
       way of making an online backup of these (see command below).
This
       backup just needs to acquire a global read lock on all tables
(using
       FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As
soon as
       this lock has been acquired, the binary log coordinates are read
and
       lock is released. So if and only if one long updating statement
is
       running when the FLUSH…  is issued, the MySQL server may get
stalled
       until that long statement finishes, and then the dump becomes
       lock-free. So if the MySQL server receives only short (in the
sense of
       “short execution time”) updating statements, even if there are
plenty
       of them, the initial lock period should not be noticeable.

       If tables are stored in the InnoDB storage engine, mysqldump
provides a
       way of making an online backup of these (see command below).
This
       backup just needs to acquire a global read lock on all tables
(using
       FLUSH TABLES WITH READ LOCK) at the beginning of the dump. As
soon as
       this lock has been acquired, the binary log coordinates are read
and
       lock is released. So if and only if one long updating statement
is
       running when the FLUSH…  is issued, the MySQL server may get
stalled
       until that long statement finishes, and then the dump becomes
       lock-free. So if the MySQL server receives only short (in the
sense of
       “short execution time”) updating statements, even if there are
plenty
       of them, the initial lock period should not be noticeable.

       shell> mysqldump –all-databases –single-transaction >
all_databases.sql

       shell> mysqldump –all-databases –single-transaction >
all_databases.sql

       For point-in-time recovery (also known as “roll-forward”, when
you need
       to restore an old backup and replay the changes which happened
since
       that backup), it is often useful to rotate the binary log (see
       Section 8.4, “The Binary Log”) or at least know the binary log
       coordinates to which the dump corresponds:

       For point-in-time recovery (also known as “roll-forward”, when
you need
       to restore an old backup and replay the changes which happened
since
       that backup), it is often useful to rotate the binary log (see
       Section 8.4, “The Binary Log”) or at least know the binary log
       coordinates to which the dump corresponds:

       shell> mysqldump –all-databases –master-data=2 >
all_databases.sql
       or
       shell> mysqldump –all-databases –flush-logs –master-data=2
> all_databases.sql

       shell> mysqldump –all-databases –master-data=2 >
all_databases.sql
       or
       shell> mysqldump –all-databases –flush-logs –master-data=2
> all_databases.sql

       The simultaneous use of –master-data and –single-transaction
works as
       of MySQL 4.1.8. It provides a convenient way to make an online
backup
       suitable for point-in-time recovery if tables are stored in the
InnoDB
       storage engine.

       The simultaneous use of –master-data and –single-transaction
works as
       of MySQL 4.1.8. It provides a convenient way to make an online
backup
       suitable for point-in-time recovery if tables are stored in the
InnoDB
       storage engine.

       For more information on making backups, see Section 6.1,
“Database
       Backups”.

       For more information on making backups, see Section 6.1,
“Database
       Backups”.

SEE ALSO
       isamchk(1), isamlog(1), msql2mysql(1), myisamchk(1),
myisamlog(1),
       myisampack(1), mysql(1), mysql.server(1), mysql_config(1),
       mysql_fix_privilege_tables(1), mysql_zap(1),
mysqlaccess(1),
       mysqladmin(1), mysqlbinlog(1), mysqlcheck(1), mysqld(1),
       mysqld_multi(1), mysqld_safe(1), mysqlhotcopy(1),
mysqlimport(1),
       mysqlshow(1), pack_isam(1), perror(1), replace(1),
safe_mysqld(1)

SEE ALSO
       isamchk(1), isamlog(1), msql2mysql(1), myisamchk(1),
myisamlog(1),
       myisampack(1), mysql(1), mysql.server(1), mysql_config(1),
       mysql_fix_privilege_tables(1), mysql_zap(1),
mysqlaccess(1),
       mysqladmin(1), mysqlbinlog(1), mysqlcheck(1), mysqld(1),
       mysqld_multi(1), mysqld_safe(1), mysqlhotcopy(1),
mysqlimport(1),
       mysqlshow(1), pack_isam(1), perror(1), replace(1),
safe_mysqld(1)

       For more information, please refer to the MySQL Reference Manual,
which
       may already be installed locally and which is also available
online at
       .

       For more information, please refer to the MySQL Reference Manual,
which
       may already be installed locally and which is also available
online at
       .

AUTHOR
       MySQL AB ().  This software comes with no
       warranty.

AUTHOR
       MySQL AB ().  This software comes with no
       warranty.

复制代码 代码如下: mysqldump
-u用户名 -p密码 -h主机 数据库 a -w “sql条件” –lock-all-tables 路径mysqldump还原: 复制代码 代码如…

您或者感兴趣的稿子:

  • mysqldump备份还原和mysqldump导入导出语句大全详解
  • 用mysqldump备份和还原钦命表的不二等秘书诀
  • mysql
    mysqldump数据备份和增量备份
  • 应用mysqldump导入数据和mysqldump增量备份(mysqldump使用办法)
  • Windows下促成MySQL自动备份的批管理(复制目录或mysqldump备份)
  • mysql数据库备份及回复命令
    mysqldump,source的用法
  • mysql备份复苏mysqldump.exe几个常用用例
  • linux下改正版本mysqldump来备份MYSQL数据库
  • mysqldump加-w参数备份数据时要求留意的事项
  • mysql备份脚本
    mysqldump使用格局详解

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website