Rechercher dans le manuel MySQL
17.1.3.4 Setting Up Replication Using GTIDs
This section describes a process for configuring and starting GTID-based replication in MySQL 8.0. This is a “cold start” procedure that assumes either that you are starting the replication master for the first time, or that it is possible to stop it; for information about provisioning replication slaves using GTIDs from a running master, see Section 17.1.3.5, “Using GTIDs for Failover and Scaleout”. For information about changing GTID mode on servers online, see Section 17.1.5, “Changing Replication Modes on Online Servers”.
The key steps in this startup process for the simplest possible GTID replication topology, consisting of one master and one slave, are as follows:
If replication is already running, synchronize both servers by making them read-only.
Stop both servers.
Restart both servers with GTIDs enabled and the correct options configured.
The mysqld options necessary to start the servers as described are discussed in the example that follows later in this section.
Instruct the slave to use the master as the replication data source and to use auto-positioning. The SQL statements needed to accomplish this step are described in the example that follows later in this section.
Take a new backup. Binary logs containing transactions without GTIDs cannot be used on servers where GTIDs are enabled, so backups taken before this point cannot be used with your new configuration.
Start the slave, then disable read-only mode on both servers, so that they can accept updates.
In the following example, two servers are already running as
master and slave, using MySQL's binary log position-based
replication protocol. If you are starting with new servers, see
Section 17.1.2.3, “Creating a User for Replication” for information about
adding a specific user for replication connections and
Section 17.1.2.1, “Setting the Replication Master Configuration” for
information about setting the
server_id
variable. The following
examples show how to store mysqld startup
options in server's option file, see
Section 4.2.2.2, “Using Option Files” for more information. Alternatively
you can use startup options when running
mysqld.
Most of the steps that follow require the use of the MySQL
root
account or another MySQL user account that
has the SUPER
privilege.
mysqladmin shutdown
requires
either the SUPER
privilege or the
SHUTDOWN
privilege.
Step 1: Synchronize the servers.
This step is only required when working with servers which are
already replicating without using GTIDs. For new servers proceed
to Step 3. Make the servers read-only by setting the
read_only
system variable to
ON
on each server by issuing the following:
Wait for all ongoing transactions to commit or roll back. Then, allow the slave to catch up with the master. It is extremely important that you make sure the slave has processed all updates before continuing.
If you use binary logs for anything other than replication, for example to do point in time backup and restore, wait until you do not need the old binary logs containing transactions without GTIDs. Ideally, wait for the server to purge all binary logs, and wait for any existing backup to expire.
It is important to understand that logs containing transactions without GTIDs cannot be used on servers where GTIDs are enabled. Before proceeding, you must be sure that transactions without GTIDs do not exist anywhere in the topology.
Step 2: Stop both servers.
Stop each server using mysqladmin as shown
here, where username
is the user name
for a MySQL user having sufficient privileges to shut down the
server:
shell> mysqladmin -uusername -p shutdown
Then supply this user's password at the prompt.
Step 3: Start both servers with GTIDs enabled.
To enable GTID-based replication, each server must be started
with GTID mode enabled by setting the
gtid_mode
variable to
ON
, and with the
enforce_gtid_consistency
variable enabled to ensure that only statements which are safe
for GTID-based replication are logged. For example:
gtid_mode=ON
enforce-gtid-consistency=true
In addition, you should start slaves with the
--skip-slave-start
option before
configuring the slave settings. For more information on GTID
related options and variables, see
Section 17.1.6.5, “Global Transaction ID Options and Variables”.
It is not mandatory to have binary logging enabled in order to use
GTIDs when using the
mysql.gtid_executed Table. Masters
must always have binary logging enabled in order to be able to
replicate. However, slave servers can use GTIDs but without binary
logging. If you need to disable binary logging on a slave server,
you can do this by specifying the
--skip-log-bin
and
--skip-log-slave-updates
options for the slave.
Step 4: Configure the slave to use GTID-based auto-positioning.
Tell the slave to use the master with GTID based transactions as
the replication data source, and to use GTID-based
auto-positioning rather than file-based positioning. Issue a
CHANGE MASTER TO
statement on the
slave, including the MASTER_AUTO_POSITION
option in the statement to tell the slave that the master's
transactions are identified by GTIDs.
You may also need to supply appropriate values for the master's host name and port number as well as the user name and password for a replication user account which can be used by the slave to connect to the master; if these have already been set prior to Step 1 and no further changes need to be made, the corresponding options can safely be omitted from the statement shown here.
- > MASTER_HOST = host,
- > MASTER_PORT = port,
- > MASTER_AUTO_POSITION = 1;
Neither the MASTER_LOG_FILE
option nor the
MASTER_LOG_POS
option may be used with
MASTER_AUTO_POSITION
set equal to 1. Attempting
to do so causes the CHANGE MASTER
TO
statement to fail with an error.
Step 5: Take a new backup. Existing backups that were made before you enabled GTIDs can no longer be used on these servers now that you have enabled GTIDs. Take a new backup at this point, so that you are not left without a usable backup.
For instance, you can execute FLUSH
LOGS
on the server where you are taking backups. Then
either explicitly take a backup or wait for the next iteration of
any periodic backup routine you may have set up.
Step 6: Start the slave and disable read-only mode. Start the slave like this:
The following step is only necessary if you configured a server to be read-only in Step 1. To allow the server to begin accepting updates again, issue the following statement:
GTID-based replication should now be running, and you can begin (or resume) activity on the master as before. Section 17.1.3.5, “Using GTIDs for Failover and Scaleout”, discusses creation of new slaves when using GTIDs.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-replication-gtids-howto.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.