MySQL主从复制基本要义和总结

本文主要给大家介绍MySQL主从复制基本要义和总结，希望可以给大家补充和更新些知识，如有其它问题需要了解的可以持续在亿速云行业资讯里面关注我的更新文章的。

1.1 MySQL主从复制介绍

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

MySQL数据库支持单向、双向、链式级联、环状等不同业务场景的复制。在复制过程中，一台服务器充当主服务器（Master），接收来自用户的内容更新，而一个或多个其他的服务器充当从服务器（Slave），接收来自主服务器binlog文件的日志内容，解析出SQL重新更新到从服务器，使得主从服务器达到一致。

如果设置了链式级复制，那么，从（slave）服务器本身除了充当从服务器外，也会同时充当其下面从服务器的主服务器。链式级联复制类似A-->B-->C的复制形式。

1.2 MySQL主从复制的企业应用场景

MySQL主从复制集群功能使得MySQL数据库支持大规模高并发读写成为可能，同时有效地保护了物理服务器宕机场景的数据备份。

应用场景一：从服务器作为主服务器的实时数据备份

主从服务器架构的设置，可以大大加强MySQL数据库架构的强壮性。例如：当主服务器出现问题时，我们可以人工或设置自动切换到从服务器继续提供服务，此时从服务器的数据和宕机时的主数据库几乎是一致的。

这类似NFS存储数据通过inotify+rsync同步到备份的NFS服务器，只不过MySQL的复制方案是其自带的工具

利用MySQL的复制功能做数据备份时，在硬件故障、软件故障的场景下，该数据备份是有效的，但对于人为地执行drop、delete等语句删除数据的情况，从库的备份功能就没用了，因为从服务器也会执行删除的语句。

应用场景二：主从服务器实现读写分离，从服务器实现负载均衡

主从服务器架构可通过程序（PHP、java等）或代理软件（mysql-proxy、Amoeba）实现对用户（客户端）的请求读写分离，即让从服务器仅仅处理用户的select查询请求，降低用户查询响应时间及读写同时在主服务器上带来的访问压力。对于更新的数据（例如uodate、insert、delete语句）仍然交给主服务器处理，确保主服务器和从服务器保持实时同步。

应用场景三：把多个从服务器根据业务重要性进行拆分访问

可以把几个不同的从服务器，根据公司的业务进行拆分。例如：有为外部用户提供查询服务的从服务器，有内部DBA用来数据备份的从服务器，还有为公司内部人员提供访问的后台、脚本、日志分析及供开发人员查询使用的从服务器。这样的拆分除了减轻主服务器的压力外，还可以使数据库对外部用户浏览、内部用户业务处理及DBA人员的备份等互不影响。

1.3 MySQL主从复制原理介绍

MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Slave），在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和IO线程）在Slave端，另外一个线程（I/O线程）在Master端。

要实现MySQL的主从复制，首先必须要打开Master端的Binlog记录功能，否则就无法实现。因为整个复制过程实际上就是Slave从Master端获取Binlog日志，然后在Slave上以相同顺序执行获取的Binlog日志中所记录的各种SQL操作。

下面针对MySQL主从复制原理的重点进行小结。

◆   主从复制是异步的逻辑的SQL语句级的复制。

◆   复制时，主库有一个I/O线程，从库有两个线程，I/O和SQL线程。

◆   实现主从复制的必要条件是主库要开启记录binlog功能。

◆   作为复制的所有MySQL节点的server-id都不能相同。

◆   binlog文件只记录对数据有更改的SQL语句（来自主数据库内容的变更），不记录任何查询（select，show）语句。

忘了数据库密码

mysqld_safe--defaults-file=/data/3306/my.cnf --skip-grant-table --user=mysql &

然后不用输入密码进行登录

mysql-uroot -p -S /data/3306/mysql.sock

进入数据库后设置密码

updatemysql.user set password=password('oldboy123') where user='root' andhost='localhost';

刷新权限

flushprivileges; 

1.4 MySQL主从复制操作步骤

架构实践:

3306---->3307

主--->从

1.开启主库binlog,配置server-id

[root@db02~]# egrep -i "server-id|log-bin" /data/3306/my.cnf

log-bin= /data/3306/mysql-bin

server-id= 6

重启服务

/data/3306/mysqlrestart

从库

[root@db02~]# egrep -i "server-id|log-bin" /data/3307/my.cnf

#log-bin= /data/3307/mysql-bin

server-id= 7

2.主库创建用户

grantreplication slave on *.* to 'rep'@'172.16.1.%' identified by 'oldboy123';

mysql>grant replication slave on *.* to 'rep'@'172.16.1.%' identified by 'oldboy123';

Query OK, 0rows affected (0.04 sec)

mysql>select user,host from mysql.user;

+------+------------+

| user |host       |

+------+------------+

| root |127.0.0.1  |

| rep  | 172.16.1.% |

3.从主库导出数据

按照我们讲过的内容,直接取今天00点的备份就可以.

先锁表flush table with read lock;

mysql>flush table with read lock;

Query OK, 0rows affected (0.00 sec)

mysql> showmaster status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

|mysql-bin.000001 |      120 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set(0.00 sec)

mysqldump

cp/tar

xtrabackup

拿到位置点是关键[A1]  sed -n '22p'all_2017-06-28.sql

主库全备

[root@db02 ~]# mysqldump -B --master-data=2 --single-transaction-S /data/3306/mysql.sock  -A|gzip>/data/backup/all_$(date+%F).sql.gz

[root@db02 ~]#ls -l /data/backup/

总用量 228

-rw-r--r-- 1root  root  178468 6月  28 11:11 all_2017-06-28.sql.gz

主库解锁:

mysql> unlock table;

Query OK, 0rows affected (0.00 sec)

4.从库导入全备的数据

[root@db02scripts]# cd /data/backup/

[root@db02backup]# gzip -d all_2017-06-28.sql.gz

[root@db02backup]# mysql -S /data/3307/mysql.sock<all_2017-06-28.sql

5.找位置点,然后change master从库

[root@db02backup]# sed -n '22p' all_2017-06-28.sql

-- CHANGEMASTER TO MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=120;

change master从库

CHANGE MASTER TO 

MASTER_HOST='172.16.1.52',

MASTER_PORT=3306,

MASTER_USER='rep',

MASTER_PASSWORD='oldboy123',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=120;

打开slave

mysql> start slave;

Query OK, 0rows affected (0.03 sec)

mysql> show slave status\G

显示如下就说明成功

[root@db02backup]# mysql -S /data/3307/mysql.sock -e"show slave status\G"|egrep "_Running|Behind_Master"|head-3

             Slave_IO_Running:Yes

             Slave_SQL_Running:Yes

             Seconds_Behind_Master:0

3306查看管理的主机

mysql> show slave hosts;

+-----------+------+------+-----------+--------------------------------------+

| Server_id |Host | Port | Master_id | Slave_UUID                           |

+-----------+------+------+-----------+--------------------------------------+

|         7 |      | 3307 |         6 |295750c8-54c1-11e7-80dd-000c29fc02ee |

|         8 |      | 3308 |         6 |328e8c80-54c1-11e7-80dd-000c29fc02ee |

+-----------+------+------+-----------+--------------------------------------+

2 rows in set(0.00 sec)

看了以上关于MySQL主从复制基本要义和总结，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。