Adding a replicated MySQL database instance using a Group Replication server as the source
March 25, 2019 Leave a comment
You say you want a Replication?
One of the best features of MySQL is the ability to use MySQL‘s built-in database replication feature to automatically replicate data from one server (source/master) to another (slave/replica). Group Replication was added in MySQL 5.7 as a way to provide a high-availability solution using a new variation of MySQL replication.
(In some earlier posts, I explained how to setup Group Replication using three MySQL database servers and how to create a sandbox for testing MySQL 8.0 InnoDB Cluster.)
The “regular version” of MySQL replication is still very powerful and relatively easy to setup. Some advantages of replication in MySQL include:
- Scale-out solutions – spreading the load among multiple slaves to improve performance. In this environment, all writes and updates must take place on the master server. Reads, however, may take place on one or more slaves. This model can improve the performance of writes (since the master is dedicated to updates), while dramatically increasing read speed across an increasing number of slaves.
- Data security – because data is replicated to the slave, and the slave can pause the replication process, it is possible to run backup services on the slave without corrupting the corresponding master data.
- Analytics – live data can be created on the master, while the analysis of the information can take place on the slave without affecting the performance of the master.
- Long-distance data distribution – you can use replication to create a local copy of data for a remote site to use, without permanent access to the master.
- For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master. See Section 17.2, “Replication Implementation”.
- Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup. <a target=new href=https://dev.mysql.com/doc/refman/8.0/en/point-in-time-recovery.htmlSee Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
Source: https://dev.mysql.com/doc/refman/8.0/en/replication.html
Even if you have Group Replication installed, you still might want to replicate that data to a separate server for various reasons. This post will explain a little about MySQL replication, and show you how to connect a replica (slave) server to a single server in a Group Replication group.
What is replication and how does it work?
To use MySQL replication, you need to understand something called the binary log (also called the binlog).
The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched zero rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:
Source: https://dev.mysql.com/doc/refman/8.0/en/binary-log.html
The replication process is simple. The replica (slave) database connects to the source (master) database via the I/O thread and retrieves the events from the binary log which have occurred since the last time it connected to the source (master) database. The source (master) database performs a binlog dump (which contains the new events), and the replica’s I/O thread transfers this dump to the replica (slave) server, and then stores these events on the replica’s MySQL instance in a “binary-log-type” file known as the relay log. Another thread (the SQL thread) then reads the events from the relay log and applies these changes to the database. This type of replication is asynchronous replication, because it is a one-way transfer of data (See the image below). There is another replication option called semi-synchronous, which ensures that the transaction on the source (master) database is written to the relay log of the replica (slave) before the transaction is committed.
For more information about the threads, see: https://dev.mysql.com/doc/refman/8.0/en/replication-implementation-details.html
The replica (slave) database has two options for retrieving the transactions from the source (master) database. The first is an older method, which requires the replica (slave) server to keep track of the last binary log accessed on the master for retrieving the transactions. The replica (slave) also has to keep track of the last position within the last binary log it used. For example, the last binary log was mysql-bin.000343 and the last position in that log was 49583. So, the next time the replica (slave) connects to the source (master) database, it asks for all of the transactions which occurred after this position (49583) in this binary log (mysql-bin.000343).
Global Transaction Identifiers
The second method involves using global transaction identifiers (GTID), which was introduced in MySQL version 5.6. A GTID is a unique identifier created and associated with each transaction committed on the server of origin (the source/master database). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology. Source: https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html
The GTID is comprised of a server’s UUID (a 36-character unique server identifier), a colon, and an incremental number. A GTID for a single transaction would look something like this: 3E11FA47-71CA-11E1-9E33-C80AA9429562:23 (as in: UUID:transaction number).
With GTID replication, the replica (slave) doesn’t need to keep track of the last binary log being used, nor does it need to remember the position with that binary log. Since the GTID’s contain incremental numbers, the replica (slave) only has to remember the last GTID it processed. For example, the source (master) server may have processed a group of GTID’s such as 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-30493. If the last GTID the replica (slave) retrieved was 3E11FA47-71CA-11E1-9E33-C80AA9429562:30201, then the replica (slave) will tell the source (master) that it needs every transaction (GTID) after 3E11FA47-71CA-11E1-9E33-C80AA9429562:30201.
Also, using GTID’s makes it much easier to see how many transactions have been executed on the source (master) database, and how many transactions have been retrieved and applied by the replica (slave) database. On the replica (slave) database, after replication has been started, the “SHOW SLAVE STATUS” command will display something like this:
mysql> SHOW SLAVE STATUS\G
…
Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-35683
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-34652
…
This tells the database administrator (DBA) that the replica (slave) server has retrieved 35683 transactions from the source (master) database, but has only applied (executed) 34652 transactions.
GTID’s also make it easier to skip bad transactions on the slave. Let’s say that the last transaction, 34652, was “bad”. The DBA would need to view that transaction by looking at it from the relay log using the mysqlbinlog tool (example: mysqlbinlog –include-gtids=8e2f4761-c55c-422f-8684-d086f6a1db0e:34652). And, let’s assume it was safe to delete this transaction, then the DBA would issue this command, effectively skipping this transaction and setting the next GTID to be used as 8e2f4761-c55c-422f-8684-d086f6a1db0e:34653:
STOP SLAVE;
SET GTID_NEXT=”8e2f4761-c55c-422f-8684-d086f6a1db0e:34653″;
BEGIN; COMMIT;
SET GTID_NEXT=”AUTOMATIC”;
START SLAVE;
Group Replication
Group Replication (GR) was introduced in MySQL version 5.7, and GR allows you to have a minimum of three servers in a high-availability group (with a maximum of nine servers). Groups can operate in a single-primary mode with automatic primary election, where only one server accepts updates at a time. Alternatively, for more advanced users, groups can be deployed in multi-primary mode, where all servers can accept updates, even if they are issued concurrently. Source: https://dev.mysql.com/doc/refman/8.0/en/group-replication.html
Group Replication requires the use of Global Transaction Identifiers (GTID’s). With “regular” single-source replication (master/slave), you can also use GTID’s – or the older method of specifying the binary log and position in the binary log. However, for replicating from a Group Replication group member, you must use GTID’s.
Replicating from a member in Group Replication
To replicate the data from a member of a Group Replication group, you simply need to point the replica (slave) database to one of the nodes in the group. This is the same method you would use if you were simply replicating off a single server. However, if the Group Replication server you are using for the source (master) goes down, then you will need to point the replica (slave) database to another member of the group. Since Group Replication uses the same UUID in the GTIDs, you can simply point the replica (slave) to any of the other servers in the group and replication will continue without any problems.
Configuring the replica (slave) server
You will need to add the following to your MySQL configuration file (my.cnf or my.ini) under the [mysqld] section, to enable replication using GTID’s:
|
gtid-mode = on enforce-gtid-consistency = 1 master-info-repository=TABLE relay-log-info-repository=TABLE binlog_format=row |
For this example, I will be using a newly-created (without any data) Group Replication group, and I will be adding a new replica (slave) server to it.
NOTE: If you have a Group Replication group with data on it, then you will need to import a copy of your current data over to the new replica (slave) server before starting replication. For more information – see “What if my Group Replication (GR) group already has data?”
After you have added the variables above to the MySQL server and rebooted the instance, you are now ready to make the server a replica (slave) of one of the servers in the Group Replication group. But first, we need to add a replication user to the Group Replication group.
Configuring the source (master) server
The new replica (slave) server will be replicating off a new three-node Group Replication (GR) group. I have already started Group Replication, and the GR is in single-primary mode.
Before I turn on replication to my new replica (slave) server, I can use the SHOW MASTER STATUS command to look at the primary server to see if any transactions have been executed yet (under the Executed_Gtid_Set heading):
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 1164
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
1 row in set (0.00 sec)
The Executed_Gtid_Set shows three transactions. When I look at these transactions using mysqlbinlog, I can see all three transactions are a SET TIMESTAMP command:
# mysqlbinlog mysql-bin.000001 ... SET TIMESTAMP=1553124003/*!*/; COMMIT ...
When you start Group Replication and a single member to the group, a view of the group is created, and this SET TIMESTAMP is executed. Since I have three members in this GR group, I will have three SET TIMESTAMP transactions. These transactions will be sent over to the new slave, but the transactions are harmless. I could tell the replica (slave) to ignore the transactions, but for this example, I will use them to see if replication is working.
When I created the Group Replication group, I already created a replication user named rpl_user on all three servers in the group. I can use this same user for my new single replica (slave). You don’t need to create this user on the replica (slave) server. However, I created the user with a domain wildcard (%) – so you might want to restrict user access via a domain or IP address.
Starting the replica (slave) server
To start replication, you need to tell the replica (slave) server which server you will use to retrieve data. You do this with the CHANGE MASTER command:
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.1.152',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'rpl_user',
-> MASTER_PASSWORD = 'R3plic4tion!',
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
You are now ready to start the replica (slave) server with the START SLAVE command:
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
Next, we need to see if replication has started. You can check this with the SHOW SLAVE STATUS command:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.152
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1164
Relay_Log_File: Rep01-relay-bin.000002
Relay_Log_Pos: 1370
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1164
Relay_Log_Space: 1570
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 152
Master_UUID: 247898e0-4cb7-11e9-97a9-12f28adcadd1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
As you can see above under the Retrieved_Gtid_Set – the replica (slave) has already retrieved and processed the three transactions which were on the Group Replication group.
Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
You can also see that the I/O and SQL threads are running:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Let’s test replication by adding a database on one of the Group Replication nodes, and then checking to see if it replicates to the replica (slave).
I am going to create the new database on the primary write server – which has an IP address of 192.168.1.151. We pointed the replica (slave) to 192.168.1.152, so the new database will get replicated to 192.168.1.152, and then over to our replica (slave) server (which has an IP address of 192.168.1.220).
On 192.168.1.151:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 1164
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database rep_test;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| rep_test |
| sys |
+--------------------+
5 rows in set (0.00 sec)
And we can see the new database has been replicated to the MySQL server on 192.168.1.152:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | rep_test | | sys | +--------------------+ 5 rows in set (0.00 sec)
And it is now also on the new replica (slave) server:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | rep_test | | sys | +--------------------+ 5 rows in set (0.02 sec)
That’s all you need to do. Replication is now up and running!
What if my Group Replication (GR) group already has data?
If you have an existing GR group with data, you need the new replica (slave) to have a copy of the data that is already in the group – to give the new replica (slave) a starting point for replication. The binary logs will probably not have all of the transactions that have been applied to the database – and even if it did, it would take an extremely long time to replicate those events. It is much easier to restore a backup to the new replica (slave) server.
Once you load the new replica (slave) with the data from a backup, you can then start replication from the last GTID that was executed on the source (master) before you performed the backup. To get a copy of the data to move over to the new replica (slave), you can use the mysqldump utility – or if you are an Enterprise Edition subscriber, you can use the mysqlbackup utility (which is much faster than mysqldump). Using mysqldump will take a considerable amount of time if you have a lot of data (Example: mysqldump will take about an hour to dump 15-20 gigabytes and 4-5 hours to restore 15-20 gigabytes – while mysqlbackup would take less than 10 minutes for both processes. Your actual results may vary.)
After you restore the backup to the replica (slave) server, you can then start replication. Prior to the backup, I executed a few transactions on the Group Replication servers to increase the GTID numbers. I used this mysqlbackup command to backup one of the read-only servers in the Group Replication group:
/usr/local/meb/bin/mysqlbackup --user=mysqlbackup --password --backup-dir=/Users/tonydarnell/hotbackups backup-and-apply-log --with-timestamp --encrypt-password
Also look at the screen output from the backup, and you will see that it contains the GTID information of the source (master) database:
... 190322 21:07:48 MAIN INFO: GTID_EXECUTED is 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45695 ...
This number also matches the SHOW MASTER STATUS\G output from one of the Group Replication servers:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 13044
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45695
1 row in set (0.00 sec)
I can now restore this backup to the new replica (slave) server using the mysqlbackup utility. First, I will shut down the MySQL instance, copy the backup to this server, and then I can run the copy-back command:
# /usr/local/meb/bin/mysqlbackup --defaults-file=/etc/my.cnf --backup-dir=/Users/tonydarnell/Desktop/2019-03-22_21-07-46 copy-back MySQL Enterprise Backup Ver 8.0.15-commercial for macos10.14 on x86_64 (MySQL Enterprise - Commercial) Copyright (c) 2003, 2019, Oracle and/or its affiliates. All rights reserved. ... mysqlbackup completed OK! with 7 warnings
Here are a few things to consider when restoring data:
- If you don’t delete the old data directory, you will need to use the force variable at the end of the command to overwrite the existing MySQL data directory.
- The MySQL config files (my.cnf or my.ini) are not restored. If you don’t have the same settings for variables like innodb_data_file_path, you will get an error when you try and restart MySQL after the restore.
- Be sure to change the owner of your MySQL directories. If you run mysqlbackup as root, the directories will be owned by root and you won’t be able to start MySQL.
# cd /usr/local/mysql # ls -l total 1224 ... drwxr-x--- 26 root wheel 884 Mar 22 21:30 data ... # chown -R mysql data
The backup from the source (master) server has been restored to the replica (slave), and I have restarted the MySQL database instance. Now I can reset the slave, use the CHANGE MASTER TO command, start the slave, and I will have the same starting point as the source (master) database.
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.1.152',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'rpl_user',
-> MASTER_PASSWORD = 'R3plic4tion!',
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
...
Retrieved_Gtid_Set:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45695
...
mysql>
If you look at the Executed_Gtid_Set, you can see the replica (slave) database has 45695 transactions from the source (master) database. But, since we haven’t started replication yet, and the replica (slave) database has not connected to the source (master) database, the Retrieved_Gtid_Set is blank. Since we restored the first 45695 transactions, those will already be included in the Executed_Gtid_Set.
After more transactions are executed on the source (master) database, when you execute the SHOW SLAVE STATUS command later, you will see something like the following – where the Retrieved_Gtid_Set doesn’t begin with the number one, but instead it begins with the next number (45696) after the original set of GTID’s (45695) that were restored from the backup. Since the replica (slave) didn’t actually retrieve the first 45695 transactions, those won’t appear in the Retrieved_Gtid_Set, but you can see all of the GTID’s are in the Executed_Gtid_Set.
mysql> SHOW SLAVE STATUS\G
...
Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:45696-45753
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45753
...
And we can compare this list to the source (master) database:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 13044
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-45753
1 row in set (0.00 sec)
Now we know the replica (slave) database is caught up to the source (master) database.
Restoring a slave using mysqldump
Using mysqldump to backup your source (master) database and restore it to a replica (slave) database isn’t as easy as using mysqlbackup. With mysqldump, you can’t backup and restore the mysql.user table, which contains all of your user information. So, you can’t backup the mysql database and restore it. Since I only have one database (rep_test) on my Group Replication source (master) database, I can use the following command to create a mysqldump backup file.
/usr/local/mysql/bin/mysqldump --databases rep_test --set-gtid-purged=ON --master-data --add-drop-database --add-drop-table --triggers --user=root -p > /users/tonydarnell/Desktop/2019_03_22_2300_dbdump.txt
This creates a backup file which also includes the GTID information from the source (master) server. If you had more databases, you would need to list them after the databases variable. I have a new install of MySQL version 8.0.15 that I will be using to restore this data, and after copying the backup file to the new replica (slave) server, the restore command is very simple:
# mysql -uroot -p < 2019_03_22_2300_dbdump.txt Enter password:
I can now take a look at the transactions which have been applied to the server by using the SHOW MASTER STATUS\G command:
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 155
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-26
1 row in set (0.00 sec)
And – this Executed_Gtid_Set matches the source (master) database (this was before I added thousands of records as shown in the mysqlbackup example). But – I don’t have any of the users that were created on the master, because I can’t export and import the mysql database. It is easier and probably more secure if you just add whatever users you need on this new replica (slave) database.
If you don’t have access to the users you need, there is a way to get the information from the mysql.user table.
mysql> select user, host from mysql.user; +------------------+----------------+ | user | host | +------------------+----------------+ | mysqlbackup | % | | root | % | | rpl_user | % | | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | | root | macvm151.local | +------------------+----------------+ 8 rows in set (0.00 sec)
In this example, I am only going to re-create the user named mysqlbackup. I could use the SHOW CREATE USER command, but it doesn’t give me the exact format I need to re-create the user:
mysql> show create user mysqlbackup\G *************************** 1. row *************************** CREATE USER for mysqlbackup@%: CREATE USER 'mysqlbackup'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$:\n*~t^;+s/,+g1L6EDOPfmWHQxa/z7C.mHStg15xDyMq7UzHsi2hTKM10' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT 1 row in set (0.00 sec)
Instead of trying to extract what I need from the mysql table, I have found it is easier to just re-create the user, and then re-apply the grants. BUT – I don’t really want this to be stored in the binary log, so I can prevent it from writing to the binary log with the SET SQL_LOG_BIN=0 command:
SET SQL_LOG_BIN=0; CREATE USER 'mysqlbackup'@'%' IDENTIFIED WITH sha256_password BY 'new-password'; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1;
(I turned on writing to the binary log with the SET SQL_LOG_BIN=1 command.
As for the grants, I have found it is easier to just grab the grants from the mysql.tables_priv table:
mysql> show grants for mysqlbackup; +----------------------------------------------------------------------------------------------------+ | Grants for mysqlbackup@% | +----------------------------------------------------------------------------------------------------+ | GRANT RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO `mysqlbackup`@`%` | | GRANT INSERT, CREATE, DROP, ALTER ON `mysql`.`backup_history_new` TO `mysqlbackup`@`%` | | GRANT INSERT, CREATE, DROP ON `mysql`.`backup_history_old` TO `mysqlbackup`@`%` | | GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `mysql`.`backup_history` TO `mysqlbackup`@`%` | | GRANT INSERT, UPDATE, CREATE, DROP ON `mysql`.`backup_progress` TO `mysqlbackup`@`%` | | GRANT SELECT ON `performance_schema`.`replication_group_members` TO `mysqlbackup`@`%` | +----------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec)
And, with a little editing, I can extract the GRANT commands and run these as well – and again, I am going to suppress these from writing to the binary logs.
SET SQL_LOG_BIN=0; GRANT RELOAD, PROCESS, SUPER, REPLICATION CLIENT ON *.* TO `mysqlbackup`@`%`; GRANT INSERT, CREATE, DROP, ALTER ON `mysql`.`backup_history_new` TO `mysqlbackup`@`%`; GRANT INSERT, CREATE, DROP ON `mysql`.`backup_history_old` TO `mysqlbackup`@`%`; GRANT SELECT, INSERT, UPDATE, CREATE, DROP, ALTER ON `mysql`.`backup_history` TO `mysqlbackup`@`%`; GRANT INSERT, UPDATE, CREATE, DROP ON `mysql`.`backup_progress` TO `mysqlbackup`@`%`; GRANT SELECT ON `performance_schema`.`replication_group_members` TO `mysqlbackup`@`%`; FLUSH PRIVILEGES; SET SQL_LOG_BIN=1;
I now have all the users I need, and I can start replication:
mysql> select user, host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysqlbackup | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
mysql> CHANGE MASTER TO
-> MASTER_HOST = '192.168.1.152',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'rpl_user',
-> MASTER_PASSWORD = 'R3plic4tion!',
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.152
Master_User: rpl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 9463
Relay_Log_File: Rep01-relay-bin.000002
Relay_Log_Pos: 408
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 9463
Relay_Log_Space: 616
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 152
Master_UUID: 247898e0-4cb7-11e9-97a9-12f28adcadd1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-26
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)
I am going to test replication by adding another database on the source (master):
mysql> create database rpl_test2; Query OK, 1 row affected (0.01 sec)
I can now see the new database on the replica (slave) server:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | rep_test | | rpl_test2 | | sys | +--------------------+ 6 rows in set (0.00 sec)
And when I check the GTID’s, I can see that GTID 27 (the transaction which created the database rpl_test2) has been retrieved and applied:
mysql> show slave status\G
...
Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:27
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-27
...
Since I imported the first 26 GTID’s, those will not appear in the Retrieved_Gtid_Set. But, the Executed_Gtid_Set shows that the new replica (slave) database has all of the same transactions as the source (master) database.
Note: For more information on mysqldump replication options, see: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-replication-options
Other posts on restoring backups to servers
I have two older posts which explain how to do this. Some of the MySQL variables might have changed, but these posts will explain the process behind using both utilities.
Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2
Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2
 |
Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn. |
 |
Tony is the author of Twenty Forty-Four: The League of Patriots Visit http://2044thebook.com for more information. |
 |
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition Visit https://amzn.to/2oPFLI0 for more information. |
info [at] Scripting
Scripting MySQL 