<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="alter-database">
      </a>
      15.1.2 ALTER DATABASE Statement
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045190070192">
  </a>
  <a class="indexterm" name="idm46045190069152">
  </a>
  <a class="indexterm" name="idm46045190068080">
  </a>
  <a class="indexterm" name="idm46045190066592">
  </a>
  <a class="indexterm" name="idm46045190065104">
  </a>
  <a class="indexterm" name="idm46045190063616">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72909117"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> {<span class="token keyword">DATABASE</span> <span class="token operator">|</span> <span class="token keyword">SCHEMA</span>} <span class="token punctuation">[</span><em class="replaceable">db_name</em><span class="token punctuation">]</span>
    <em class="replaceable">alter_option</em> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>

<em class="replaceable">alter_option</em>: {
    <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token punctuation">]</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">charset_name</em>
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token punctuation">]</span> <span class="token keyword">COLLATE</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> <em class="replaceable">collation_name</em>
  <span class="token operator">|</span> <span class="token punctuation">[</span><span class="token keyword">DEFAULT</span><span class="token punctuation">]</span> <span class="token keyword">ENCRYPTION</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token string">'Y'</span> <span class="token operator">|</span> <span class="token string">'N'</span>}
  <span class="token operator">|</span> <span class="token keyword">READ</span> <span class="token keyword">ONLY</span> <span class="token punctuation">[</span><span class="token operator">=</span><span class="token punctuation">]</span> {<span class="token keyword">DEFAULT</span> <span class="token operator">|</span> <span class="token number">0</span> <span class="token operator">|</span> <span class="token number">1</span>}
}</code></pre>
  </div>
  <p>
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   enables you to
      change the overall characteristics of a database. These
      characteristics are stored in the data dictionary. This statement
      requires the
   <a class="link" href="privileges-provided.html#priv_alter">
    <code class="literal">
     ALTER
    </code>
   </a>
   privilege on the
      database.
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER
      SCHEMA
    </code>
   </a>
   is a synonym for
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER
      DATABASE
    </code>
   </a>
   .
  </p>
  <p>
   If the database name is omitted, the statement applies to the
      default database. In that case, an error occurs if there is no
      default database.
  </p>
  <p>
   For any
   <em class="replaceable">
    <code>
     alter_option
    </code>
   </em>
   omitted from the
      statement, the database retains its current option value, with the
      exception that changing the character set may change the collation
      and vice versa.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="alter-database.html#alter-database-charset" title="Character Set and Collation Options">
       Character Set and Collation Options
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-database.html#alter-database-encryption" title="Encryption Option">
       Encryption Option
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="alter-database.html#alter-database-read-only" title="Read Only Option">
       Read Only Option
      </a>
     </p>
    </li>
   </ul>
  </div>
  <h4>
   <a name="alter-database-charset">
   </a>
   Character Set and Collation Options
  </h4>
  <p>
   The
   <code class="literal">
    CHARACTER SET
   </code>
   option changes the default
      database character set. The
   <code class="literal">
    COLLATE
   </code>
   option
      changes the default database collation. For information about
      character set and collation names, see
   <a class="xref" href="charset.html" title="Chapter 12 Character Sets, Collations, Unicode">
    Chapter 12,
    <i>
     Character Sets, Collations, Unicode
    </i>
   </a>
   .
  </p>
  <p>
   To see the available character sets and collations, use the
   <a class="link" href="show-character-set.html" title="15.7.7.4 SHOW CHARACTER SET Statement">
    <code class="literal">
     SHOW CHARACTER SET
    </code>
   </a>
   and
   <a class="link" href="show-collation.html" title="15.7.7.5 SHOW COLLATION Statement">
    <code class="literal">
     SHOW COLLATION
    </code>
   </a>
   statements,
      respectively. See
   <a class="xref" href="show-character-set.html" title="15.7.7.4 SHOW CHARACTER SET Statement">
    Section 15.7.7.4, “SHOW CHARACTER SET Statement”
   </a>
   , and
   <a class="xref" href="show-collation.html" title="15.7.7.5 SHOW COLLATION Statement">
    Section 15.7.7.5, “SHOW COLLATION Statement”
   </a>
   .
  </p>
  <p>
   A stored routine that uses the database defaults when the routine
      is created includes those defaults as part of its definition. (In
      a stored routine, variables with character data types use the
      database defaults if the character set or collation are not
      specified explicitly. See
   <a class="xref" href="create-procedure.html" title="15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements">
    Section 15.1.17, “CREATE PROCEDURE and CREATE FUNCTION Statements”
   </a>
   .) If
      you change the default character set or collation for a database,
      any stored routines that are to use the new defaults must be
      dropped and recreated.
  </p>
  <h4>
   <a name="alter-database-encryption">
   </a>
   Encryption Option
  </h4>
  <p>
   The
   <code class="literal">
    ENCRYPTION
   </code>
   option, defines the default
      database encryption, which is inherited by tables created in the
      database. The permitted values are
   <code class="literal">
    'Y'
   </code>
   (encryption enabled) and
   <code class="literal">
    'N'
   </code>
   (encryption
      disabled).
  </p>
  <p>
   The
   <code class="literal">
    mysql
   </code>
   system schema cannot be set to
      default encryption. The existing tables within it are part of the
      general
   <code class="literal">
    mysql
   </code>
   tablespace, which may be
      encrypted. The
   <code class="literal">
    information_schema
   </code>
   contains only
      views. It is not possible to create any tables within it. There is
      nothing on the disk to encrypt. All tables in the
   <code class="literal">
    performance_schema
   </code>
   use the
   <a class="link" href="performance-schema.html" title="Chapter 29 MySQL Performance Schema">
    <code class="literal">
     PERFORMANCE_SCHEMA
    </code>
   </a>
   engine, which is
      purely in-memory. It is not possible to create any other tables in
      it. There is nothing on the disk to encrypt.
  </p>
  <p>
   Only newly created tables inherit the default database encryption.
      For existing tables associated with the database, their encryption
      remains unchanged. If the
   <a class="link" href="server-system-variables.html#sysvar_table_encryption_privilege_check">
    <code class="literal">
     table_encryption_privilege_check
    </code>
   </a>
   system variable is enabled, the
   <a class="link" href="privileges-provided.html#priv_table-encryption-admin">
    <code class="literal">
     TABLE_ENCRYPTION_ADMIN
    </code>
   </a>
   privilege is
      required to specify a default encryption setting that differs from
      the value of the
   <a class="link" href="server-system-variables.html#sysvar_default_table_encryption">
    <code class="literal">
     default_table_encryption
    </code>
   </a>
   system
      variable. For more information, see
   <a class="xref" href="innodb-data-encryption.html#innodb-schema-tablespace-encryption-default" title="Defining an Encryption Default for Schemas and General Tablespaces">
    Defining an Encryption Default for Schemas and General Tablespaces
   </a>
   .
  </p>
  <h4>
   <a name="alter-database-read-only">
   </a>
   Read Only Option
  </h4>
  <a class="indexterm" name="idm46045190019984">
  </a>
  <p>
   The
   <code class="literal">
    READ ONLY
   </code>
   option controls whether to permit
      modification of the database and objects within it. The permitted
      values are
   <code class="literal">
    DEFAULT
   </code>
   or
   <code class="literal">
    0
   </code>
   (not
      read only) and
   <code class="literal">
    1
   </code>
   (read only). This option is
      useful for database migration because a database for which
   <code class="literal">
    READ ONLY
   </code>
   is enabled can be migrated to another
      MySQL instance without concern that the database might be changed
      during the operation.
  </p>
  <p>
   With NDB Cluster, making a database read only on one
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   server is synchronized to other
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   servers in the same cluster, so that the
      database becomes read only on all
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   servers.
  </p>
  <p>
   The
   <code class="literal">
    READ ONLY
   </code>
   option, if enabled, is displayed
      in the
   <code class="literal">
    INFORMATION_SCHEMA
   </code>
   <a class="link" href="information-schema-schemata-extensions-table.html" title="28.3.32 The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table">
    <code class="literal">
     SCHEMATA_EXTENSIONS
    </code>
   </a>
   table. See
   <a class="xref" href="information-schema-schemata-extensions-table.html" title="28.3.32 The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table">
    Section 28.3.32, “The INFORMATION_SCHEMA SCHEMATA_EXTENSIONS Table”
   </a>
   .
  </p>
  <a class="indexterm" name="idm46045190005888">
  </a>
  <p>
   The
   <code class="literal">
    READ ONLY
   </code>
   option cannot be enabled for
      these system schemas:
   <code class="literal">
    mysql
   </code>
   ,
   <code class="literal">
    information_schema
   </code>
   ,
   <code class="literal">
    performance_schema
   </code>
   .
  </p>
  <p>
   In
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   statements, the
   <code class="literal">
    READ ONLY
   </code>
   option interacts with other instances
      of itself and with other options as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      An error occurs if multiple instances of
      <code class="literal">
       READ
          ONLY
      </code>
      conflict (for example,
      <code class="literal">
       READ ONLY = 1
          READ ONLY = 0
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      An
      <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
       <code class="literal">
        ALTER DATABASE
       </code>
      </a>
      statement
          that contains only (nonconflicting)
      <code class="literal">
       READ
          ONLY
      </code>
      options is permitted even for a read-only
          database.
     </p>
    </li>
    <li class="listitem">
     <p>
      A mix of (nonconflicting)
      <code class="literal">
       READ ONLY
      </code>
      options
          with other options is permitted if the read-only state of the
          database either before or after the statement permits
          modifications. If the read-only state both before and after
          prohibits changes, an error occurs.
     </p>
     <p>
      This statement succeeds whether or not the database is read
          only:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa8316771"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">DATABASE</span> mydb <span class="token keyword">READ</span> <span class="token keyword">ONLY</span> <span class="token operator">=</span> <span class="token number">0</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This statement succeeds if the database is not read only, but
          fails if it is already read only:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa9562597"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">DATABASE</span> mydb <span class="token keyword">READ</span> <span class="token keyword">ONLY</span> <span class="token operator">=</span> <span class="token number">1</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">COLLATE</span> utf8mb4_bin<span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Enabling
   <code class="literal">
    READ ONLY
   </code>
   affects all users of the
      database, with these exceptions that are not subject to read-only
      checks:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Statements executed by the server as part of server
          initialization, restart, upgrade, or replication.
     </p>
    </li>
    <li class="listitem">
     <p>
      Statements in a file named at server startup by the
      <a class="link" href="server-system-variables.html#sysvar_init_file">
       <code class="literal">
        init_file
       </code>
      </a>
      system variable.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       TEMPORARY
      </code>
      tables; it is possible to create,
          alter, drop, and write to
      <code class="literal">
       TEMPORARY
      </code>
      tables
          in a read-only database.
     </p>
    </li>
    <li class="listitem">
     <p>
      NDB Cluster non-SQL inserts and updates.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Other than for the excepted operations just listed, enabling
   <code class="literal">
    READ ONLY
   </code>
   prohibits write operations to the
      database and its objects, including their definitions, data, and
      metadata. The following list details affected SQL statements and
      operations:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The database itself:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
          <code class="literal">
           CREATE DATABASE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
          <code class="literal">
           ALTER DATABASE
          </code>
         </a>
         (except to
              change the
         <code class="literal">
          READ ONLY
         </code>
         option)
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="drop-database.html" title="15.1.24 DROP DATABASE Statement">
          <code class="literal">
           DROP DATABASE
          </code>
         </a>
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Views:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="create-view.html" title="15.1.23 CREATE VIEW Statement">
          <code class="literal">
           CREATE VIEW
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="alter-view.html" title="15.1.11 ALTER VIEW Statement">
          <code class="literal">
           ALTER VIEW
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="drop-view.html" title="15.1.35 DROP VIEW Statement">
          <code class="literal">
           DROP VIEW
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         Selecting from views that invoke functions with side
              effects.
        </p>
       </li>
       <li class="listitem">
        <p>
         Updating updatable views.
        </p>
       </li>
       <li class="listitem">
        <p>
         Statements that create or drop objects in a writable
              database are rejected if they affect metadata of a view in
              a read-only database (for example, by making the view
              valid or invalid).
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Stored routines:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="create-procedure.html" title="15.1.17 CREATE PROCEDURE and CREATE FUNCTION Statements">
          <code class="literal">
           CREATE PROCEDURE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="drop-procedure.html" title="15.1.29 DROP PROCEDURE and DROP FUNCTION Statements">
          <code class="literal">
           DROP PROCEDURE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="call.html" title="15.2.1 CALL Statement">
          <code class="literal">
           CALL
          </code>
         </a>
         (of procedures with
              side effects)
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="create-function.html" title="15.1.14 CREATE FUNCTION Statement">
          <code class="literal">
           CREATE FUNCTION
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="drop-function.html" title="15.1.26 DROP FUNCTION Statement">
          <code class="literal">
           DROP FUNCTION
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="select.html" title="15.2.13 SELECT Statement">
          <code class="literal">
           SELECT
          </code>
         </a>
         (of functions with
              side effects)
        </p>
       </li>
       <li class="listitem">
        <p>
         For procedures and functions, read-only checks follow
              prelocking behavior. For
         <a class="link" href="call.html" title="15.2.1 CALL Statement">
          <code class="literal">
           CALL
          </code>
         </a>
         statements, read-only
              checks are done on a per-statement basis, so if some
              conditionally executed statement writing to a read-only
              database does not actually execute, the call still
              succeeds. On the other hand, for a function called within
              a
         <a class="link" href="select.html" title="15.2.13 SELECT Statement">
          <code class="literal">
           SELECT
          </code>
         </a>
         , execution of the
              function body happens in prelocked mode. As long as a some
              statement within the function writes to a read-only
              database, execution of the function fails with an error
              regardless of whether the statement actually executes.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Triggers:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="create-trigger.html" title="15.1.22 CREATE TRIGGER Statement">
          <code class="literal">
           CREATE TRIGGER
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="drop-trigger.html" title="15.1.34 DROP TRIGGER Statement">
          <code class="literal">
           DROP TRIGGER
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         Trigger invocation.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Events:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="create-event.html" title="15.1.13 CREATE EVENT Statement">
          <code class="literal">
           CREATE EVENT
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="alter-event.html" title="15.1.3 ALTER EVENT Statement">
          <code class="literal">
           ALTER EVENT
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="drop-event.html" title="15.1.25 DROP EVENT Statement">
          <code class="literal">
           DROP EVENT
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         Event execution:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            Executing an event in the database fails because that
                  would change the last-execution timestamp, which is
                  event metadata stored in the data dictionary. Failure
                  of event execution also has the effect of causing the
                  event scheduler to stop.
           </p>
          </li>
          <li class="listitem">
           <p>
            If an event writes to an object in a read-only
                  database, execution of the event fails with an error,
                  but the event scheduler is not stopped.
           </p>
          </li>
         </ul>
        </div>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Tables:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE TABLE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER TABLE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
          <code class="literal">
           CREATE INDEX
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="drop-index.html" title="15.1.27 DROP INDEX Statement">
          <code class="literal">
           DROP INDEX
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
          <code class="literal">
           RENAME TABLE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="truncate-table.html" title="15.1.37 TRUNCATE TABLE Statement">
          <code class="literal">
           TRUNCATE TABLE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="drop-table.html" title="15.1.32 DROP TABLE Statement">
          <code class="literal">
           DROP TABLE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
          <code class="literal">
           DELETE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
          <code class="literal">
           INSERT
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="import-table.html" title="15.2.6 IMPORT TABLE Statement">
          <code class="literal">
           IMPORT TABLE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="load-data.html" title="15.2.9 LOAD DATA Statement">
          <code class="literal">
           LOAD DATA
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="load-xml.html" title="15.2.10 LOAD XML Statement">
          <code class="literal">
           LOAD XML
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="replace.html" title="15.2.12 REPLACE Statement">
          <code class="literal">
           REPLACE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
          <code class="literal">
           UPDATE
          </code>
         </a>
        </p>
       </li>
       <li class="listitem">
        <p>
         For cascading foreign keys where the child table is in a
              read-only database, updates and deletes on the parent are
              rejected even if the child table is not directly affected.
        </p>
       </li>
       <li class="listitem">
        <p>
         For a
         <code class="literal">
          MERGE
         </code>
         table such as
         <code class="literal">
          CREATE TABLE s1.t(i int) ENGINE MERGE UNION
              (s2.t, s3.t), INSERT_METHOD=...
         </code>
         , the following
              behavior applies:
        </p>
        <div class="itemizedlist">
         <ul class="itemizedlist" style="list-style-type: square; ">
          <li class="listitem">
           <p>
            Inserting into the
            <code class="literal">
             MERGE
            </code>
            table
                  (
            <code class="literal">
             INSERT into s1.t
            </code>
            ) fails if at
                  least one of
            <code class="literal">
             s1
            </code>
            ,
            <code class="literal">
             s2
            </code>
            ,
            <code class="literal">
             s3
            </code>
            is read
                  only, regardless of insert method. The insert is
                  refused even if it would actually end up in a writable
                  table.
           </p>
          </li>
          <li class="listitem">
           <p>
            Dropping the
            <code class="literal">
             MERGE
            </code>
            table
                  (
            <code class="literal">
             DROP TABLE s1.t
            </code>
            ) succeeds as long
                  as
            <code class="literal">
             s1
            </code>
            is not read only. It is
                  permitted to drop a
            <code class="literal">
             MERGE
            </code>
            table
                  that refers to a read-only database.
           </p>
          </li>
         </ul>
        </div>
       </li>
      </ul>
     </div>
    </li>
   </ul>
  </div>
  <p>
   An
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   statement blocks
      until all concurrent transactions that have already accessed an
      object in the database being altered have committed. Conversely, a
      write transaction accessing an object in a database being altered
      in a concurrent
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   blocks until the
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   has
      committed.
  </p>
  <p>
   If the Clone plugin is used to clone a local or remote data
      directory, the databases in the clone retain the read-only state
      they had in the source data directory. The read-only state does
      not affect the cloning process itself. If it is not desirable to
      have the same database read-only state in the clone, the option
      must be changed explicitly for the clone after the cloning process
      has finished, using
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   operations on the clone.
  </p>
  <p>
   When cloning from a donor to a recipient, if the recipient has a
      user database that is read only, cloning fails with an error
      message. Cloning may be retried after making the database
      writable.
  </p>
  <p>
   <code class="literal">
    READ ONLY
   </code>
   is permitted for
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   , but not for
   <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
    <code class="literal">
     CREATE DATABASE
    </code>
   </a>
   . However, for a
      read-only database, the statement produced by
   <a class="link" href="show-create-database.html" title="15.7.7.7 SHOW CREATE DATABASE Statement">
    <code class="literal">
     SHOW CREATE DATABASE
    </code>
   </a>
   does include
   <code class="literal">
    READ ONLY=1
   </code>
   within a comment to indicate its
      read-only status:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa67267831"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">DATABASE</span> mydb <span class="token keyword">READ</span> <span class="token keyword">ONLY</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">DATABASE</span> mydb\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
       Database<span class="token punctuation">:</span> mydb
Create Database<span class="token punctuation">:</span> CREATE DATABASE `mydb`
                 /<span class="token punctuation">*</span>!40100 DEFAULT CHARACTER SET utf8mb4
                          COLLATE utf8mb4_0900_ai_ci <span class="token punctuation">*</span>/
                 /<span class="token punctuation">*</span>!80016 DEFAULT ENCRYPTION='N' <span class="token punctuation">*</span>/
                 /<span class="token punctuation">*</span> READ ONLY = 1 <span class="token punctuation">*</span>/</span></code></pre>
  </div>
  <p>
   If the server executes a
   <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
    <code class="literal">
     CREATE
      DATABASE
    </code>
   </a>
   statement containing such a comment, the server
      ignores the comment and the
   <code class="literal">
    READ ONLY
   </code>
   option is
      not processed. This has implications for
   <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
    <span class="command">
     <strong>
      mysqldump
     </strong>
    </span>
   </a>
   , which uses
   <a class="link" href="show-create-database.html" title="15.7.7.7 SHOW CREATE DATABASE Statement">
    <code class="literal">
     SHOW
      CREATE DATABASE
    </code>
   </a>
   to produce
   <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
    <code class="literal">
     CREATE
      DATABASE
    </code>
   </a>
   statements in dump output:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      In a dump file, the
      <a class="link" href="create-database.html" title="15.1.12 CREATE DATABASE Statement">
       <code class="literal">
        CREATE
          DATABASE
       </code>
      </a>
      statement for a read-only database contains
          the commented
      <code class="literal">
       READ ONLY
      </code>
      option.
     </p>
    </li>
    <li class="listitem">
     <p>
      The dump file can be restored as usual, but because the server
          ignores the commented
      <code class="literal">
       READ ONLY
      </code>
      option, the
          restored database is
      <span class="emphasis">
       <em>
        not
       </em>
      </span>
      read only. If
          the database is to be read only after being restored, you must
          execute
      <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
       <code class="literal">
        ALTER DATABASE
       </code>
      </a>
      manually
          to make it so.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Suppose that
   <code class="literal">
    mydb
   </code>
   is read only and you dump it
      as follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa20385081"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysqldump</span> <span class="token property">--databases</span> mydb &gt; mydb<span class="token punctuation">.</span>sql</code></pre>
  </div>
  <p>
   A restore operation later must be followed by
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   if
   <code class="literal">
    mydb
   </code>
   should still be read only:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa38204086"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span>
<span class="token prompt">mysql&gt;</span><span class="token command"> SOURCE</span> mydb<span class="token punctuation">.</span>sql<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span><span class="token command"> ALTER</span> DATABASE mydb READ ONLY = 1<span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   MySQL Enterprise Backup is not subject to this issue. It backs up and restores a
      read-only database like any other, but enables the
   <code class="literal">
    READ
      ONLY
   </code>
   option at restore time if it was enabled at backup
      time.
  </p>
  <p>
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   is written to the
      binary log, so a change to the
   <code class="literal">
    READ ONLY
   </code>
   option
      on a replication source server also affects replicas. To prevent
      this from happening, binary logging must be disabled prior to
      execution of the
   <a class="link" href="alter-database.html" title="15.1.2 ALTER DATABASE Statement">
    <code class="literal">
     ALTER DATABASE
    </code>
   </a>
   statement. For example, to prepare for migrating a database
      without affecting replicas, perform these operations:
  </p>
  <div class="orderedlist">
   <ol class="orderedlist" type="1">
    <li class="listitem">
     <p>
      Within a single session, disable binary logging and enable
      <code class="literal">
       READ ONLY
      </code>
      for the database:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50424908"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> sql_log_bin <span class="token operator">=</span> <span class="token keyword">OFF</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">DATABASE</span> mydb <span class="token keyword">READ</span> <span class="token keyword">ONLY</span> <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Dump the database, for example, with
      <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
       <span class="command">
        <strong>
         mysqldump
        </strong>
       </span>
      </a>
      :
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa39855320"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysqldump</span> <span class="token property">--databases</span> mydb &gt; mydb<span class="token punctuation">.</span>sql</code></pre>
     </div>
    </li>
    <li class="listitem">
     <p>
      Within a single session, disable binary logging and disable
      <code class="literal">
       READ ONLY
      </code>
      for the database:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa85436538"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> sql_log_bin <span class="token operator">=</span> <span class="token keyword">OFF</span><span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">ALTER</span> <span class="token keyword">DATABASE</span> mydb <span class="token keyword">READ</span> <span class="token keyword">ONLY</span> <span class="token operator">=</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
     </div>
    </li>
   </ol>
  </div>
 </div>
 <br/>
</div>
