First, environmental preparation

We need to prepare two MySql containers, so I’m using Docker here, so I need to open two MySql containers. Docker install MySql docker install MySql docker install MySql

\

Master/slave replication

2-1: Configure the primary server

2-1-1: Configure my.cnf for the primary server add the following

[mysqld] # mysqld # mysqld # mysqld # mysqld # mysqld # mysqld # mysqld # mysqld # mysqldCopy the code

If you start MySql the way I did above, you just need to go to the configuration folder of your associated host and create a my.cnf and write the class contents above.

For example: my startup command is as follows (should not break line, here for convenience, I give it a branch)

/docker/mysql_master/conf/my.cnf /mysql_master/conf/my.cnf

docker run -p 12345:3306 --name mysql_master -v /docker/mysql_master/conf:/etc/mysql/conf.d -v / docker mysql_master/logs: / logs - v/docker mysql_master/data: / var/lib/mysql - e MYSQL_ROOT_PASSWORD = 123456 - d mysql: 5.7Copy the code

2-1-2: Restart the service

 service mysql restart
Copy the code

2-1-3: Viewing the skip_networking status

Make sure the skip_networking option is turned OFF on the primary server, which is the default. If enabled, the slave cannot communicate with the master and replication fails.

mysql> show variables like '%skip_networking%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | skip_networking | OFF | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

2-1-4: Create a user dedicated to replication

CREATE USER 'repl'@'%' identified by '123456';

GRANT REPLICATION SLAVE ON *.*  TO  'repl'@'%';
Copy the code

2-2: Configure the secondary server

2-2-1: configures the secondary servermy.cnf(Same as above)

[mysqld]
server-id=2
Copy the code

docker run -p 12346:3306 --name mysql_from 
-v /docker/mysql_from/conf:/etc/mysql/conf.d 
-v /docker/mysql_from/logs:/logs 
-v /docker/mysql_from/data:/var/lib/mysql 
-e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
Copy the code

2-2-2: Restart the secondary server (same as above)

2-2-3: Configure information about the connection to the primary server

mysql>  CHANGE MASTER TO MASTER_HOST='xxxxx', MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='123456';
Copy the code

Docker inspect - format = '{{. NetworkSettings. IPAddress}}' containers name/idCopy the code

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
Copy the code

2-2-4: View the synchronization status

mysql>  show slave status \G;
Copy the code

\

2-3: test

2-4: other

2-4-1: If you want another slave server, then you just need to configure another slave server as above.

2-4-2: New secondary server that automatically saves data from the previous primary server. (Test results)

\

Master master replication

1, modify the above secondary server’s my.cnf file to be the same as the primary server’s (note that the server-ID cannot be the same) and restart the server

2, create a replicating user from the replicating server.

3. Run the following code on the previous master server

 CHANGE MASTER TO MASTER_HOST='xxxx', MASTER_PORT=3306,MASTER_USER='repl2',MASTER_PASSWORD='123456';

start slave;
Copy the code

4, test,

Four, other

The above is mainly about how to set up a MySql cluster, but there are many other problems. It is also the problem I think about in the learning process. Some friends may not be able to read the long article and just want to realize the clustering function, so I write the problem in the following.

MySQL replication and PXC

MySQL replication data synchronization principle

The binary log is enabled during configuration and will be updated to this log every time we operate the database. Master and slave synchronize this log to ensure data consistency.

\

3, can not synchronize all data

4. How to turn off and start synchronization

mysql> STOP SLAVE;

mysql> START SLAVE;
Copy the code

\

5. I have drawn, as I understand it, master slave, master slave, master master, copy.

6. Video explanation

www.bilibili.com/video/BV1BK…