102 lines
5.1 KiB
Plaintext
102 lines
5.1 KiB
Plaintext
include/group_replication.inc [rpl_server_count=2]
|
|
Warnings:
|
|
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
|
|
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
|
|
[connection server1]
|
|
CREATE ROLE 'gr_user','gr_recovery_user';
|
|
GRANT SYSTEM_VARIABLES_ADMIN ON *.* TO 'gr_user';
|
|
GRANT REPLICATION SLAVE ON *.* TO 'gr_recovery_user';
|
|
CREATE USER 'alpha' IDENTIFIED BY 'alpha';
|
|
GRANT gr_user TO alpha;
|
|
# Check that 'gr_user' role is assigned to 'alpha' user
|
|
[connection alpha]
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
|
|
SET GLOBAL group_replication_bootstrap_group= 1;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
|
|
START GROUP_REPLICATION;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or GROUP_REPLICATION_ADMIN privilege(s) for this operation
|
|
[connection server1]
|
|
ALTER USER alpha DEFAULT ROLE 'gr_user';
|
|
# Check that 'gr_user' role is enabled for 'alpha' user
|
|
[connection alpha]
|
|
SELECT CURRENT_ROLE();
|
|
CURRENT_ROLE()
|
|
`gr_user`@`%`
|
|
SET GLOBAL group_replication_group_name= "GROUP_REPLICATION_GROUP_NAME";
|
|
SET GLOBAL group_replication_bootstrap_group= 1;
|
|
START GROUP_REPLICATION;
|
|
ERROR 42000: Access denied; you need (at least one of) the SUPER or GROUP_REPLICATION_ADMIN privilege(s) for this operation
|
|
[connection server1]
|
|
GRANT GROUP_REPLICATION_ADMIN ON *.* TO 'gr_user';
|
|
[connection alpha]
|
|
START GROUP_REPLICATION;
|
|
SET GLOBAL group_replication_bootstrap_group= 0;
|
|
[connection server1]
|
|
# Wait until server1 is ONLINE
|
|
[connection server2]
|
|
SET @group_replication_recovery_retry_count_save= @@GLOBAL.group_replication_recovery_retry_count;
|
|
SET @group_replication_recovery_reconnect_interval_save= @@GLOBAL.group_replication_recovery_reconnect_interval;
|
|
set sql_log_bin=0;
|
|
call mtr.add_suppression(".*Slave I/O for channel 'group_replication_recovery': Master command COM_REGISTER_SLAVE failed.*");
|
|
call mtr.add_suppression(".*Slave I/O thread couldn't register on master");
|
|
call mtr.add_suppression("Maximum number of retries when*");
|
|
call mtr.add_suppression("Fatal error during the incremental recovery process of Group Replication. The server will leave the group.");
|
|
call mtr.add_suppression("Skipping leave operation: concurrent attempt to leave the group is on-going.");
|
|
call mtr.add_suppression("There was an error when connecting to the donor*");
|
|
call mtr.add_suppression("For details please check performance_schema.replication_connection_status table and error log messages of Slave I/O for channel group_replication_recovery.");
|
|
call mtr.add_suppression("The server was automatically set into read only mode after an error was detected.");
|
|
set sql_log_bin=1;
|
|
SET GLOBAL group_replication_group_name= "GROUP_REPLICATION_GROUP_NAME";
|
|
SET GLOBAL group_replication_recovery_retry_count= 1;
|
|
# Set the reconnect interval to one. To prevent timeout on recovery.
|
|
SET GLOBAL group_replication_recovery_reconnect_interval= 1;
|
|
CHANGE MASTER TO MASTER_USER='alpha', MASTER_PASSWORD='alpha' FOR CHANNEL 'group_replication_recovery';
|
|
Warnings:
|
|
Note 1759 Sending passwords in plain text without SSL/TLS is extremely insecure.
|
|
Note 1760 Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
|
|
include/start_group_replication.inc
|
|
Pattern found.
|
|
Pattern found.
|
|
include/stop_group_replication.inc
|
|
[connection server1]
|
|
GRANT gr_recovery_user TO alpha;
|
|
ALTER USER alpha DEFAULT ROLE ALL;
|
|
# Check that all roles assigned to 'alpha' user are enabled
|
|
SELECT DEFAULT_ROLE_USER FROM mysql.default_roles WHERE USER='alpha';
|
|
DEFAULT_ROLE_USER
|
|
gr_recovery_user
|
|
gr_user
|
|
SELECT FROM_USER FROM mysql.role_edges WHERE TO_USER='alpha';
|
|
FROM_USER
|
|
gr_recovery_user
|
|
gr_user
|
|
[connection alpha]
|
|
SELECT CURRENT_ROLE();
|
|
CURRENT_ROLE()
|
|
`gr_recovery_user`@`%`,`gr_user`@`%`
|
|
SHOW GRANTS;
|
|
Grants for alpha@%
|
|
GRANT REPLICATION SLAVE ON *.* TO `alpha`@`%`
|
|
GRANT GROUP_REPLICATION_ADMIN,SYSTEM_VARIABLES_ADMIN ON *.* TO `alpha`@`%`
|
|
GRANT `gr_recovery_user`@`%`,`gr_user`@`%` TO `alpha`@`%`
|
|
CREATE DATABASE newtest;
|
|
ERROR 42000: Access denied for user 'alpha'@'%' to database 'newtest'
|
|
[connection server2]
|
|
include/start_group_replication.inc
|
|
GRANT ALL ON newtest.* TO gr_recovery_user;
|
|
[connection alpha]
|
|
CREATE DATABASE newtest;
|
|
use newtest;
|
|
CREATE TABLE t1(a INT PRIMARY KEY);
|
|
INSERT INTO t1 VALUES (1),(2);
|
|
UPDATE t1 SET a=4 WHERE a=1;
|
|
DELETE FROM t1;
|
|
[connection server2]
|
|
SET GLOBAL group_replication_recovery_retry_count= @group_replication_recovery_retry_count_save;
|
|
SET GLOBAL group_replication_recovery_reconnect_interval= @group_replication_recovery_reconnect_interval_save;
|
|
DROP TABLE newtest.t1;
|
|
DROP DATABASE newtest;
|
|
DROP ROLE gr_user, gr_recovery_user;
|
|
DROP USER alpha;
|
|
include/group_replication_end.inc
|