Shulou(Shulou.com)06/02 Report--

This article mainly introduces how MacOS uses Docker to create a MySQL master-slave database. It is very detailed and has a certain reference value. Interested friends must read it!

1. Pull MySQL image

Get the latest MySQL image through the terminal

Docker pull mysql/mysql-server

Create a directory corresponding to the configuration file of the MySQL database container

We create a set of directories under the current user to store MySQL container configuration files. (this step can be omitted under Linux) refer to the following figure:

Note: after the MySQL8 version, you need to add mysql-files to the mapping file, otherwise the creation of the MySQL database container will fail.

Because vi/vim does not support direct modification of my.cnf files under MacOS, nor does it support apt-get to install vim, you need to create two new my.cnf mapping files locally. (under Linux, you can modify the configuration file directly through vim)

The my.cnf configuration file corresponding to the master master library is:

[mysqld] server_id = 1logmurbin = mysql-binread-only=0replicate-ignore-db=mysqlreplicate-ignore-db=sysreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schema

The my.cnf configuration file corresponding to the slave slave library is:

[mysqld] server_id = 2logmurbin = mysql-binread-only=1replicate-ignore-db=mysqlreplicate-ignore-db=sysreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schema

Create two MySQL database containers

Create a master master database container

Docker run-- name mysql-master-d-p 3307 MYSQL_ROOT_PASSWORD=123456-v / Users/yumaster/test/mysql_master_slave/master/data:/var/lib/mysql-v / Users/yumaster/test/mysql_master_slave/master/conf/my.cnf:/etc/mysql/my.cnf-v / Users/yumaster/test/mysql_master_slave/master/mysql-files:/var/lib/mysql-files mysql/mysql-server

Create a slave from the database container

Docker run-- name mysql-slave-d-p 3308 MYSQL_ROOT_PASSWORD=123456-v / Users/yumaster/test/mysql_master_slave/slave/data:/var/lib/mysql-v / Users/yumaster/test/mysql_master_slave/slave/conf/my.cnf:/etc/mysql/my.cnf-v / Users/yumaster/test/mysql_master_slave/slave/mysql-files:/var/lib/mysql-files mysql/mysql-server

As shown in the following figure, the two MySQL containers are created successfully

At this point we open the Docker dashboard and see that the two containers are already running. And the port is the corresponding port we created earlier.

We will report a 1130 error through Navicat connection because the connected user account does not have permission to connect remotely. You need to change the host entry in the user table in the mysql database

Change localhost to%

Specific steps:

Mysql > use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > select host from user where user='root';+-+ | host | +-+ | localhost | +-+ 1 row in set (0.01 sec) mysql > update user set host='%' where user='root' Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > select host from user where user='root';+-+ | host | +-+ |% | +-+ 1 row in set (0.00 sec) mysql > flush privileges;Query OK, 0 rows affected (0.01 sec)

IV. Master-slave database configuration

Master master database configuration:

/ / enter the master master data container docker exec-it mysql-master mysql- uroot-p123456 / create a user to synchronize the data, and each slave connects to the master using the standard MySQL username and password. The user performing the replication operation is granted REPLICATION SLAVE permission. In previous versions of mysql8, the encryption rule was mysql_native_password, while after mysql8, the encryption rule was caching_sha2_passwordCREATE USER 'slave'@'%' IDENTIFIED BY' 123456 slave'@'%'; (it is possible that there was a time error in creating a connection with master in slave) or CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY' 123456 authorization GRANT REPLICATION SLAVE ON *. * to 'slave'@'%' / / check the status and remember the values of File and Position. In Slave, show master status;// will be used to query the IP of the master container, and docker inspect mysql-master will be used when slave sets the connection to the main library. | grep IPA

Status of mster, File mysql-bin.000003 Position 661

Slave configuration from the database:

/ / enter slave from the data container docker exec-it mysql-slave mysql- uroot-p123456 move / set the master library link change master tochange master to master_host='172.17.0.2',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=661,master_port=3306;// to launch the slave library synchronization start slave;// view status show slave status\ G / / if "/ / Slave_IO_Running: Yes//Slave_SQL_Running: Yes//" appears in the result of the show slave status\ G command, both of the above items are Yes, that means there is no problem. / / otherwise, reconfigure stop slave;reset slave all from data

Synchronization of slave library started successfully

V. Master-slave verification

If we create a database on master, then create a table, and then insert a piece of data, the corresponding slave will be increased.

Create database master_slave_demo;use master_slave_demo;create table userinfo (username varchar (50), age int); insert into userinfo values ('Toulon',25); select * from userinfo

Before executing the command, the number of master and slave databases is the same.

Slave increases the corresponding data after master executes the command

You can find that the new data in the master database has been synchronized, and the master-slave replication of MySQL has been set up. (test environment, MacOS M1 ARM64 machine, Docker,MySQL 8.0.27)

The above is all the contents of the article "how MacOS uses Docker to create MySQL master-slave database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.