MySQL master and slave and problem encountered solves

MySQL master and slave and problem solving

1. Docker based on mysql 8.0

2. Configure the configuration of mysql

Create the master folder and create the file:

[mysqld]
## Set server_id, be sure to be unique in the same LANserver-id=1
## Turn on binloglog-bin=mysql-bin
## binlog format (mixed, statement, row, default format is statement)binlog_format=mixed

Create a slave folder and create a file

[mysqld]
## Set server_id, be sure to be uniqueserver-id=2
## You can enable binlog for use when Slave is used as the master of other Slaves#log-bin=mysql-bin
## relay_log configure relay logrelay_log=relay-bin
## If synchronous functions or stored procedures are requiredlog_bin_trust_function_creators=true
## binlog format (mixed, statement, row, default format is statement)binlog_format=mixed
## Skip all errors encountered in master-slave replication or errors of the specified type to avoid interruption of slave-side replication## For example: 1062 error refers to some primary key duplication, 1032 error is because the master and slave database data is inconsistentslave_skip_errors=1062   ## Skip errors encountered in master-slave replication, can be matched or notread-only=1 #1Read only，0Read and write

The sh file can be created at the same level

#!/bin/sh
# $0 gets the file that executes the command; realpath gets the real path; dirname gets the absolute path of the filescript_dir=$(dirname "$(realpath "$0")")
docker run -itd --name doyen$1 -p ${2}:3306 -e MYSQL_ROOT_PASSWORD=123456 \
-v ${script_dir}/${1}/:/etc/ \
-v ${script_dir}/${1}/data:/var/lib/mysql \
-v ${script_dir}/${1}/log:/var/log --restart always mysql:8.0

Execute sh:

#Enable master container, master file name, port 3306sh  master 3306

#Enable slave container, slave file name, port 3307sh  master 3307

#Enter the container, if it fails to use shdocker exec -it master bash
#Login mysqlmysql -uroot -p123456
use mysql;
#You can remove all regions of root login, and only the local login can be retaineddrop user 'root'@'%';
#Create a slave user and connect with a plaintext passwordcreate user 'slaver'@'%' identified with mysql_native_password by '123456';
grant replication SLAVE ON *.* TO 'slaver'@'%';
flush privileges;

#View binlog data for offset migration of slaveshow master status;

#master_log_file and master_log_pos are the data displayed in the show in masterchange master to master_host='master-ip',
                     master_port=3306,
					 master_user='slaver',
					 master_password='123456',
					 master_log_file='mysql-bin.000002',
					 master_log_pos=157;
start SLAVE;
show slave status\G;

Master-slave mysql completed,Slave_IO_RunningandSlave_SQL_RunningAll forYes。

Description of the problems encountered

Some blogs prompt that the configuration location of mysql is incorrect, resulting in the path mapping. Using the default configuration, the serverId is all 1, which will cause the master-slave conflict, and the location is /etc/. Docker restart is required after modifying the configuration.

Query file:

docker exec -it container find / -name ""

If the slave's change connection master is written incorrectly, you can execute stop slave; reset slave; and then rebind.

This is to start copying over and over if the master has been running for a while

#master container: full dump, --source-data=2 means that the generated backup file contains CHANGE MASTER TO statement#--single-transaction: Ensure backup is completed at the consistency point#--flush-logs: Refresh MySQL's binary log at the start of the backup#--hex-blob: Use hexadecimal representation when processing binary fields
mysqldump -u root -p --all-databases --source-data=2 --single-transaction --flush-logs --hex-blob &gt; /var/lib/mysql/

#Mv the master file into the mapping path of slave and enter the slave container
mysql -u root -p &lt; 

#After execution, configure the binlog synchronization point and find the offset from the sql file
CHANGE MASTER TO 。。。

#-------------------------------two.  Too much data, just dump the table structure ------mysqldump -u root -p123456 --no-data --databases your_database &gt; 
mysql -u root -p123456 &lt; 

start slave;

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.