A problem encountered while configuring MySQL master-slave server in Docker.
Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘ANONYMOUS’ at master log mall-mysql-bin.000001, end_log_pos 2251. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Based on the hints given in the error message, execute in the mysql client to view the detailed error message.
select * from performance_schema.replication_applier_status_by_worker;
Worker 1 failed executing transaction ‘ANONYMOUS’ at master log
mall-mysql-bin.000001, end_log_pos 889; Error ‘Can’t create database
‘t1’; database exists’ on query. Default database: ‘t1’. Query:
‘create database t1’
Reasons:
1. The password policy problem of MySQL8, change the configuration file and use the policy of the previous version.
Execute these two commands in MySQL host client.
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'root';
Add a line to my.cnf that aligns MySQL8 with MySQL5.7 password authentication.
default_authentication_plugin=mysql_native_password
After the master and slave have changed this configuration, restart the master and slave.
(Each line of configuration in my.cnf file must remember to check if there are spaces at the end of the line. If there are spaces, delete them.)
docker restart mysql-master（Your own mysql host container name）
docker ps
docker restart mysql-slave（Your own mysql slave container name）
docker ps
2. My understanding is that the table already exists does not mean that your slave already exists. It means that the table already exists on the host before you configure the slave, so this problem will be reported.
Execute the following command on the slave MySQL client.
stop slave;
reset master;
Go to MySQL master and delete the database added by your own test.
drop database Add the database for your own testing;
show master status;
According to the values of File and Position of mysql-master, change the master_log_file and master_log_pos of the following command.
change master to master_host=‘192.168.159.200’, master_user=‘slave’,
master_password=‘root’, master_port=3307,
master_log_file=‘mall-mysql-bin.000002’, master_log_pos=331,
master_connect_retry=30;
After the change, execute this command on mysql-slave;
start slave;
show slave status\G
If you find that both Slave_IO_Running and Slave_SQL_Running show Yes, the MySQL master-slave configuration is successful.
As long as one of them is not Yes, it is something like Connecting or No, it means that the configuration is not successful.
After configuring the master-slave, create a new database and table on mysql-master, insert the data, and then go to the slave to verify that the data is synchronized over.
mysql-master
mysql-slave
So far, the installation of MySQL master-slave in docker is completed.
