MySQL主从复制

最新推荐文章于 2025-05-13 12:26:39 发布

XMJDCH

最新推荐文章于 2025-05-13 12:26:39 发布

阅读量270

点赞数

什么是MySQL主从复制

简单来说就是保证主SQL（Master）和从SQL（Slave）的数据是一致性的，向Master插入数据后，Slave会自动从Master把修改的数据同步过来（有一定的延迟），通过这种方式来保证数据的一致性，就是主从复制

Mysql主从复制

https://www.mysql.com/(mysql官网)

1.pos | gtid(生产中使用的环境)

2.主从复制核心：二进制日志，因此一定要打开二进制日志
3.主从复制默认是异步的(异步：半同步|全同步)
异步：
半同步：数据提同步要等到受到后端返回ack才提交数据
全同步：等待数据完全复制成功之后才结束(速度最慢)
4.master(多线程)———–slave(单线程)
会造成延迟
5.灾备
6.组提交

mysql1:主
mysql2:备

[root@mysql1 ~]# yum install mysql-server -y
[root@mysql1 ~]# vim /etc/my.cnf 
.....
     8  server-id=1     #主服务器的id值 
     9  log-bin=binlog      #启动二进制日至系统
    10  binlog-do-db=test   #二进制需要同步的数据库名，如果需要同步多个库，按照这个参数，再添加，然后修改后面的数据库名字就行 
    11  binlog-ignore-db=mysql  #禁止同步mysql数据库
.....
[root@mysql1 ~]# /etc/init.d/mysqld start
[root@mysql1 ~]# cd /var/lib/mysql/
[root@mysql1 mysql]# ls
binlog.000001  binlog.000003  ibdata1      ib_logfile1  mysql.sock
binlog.000002  binlog.index   ib_logfile0  mysql        test
[root@mysql1 mysql]# cat binlog.index 
./binlog.000001
./binlog.000002
./binlog.000003
[root@mysql1 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 |      106 | test         | mysql            |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#创建复制帐号1，在master的数据库中建立一个备用账户，每个slave使用用户名和密码连接master，进行复制操作的用户会授予replicati on slave权限，用户名的密码都会存储在slave服务器的master.info中。 
mysql> grant replication slave on *.* to repl@'172.25.30.%' identified by 'westos';     
#建立一个账户repl，并且只能允许172.25.64.%这个网段地址来登陆,密码是westos 
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 |      405 | test         | mysql            |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> quit
Bye
[root@mysql1 mysql]#



[root@mysql2 ~]# mysql -h 172.25.30.6 -u repl -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@mysql2 ~]# vim /etc/my.cnf   #修改从配置文件
..... 
     8  server-id=2 #从服务器 ID 号,不要和主 ID 相同,如果设置多个从服务器,每个从服务器必须有一个唯一的 server-id 值,必须与主服务器的以及其它从服务器的不相同。可以认为 server-id 值类似于 IP 地址:这些 ID 值能唯一识别复制服务器群集 
     9  read-only=1
.....
[root@mysql2 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@mysql2 ~]# tail -f /var/log/mysqld.log 
[root@mysql2 ~]# cd /var/lib/mysql/
[root@mysql2 mysql]# ls
ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test
[root@mysql2 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status;
Empty set (0.00 sec)

mysql> change master to master_host='172.25.30.6',master_user='repl',master_password='westos',master_log_file='binlog.000003',master_log_pos=405;
Query OK, 0 rows affected (0.10 sec)    #同步主服务器
.....
参数解释：
    MASTER_HOST : 设置要连接的主服务器的ip地址
　　　　MASTER_USER : 设置要连接的主服务器的用户名
　　　　MASTER_PASSWORD : 设置要连接的主服务器的密码
　　　　MASTER_LOG_FILE : 设置要连接的主服务器的bin日志的日志名称，即查看master服务
MASTER_LOG_POS : 设置要连接的主服务器的bin日志的记录位置，即show master status中的postion，（这里注意，最后一项不需要加引号。否则配置失败）
....
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;     
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.30.6
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 405
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 248
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes  #IO和SQL必须是yes，说明开启了这两个进程
            Slave_SQL_Running: Yes
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
从库生成两个线程，一个I/O线程，一个SQL线程；

i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；
主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；

SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
.....
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> quit
Bye
[root@mysql2 mysql]# 




&&GTID的工作过程

先保持数据一致性之后，再主从

[root@mysql1 ~]# ls
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
[root@mysql1 ~]# cd /var/lib/mysql/
[root@mysql1 mysql]# ls
binlog.000001  binlog.000003  ibdata1      ib_logfile1  mysql.sock
binlog.000002  binlog.index   ib_logfile0  mysql        test
[root@mysql1 mysql]# rm -fr *
[root@mysql1 mysql]# rpm -qa | grep mysql
mysql-5.1.71-1.el6.x86_64
mysql-server-5.1.71-1.el6.x86_64
mysql-libs-5.1.71-1.el6.x86_64
[root@mysql1 mysql]# yum remove mysql mysql-server
[root@mysql1 ~]# ls
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
[root@mysql1 ~]# yum install * -y
[root@mysql1 ~]# vim /etc/my.cnf
.....
server_id=1
log-bin=binlog
gtid_mode=ON        #打开gtid模式
enforce-gtid-consistency=true
binlog-do-db=test
binlog-ignore-db=mysql
.....
[root@mysql1 ~]# /etc/init.d/mysqld start
[root@mysql1 ~]# mysql_secure_installation 
[root@mysql1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to repl@'172.25.30.%' identified by 'Xiamin0099+';    #主授权
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show master status;      #完成授权之后，Executed_Gtid_Set里面才有参数
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000006 |      447 | test         | mysql            | 3456e9d3-b157-11e7-a3eb-525400931c42:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> 




##在从上同步，初始化密码
[root@mysql2 ~]# vim /etc/my.cnf
.....
server_id=2
read-only=1
gtid_mode=ON    #大概gtid工作模式
enforce-gtid-consistency=true
.....
[root@mysql2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status;
Empty set (0.00 sec)

mysql> change master to master_host='172.25.30.6',master_user='repl',master_password='Xiamin0099+',MASTER_AUTO_POSITION=1;  #从同步
Query OK, 0 rows affected, 2 warnings (0.05 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.30.6
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 447
               Relay_Log_File: mysql2-relay-bin.000002
                Relay_Log_Pos: 654
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 .....
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 
[root@mysql2 ~]#

***主创建数据库&&从可看到同步的数据
主：

[root@mysql1 ~]# mysql -p
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> create database test;
Query OK, 1 row affected (0.11 sec)

mysql> use test
Database changed
mysql> create table usertb(
    -> username varchar(15) not null,
    -> password varchar(15) not null);
Query OK, 0 rows affected (1.16 sec)

mysql> insert into usertb values ('user1','123');
Query OK, 1 row affected (0.19 sec)

mysql> insert into usertb values ('user2','456');
Query OK, 1 row affected (0.31 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| usertb         |
+----------------+
1 row in set (0.00 sec)

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@mysql1 ~]# 

从：
[root@mysql2 ~]# mysql -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| usertb         |
+----------------+
1 row in set (0.00 sec)

mysql> select * from usertb;        #表格同步成功
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.00 sec)
[root@mysql2 ~]# 








&&Mysql并行复制：

为什么需要并行复制：
　　一般主从复制，有三个线程参与，都是单线程：Binlog Dump（主） —–>IO Thread （从） —–> SQL Thread（从）。复制出现延迟一般出在两个地方
1）SQL线程忙不过来（可能需要应用数据量较大，可能和从库本身的一些操作有锁和资源的冲突；主库可以并发写，SQL线程不可以；主要原因）
2）网络抖动导致IO线程复制延迟（次要原因）。
MySQL从5.6开始有了SQL Thread多个的概念，可以并发还原数据，即并行复制技术。
　　在MySQL 5.7中，引入了基于组提交的并行复制（Enhanced Multi-threaded Slaves），设置参数slave_parallel_workers>0并且global.slave_parallel_type＝‘LOGICAL_CLOCK’，
即可支持一个schema下，slave_parallel_workers个的worker线程并发执行relay log中主库提交的事务。其核心思想：一个组提交的事务都是可以并行回放（配合binary log group commit）；
slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行。
　　其中，变量slave-parallel-type可以有两个值：DATABASE 默认值，基于库的并行复制方式；LOGICAL_CLOCK：基于组提交的并行复制方式

从
[root@mysql2 ~]# vim /etc/my.cnf
.....
server_id=2
read-only=1
gtid_mode=ON
enforce-gtid-consistency=true
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
.....

在主上：
root@mysql1 ~]# mysql -p
.....
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.13 sec)

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.08 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000005 |     1363 | test         | mysql            | ad92abab-b7df-11e7-acfb-5254003cc83a:1-5 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.01 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> insert into usertb values ('user3','333');
Query OK, 1 row affected (0.28 sec)

mysql> insert into usertb values ('user4','444');
Query OK, 1 row affected (0.23 sec)

mysql> sselect * from usertb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'sselect * from usertb' at line 1
mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
| user3    | 333      |
| user4    | 444      |
+----------+----------+
4 rows in set (0.00 sec)

mysql> ^DBye
[root@mysql1 ~]# 

从上：

[root@mysql2 ~]# mysql -p
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.11 sec)

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.08 sec)

mysql> set global rpl_semi_sync_master_enabled=on;  #-》》打开半同步复制
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set (0.01 sec)
开启半同步复制之后，如果从机的io关闭，主机提交数据之后十秒左右之后得不到响应就会自动转换为异步复制，然后才能重新提交。
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.18 sec)

mysql> show slave status\G;
.....
   SQL_Delay: 0
.....
mysql> change master to master_delay=100; ->>修改延迟时间。当主机提交数据之后，从机需要100s才可以得到。
Query OK, 0 rows affected (0.20 sec)


&&IO线程

&&半同步复制






官方文档：https://dev.mysql.com/doc/refman/5.7/en/group-replication-configuring-instances.html


作业：数据库高可用和数据库读写分离套件




mysql主从复制和并行复制:
http://blog.csdn.net/Mryiyi/article/details/73307317