【Web 集群实战】17_MySQL 主从复制

Web集群实战

1. 主从复制原理

• 概述

  • MySQL 数据库的主从复制方案，与使用 scp/rsync 等命令进行的文件级别复制类似，都是数据的远程传输，只不过 MySQL 的主从复制是其自带的功能，无需借助第三方工具，而且，MySQL 的主从复制并不是数据库从磁盘上的文件直接拷贝，而是通过逻辑的 binlog 日志复制到要同步的服务器本地，然后由本地的线程读取日志里面的 SQL 语句，重新应用到 MySQL 数据库中。
  • MySQL 数据库支持单向主从同步模式、双向主主复制、单向线性级联同步、单向环状级联同步等不同业务场景的复制。

• 主从复制原理重点小结

  • 主从复制是异步的逻辑的 SQL 语句级的复制；
  • 复制时，主库有一个 I/O 线程，从库有两个线程，即 I/O 和 SQL 线程；
  • 实现主从复制的必要条件是主库要开启记录 binlog 功能；
  • 作为复制的所有 MySQL 节点的 server-id 都不能相同；
  • binlog 文件只记录对数据库有更改的 SQL 语句（来自主数据库内容的变更），不记录任何查询（如 select、show）语句。

2. 主从复制数据库环境准备

• 以单机数据库多实例的环境为例
• 3306 实例作为主库（master），3307 实例作为从库（slave）

[root@ylt001 ~]# ss -lnt|grep 330
LISTEN     0      50           *:3307                     *:*
LISTEN     0      50           *:3308                     *:*
LISTEN     0      50           *:3306                     *:*

3. 在主库上执行操作配置

1) 设置 server-id 值并开启 binlog 功能参数

[root@ylt001 ~]# cd /data/3306
[root@ylt001 3306]# vi my.cnf
[mysqld]
server-id = 1
log-bin = /data/3306/mysql-bin
[root@ylt001 3306]# egrep "server-id|log-bin" my.cnf
server-id       = 1
log-bin=/data/3306/mysql-bin
[root@ylt001 3306]# /data/3306/mysql restart
Restarting MySQL...
Stopping MySQL...
Starting MySQL...
[root@ylt001 3306]# mysql -uroot -ppassword -S /data/3306/mysql.sock
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> quit
Bye

2）在主库上建立用于主从复制的账号

[root@ylt001 3306]# mysql -uroot -ppassword -S /data/3306/mysql.sock
mysql> grant replication slave on *.* to 'rep'@'192.168.2.%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from mysql.user;
+------+-------------+
| user | host        |
+------+-------------+
| root | 127.0.0.1   |
| rep  | 192.168.2.% |
| root | localhost   |
+------+-------------+
3 rows in set (0.00 sec)
mysql> select user,host from mysql.user where user='rep';
+------+-------------+
| user | host        |
+------+-------------+
| rep  | 192.168.2.% |
+------+-------------+
1 row in set (0.00 sec)
mysql> show grants for rep@'192.168.2.%';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@192.168.2.%                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.2.%' IDENTIFIED BY PASSWORD '*91F687E9EA9F80F26008D0CA416D9C233B14C9E3' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3）实现主数据库锁表只读

mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
...
| interactive_timeout        | 28800    |
...
| wait_timeout               | 28800    |
+----------------------------+----------+
10 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      664 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
[root@ylt001 3306]# mkdir -p /server/backup/
[root@ylt001 3306]# mysqldump -uroot -ppassword -S /data/3306/mysql.sock --events -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
[root@ylt001 3306]# ls -l /server/backup/mysql_bak.$(date +%F).sql.gz
-rw-r--r-- 1 root root 155029 Sep 30 21:03 /server/backup/mysql_bak.2018-09-30.sql.gz
[root@ylt001 3306]# mysql -uroot -ppassword -S /data/3306/mysql.sock -e"show master status"
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      664 |              |                  |
+------------------+----------+--------------+------------------+

• 解锁主库，恢复可写

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

4) 把主库导出的 MySQL 数据迁移到从库

4. 在 MySQL 从库上执行的操作过程

1) 设置 server-id 值并关闭 binlog 功能参数

[root@ylt001 ~]# cd /data/3307
[root@ylt001 3307]# vi my.cnf
[mysqld]
server-id = 3
#log-bin = /data/3307/mysql-bin
[root@ylt001 3307]# egrep "server-id|log-bin" my.cnf
server-id       = 3
#log-bin=/data/3307/mysql-bin
[root@ylt001 3307]# /data/3307/mysql restart
Restarting MySQL...
Stopping MySQL...
Starting MySQL...
[root@ylt001 3307]# mysql -uroot -ppassword -S /data/3307/mysql.sock
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF    |
+---------------+-------+
1 row in set (0.00 sec)
mysql> quit
Bye

2）把从主库 mysqldump 导出的数据恢复到从库

[root@ylt001 3306]# cd /server/backup/
[root@ylt001 backup]# ll
total 152
-rw-r--r-- 1 root root 155029 Sep 30 21:03 mysql_bak.2018-09-30.sql.gz
[root@ylt001 backup]# gzip -d mysql_bak.2018-09-30.sql.gz
[root@ylt001 backup]# ll
total 552
-rw-r--r-- 1 root root 561654 Sep 30 21:03 mysql_bak.2018-09-30.sql
[root@ylt001 backup]# mysql -uroot -ppassword -S /data/3307/mysql.sock <mysql_bak.2018-09-30.sql

3）登录 3307 从库，配置复制参数

[root@ylt001 data]# mysql -uroot -ppassword -S /data/3307/mysql.sock
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.2.188',  MASTER_PORT=3306,  MASTER_USER='rep',  MASTER_PASSWORD='password',  MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=664;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
[root@ylt001 data]# cat master.info
18
mysql-bin.000001
664
192.168.2.188
rep
password
3306
60
0
...

5. 启动从库同步开关，测试主从复制配置情况

• 启动从库复制开关，并查看复制状态

[root@ylt001 data]# mysql -uroot -ppassword -S /data/3307/mysql.sock -e"start slave;"
[root@ylt001 data]# mysql -uroot -ppassword -S /data/3307/mysql.sock -e"show slave status\G;"
[root@ylt001 data]# mysql -uroot -ppassword -S /data/3307/mysql.sock -e"show slave status\G;"|egrep "IO_Running|SQL_Running|_Behind_Master"
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0

• 测试主从复制结果

[root@ylt001 data]# mysql -uroot -ppassword -S /data/3306/mysql.sock -e"create database ylt;"
[root@ylt001 data]# mysql -uroot -ppassword -S /data/3307/mysql.sock -e"show databases like 'ylt';"
+----------------+
| Database (ylt) |
+----------------+
| ylt            |
+----------------+
[root@ylt001 data]# mysql -uroot -ppassword -S /data/3306/mysql.sock -e"drop database ylt;"
[root@ylt001 data]# mysql -uroot -ppassword -S /data/3307/mysql.sock -e"show databases like 'ylt';"