Mysql主从数据库搭建(一主两从)

1024程序员

已于 2022-11-26 23:16:18 修改

阅读量4k

点赞数 2

分类专栏：数据库专栏文章标签：数据库 mysql 服务器

于 2022-11-26 23:14:30 首次发布

本文链接：https://blog.csdn.net/weixin_52324111/article/details/128058857

版权

数据库专栏专栏收录该内容

2 篇文章 0 订阅

订阅专栏

3.2、master节点创建同步用户和权限

3.3、slave1和slave2节点配置文件修改

3.4、slave1和slave2节点执行同步任务

3.5、解决虚拟机克隆导致mysql所有服务器uuid都一样的问题

4、bin-log相关说明

5、主从同步注意事项

1、服务器规划

服务器名称	规划	ip和端口
bigdata01	master	192.168.118.121:3306
bigdatamodel	slave01	192.168.118.120:3306
bigdata02	slave02	192.168.118.122:3306

2、主从同步流程

Mysql的主从复制中主要有三个线程：

master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

master（binlog dump thread）主要负责Master库中有数据更新的时候，会按照binlog格式，将更新的事件类型写入到主库的

binlog文件中。并且，Master会创建log dump线程通知Slave主库中存在数据更新，这就是为什么主库的binlog日志一定要开启的原因。

I/O thread线程在Slave中创建，该线程用于请求Master，Master会返回binlog的名称以及当前数据更新的位置、binlog文件位置的副本。

然后，将binlog保存在「relay log（中继日志）」中，中继日志也是记录数据更新的信息。

SQL线程也是在Slave中创建的，当Slave检测到中继日志有更新，就会将更新的内容同步到Slave数据库中，这样就保证了主从的数据的同步。

以上就是主从复制的过程，当然，主从复制的过程有不同的策略方式进行数据的同步，主要包含以下几种：

「同步策略」：Master会等待所有的Slave都回应后才会提交，这个主从的同步的性能会严重的影响。
「半同步策略」：Master至少会等待一个Slave回应后提交。
「异步策略」：Master不用等待Slave回应就可以提交。
「延迟策略」：Slave要落后于Master指定的时间。

3、主从搭建

3.1、master节点上配置文件修改

root@bigdata01 etc]# vim my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin
#[可选] 0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000
#控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M
#[可选]设置不要复制的数据库
binlog-ignore-db=information_schema,mysql,quartz
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=tokercart
#[可选]设置binlog格式
binlog_format=STATEMENT
# 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
# 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
sync_binlog=0

#character config
character_set_server=utf8mb4
explicit_defaults_for_timestamp=true

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

# 每次commit 日志缓存中的数据刷到磁盘中。通常设置为 1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能力
innodb_flush_log_at_trx_commit=1

启动失败，查看日志找到原因，有个参数设置错误

[root@bigdata01 etc]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@bigdata01 etc]# cat /var/log/mysqld.log |grep ERROR
2022-11-26T13:15:09.415695Z 0 [ERROR] unknown variable 'binlog_expire_logs_seconds=6000'
2022-11-26T13:15:09.415711Z 0 [ERROR] Aborting

在mysql 8.0版本中新增了binlog_expire_logs_seconds，该参数表示binlog的失效日期单位秒。8.0之前的版本，binlog的失效日志用expire_logs_days来配置，单位是天

修改my.cnf后，#设置日志文件保留的天数 expire_logs_days=10 重启成功,分别查看当前serverid、是否开启bin-log以及master节点上所有的的日志名称和position

[root@bigdata01 etc]# systemctl start mysqld
[root@bigdata01 etc]# mysql -uroot -p
............................................................................................
mysql> show global variables like '%server%';
+---------------------------------+--------------------------------------+
| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+
| character_set_server            | utf8mb4                              |
| collation_server                | utf8mb4_general_ci                   |
| innodb_ft_server_stopword_table |                                      |
| server_id                       | 1                                    |
| server_id_bits                  | 32                                   |
| server_uuid                     | 542516d8-1c3a-11ed-986c-000c290154b1 |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)
mysql> show global variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
+---------------------------------+--------------------------------+
5 rows in set (0.01 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       154 |
+------------------+-----------+
2 rows in set (0.00 sec)

3.2、master节点创建同步用户和权限

mysql> grant replication slave on . TO 'slave1'@'%' identified  by 'toker';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>grant replication slave on *.* TO 'slave1'@'%' identified  by 'Test#2021';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show grants for 'slave1'@'%';
+------------------------------------------------+
| Grants for slave1@%                            |
+------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%' |
+------------------------------------------------+
1 row in set (0.00 sec)

查看master节点日志最新状态和binlog名称

mysql> show master status;
+------------------+----------+--------------+---------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------+-------------------+
| mysql-bin.000002 |      591 | tokercart    | information_schema,mysql,quartz |                   |
+------------------+----------+--------------+---------------------------------+-------------------+
1 row in set (0.00 sec)

3.3、slave1和slave2节点配置文件修改

+------------------+----------+--------------+---------------------------------+-------------------+
slave1节点my.cnf
[mysqld]
server-id=2
relay-log=mysql-salve1-log
+------------------+----------+--------------+---------------------------------+-------------------+
slave2节点my.cnf
[mysqld]
server-id=3
relay-log=mysql-salve2-log

3.4、slave1和slave2节点执行同步任务

命令如下:

CHANGE MASTER TO MASTER_HOST='192.168.118.121',MASTER_USER='slave1',MASTER_PASSWORD='Test#2021',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1313;

启动start slave; 如果启动碰到错误可以执行reset slave; #删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件，然后重新执行change命令和start slave即可.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.118.121',MASTER_USER='slave1',MASTER_PASSWORD='Test#2021',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1313;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.118.121
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1313
               Relay_Log_File: mysql-salve2-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000002
           Slave_IO_Running: No
            Slave_SQL_Running: Yes
            .....................................
         Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
         .....................................

上述show slave status\G;查看同步状态的时候发现 Slave_IO_Running: No，仔细看下面错误有一条:

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

首先我们通过配置文件排除了server-id一致性的问题，我配置的server-id=1,server-id=2,server-id=3,所以不是此处问题
查看server_uuid属性，发现几台机器的server_uuid确实一样。为什么呢？因为我们的几台服务器都是虚拟机克隆出来的

mysql> show variables like '%server_uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | 542516d8-1c3a-11ed-986c-000c290154b1 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)

3.5、解决虚拟机克隆导致mysql所有服务器uuid都一样的问题

解决方法就是找到主机和从机的auto.cnf文件修改uuid值或删除auto.cnf这个文件。但是通过，查询uuid值还是一样

后来发现/var/lib/mysql/auto.cnf还有一个auto.cnf文件

[root@bigdata02 etc]# cd /var/lib/mysql
[root@bigdata02 mysql]# ls
auto.cnf    ca.pem           client-key.pem  ibdata1      ib_logfile1  master.info  mysql                    mysql-salve2-log.index  mysql.sock.lock     private_key.pem  quartz          server-cert.pem  sys
ca-key.pem  client-cert.pem  ib_buffer_pool  ib_logfile0  ibtmp1       mydb         mysql-salve2-log.000002  mysql.sock              performance_schema  public_key.pem   relay-log.info  server-key.pem
[root@bigdata02 mysql]# vi auto.cnf 

[auto]
server-uuid=542516d8-1c3a-11ed-986c-000c290154b1

删除掉从服务器的mysql下的auto.cnf，重启mysqld即可

[root@bigdata02 mysql]# rm -f auto.cnf 
[root@bigdata02 mysql]# systemctl restart mysqld
[root@bigdata02 mysql]# vi auto.cnf 
[auto]
server-uuid=8270d752-6d97-11ed-9c83-000c295a2050
[root@bigdata02 mysql]# mysql -uroot -p

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.118.121
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1313
               Relay_Log_File: mysql-salve2-log.000004
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
........................................................................

4、bin-log相关说明

根据 MySQL 官方文档的介绍，开启 binlog 之后，大概会有 1% 的性能损，binlog主要用作数据恢复和主从复制

MySQL 5.7.3以后版本必须配置server-id，否则无法启用MySQL二进制日志

binlog格式说明:MySQL默认采用的是STATEMENT，建议使用MIXED

binlog_format = MIXED

STATEMENT模式（SBR）

基于SQL语句的复制（statement-based replication），每一条会修改数据的sql语句都会记录到binlog中。

优点：

不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能

缺点：

某些情况会导致master-slave中的数据不一致，例如sleep()，last_insert_id()等

ROW模式（RBR）

基于行的复制（row-based replication），不记录每条sql语句的上下文信息，仅记录哪条数据被修改了，修改成什么样。

优点：

任何情况都可以复制，并且不会出现特定情况下存储过程、function等调用或者触发无法被正确复制的问题

缺点：

binlog日志文件会非常大

master上执行update语句时，所有变化都会写到binlog里面，SBR只会写一次，所以会导致频繁发生binlog的并发写问题

MIXED模式

上面两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

5、主从同步注意事项

搭建完主从，再去创建数据库（因为主从之前的数据库不在binlog里）

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.118.121
                  Master_User: slave1
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1830
               Relay_Log_File: mysql-salve1-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             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: 1146

解决办法：

先把库从线下手动拷贝过去，然后重启从节点的slave即可

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

1024程序员

关注

2
点赞
踩
28

收藏

觉得还不错? 一键收藏
0
评论
Mysql主从数据库搭建(一主两从)

Mysql的主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。master（binlog dump thread）主要负责Master库中有数据更新的时候，会按照binlog格式，将更新的事件类型写入到主库的binlog文件中。并且，Master会创建log dump线程通知Slave主库中存在数据更新，这就是为什么主库的binlog日志一定要开启的原因
复制链接

扫一扫