MySQL主从同步

主从同步原理

Master

• 启用binlog日志

Slave：

• Slave_IO：复制master主机 binlog日志文件里的SQL命令到本机的relay-log文件里。
• Slave_SQL：执行本机relay-log文件里的SQL语句，实现与Master数据一致。

构建主从同步

master(master:192.168.1.11)-->slave(slave:192.168.1.12)

主从同步实现步骤：

主服务器

• 启用binlog日志
• 授权用户
• 查看binlog日志信息

从服务器

• 设置server_id
• 确保与主服务器数据一致
• 指定主库信息
• 启动slave程序
• 查看状态信息

准备主服务器数据

[root@mysql1 ~]# mysql -uroot -p123456 < mysql_scripts/mydb_data.sql 

主服务器配置

# 修改配置文件
[root@mysql1 ~]# vim /etc/my.cnf
[mysqld]
server_id = 11
log-bin = master11
... ...

# 启动服务
[root@mysql1 ~]# systemctl restart mysqld

# 验证配置
[root@mysql1 ~]# ls /var/lib/mysql/master11.*
/var/lib/mysql/master11.000001  /var/lib/mysql/master11.index
[root@mysql1 ~]# mysql -uroot -p123456
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master11.000001 |      154 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 如果日志不是master11.000001，希望回到最初状态，可以执行以下命令：

mysql> reset master;

# 授权辅助服务器可以同步数据
mysql> grant replication slave on *.* to repluser@'%' identified by '123456';
mysql> show grants for repluser@'%';
+--------------------------------------------------+
| Grants for repluser@%                            |
+--------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'%' |
+--------------------------------------------------+
1 row in set (0.00 sec)

配置从服务器

• 修改配置文件

[root@mysql2 ~]# vim /etc/my.cnf
[mysqld]
server_id = 12
... ...

[root@mysql2 ~]# systemctl restart mysqld

• 如果主服务器上已有一段时间的数据，需要将主服务器数据完全同步到从服务器。

# 主服务器备份数据
[root@mysql2 ~]# mysqldump -uroot -p123456 --master-data mydb > /root/fullbackup.sql

[root@mysql2 ~]# scp /root/fullbackup.sql 192.168.1.12:/root/

# 从服务器恢复数据
mysql> create database mydb default charset utf8mb4;
[root@mysql2 ~]# mysql -uroot -p123456 mydb < /root/fullbackup.sql

--master-data`的作用：如果完全备份完成后，又有新的数据产生，它可记录备份时的数据状态信息。

• • 查看binlog日志名和偏移量

# 使用备份的方式同步主服务器数据，在备份文件中查找
[root@mysql2 ~]# grep master11 fullbackup.sql
CHANGE MASTER TO MASTER_LOG_FILE='master11.000001', MASTER_LOG_POS=174149;

# 没有进行数据同步的，查看主服务器日志状态
[root@mysql1 ~]# mysql -uroot -p123456
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master11.000001 |   174149 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• 在从服务器上配置的主库信息

[root@node30 ~]# mysql -uroot -p123456
mysql> change master to
    -> master_host="192.168.1.11",
    -> master_user="repluser",
    -> master_password=123456,
    -> master_log_file="master11.000001",
    -> master_log_pos=174149;
Query OK, 0 rows affected, 2 warnings (0.10 sec)

-- 启动slave程序

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

• 验证：查看状态信息

mysql> show slave status\G
... ...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
... ...