MySQL 主从同步

主从同步原理

• Mysql 服务器之间的主从同步是基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致。
• Slave 会执行以下两个线程读取和执行该日志文件。
  • Slave_IO：复制 master 主机 binlog 日志文件里的 SQL 命令到本机的 relay-log 文件里。
  • Slave_SQL：执行本机 relay-log 文件里的 SQL 语句，实现与 Master 数据一致。
• 在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接收到该日志的一个副本，从服务器可以指定执行该日志中的哪一类事件（例如只插入数据或者只更新数据），默认会执行日志中的所有语句。
• 每一个从服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以分别执行同一个二进制日志的不同部分，并且从服务器可以随时连接或者中断和服务器的连接。
• 主服务器和每一个从服务器都必须配置一个唯一的 ID 号（在 my.cnf 文件的[mysqld]模块下有一个 server-id 配置项），另外，每一个从服务器还需要通过 CHANGE MASTER TO 语句来配置它要连接的主服务器的 ip 地址，日志文件名称和该日志里面的位置（这些信息存储在主服务器的数据库里）

配置主从同步

1. 在主服务器上，必须开启二进制日志机制和配置一个独立的 ID
2. 在每一个从服务器上，配置一个唯一的 ID，创建一个用来专门复制主服务器数据的账号。
3. 在开始复制进程前，在主服务器上记录二进制文件的位置信息。
4. 如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用 mysqldump 导出数据库，或者直接复制数据文件）
5. 配置从服务器要连接的主服务器的 IP 地址和登陆授权，二进制日志文件名和位置。

配置主服务器

1. 更改配置文件：主服务器上的 my.cnf 文件。

[mysqld]
log-bin=/var/log/mysql/mysql-bin.log
server-id=1
bind-address = 192.168.33.22 #your master ip

2. 创建用户，每一个从服务器都需要用到一个账户名和密码来连接主服务器，可以为每一个从服务器都创建一个账户，也可以让全部服务器使用同一个账户，下面就为同一个 ip 网段的所有从服务器创建一个只能进行主从同步的账户。

##创建slave1用户，并指定该用户只能在网段192.168.33.33上登录
mysql> CREATE USER 'slave1'@'192.168.33.33' IDENTIFIED BY 'slavepass';
Query OK, 0 rows affected (0.00 sec)

##为slave1赋予REPLICATION SLAVE权限
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.33.33';
Query OK, 0 rows affected (0.00 sec)

3. 为 MYSQL 加读锁为了主库与从库的数据保持一致，先为 MySQL 加入读锁，使其变为只读。

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

4. 记录下来 MASTER REPLICATION LOG 的位置该信息稍后会用到。

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File    | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |  613 |    |     |     |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

• File 的值是当前使用的二进制日志的文件名。
• Position 是该日志里面的位置信息。

5. 将 master DB 中现有的数据信息导出。

$ mysqldump -u root -p --all-databases --master-data > dbdump.sql

6. 解除 master DB 的读锁。

mysql> UNLOCK TABLES;

7. dbdump. sql 文件 copy 到 slave

scp dbdump.sql ubuntu@192.168.33.33:/home/ubuntu

配置从服务器

1. 修改配置文件：从服务器上的 my.cnf 文件。

server-id = 2 #master-slave结构中，唯一的server-id
bind-address = 192.168.33.33 #your slave ip

2. 重启 mysql，以使配置文件生效。

$ systemctl restart mysql

3. 导入从 master DB，导出的 dbdump. sql 文件，以使 master-slave 数据一致。

$ mysql -u root -p < /home/ubuntu/dbdump.sql

4. 使 slave 与 master 建立连接，从而同步。

mysql> STOP SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO
 -> MASTER_HOST='192.168.33.22',
 -> MASTER_USER='slave1',
 -> MASTER_PASSWORD='slavepass',
 -> MASTER_LOG_FILE='mysql-bin.000001',
 -> MASTER_LOG_POS=613;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

• MASTER_LOG_FILE='mysql-bin.000001' 与 MASTER_LOG_POS=613 的值，是从上面的 SHOW MASTER STATUS 得到的。

• 启动主从同步进程。

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

• 检查状态。

mysql > SHOW SLAVE STATUS \G

• 上面的两个进程都显示 YES 则表示配置成功。