Detailed explanation of the configuration method of mysql double master-Mysql Tutorial-php.cn

Detailed explanation of the configuration method of mysql double master

藏色散人

Release： 2021-09-15 17:30:20

forward

2091 people have browsed it

Assume that the IP addresses of the two machines are machine one: 192.168.14.37 machine two: 192.168.14.38, and the server is linux Rhel 5.9

Execute the create user statement on two servers:

mysql:>create user 'repl'@'%' identified by '135246';       -- 创建用户  repl  密码 135246

Copy after login

Server one execution:

mysql:>grant replication client,replication slave on *.* to 'repl'@'192.168.14.38' identified by '135246';    -- 授权服务器一可以远程访问服务器二

Copy after login

Server two execution:

mysql:>grant replication client,replication slave on *.* to 'repl'@'192.168.14.37' identified by '135246';     -- 授权服务器二可以远程访问服务器一

Copy after login

Verification (enter the password according to the prompt) ：
Connect to server two on server one

mysql -h 192.168.14.38 -u repl -p

Copy after login

Connect to server one on server two

mysql -h 192.168.14.37 -u repl -p

Copy after login

View mysql:

First:vi /etc/my.cnf
On server 1, add the following content:

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
server_id = 1
log-bin
character-set-server=utf8
#表名不区分大小写
lower_case_table_names=1
#server_id = 1 # uniquely identify  从为2


show master  status

Copy after login

You can get the MASTER_LOG_FILE and MASTER_LOG_POS information of server one and server two.

假设服务器一为   " localhost-bin.000004" 和 "120"


   服务器二为   " localhost-bin.000005" 和 "667"

Copy after login

Execute on server one:

stop slave;

CHANGE MASTER TO MASTER_HOST = '192.168.14.38', MASTER_USER = 'repl', MASTER_PASSWORD = '135246', MASTER_LOG_FILE = 'localhost-bin.000004', MASTER_LOG_POS = 120; 

start slave;

Copy after login

On the server Execute on the second server:

stop slave;

CHANGE MASTER TO MASTER_HOST = '192.168.14.37', MASTER_USER = 'repl', MASTER_PASSWORD = '135246', MASTER_LOG_FILE = 'localhost-bin.000005', MASTER_LOG_POS = 667; 

start slave;

Copy after login

Finally verify whether the main-master synchronization is successful:

Add the table example

mysql:> create database example1 ;

use example1;

create table example1 (length int);

Copy after login

on server one mysql and finally check whether there is this database on server two. This table, and this data.

View the synchronization status:show slave status \G

If an error occurs, you can see the error log.

Errors causing synchronization failure refer to mysql slave-skip-errors=all for in-depth understanding

Note: The two servers will not be synchronized before setting the double master;

Recommended Study: "mysql video tutorial"

The above is the detailed content of Detailed explanation of the configuration method of mysql double master. For more information, please follow other related articles on the PHP Chinese website!