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

目录

1、服务器规划

2、主从同步流程

3、主从搭建

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

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

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

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

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

4、bin-log相关说明

5、主从同步注意事项

---

1、服务器规划

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节点上配置文件修改

1.  
   root@bigdata01 etc]# vim my.cnf
2.  
    
3.  
   [mysqld]
4.  
   server-id=1
5.  
   log-bin=mysql-bin
6.  
   #[可选] 0（默认）表示读写（主机），1表示只读（从机）
7.  
   read-only=0
8.  
   #设置日志文件保留的时长，单位是秒
9.  
   binlog_expire_logs_seconds=6000
10.  
    #控制单个二进制日志大小。此参数的最大和默认值是1GB
11.  
    max_binlog_size=200M
12.  
    #[可选]设置不要复制的数据库
13.  
    binlog-ignore-db=information_schema,mysql,quartz
14.  
    #[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
15.  
    binlog-do-db=tokercart
16.  
    #[可选]设置binlog格式
17.  
    binlog_format=STATEMENT
18.  
    # 写缓存多少次，刷一次磁盘，默认 0 表示这个操作由操作系统根据自身负载自行决定多久写一次磁盘
19.  
    # 1 表示每一条事务提交都会立即写磁盘，n 则表示 n 个事务提交才会写磁盘
20.  
    sync_binlog=0
21.  
     
22.  
    #character config
23.  
    character_set_server=utf8mb4
24.  
    explicit_defaults_for_timestamp=true
25.  
     
26.  
    datadir=/var/lib/mysql
27.  
    socket=/var/lib/mysql/mysql.sock
28.  
     
29.  
    # Disabling symbolic-links is recommended to prevent assorted security risks
30.  
    symbolic-links=0
31.  
     
32.  
    log-error=/var/log/mysqld.log
33.  
    pid-file=/var/run/mysqld/mysqld.pid
34.  
     
35.  
    # 每次commit 日志缓存中的数据刷到磁盘中。通常设置为 1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能力
36.  
    innodb_flush_log_at_trx_commit=1

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

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

1.  
   [root@bigdata01 etc]# systemctl start mysqld
2.  
   [root@bigdata01 etc]# mysql -uroot -p
3.  
   ............................................................................................
4.  
   mysql> show global variables like '%server%';
5.  
   --------------------------------- --------------------------------------
6.  
   | Variable_name | Value |
7.  
   --------------------------------- --------------------------------------
8.  
   | character_set_server | utf8mb4 |
9.  
   | collation_server | utf8mb4_general_ci |
10.  
    | innodb_ft_server_stopword_table | |
11.  
    | server_id | 1 |
12.  
    | server_id_bits | 32 |
13.  
    | server_uuid | 542516d8-1c3a-11ed-986c-000c290154b1 |
14.  
    --------------------------------- --------------------------------------
15.  
    6 rows in set (0.00 sec)
16.  
    mysql> show global variables like '%log_bin%';
17.  
    --------------------------------- --------------------------------
18.  
    | Variable_name | Value |
19.  
    --------------------------------- --------------------------------
20.  
    | log_bin | ON |
21.  
    | log_bin_basename | /var/lib/mysql/mysql-bin |
22.  
    | log_bin_index | /var/lib/mysql/mysql-bin.index |
23.  
    | log_bin_trust_function_creators | OFF |
24.  
    | log_bin_use_v1_row_events | OFF |
25.  
    --------------------------------- --------------------------------
26.  
    5 rows in set (0.01 sec)
27.  
     
28.  
    mysql> show master logs;
29.  
    ------------------ -----------
30.  
    | Log_name | File_size |
31.  
    ------------------ -----------
32.  
    | mysql-bin.000001 | 177 |
33.  
    | mysql-bin.000002 | 154 |
34.  
    ------------------ -----------
35.  
    2 rows in set (0.00 sec)
36.  
     

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

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

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

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

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

1.  
   ------------------ ---------- -------------- --------------------------------- -------------------
2.  
   slave1节点my.cnf
3.  
   [mysqld]
4.  
   server-id=2
5.  
   relay-log=mysql-salve1-log
6.  
   ------------------ ---------- -------------- --------------------------------- -------------------
7.  
   slave2节点my.cnf
8.  
   [mysqld]
9.  
   server-id=3
10.  
    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即可.

1.  
   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;
2.  
   Query OK, 0 rows affected, 2 warnings (0.01 sec)
3.  
    
4.  
   mysql> start slave;
5.  
   Query OK, 0 rows affected (0.01 sec)
6.  
    
7.  
   mysql> show slave status\G;
8.  
   *************************** 1. row ***************************
9.  
   Slave_IO_State:
10.  
    Master_Host: 192.168.118.121
11.  
    Master_User: slave1
12.  
    Master_Port: 3306
13.  
    Connect_Retry: 60
14.  
    Master_Log_File: mysql-bin.000002
15.  
    Read_Master_Log_Pos: 1313
16.  
    Relay_Log_File: mysql-salve2-log.000001
17.  
    Relay_Log_Pos: 4
18.  
    Relay_Master_Log_File: mysql-bin.000002
19.  
    Slave_IO_Running: No
20.  
    Slave_SQL_Running: Yes
21.  
    .....................................
22.  
    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.
23.  
    .....................................

上述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.

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

1.  
   mysql> show variables like '%server_uuid%';
2.  
   --------------- --------------------------------------
3.  
   | Variable_name | Value |
4.  
   --------------- --------------------------------------
5.  
   | server_uuid | 542516d8-1c3a-11ed-986c-000c290154b1 |
6.  
   --------------- --------------------------------------
7.  
   1 row in set (0.00 sec)

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

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

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

1.  
   [root@bigdata02 etc]# cd /var/lib/mysql
2.  
   [root@bigdata02 mysql]# ls
3.  
   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
4.  
   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
5.  
   [root@bigdata02 mysql]# vi auto.cnf
6.  
    
7.  
   [auto]
8.  
   server-uuid=542516d8-1c3a-11ed-986c-000c290154b1

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

1.  
   [root@bigdata02 mysql]# rm -f auto.cnf
2.  
   [root@bigdata02 mysql]# systemctl restart mysqld
3.  
   [root@bigdata02 mysql]# vi auto.cnf
4.  
   [auto]
5.  
   server-uuid=8270d752-6d97-11ed-9c83-000c295a2050
6.  
   [root@bigdata02 mysql]# mysql -uroot -p
7.  
    
8.  
   mysql> show slave status\G;
9.  
   *************************** 1. row ***************************
10.  
    Slave_IO_State: Waiting for master to send event
11.  
    Master_Host: 192.168.118.121
12.  
    Master_User: slave1
13.  
    Master_Port: 3306
14.  
    Connect_Retry: 60
15.  
    Master_Log_File: mysql-bin.000002
16.  
    Read_Master_Log_Pos: 1313
17.  
    Relay_Log_File: mysql-salve2-log.000004
18.  
    Relay_Log_Pos: 320
19.  
    Relay_Master_Log_File: mysql-bin.000002
20.  
    Slave_IO_Running: Yes
21.  
    Slave_SQL_Running: Yes
22.  
    ........................................................................

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、主从同步注意事项

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

1.  
   mysql> show slave status\G;
2.  
   *************************** 1. row ***************************
3.  
   Slave_IO_State: Waiting for master to send event
4.  
   Master_Host: 192.168.118.121
5.  
   Master_User: slave1
6.  
   Master_Port: 3306
7.  
   Connect_Retry: 60
8.  
   Master_Log_File: mysql-bin.000002
9.  
   Read_Master_Log_Pos: 1830
10.  
    Relay_Log_File: mysql-salve1-log.000002
11.  
    Relay_Log_Pos: 320
12.  
    Relay_Master_Log_File: mysql-bin.000002
13.  
    Slave_IO_Running: Yes
14.  
    Slave_SQL_Running: No
15.  
    Replicate_Do_DB:
16.  
    Replicate_Ignore_DB:
17.  
    Replicate_Do_Table:
18.  
    Replicate_Ignore_Table:
19.  
    Replicate_Wild_Do_Table:
20.  
    Replicate_Wild_Ignore_Table:
21.  
    Last_Errno: 1146

解决办法：

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

1.  
   mysql> stop slave;
2.  
   Query OK, 0 rows affected (0.00 sec)
3.  
   mysql> start slave;
4.  
   Query OK, 0 rows affected (0.01 sec)

这篇好文章是转载于：学新通技术网