• notice
  • Congratulations on the launch of the Sought Tech site

Docker deploys master-slave database

Foreword: In the completed project, the database read and write separation is used. The master database is responsible for writing and the slave database is responsible for reading. Due to the configuration problem of the cloud server, a master and a slave database are introduced.

1. Pull mysql

docker pull mysql
docker images

insert image description here

Second, run the master-slave database

#Main database master
docker run -d -p 3301:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=123456 mysql
#Slave database slave
docker run -d -p 3302:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD=123456 mysql
#View running status
docker ps

insert image description here

IDEA test connection

insert image description here

insert image description here

3. Configure the master-slave database

  1. master database

#enter master
docker exec -it mysql-master /bin/bash
#Switch to the /etc/mysql directory
cd /etc/mysql
#Install vim first
apt-get update
apt-get install vim
#Use the vim command to modify my.cnf
vim my.cnf

insert image description here

Edit my.cnf

[mysqld]
#Set server-id, pay attention to be unique
server-id=1
#Enable binary log function
log_bin=mysql-bin
#Secondary log format, there are three kinds of row, statement, mixed
binlog-format=ROW
#Control binlog log file retention time, binlog logs that exceed the retention time will be automatically deleted
expire_logs_days=7
#synchronized database, if it does not match, it will synchronize all libraries
binlog-do-db=test
# out of sync database
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# exit the container
exit
# restart the container
docker restart mysql-master

2、slave

#enter slave
docker exec -it mysql-slave /bin/bash
#Switch to the /etc/mysql directory
cd /etc/mysql
#Install vim first
apt-get update
apt-get install vim
#Use the vim command to modify my.cnf
vim my.cnf

Edit my.cnf

[mysqld]
#Set server-id, pay attention to be unique
server-id=2
#relay_logConfiguring relay logs
relay_log=mysql-relay-bin
# set to read only
read_only=1
# exit the container
exit
# restart the container
docker restart mysql-slave

4. Enable master-slave master-slave replication

  1. master

    docker exec -it mysql-master /bin/bash
    mysql -uroot -p123456
#create user
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
#Authorize the user
GRANT ALL PRIVILEGES ON *.* TO 'slave'@'%' WITH GRANT OPTION;
#Modify the encryption method
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
# refresh configuration
FLUSH PRIVILEGES;
#View Master status
show master status;

insert image description here

#exit mysql
exit
# exit the container
exit
#View the ip of this container
docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql-master
172.17.0.4

2、slave

docker exec -it mysql-slave /bin/bash
mysql -uroot -p123456
change master to
#Main database ip
master_host='172.17.0.4',
#Main database username
master_user='slave',
#Master database password
master_password='123456',
#Main database port number
master_port=3306,
#log file start copying data
master_log_file='mysql-bin.000004',
#Start from which Position to read, that is, the value of the Position field mentioned above
master_log_pos=708,
#If the connection fails, the retry interval
master_connect_retry=30;
#start slave
start slave;
#View slave
show slave status \G;
  1. insert image description here

  2. If it fails, you can get from the container IP, port, user and password, Position value

Five, test the master-slave database

  1. master

    insert image description here

  2. slave

    insert image description here


Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+