mysql学习笔记--主从复制

主从复制简介

首先需要两台机器，并且主从复制的前提是需要先做数据同步，先在我的master机器上用mysqldump 将所有的数据备份，然后scp传输到我的slave机器上，然后在slave机器上再将数据导入实现数据同步

然后master机器必须要开启二进制日志，主从服务器配置不同的server_id ,从服务器需要开启slave服务，保证我的I/O thread 和SQL thread 都是开启的，当我的主服务器上的数据改变时，首先会记录到二进制日志，主上的log dump 会向我的从服务器的io thread 发送消息，让它来拿我更新的二进制日志，然后slave的io线程会连接到master ，来读取master的二进制日志，写入到slave的中继日志relaylog中，然后Slave中的SQL线程读取中继日志relaylog进行SQL 的回放（replay）操作，完成主从复制，从而达到主从服务器上的数据一致性

实操

环境准备：两台机器（master slave），编译安装mysql，且最好mysql版本一致

异步复制：
同步复制：
半同步复制：

主从复制的前提：先做主从复制的数据同步

二进制日志的位置号：主的二进制位置号肯定比从的二进制号大，所以就会存在一个问题：主并不知道从哪个号码开始推送

I/O thread
SQL thread

两台机器，master --二进制

实操

在master主机上要进行的操作

1，开启mysql的二进制日志

[root@master backup]# cat /etc/my.cnf
[mysqld_safe]

[client]
socket=/data/mysql/mysql.sock

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
character-set-server=utf8
#skip-grant-tables
#binary log
server_id=1
log_bin 


[mysql]
auto-rehash
prompt=\\u@\\d \\R:\\m  mysql>

#查看二进制日志是否开启
root@(none) 09:18  mysql>show variables like "%log_bin%";
+---------------------------------+----------------------------------------+
| Variable_name                   | Value                                  |
+---------------------------------+----------------------------------------+
| log_bin                         | ON                                     |
| log_bin_basename                | /usr/local/mysql/data/master-bin       |
| log_bin_index                   | /usr/local/mysql/data/master-bin.index |
| log_bin_trust_function_creators | OFF                                    |
| log_bin_use_v1_row_events       | OFF                                    |
| sql_log_bin                     | ON                                     |
+---------------------------------+----------------------------------------+
6 rows in set (0.01 sec)

2，建立一个mysql新用户并授予复制权限

root@(none) 08:38  mysql>grant  replication slave on *.* to 'xionghan'@'192.168.0.173' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

root@(none) 08:38  mysql>show slave status\G
Empty set (0.00 sec)

3.备份所有的库

[root@master ~]# mysqldump -uroot -p'Sanchuang1234#' --all-databases >/backup/2020-10-23-all-db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

4，上传数据到从服务器

[root@master ~]# cd /backup/
[root@master backup]# ls
2020-10-22-all-db.sql  2020-10-23-all-db.sql
[root@master backup]# scp 2020-10-23-all-db.sql  root@192.168.0.173:/root
root@192.168.0.173's password: 
2020-10-23-all-db.sql                100%  834KB  24.3MB/s   00:00    

5.查看当前的二进制日志的文件名和位置号

root@(none) 08:54  mysql>show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      453 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在从服务器上要做的操作

1.配置server_id（必须的）也开启二进制日志（二进制日志不是必须的）

[root@slave ~]# vim /etc/my.cnf

[root@slave ~]# cat /etc/my.cnf
[mysqld_safe]

[client]
socket=/data/mysql/mysql.sock

[mysqld]
socket=/data/mysql/mysql.sock
port = 3306
open_files_limit = 8192
innodb_buffer_pool_size = 512M
server_id=2
log_bin

[mysql]
auto-rehash
prompt=\\u@\\d \\R:\\m  mysql>

[root@slave ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@slave ~]# ps aux|grep mysqld
root      46275  0.1  0.0  11816  1592 pts/1    S    08:56   0:00 /bin/sh /usr/local/sc_mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/slave.pid
mysql     46443  1.5  5.2 2026404 203020 pts/1  Sl   08:56   0:00 /usr/local/sc_mysql/bin/mysqld --basedir=/usr/local/sc_mysql --datadir=/data/mysql --plugin-dir=/usr/local/sc_mysql/lib/plugin --user=mysql --log-error=slave.err --open-files-limit=8192 --pid-file=/data/mysql/slave.pid --socket=/data/mysql/mysql.sock --port=3306
root      46475  0.0  0.0 112824   980 pts/1    S+   08:56   0:00 grep --color=auto mysqld

#查看二进制日志是否开启
root@(none) 20:33  mysql>show variables like "%log_bin%";
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /data/mysql/slave-bin       |
| log_bin_index                   | /data/mysql/slave-bin.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.24 sec)

2，导入master上的数据

[root@slave ~]# mysql -uroot -p'Sanchuang1234#' < 2020-10-23-all-db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

3.在slave上配置master的信息和授权的用户名和密码

root@(none) 08:59  mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.171',
    -> MASTER_USER='xionghan',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='master-bin.000002',
    -> MASTER_LOG_POS=453;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

5.查看slave的状态

root@(none) 09:00  mysql>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.0.171
                  Master_User: xionghan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 453
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 


#启动slave功能
root@(none) 09:00  mysql>start slave;
Query OK, 0 rows affected (0.00 sec)

#再次查看

```bash
root@(none) 09:01  mysql>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.171
                  Master_User: xionghan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 453
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 

7.验证主从复制的功能
1.在主上插入新的数据
2.在从上看是否有数据

在主服务器上的操作

#新建库

root@(none) 09:03  mysql>create database xh1;
Query OK, 1 row affected (0.01 sec)

root@(none) 09:03  mysql>use xh1;
Database changed
#新建表
root@xh1 09:03  mysql>create table t1(id int);
Query OK, 0 rows affected (0.03 sec)

root@xh1 09:04  mysql>show tables;
+---------------+
| Tables_in_xh1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)
#插入数据
root@xh1 09:04  mysql>insert into t1(id) values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

在从服务器上查看数据是否一致

root@(none) 09:01  mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aliwangwang        |
| chenran            |
| mysql              |
| performance_schema |
| sanchuang          |
| sc                 |
| sys                |
| wangtc             |
| xh1                |
| xionghan           |
| zwx                |
+--------------------+
12 rows in set (0.00 sec)

root@(none) 09:04  mysql>use xh1;
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
root@xh1 09:04  mysql>show tables;
+---------------+
| Tables_in_xh1 |
+---------------+
| t1            |
+---------------+
1 row in set (0.00 sec)

root@xh1 09:09  mysql>select * from t1;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

扩展

上述是一主一从，可不可以一主多从？答案是可以的
一主多从的优点：
1，互相备份
2，流量分流，读写分离，写只往主服务器上写，读主从服务器都可以读，达到一个冗余和负载均衡的效果

异步主从复制的缺点：数据有延迟，如何解决？ —引出半同步

日志首先在内存产生—》再写到磁盘 —二进制日志的优点：方便数据恢复

如果slave迟迟没有回复，会自动切换为异步模式

半同步复制的优点：减小数据延迟，master在写数据时会先向从服务器发送消息，主从服务器一起写数据

日志文件 表文件

半同步实操

1，在master上的配置

root@(none) 19:39  mysql>install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.05 sec)

#查看有哪些插件
root@(none) 19:41  mysql>show plugins;
+----------------------------+----------+--------------------+--------------------+---------+
| Name                       | Status   | Type               | Library            | License |
+----------------------------+----------+--------------------+--------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL               | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLES          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESTATS      | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_INDEXES         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_COLUMNS         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FIELDS          | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_TABLESPACES     | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_DATAFILES       | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| INNODB_SYS_VIRTUAL         | ACTIVE   | INFORMATION SCHEMA | NULL               | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| partition                  | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL               | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL               | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL               | GPL     |
| rpl_semi_sync_master       | ACTIVE   | REPLICATION        | semisync_master.so | GPL     |
+----------------------------+----------+--------------------+--------------------+---------+
45 rows in set (0.00 sec)

开启相应的服务

root@(none) 19:43  mysql>set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)

查看一下

root@(none) 19:44  mysql>show global variables like '%rpl_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)

rpl_semi_sync_master_wait_point | AFTER_SYNC
在master 同步了二进制日志后，

在从服务器上安装插件

root@(none) 19:51  mysql>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.02 sec)

开启相应的服务

root@(none) 19:51  mysql>set global  rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)

查看一下

root@(none) 19:52  mysql>show global variables like '%rpl_semi%'; 
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
2 rows in set (0.00 sec)

半同步配置成功

问题：从服务器是如何知道主服务器更新了哪些数据，从服务器又是怎么知道该从哪里拿数据呢？

[root@slave mysql]# cd /data/mysql
[root@slave mysql]# ls
aliwangwang                relay-log.info
auto.cnf                   sanchuang
ca-key.pem                 sc
ca.pem                     server-cert.pem
chenran                    server-key.pem
client-cert.pem            slave-bin.000001
client-key.pem             slave-bin.000002
ib_buffer_pool             slave-bin.000003
ibdata1                    slave-bin.index
ib_logfile0                slave.err
ib_logfile1                slave.pid
ibtmp1                     slave-relay-bin.000001
localhost.localdomain.err  slave-relay-bin.000002
localhost.localdomain.pid  slave-relay-bin.index
master.info                sys
mysql                      wangtc
mysql.sock                 wangwang
mysql.sock.lock            wangwang1
node2-db.err               xh1
performance_schema         xionghan
private_key.pem            zwx
public_key.pem

[root@slave mysql]# cat master.info
25
master-bin.000002
1370
192.168.0.171
xionghan
123456
3306
60
0





0
30.000

0
3b416199-07a7-11eb-83b5-000c29ab471a
86400


0

[root@slave mysql]# cat relay-log.info 
7
./slave-relay-bin.000002
1238
master-bin.000002
1370
0
0
1