窥探MySQL集群之主从复制

程序员文章站 2024-03-20 21:05:46

...

1 MySQL之主从复制

1.1 MySQL主从复制

1.1.1 MySQL主从复制架构及原理

性能扩展
- 服务性能扩展方式： Scale Up ，Scale Out
MySQL的扩展
- 读写分离
- 复制：每个节点都有相同的数据集，向外扩展，基于二进制日志的单向复制
- 复制的功用
  - 数据分布
  - 负载均衡读
  - 备份
  - 高可用和故障切换
  - MySQL升级测试
复制架构
- 一主一从复制架构
- 一主一从复制架构
主从复制原理

1.1.2 实现MySQL主从复制配置

参考官网
- https://mariadb.com/kb/en/library/setting-up-replication/
- https://dev.mysql.com/doc/refman/5.5/en/replication-configuration.html

1.2.2.1 主节点配置

系统选用的CentOS8

(1) 启用二进制日志

vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
server_id=1 # 为当前节点设置一个全局惟一的ID号
log_bin=/data/mysql/logbin/mysql-bin #启用二进制日志，及其日志存放路径（根据个人来选择二进制日志存放位置）
binlog_format=row
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
# 保存退出
systemctl start mariadb

（2）创建有复制权限的用户账号
语法：

GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'HOST' IDENTIFIED BY 'replpass';
#范例：
# MariaDB [(none)]> grant replication slave on *.* to aaa@qq.com'192.168.66.%' identified by 'magedu';
Query OK, 0 rows affected (0.001 sec)
# MariaDB [(none)]> select host,user,password from mysql.user;
+--------------+----------+-------------------------------------------+
| host         | user     | password                                  |
+--------------+----------+-------------------------------------------+
| localhost    | root     |                                           |
| centos8      | root     |                                           |
| 127.0.0.1    | root     |                                           |
| ::1          | root     |                                           |
| 192.168.66.% | repluser | *6B8CCC83799A26CD19D7AD9AEEADBCD30D8A8664 |
+--------------+----------+-------------------------------------------+
# MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       529 |
+------------------+-----------+

1.2.2.2 从节点配置

系统选用的CentOS7

(1) 启用中继日志

# vim /etc/my.cnf.d/server.cnf
[mysqld]
server_id=# #为当前节点设置一个全局惟的ID号
read_only=ON #设置数据库只读，针对supper user无效
relay_log=relay-log #relay log的文件路径，默认值hostname-relay-bin
relay_log_index=relay-log.index #默认值hostname-relay-bin.index
# systemctl restart mariadb.service

+（2）使用有复制权限的用户账号连接至主服务器，并启动复制线程
语法：

CHANGE MASTER TO MASTER_HOST='host',
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mariadb-bin.xxxxxx',
MASTER_LOG_POS=#;
#范例：    #范例内容与主节点相对应
change master to master_host='192.168.66.8',
master_user='repluser',
master_password='magedu',
master_port=3306,
master_log_file='mysql-bin.000001',
master_log_pos=529;

start slave;
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

show slave status;
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.66.8
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 529
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 540
        Relay_Master_Log_File: mysql-bin.000001
             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: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 529
              Relay_Log_Space: 836
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0 #复制的延迟时间
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
1 row in set (0.00 sec)

1.1.3 实验：主服务器非新建时，实现主从

如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点
- 通过备份恢复数据至从服务器
- 复制起始位置为备份时，二进制日志文件及其POS
范例：
- 主服务器运行一段时间后，新增从节点服务器

#在主服务器完全备份
[aaa@qq.com ~]#mysqldump -A -F --single-transaction --master-data=1 >
/backup/fullbackup_`date +%F_%T`.sql
[aaa@qq.com ~]#ll /backup/
total 2988
-rw-r--r-- 1 root root 3055918 Nov 27 17:41 fullbackup_2019-11-27_17:41:17.sql
[aaa@qq.com ~]#scp /data/backup/fullbackup_2019-11-28_17\:37\:31.sql  192.168.66.18:/data
#将完全备份还原到新的从节点
[aaa@qq.com ~]#dnf -y install mariadb-server
[aaa@qq.com ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only
[aaa@qq.com ~]#systemctl restart mariadb

#配置从节点，从完全备份的位置之后开始复制
[aaa@qq.com ~]#grep -i '^change master' /data/fullbackup_2019-11-28_17\:37\:31.sql 
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;

[aaa@qq.com ~]#vim /data/fullbackup_2019-11-28_17\:37\:31.sql

# CHANGE MASTER TO
  MASTER_HOST='192.168.66.8',
  MASTER_USER='repluser',
  MASTER_PASSWORD='magedu',
  MASTER_PORT=3306,
# MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389; 
**注意：**
	上述注释的两行在备份文件中是存在的，所以将change master to 语句中的其他部分补上
[aaa@qq.com ~]#mysql < /data/fullbackup_2019-11-28_17\:37\:31.sql
**注意**
	 经自己实验CentOS8与CentOS7可能存在差异，当导入完全备份时报错，不可导入。（有可能是我人品差）
[aaa@qq.com ~]#mysql
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.66.8
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000003
           Read_Master_Log_Pos: 389
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 557
         Relay_Master_Log_File: mariadb-bin.000003
              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: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 389
               Relay_Log_Space: 868
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 0
                Last_SQL_Error: 
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 8
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.001 sec)

1.1.4 主从复制相关

1. 限制从服务器只读

read_only=on
#注意：此限制对拥有super权限的用户均无效

注意：以下命令会阻止所有用户，包括主服务器复制的更新

FLUSH TABLES WITH READ LOCK;

2.在从节点清除信息

注意：以下都需要先 STOP SLAVE

RESET SLAVE #从服务器清除master.info ，relay-log.info, relay log ，开始新的relay log
RESET SLAVE ALL #清除所有从服务器上设置的主服务器同步信息，如PORT, HOST, USER和PASSWORD 等

3.复制错误解决方法

可以在从服务器忽略几个主服务器的复制事件，此为global变量

sql_slave_skip_counter = N #global
[mysqld]
slave_skip_errors=1007|ALL # 静态，需写进配置文件

范例：
1.构建错误，先在从服务器创建一个错误：

MariaDB [(none)]> create database db4;
Query OK, 1 row affected (0.001 sec)

2.再在主服务器上构建

MariaDB [(none)]> create database db4;
Query OK, 1 row affected (0.001 sec)

3.从服务器上查看slave状态

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.66.8
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mariadb-bin.000003
           Read_Master_Log_Pos: 643
                Relay_Log_File: mariadb-relay-bin.000002
                 Relay_Log_Pos: 684
         Relay_Master_Log_File: mariadb-bin.000003
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table: 
        Replicate_Ignore_Table: 
       Replicate_Wild_Do_Table: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 1007 #错误代码
                    Last_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4' # 错误信息
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 516
               Relay_Log_Space: 1122
               Until_Condition: None
                Until_Log_File: 
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File: 
            Master_SSL_CA_Path: 
               Master_SSL_Cert: 
             Master_SSL_Cipher: 
                Master_SSL_Key: 
         Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error: 
                Last_SQL_Errno: 1007 #错误代码
                Last_SQL_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4' #错误信息
   Replicate_Ignore_Server_Ids: 
              Master_Server_Id: 8
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: 
              Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

ERROR: No query specified

4.解决方法

#方法1
MariaDB [(none)]> stop slave;  需停掉该线程
MariaDB [(none)]> set global sql_slave_skip_counter=1;
MariaDB [(none)]> start slave;
#方法2
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
slave_skip_errors=1007|ALL
[root@slave1 ~]#systemctl restart mariadb

方法1实例如下

# 从服务操作
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
#主服务操作
MariaDB [(none)]> create database db5;
Query OK, 1 row affected (0.001 sec)
#从操作
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| db3                |
| db4                |
| db5                |
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
9 rows in set (0.001 sec)
**发现实现同步**

1.1.5 保证主从复制的事务安全

参看：https://mariadb.com/kb/en/library/server-system-variables/

1 在master节点启用参数

sync_binlog=1 每次写后立即同步二进制日志到磁盘，性能差
#如果用到的为InnoDB存储引擎：
innodb_flush_log_at_trx_commit=1 #每次事务提交立即同步日志写磁盘
innodb_support_xa=ON #分布式事务MariaDB10.3.0废除
sync_master_info=# #次事件后master.info同步到磁盘

2 在slave节点启用服务器选项

skip-slave-start=ON #不自动启动slave

3 在slave节点启用参数

sync_relay_log=# #次写后同步relay log到磁盘
sync_relay_log_info=# #次事务后同步relay-log.info到磁盘

上一篇：如何自动以管理员身份运行.NET程序？博客分类： MicrosoftVisual Studio

下一篇： MFC中单文档和多文档的工具栏停靠问题

窥探MySQL集群之主从复制

1 MySQL之主从复制

1.1 MySQL主从复制

1.1.1 MySQL主从复制架构及原理

1.1.2 实现MySQL主从复制配置

1.2.2.1 主节点配置

1.2.2.2 从节点配置

1.1.3 实验：主服务器非新建时，实现主从

1.1.4 主从复制相关

1.1.5 保证主从复制的事务安全

窥探MySQL集群之主从复制

MySQL主从复制之GTID模式详细介绍

MySQL主从复制之GTID模式详细介绍

MySQL的主从复制Replication之MyIsam和InnoDB数据复制发布

MySQL DB 主从复制之SSL

MySQL高可用方案之集群(Cluster)

mysql高可用集群之MHA详细搭建过程

MySQL的主从复制Replication之MyIsam和InnoDB数据复制发布

MySQL之——MS主从复制(读写分离)实现

MySQL之——主从复制的一些参数配置

窥探MySQL集群之主从复制

1 MySQL之主从复制

1.1 MySQL主从复制

1.1.1 MySQL主从复制架构及原理

1.1.2 实现MySQL主从复制配置

1.2.2.1 主节点配置

1.2.2.2 从节点配置

1.1.3 实验 ：主服务器非新建时，实现主从

1.1.4 主从复制相关

1.1.5 保证主从复制的事务安全

窥探MySQL集群之主从复制

MySQL主从复制之GTID模式详细介绍

MySQL主从复制之GTID模式详细介绍

MySQL的主从复制Replication之MyIsam和InnoDB数据复制发布

MySQL DB 主从复制之SSL

MySQL高可用方案之集群(Cluster)

mysql高可用集群之MHA详细搭建过程

MySQL的主从复制Replication之MyIsam和InnoDB数据复制发布

MySQL之——MS主从复制(读写分离)实现

MySQL之——主从复制的一些参数配置

1.1.3 实验：主服务器非新建时，实现主从