13.1.24 DROP DATABASE Statement

Содержание

13.7.8.3 FLUSH Statement
13.1.37 TRUNCATE TABLE Statement
13.1.24 DROP DATABASE Statement

13.7.8.3 FLUSH Statement

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {
    flush_option [, flush_option] ...
  | tables_option
}

flush_option: {
    BINARY LOGS
  | ENGINE LOGS
  | ERROR LOGS
  | GENERAL LOGS
  | HOSTS
  | LOGS
  | PRIVILEGES
  | OPTIMIZER_COSTS
  | RELAY LOGS [FOR CHANNEL channel]
  | SLOW LOGS
  | STATUS
  | USER_RESOURCES
}

tables_option: {
    TABLES
  | TABLES tbl_name [, tbl_name] ...
  | TABLES WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... FOR EXPORT
}

The FLUSH statement has several
variant forms that clear or reload various internal caches,
flush tables, or acquire locks. Each
FLUSH operation requires the
privileges indicated in its description.

By default, the server writes
FLUSH statements to the binary
log so that they replicate to replicas. To suppress logging,
specify the optional NO_WRITE_TO_BINLOG
keyword or its alias LOCAL.

The FLUSH statement causes an
implicit commit. See Section 13.3.3, “Statements That Cause an Implicit Commit”.

Sending a SIGHUP or
SIGUSR1 signal to the server causes several
flush operations to occur that are similar to various forms of
the FLUSH statement. Signals can
be sent by the root system account or the
system account that owns the server process. This enables the
flush operations to be performed without having to connect to
the server, which requires a MySQL account that has privileges
sufficient for those operations. See
Section 4.10, “Unix Signal Handling in MySQL”.

The RESET statement is similar to
FLUSH. See
Section 13.7.8.6, “RESET Statement”, for information about using
RESET with replication.

FLUSH BINARY LOGS
Closes and reopens any binary log file to which the server
is writing. If binary logging is enabled, the sequence
number of the binary log file is incremented by one relative
to the previous file.
This operation requires the
RELOAD privilege.
FLUSH ENGINE LOGS
Closes and reopens any flushable logs for installed storage
engines. This causes InnoDB to flush its
logs to disk.
This operation requires the
RELOAD privilege.
FLUSH ERROR LOGS
Closes and reopens any error log file to which the server is
writing.
This operation requires the
RELOAD privilege.
FLUSH GENERAL LOGS
Closes and reopens any general query log file to which the
server is writing.
This operation requires the
RELOAD privilege.
This operation has no effect on tables used for the general
query log (see Section 5.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).
This operation requires the
RELOAD privilege.
For information about why host cache flushing might be
advisable or desirable, see Section 5.1.12.3, “DNS Lookups and the Host Cache”.
FLUSH HOSTS is deprecated
as of MySQL 8.0.23; expect it to be removed in a future
MySQL release. Instead, truncate the Performance Schema
host_cache table:
```
TRUNCATE TABLE performance_schema.host_cache;
```
The TRUNCATE TABLE
operation requires the DROP
privilege for the table rather than the
RELOAD privilege. You
should be aware that the TRUNCATE TABLE
statement is not written to the binary log. To obtain the
same behavior from FLUSH HOSTS, specify
NO_WRITE_TO_BINLOG or
LOCAL as part of the statement.
Closes and reopens any log file to which the server is
writing.
This operation requires the
RELOAD privilege.
The effect of this operation is equivalent to the combined
effects of these operations:
```
FLUSH BINARY LOGS
FLUSH ENGINE LOGS
FLUSH ERROR LOGS
FLUSH GENERAL LOGS
FLUSH RELAY LOGS
FLUSH SLOW LOGS
```
Re-reads the cost model tables so that the optimizer starts
using the current cost estimates stored in them.
This operation requires the
FLUSH_OPTIMIZER_COSTS or
RELOAD privilege.
The server writes a warning to the error log for any
unrecognized cost model table entries. For information about
these tables, see Section 8.9.5, “The Optimizer Cost Model”. This
operation affects only sessions that begin subsequent to the
flush. Existing sessions continue to use the cost estimates
that were current when they began.
Re-reads the privileges from the grant tables in the
mysql system schema. As part of this
operation, the server reads the
global_grants table containing dynamic
privilege assignments and registers any unregistered
privileges found there.
This operation requires the
RELOAD privilege.
If the --skip-grant-tables
option was specified at server startup to disable the MySQL
privilege system, FLUSH PRIVILEGES provides a way to enable the privilege
system at runtime.
Resets failed-login tracking (or enables it if the server
was started with
--skip-grant-tables) and
unlocks any temporarily locked accounts. See
Section 6.2.15, “Password Management”.
Clears the in-memory cache used by the
caching_sha2_password authentication
plugin. See
Cache Operation for SHA-2 Pluggable Authentication.
Closes and reopens any relay log file to which the server is
writing. If relay logging is enabled, the sequence number of
the relay log file is incremented by one relative to the
previous file.
This operation requires the
RELOAD privilege.
FLUSH SLOW LOGS
Closes and reopens any slow query log file to which the
server is writing.
This operation requires the
RELOAD privilege.
This operation has no effect on tables used for the slow
query log (see Section 5.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).
Flushes status indicators.
This operation adds the current thread’s session status
variable values to the global values and resets the session
values to zero. Some global variables may be reset to zero
as well. It also resets the counters for key caches (default
and named) to zero and sets
Max_used_connections to
the current number of open connections. This information may
be of use when debugging a query. See
Section 1.5, “How to Report Bugs or Problems”.
FLUSH STATUS is unaffected by
read_only or
super_read_only, and is
always written to the binary log.
This operation requires the
FLUSH_STATUS or
RELOAD privilege.

FLUSH TABLES Syntax

FLUSH TABLES flushes tables, and,
depending on the variant used, acquires locks. Any
TABLES variant used in a
FLUSH statement must be the only
option used. FLUSH TABLE is a synonym for FLUSH TABLES.

The descriptions here that indicate tables are flushed by
closing them apply differently for InnoDB,
which flushes table contents to disk but leaves them open.
This still permits table files to be copied while the tables
are open, as long as other activity does not modify them.

Closes all open tables, forces all tables in use to be
closed, and flushes the prepared statement cache.
This operation requires the
FLUSH_TABLES or
RELOAD privilege.
For information about prepared statement caching, see
Section 8.10.3, “Caching of Prepared Statements and Stored Programs”.
With a list of one or more comma-separated table names, this
operation is like FLUSH TABLES with no names except that the server
flushes only the named tables. If a named table does not
exist, no error occurs.
This operation requires the
FLUSH_TABLES or
RELOAD privilege.
FLUSH TABLES WITH READ LOCK
Closes all open tables and locks all tables for all
databases with a global read lock.
This operation requires the
FLUSH_TABLES or
RELOAD privilege.
This operation is a very convenient way to get backups if
you have a file system such as Veritas or ZFS that can take
snapshots in time. Use
UNLOCK TABLES to release the lock.
FLUSH TABLES WITH READ LOCK
acquires a global read lock rather than table locks, so it
is not subject to the same behavior as
LOCK TABLES and
UNLOCK TABLES with respect to table locking and implicit
commits:
FLUSH TABLES WITH READ LOCK
does not prevent the server from inserting rows into the log
tables (see Section 5.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”).
Flushes and acquires read locks for the named tables.
This operation requires the
FLUSH_TABLES or
RELOAD privilege. Because it
acquires table locks, it also requires the
LOCK TABLES privilege for
each table.
This operation applies only to existing base
(non-TEMPORARY) tables. If a name refers
to a base table, that table is used. If it refers to a
TEMPORARY table, it is ignored. If a name
applies to a view, an
ER_WRONG_OBJECT error
occurs. Otherwise, an
ER_NO_SUCH_TABLE error
occurs.
Use UNLOCK TABLES to release the locks,
LOCK TABLES to release the
locks and acquire other locks, or
START TRANSACTION to release the locks and begin a new
transaction.
This operation does not perform an implicit
UNLOCK TABLES, so an error results if you perform the
operation while there is any active
LOCK TABLES or use it a
second time without first releasing the locks acquired.
If a flushed table was opened with
HANDLER, the handler is
implicitly flushed and loses its position.
This FLUSH TABLES variant
applies to InnoDB tables. It ensures that
changes to the named tables have been flushed to disk so
that binary table copies can be made while the server is
running.
This operation requires the
FLUSH_TABLES or
RELOAD privilege. Because it
acquires locks on tables in preparation for exporting them,
it also requires the LOCK TABLES and SELECT
privileges for each table.
The operation works like this:
2. It checks whether all storage engines for the tables
  support FOR EXPORT. If any do not, an
  ER_ILLEGAL_HA error
  occurs and the operation fails.
3. The operation notifies the storage engine for each table
  to make the table ready for export. The storage engine
  must ensure that any pending changes are written to
  disk.
4. The operation puts the session in lock-tables mode so
  that the metadata locks acquired earlier are not
  released when the FOR EXPORT
  operation completes.
This operation applies only to existing base
(non-TEMPORARY) tables. If a name refers
to a base table, that table is used. If it refers to a
TEMPORARY table, it is ignored. If a name
applies to a view, an
ER_WRONG_OBJECT error
occurs. Otherwise, an
ER_NO_SUCH_TABLE error
occurs.
InnoDB supports FOR EXPORT for tables that have their own
.ibd
file file (that is, tables created with the
innodb_file_per_table
setting enabled). InnoDB ensures when
notified by the FOR EXPORT operation that
any changes have been flushed to disk. This permits a binary
copy of table contents to be made while the FOR EXPORT operation is in effect because the
.ibd file is transaction consistent and
can be copied while the server is running. FOR EXPORT does not apply to InnoDB
system tablespace files, or to InnoDB
tables that have FULLTEXT indexes.
When notified by FOR EXPORT,
InnoDB writes to disk certain kinds of
data that is normally held in memory or in separate disk
buffers outside the tablespace files. For each table,
InnoDB also produces a file named
table_name.cfg
in the same database directory as the table. The
.cfg file contains metadata needed to
reimport the tablespace files later, into the same or
different server.
When the FOR EXPORT operation completes,
InnoDB has flushed all
dirty pages to the
table data files. Any
change buffer
entries are merged prior to flushing. At this point, the
tables are locked and quiescent: The tables are in a
transactionally consistent state on disk and you can copy
the .ibd tablespace files along with
the corresponding .cfg files to get a
consistent snapshot of those tables.
For the procedure to reimport the copied table data into a
MySQL instance, see Section 15.6.1.3, “Importing InnoDB Tables”.
After you are done with the tables, use
UNLOCK TABLES to release the locks,
LOCK TABLES to release the
locks and acquire other locks, or
START TRANSACTION to release the locks and begin a new
transaction.
```
FLUSH TABLES ... WITH READ LOCK
FLUSH TABLES ... FOR EXPORT
LOCK TABLES ... READ
LOCK TABLES ... WRITE
```
```
FLUSH TABLES WITH READ LOCK
FLUSH TABLES ... WITH READ LOCK
FLUSH TABLES ... FOR EXPORT
```

13.1.37 TRUNCATE TABLE Statement

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE empties a table
completely. It requires the DROP
privilege. Logically, TRUNCATE TABLE is similar to a
DELETE statement that deletes all
rows, or a sequence of DROP TABLE
and CREATE TABLE statements.

Truncate operations drop and re-create the table, which is
much faster than deleting rows one by one, particularly for
large tables.
Truncate operations cause an implicit commit, and so cannot be
rolled back. See Section 13.3.3, “Statements That Cause an Implicit Commit”.
Truncation operations cannot be performed if the session holds
an active table lock.
TRUNCATE TABLE fails for an
InnoDB table or
NDB table if there are any
FOREIGN KEY constraints from other tables
that reference the table. Foreign key constraints between
columns of the same table are permitted.
Truncation operations do not return a meaningful value for the
number of deleted rows. The usual result is “0 rows
affected,” which should be interpreted as “no
information.”
As long as the table definition is valid, the table can be
re-created as an empty table with
TRUNCATE TABLE, even if the
data or index files have become corrupted.
Any AUTO_INCREMENT value is reset to its
start value. This is true even for MyISAM
and InnoDB, which normally do not reuse
sequence values.
When used with partitioned tables,
TRUNCATE TABLE preserves the
partitioning; that is, the data and index files are dropped
and re-created, while the partition definitions are
unaffected.
The TRUNCATE TABLE statement
does not invoke ON DELETE triggers.
Truncating a corrupted InnoDB table is
supported.

TRUNCATE TABLE is treated for
purposes of binary logging and replication as DDL rather than DML,
and is always logged as a statement.

TRUNCATE TABLE for a table closes
all handlers for the table that were opened with
HANDLER OPEN.

In MySQL 5.7 and earlier, on a system with a large buffer pool and
innodb_adaptive_hash_index
enabled, a TRUNCATE TABLE operation could cause
a temporary drop in system performance due to an LRU scan that
occurred when removing the table’s adaptive hash index entries
(Bug #68184). The remapping of TRUNCATE TABLE to DROP TABLE and
CREATE TABLE in MySQL 8.0 avoids
the problematic LRU scan.

TRUNCATE TABLE can be used with
Performance Schema summary tables, but the effect is to reset the
summary columns to 0 or NULL, not to remove
rows. See Section 27.12.20, “Performance Schema Summary Tables”.

13.1.24 DROP DATABASE Statement

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

DROP DATABASE drops all tables in
the database and deletes the database. Be
careful with this statement! To use
DROP DATABASE, you need the
DROP privilege on the database.
DROP SCHEMA is a synonym for DROP DATABASE.

When a database is dropped, privileges granted specifically for
the database are automatically dropped.
They must be dropped manually. See Section 13.7.1.6, “GRANT Statement”.

IF EXISTS is used to prevent an error from
occurring if the database does not exist.

If the default database is dropped, the default database is unset
(the DATABASE() function returns
NULL).

If you use DROP DATABASE on a
symbolically linked database, both the link and the original
database are deleted.

DROP DATABASE returns the number of
tables that were removed.

If other files or directories remain in the database directory
after MySQL removes those just listed, the database directory
cannot be removed. In this case, you must remove any remaining
files or directories manually and issue the
DROP DATABASE statement again.

Dropping a database does not remove any
TEMPORARY tables that were created in that
database. TEMPORARY tables are automatically
removed when the session that created them ends. See
Section 13.1.20.2, “CREATE TEMPORARY TABLE Statement”.

Время на прочтение

Как известно, все системные администраторы делятся на две категории. Те, кто уже делают бэкапы и те, кто ещё нет.

Подобно им, администраторы БД также делятся на две категории, те, кто уже запускал процедуру удаления на большой БД с типом таблиц InnoDB, и те, кому это ещё предстоит.

Разумеется, в теории все знают, что из-за особенностей InnoDB, удаление может быть долгим, но это знание сродни тому, что «надо делать бэкапы». Многие осознают эти нехитрые истины, только наступив на грабли.

Для понимания, удаление 350М записей в таблице на 500М записей может занять более двух суток. Вторые грабли, на которые многие наступают, это попытка прибить запрос. Как мы все помним, InnoDB движок транзакционный, поэтому если вы попытаетесь прибить запрос, он попытается откатить изменения, а это может занять больше времени, чем выполнялся запрос.

Как сделать так, чтобы не было мучительно больно? Добро пожаловать под кат!

1. Если вы УЖЕ запустили удаление и теперь хотите приблизительно оценить, сколько уже сделано и сколько ещё осталось, используйте SHOW ENGINE INNODB STATUS; Получите длинный вывод. Ищите в нём вашу команду и смотрите undo log entries, это и будет количество уже обработанных записей.

---TRANSACTION 1 4141054098, ACTIVE 191816 sec, OS thread id 36004918272 updating or deleting, thread declared inside InnoDB 84
mysql tables in use 1, locked 1
686063 lock struct(s), heap size 88520688, undo log entries 229144332
MySQL thread id 56087872, query id 2202164550 1.1.1.2 database updating
DELETE
FROM table
WHERE UNIX_TIMESTAMP(moment) < 1498712335 - 365 * 86400
AND UNIX_TIMESTAMP(moment) > 0

Далее, в зависимости от того, сколько уже сделано, решаете, ждать до победного конца или прерывать запрос.

2. Если вы только планируете начать удаление, используйте LIMIT.
Количество записей подбирается эмпирически, скажем на не особо мощном сервере я использовал LIMIT 5000, на более мощном LIMIT 50000. Начните с небольших значений и увеличивайте их по необходимости.

а) надо руками запустить запрос необходимое количество раз (ну, или сделать текстовый файл с необходимым количеством строк с запросом и запускать из него.
б) каждый последующий запрос выполняется дольше, чем предыдущий

3. Используйте pt-archiver из комплекта percona-tools.

Я бы рекомендовал именно этот способ по ряду причин:

а) он быстрый;
б) его можно прервать в любой момент;
в) в нём можно наблюдать за прогрессом операции.

pt-archiver --source h=127.0.0.1,D=build4,t=b_iblock_element \
    --optimize s --purge --where 'TAGS LIKE "%САПР%"' \
    --limit 1000 --commit-each --progress 500 --charset "CP1251"

В принципе, ключи довольно очевидны, тем не менее, пройдусь по ним:

—source — описывает подключение. Хост, база и таблица. При необходимости можно дописать логин и пароль (в примере я использую креды из ~/.my.cnf);
—optimize — оптимизирует исходную таблицу, либо ту, в которую переносятся данные. Поскольку в данном случае я не переношу, а удаляю данные, оптимизирую именно исходную (s) таблицу. В принципе, делать это необязательно;
—purge — изначально утилита предназначена для переноса данных в другую таблицу (или в файл). Но можно и просто удалять строки;
—where — обычное SQL-условие, по которому будут отбираться строки для удаления;
—limit 1000 — обрабатывать за раз 1000 строк (можно больше, зависит от производительности вашего сервера);
—commit-each — делать коммит после количества строк, указанного в —limit;
—progress 500 — выводить прогресс каждые 500 строк (опять же, имеет смысл подобрать этот параметр индивидуально);
—charset — кодировка. Если будет использоваться только ASCII-кодировка, проще указать —no-check-charset. Отдельно упомяну, что необходимо чтобы локаль консоли совпадала с указанным charset’ом, иначе ошибка не выведется, но и строки обработаны не будут.

Надеюсь, эта небольшая заметка показалась вам полезной (или хотя бы интересной).
Если вам есть, что добавить, поправить или просто высказаться — пишите!

Отдельной строкой хочу принести благодарность жж-пользователю merkwurdig, поднявшему обсуждение этой проблемы и заставившему меня вспомнить, как я бегал по таким же граблям, жж-пользовательнице svetasmirnova, которая всегда приходит на помощь при затруднениях с MySQL и сообществу ru_root, благодаря которому и появилась эта небольшая статья.

Первая установка базы данных MySQL не удалась, а затем установка не удалась много раз, причина в том, что она не полностью удалила базу данных MySQL

Так как же полностью удалить базу данных MySQL?

Ввести базу данных MySQL, чтобы полностью удалить

Идеально удалите MySQL

Что ж, я остановил сервис MySQL в службе Windows. Когда вы вводите дополнительную программу удаления, найдите MySQL, чтобы выбрать удаление, не забудьте выбрать удалить.

Наконец, удалите каталог MySQL, и каталог базы данных также удален. Перезагрузите машину и установите OK. Тест.

Если вы подтвердите, что упомянутая выше операционная среда была полностью удалена. Система установки все еще недоступна снова. Пожалуйста, перейдите под свой диск. Убедитесь, что каталог данных, необходимый для запуска C: \ J2SDK1.4.1_03, c: \ mysql, c: \ stame.

Если вы не можете полностью удалить его, вам нужно удалить сервис MySQL в реестре.

Есть несколько мест:

1. hkey_local_machine \ system \ controlset001 \ services \ eventlog \ application \ mysql каталог удаляет

2. hkey_local_machine \ system \ controlset002 \ services \ eventlog \ application \ mysql каталог удаляет

3. hkey_local_machine \ System \ CurrentControlset \ Services \ EventLog \ Application \ MySQL Directory Delete

ControlSet001, ControlSet002 в зарегистрированной форме, не обязательно 001 и 002, он может быть ControlSet005, 006 и т. Д.

Проверьте, есть ли файл my.ini в каталоге C: \ Windows, удалите его