MySQL 主从服务器同步心得体会

MySQL 主从复制是数据库中常用的数据备份和分析的方法，主数据库将数据同步到从数据库，并且可以方便地进行分析和处理。为了保证主从同步正常工作，需要考虑以下几个方面：

1.配置主机和从机

1.1 主机配置

在主机上需要配置以下几个参数：

# 开启二进制日志，记录修改的数据
log-bin=mysql-bin
# 设置服务器ID，ID不要与从机重复
server-id=1

1.2 从机配置

在从机上需要配置以下几个参数：

# 从库复制账号，需要有REPLICATION SLAVE权限
replicate-user=slave_user:password
# 指定从机ID，ID不要与主机或其他从机重复
server-id=2
# 从哪个日志文件开始复制
log-slave-updates=true
# 指定从机的同步方式是使用主库或从库
# 主要适用于从库当主库的脱离和场景转移
# 值为ON和OFF
log_bin=mysql-bin
binlog-do-db=test
binlog-ignore-db=mysql    

2. 同步数据

2.1 主机执行

在主机上执行以下命令创建一个与主从机同步的用户：

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

2.2 从机开启同步

在从机上执行以下命令开启数据同步：

CHANGE MASTER TO
MASTER_HOST='<master-host>',
MASTER_PORT=<master-port>,
MASTER_USER='<slave-user>',
MASTER_PASSWORD='<password>',
MASTER_LOG_FILE='<master-bin-log-file>',
MASTER_LOG_POS=<master-bin-log-pos>;

其中主机名、用户名、密码、二进制日志文件、同步位置要替换成实际的值。

3. 监控同步状态

3.1 查看主从状态

在从机上执行以下命令查看主从状态：

SHOW SLAVE STATUS\G

该命令将输出从机的相关信息，包括数据库版本、运行状态等。

3.2 查看同步日志

在主从机上可以通过二进制日志来查看同步日志：

mysqlbinlog --base64-output=DECODE-ROWS mysql-bin.000001

其中mysql-bin.000001是二进制日志文件名。

4. 实例说明

4.1 示例1：新建主从服务器

在主机上进行以下配置：

log-bin=mysql-bin
server-id=1

在从机上进行以下配置：

replicate-user=slave_user:password
server-id=2
log-slave-updates=true

执行以下SQL脚本，创建一个测试数据库和一个测试表：

CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE IF NOT EXISTS user (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(255),
    password VARCHAR(255),
    PRIMARY KEY (id)
);

将主机的数据同步到从机上：

CHANGE MASTER TO
MASTER_HOST='master-host',
MASTER_PORT=3306,
MASTER_USER='slave_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

在从机上执行以下命令查看主从状态：

SHOW SLAVE STATUS\G

如果输出了正确的结果，就说明主从同步成功。

4.2 示例2：变更从库同步数据的方式

假设我们有两台机器，一台主机ms1，一台从机rs1，现在需要将从机rs1转化为主机ms2，并且需要让rs1的数据同步到ms2上。

首先，在rs1上进行以下配置：

server-id=2
log-slave-updates=true
log_bin=mysql-bin
binlog-do-db=test

然后在rs1上执行以下脚本：

USE test;
INSERT INTO user(username, password) VALUES('user1', '123456');
INSERT INTO user(username, password) VALUES('user2', '123456');
INSERT INTO user(username, password) VALUES('user3', '123456');

然后在ms2上进行以下配置：

server-id=3

将rs1的数据同步到ms2上：

CHANGE MASTER TO
MASTER_HOST='rs1',
MASTER_PORT=3306,
MASTER_USER='slave_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

在ms2上执行以下命令查看同步日志：

mysqlbinlog --base64-output=DECODE-ROWS master-bin.000001 > /tmp/sql

在/tmp/sql文件中可以看到同步的SQL语句：

USE test;
INSERT INTO user(username, password) VALUES('user1', '123456');
INSERT INTO user(username, password) VALUES('user2', '123456');
INSERT INTO user(username, password) VALUES('user3', '123456');

如果输出了正确的结果，就说明从库转化为主库成功，同时数据也成功同步到了ms2上。