Mysql主从复制配置实例

原创 于 2020-02-19 10:36:27 发布 · 983 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文详细介绍了在Ubuntu环境下，如何配置两台MySQL服务器进行主从复制，包括修改配置文件、创建同步用户、启动及验证从节点。同时，文章还探讨了如何扩展到多台服务器的主主同步，形成环形备份结构。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

一、环境

机器A：192.168.31.152

机器B：192.168.31.140

系统：Ubuntu 16.04

数据库：MySQL 5.7.22

二、Master配置

1、机器A-Master配置

    1.1、修改配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
#服务器的ID,必须唯一，一般设置自己的IP
server-id = 152
#开启二进制日志功能，名字可以随便取，最好有含义（比如项目名）
log_bin = /var/log/mysql/mysql-bin.log
#主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format = mixed
#为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
#二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days = 10
#如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志
max_binlog_size = 100M

Master DB

#需要同步的数据库
binlog_do_db = sync_test
#不需要备份的数据库（MySQL库一般不同步）
binlog_ignore_db = mysql
#主键自增规则，避免主从同步ID重复的问题
#自增因子（每次加2）
auto-increment-increment = 2
#自增偏移（从1开始），单数
auto-increment-offset = 1

Slave DB

#作为从服务器时的中继日志
relay_log = /var/log/mysql/relay-bin.log
#表示 slave 将复制事件写进自己的二进制日志
log_slave_updates = 1
#跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。
slave_skip_errors = all
1.2、配置
#先重启一下服务
shell> service mysql restart
#登录到mysql
shell> mysql -uroot -p123
#创建数据库同步用户，并授予相应的权限
mysql> grant replication slave, replication client on . to ‘sync’@‘192.168.31.140’ identified by ‘123’;
#刷新授权表信息
mysql> flush privileges;
#查看binlog文件的position（偏移）和File（日志文件）的值，从机上需要用到
mysql> show master status;
±---------------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±---------------------±---------±-------------±-----------------±------------------+
| mysql-bin.000003 | 809 | sync_test | mysql | |
±---------------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

2、机器B-Master配置

2.1、修改配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
#服务器的ID,必须唯一，一般设置自己的IP
server-id = 140
#开启二进制日志功能，名字可以随便取，最好有含义（比如项目名）
log_bin = /var/log/mysql/mysql-bin.log
#主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format = mixed
#为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
#二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days = 10
#如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志
max_binlog_size = 100M

Master DB

#需要同步的数据库
binlog_do_db = sync_test
#不需要备份的数据库（MySQL库一般不同步）
binlog_ignore_db = mysql
#主键自增规则，避免主从同步ID重复的问题
#自增因子（每次加2）
auto-increment-increment = 2
#自增偏移（从2开始），双数
auto-increment-offset = 2

Slave DB#

#作为从服务器时的中继日志
relay_log = /var/log/mysql/relay-bin.log
#表示 slave 将复制事件写进自己的二进制日志
log_slave_updates = 1
#跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。
slave_skip_errors = all
2.2、配置
#先重启一下服务
shell> service mysql restart
#登录到mysql
shell> mysql -uroot -p123
#创建数据库同步用户，并授予相应的权限
mysql> grant replication slave, replication client on . to ‘sync’@‘192.168.31.152’ identified by ‘123’;
#刷新授权表信息
mysql> flush privileges;
#查看binlog文件的position（偏移）和File（日志文件）的值，从机上需要用到
mysql> show master status;
±---------------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±---------------------±---------±-------------±-----------------±------------------+
| mysql-bin.000004 | 809 | sync_test | mysql | |
±---------------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

三、Slave配置

1、机器A-Slave配置

#master_user和master_password：在140上执行grant replication slave…创建的用户和密码(sync@192.168.31.152)
#master_log_file和master_log_pos：在140上运行show master status;命令执行结果对应File和Position字段的值
mysql> change master to master_host=‘192.168.31.140’,master_user=‘sync’, master_password=‘123’, master_port=3306, master_log_file=‘mysql-bin.000004’, master_log_pos=809, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.45 sec)
#查看作为从节点的状态信息
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.31.140
Master_User: sync
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 809
Relay_Log_File: edu-mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 809
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
由于此时从节点还没有启动，Slave_IO_State的值为空，Slave_IO_Running和Slave_SQL_Running线程为No表示也没有运行，然后启动Slave：
#启动从节点，开始工作接收主节点发送事件
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
#重新查看slave节点的状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.140
Master_User: sync
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 809
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 809
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 140
Master_UUID: 65bdedc1-24dd-11e7-a0ac-000c295d5365
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
2、机器B-Slave配置
#master_user和master_password：在152上执行grant replication slave…创建的用户和密码(sync@192.168.31.140)
#master_log_file和master_log_pos：在152上运行show master status;命令执行结果对应File和Position字段的值
mysql> change master to master_host=‘192.168.31.152’,master_user=‘sync’, master_password=‘123’, master_port=3306, master_log_file=‘mysql-bin.000003’, master_log_pos=809, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.45 sec)
#查看作为从节点的状态信息
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.31.152
Master_User: sync
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 809
Relay_Log_File: edu-mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 809
Relay_Log_Space: 154
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
由于此时从节点还没有启动，Slave_IO_State的值为空，Slave_IO_Running和Slave_SQL_Running线程为No表示也没有运行，然后启动Slave：
#启动从节点，开始工作接收主节点发送事件
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
#重新查看slave节点的状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.31.152
Master_User: sync
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 809
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 809
Relay_Log_Space: 531
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 152
Master_UUID: 70416044-49c8-11e8-8367-8cec450cd8d
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

四、验证

1、在机器A上登录MySQL，并创建数据库sync_test：

mysql> create database if not exists sync_test default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.05 sec)
创建user表：
mysql> use sync_test;
Database changed
mysql> create table user (id int, name varchar(25));
Query OK, 0 rows affected (0.41 sec)
插入一条数据：
mysql> insert into user(id, name) values(1, ‘menghao’);
Query OK, 1 row affected (0.10 sec)
查询结果：
mysql> select * from user;
±-----±--------+
| id | name |
±-----±--------+
| 1 | menghao |
±-----±--------+
1 row in set (0.00 sec)

2、在机器B上登录MySQL，验证同步结果：

    查询数据库列表：

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| sync_test |
| sys |
| test |
| wordpress |
±-------------------+
8 rows in set (0.03 sec)
查询表列表：
mysql> use sync_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
±--------------------+
| Tables_in_sync_test |
±--------------------+
| user |
±--------------------+
1 row in set (0.01 sec)
查询数据：
mysql> select * from user;
±-----±--------+
| id | name |
±-----±--------+
| 1 | menghao |
±-----±--------+
1 row in set (0.02 sec)

五、多台（大于等于三台）主主同步：

前面说的是2台MySQL服务器，你也可以扩展到多台，实现方法类似 

A -> D -> C-> B ->A 



这样一个环形的备份结构就形成了，最后可要记住自增长ID（主键）要设计好哦，否则会出错的。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值