MySQL-主从架构的搭建-阿里云开发者社区

生猛干货

带你搞定MySQL实战，轻松对应海量业务处理及高并发需求，从容应对大场面试

一主一从的搭建

前置工作

OS: CentOS 7
DB：MySQL5.7

131和132已经按照MySQL-CentOS7通过YUM安装MySQL5.7.29完成了MYSQL的安装，并成功启动。

操作步骤

配置 master主服务器

核心配置：开启binlog , 指定唯一的server ID

mysql的配置文件 /etc/my.cnf ,增加如下配置

#指定binlog的存储位置，日志格式为二进制
log-bin=/var/lib/mysql/data/binlog/mysql-bin
#[必须]服务器唯一ID，默认是1，多个mysql server，不重复即可
server-id=1
#每次执行写入就与硬盘同步 (如果不配置这个,会等到缓冲区满了自动刷盘, 安全和性能的权衡,配置为1是每次都会刷盘，自然慢一些，自己权衡)
sync-binlog=1
#需要同步的二进制数据库名 (有多个的话，配置多个binlog-do-db)
binlog-do-db=artisan
#只保留7天的二进制日志，以防磁盘被日志占满
expire-logs-days=7
#不备份的数据库(有多个的话，配置多个binlog-ignore-db)
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
#关闭名称解析(非必须)
skip-name-resolve

全部的配置如下：

配置项解读：

log-bin
log-bin=/var/lib/mysql/data/binlog/mysql-bin
mysql默然是不开启bin-log的，show binary logs; 可查看
开启bin-log，核心的配置

server-id = [序列号]
log-bin = [文件名]

配置该项后，会自动生成 mysql-bin.index和mysql-bin.xxx的文件
server-id：在 MySQL 5.7.3 及以后版本,如果没有设置server-id, 那么设置binlog后无法开启MySQL服务. (Bug #11763963, Bug #56739)
sync-binlog ：权衡一下刷盘的机制，性能和安全之间权衡一下。
binlog-do-db：需要同步的数据库，多个库，配置多个binlog-do-db
expire-logs-days： bin-log的保留时间
binlog-ignore-db：不同步的数据库

查看 bin-log的配置信息

File: 当前记录bin-log的文件
Position: 从服务器读取的位置
Binlog_Do_DB: 需要同步的数据库
Binlog_Ignore_DB: 忽略的数据库，不同步

Master的数据库中建立一个复制帐户并授权

在 Master 的数据库中建立一个备份帐户(user = artisan4syn,pwd=artisan)：每个 slave 使用标准的 MySQL 用户名和密码连接 master 。

进行复制操作的用户会授予 REPLICATION SLAVE 权限。

# @后面的IP段，建议仅对从服务的网段进行开放 
 CREATE USER 'artisan4syn'@'192.168.%.%' IDENTIFIED BY 'artisan'; 
 grant replication slave,replication client on *.* to artisan4syn@'192.168.%.%' identified by 'artisan';
 FLUSH PRIVILEGES;

[root@artisan ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.29-log MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>  uninstall plugin validate_password;
Query OK, 0 rows affected (0.00 sec)
mysql>  CREATE USER 'artisan4syn'@'192.168.%.%' IDENTIFIED BY 'artisan';  # 用户和密码 自定义
Query OK, 0 rows affected (0.00 sec)
mysql>  grant replication slave,replication client on *.* to artisan4syn@'192.168.%.%' identified by 'artisan';  # *.* 所有数据库的所有表
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>

查看用户：

select * from mysql.user a where a.`User` = 'artisan4syn';

重点关注 host user ,

这是啥意思呢？

意思是 授权用户user 从哪台服务器host能够登录 ----------------> 主节点（主节点上创建的用户嘛）允许 192.168网段的IP，通过 artisan4syn用户访问我主节点。

配置 slave从服务器

slave从节点主要的配置为：打开中继日志，指定唯一的 servr ID，设置只读权限

从服务器mysql的配置文件/etc/my.cnf 文件增加如下配置

#配置server-id，唯一ID号     
server-id=2  
#开启从服务器二进制日志 (从节点如果后面没有级联的从节点，binlog可以不打开，避免无谓的资源消耗)             
#log_bin = /var/lib/mysql/data/binlog/mysql-bin
#[必须开启]打开Mysql中继日志，日志格式为二进制                  
relay_log = /var/lib/mysql/data/binlog/mysql-relay-bin    
#如果salve库名称与master库名相同，使用本配置     
replicate-do-db = artisan      
#如果master库名[artisan]与salve库名[artisan01]不同，使用以下配置[需要做映射]     
#replicate-rewrite-db = artisan[主库名] -> artisan01[从库名]    
#如果不是要全部同步[默认全部同步]，则指定需要同步的表   
#replicate-wild-do-table=artisan01.t_order    
#replicate-wild-do-table=artisan01.t_order_item
#设置只读权限 
read_only = 1  
#使得更新的数据写进二进制日志中       
log_slave_updates = 1   （这个是不是应该为 on 待确认 , 给GTID用的？ 5.7以下的版本 使用GTID必须要开启  ）

配置项解读：

server-id ：唯一id ,必须设置
relay_log 中继日志，必须设置。结合主从同步的架构图去理解就非常好理解了。

[root@artisan ~]# mkdir -p /var/lib/mysql/data/binlog/mysql-bin
[root@artisan ~]# 
[root@artisan ~]# cd /var/lib/mysql
[root@artisan mysql]# chown -R mysql:mysql data/
[root@artisan mysql]# ll data/
total 0
drwxr-xr-x. 3 mysql mysql 22 Jan 28 19:07 binlog

[root@artisan ~]# cd /var/lib/mysql
[root@artisan mysql]# chown mysql:mysql mysql.sock.lock

log_bin ：从服务器可选 ,开启从服务器二进制日志 (从节点如果后面没有级联的从节点，binlog可以不打开，避免无谓的资源消耗，如果后面还有个从节点，那就必须要开启了，根据实际情况而定)
replicate-do-db ：如果salve库名称与master库名相同，使用本配置
replicate-rewrite-db ：master和slave数据库不同时，需要做映射

replicate-rewrite-db = artisan -> artisan01   
replicate-rewrite-db = artisan -> artisan02

主库artisan 映射到两个不同的数据库，这种配置是不对的，因为一个binlog只能播放一次 .
replicate-wild-do-table ：需要同步的表
注意事项：
如果也配置了同步的表，优先使用这个配置。
如果master节点有个跨库的查询，比如一个查询关联了DB1和DB2 ,这个时候如果想要同步到从节点，必须要配置 replicate-wild-do-table，指定具体的表
read_only =1 : db只可以读，不可写，但对root超级用户来讲，是不受只读设置影响的，所以，root还是可以往里面去插入数据 . 如果我们换成其他的普通用户就不会出现这样的问题了。要防止root可写，设置 flush tables with read lock; 但这样的话，主从自动同步就挂逼了。。。因为主从同步是通过把主上的 sql 语句放在从上再执行一遍来实现的，锁表的话，sql 语句就不能执行了。想要同步，需要把表解锁（执行 unlock tables;）就可以了
log_slave_updates ： https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_log_slave_updates
默认的情况下log_slave_updates参数是关闭的，从服务器从主服务器接收到的更新不记入它的二进制日志。该选项告诉从服务器将其SQL线程执行的更新记入到从服务器自己的二进制日志。为了使该选项生效，还必须用–logs-bin选项启动从服务器以启用二进制日志。如果想要应用链式复制服务器，应使用–logs-slave-updates。例如，可能你想要这样设置：
A -> B -> C
也就是说，A为从服务器B的主服务器，B为从服务器C的主服务器。为了能工作，B必须既为主服务器又为从服务器。你必须用–logs-bin启动A和B以启用二进制日志，并且用–logs-slave-updates选项启动B。
1、从库只开启log-bin功能，不添加log-slave-updates参数，从库从主库复制的数据不会写入log-bin日志文件里。
2、直接向从库写入数据时，是会写入log-bin日志的。
3、开启log-slave-updates参数后，从库从主库复制的数据会写入log-bin日志文件里。这也是该参数的功能。
开启以后可以实现主主同步，切换。

启动从服务器(slave)的复制线程

slave 连接 master ，并开始重做 master 二进制日志中的事件

先到 master上执行下

在slave节点上执行如下命令：

[root@artisan mysql-bin]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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.
# Step1.设置同步关系
mysql>  change master to master_host='192.168.18.131', master_user='artisan4syn',  master_password='artisan',  master_log_file='mysql-bin.000045',  master_log_pos=1308;
Query OK, 0 rows affected, 2 warnings (0.41 sec)
# Step2.启动复制线程
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql>

start slave 启动可能报错： ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread;run STOP SLAVE IO_THREAD FOR CHANNEL '' first.

----》原因：在设置同步关系时，没有先停止slaves线程。

----》解决办法：在设置同步关系之前，先执行下“stop slave;”，以停止slave线程。然后启动线程"start slave;"

参数说明：

mysql>  change master to master_host='192.168.18.131',  // master ip
   >master_user='artisan4syn',  // 创建的复制用户
   >master_password='artisan', // 创建的复制用户的密码
   >master_log_file='mysql-bin.000045',  //master对应的bin-log文件  
   >master_log_pos=1308;  //master对应的position

看看 mysql的log ,启动了2个线程。

查看从服务器状态

可使用SHOW SLAVE STATUS\G;查看从服务器状态,

也可用show processlist \G;查看当前复制状态

# 查看查看从节点状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.18.131
                  Master_User: artisan4syn
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000045
          Read_Master_Log_Pos: 1308
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000045
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: artisan
          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: 1308
              Relay_Log_Space: 527
              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
                  Master_UUID: 2a9b3cc0-4140-11ea-b350-000c29f66452
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
ERROR: 
No query specified
mysql>

如何确定slave节点的两个线程工作正常，看红框，需要关注的几个点

Slave_IO_Running: Yes //IO线程正常运行

Slave_SQL_Running: Yes //SQL线程正常运行

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 3
   User: root
   Host: 192.168.18.1:61080
     db: NULL
Command: Sleep
   Time: 2549
  State: 
   Info: NULL
*************************** 2. row ***************************
     Id: 4
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 3. row ***************************
     Id: 5
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 784
  State: Waiting for master to send event
   Info: NULL
*************************** 4. row ***************************
     Id: 6
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 784
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
4 rows in set (0.00 sec)
ERROR: 
No query specified
mysql>

binlog相关的命令

# 查看是否开启binlog
mysql>show binary logs;
#查看binlog格式：
mysql>show variables like 'binlog_format';
#获取binlog文件列表：
mysql>show binary logs;
#查看当前正在写入的binlog文件：
mysql>show master status;
#查看master上的binlog：
mysql>show master logs;
#只查看第一个binlog文件的内容：
mysql>show binlog events;
#查看指定binlog文件的内容：
mysql>show binlog events in 'mysql-bin.000045';
#清空所有的bin-log：
mysql>reset master;
#生成一个新的binlog：
mysql>flush logs;

#用bash查看binlog日志文件：
[root@artisan binlog]# mysqlbinlog mysql-bin.000045

问题记录

核心：根据配置文件中的配置项log-error，找到mysql的日志文件，从日志中发掘报错信息，从而进一步去解决问题。

linux的默认配置文件位于 /etc/my.cnf

# 过滤空行和注释行
grep -Ev "^$|[#;]" /etc/my.cnf

所以启停mysql的时候，查看 /var/log/mysqld.log 即可

File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 2 - No such file or directory)

开启binlog后，重启过程中碰到的问题一

配置开启binlog的时候指定了 log-bin

开启binlog后，重启mysql报错如下

[root@artisan ~]# tail -f /var/log/mysqld.log 
2020-01-28T01:10:48.022822Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
2020-01-28T01:10:48.746119Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-01-28T01:10:48.748077Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-log) starting as process 114966 ...
mysqld: File '/var/lib/mysql/data/binlog/mysql-bin.index' not found (Errcode: 2 - No such file or directory)
2020-01-28T01:10:48.750840Z 0 [ERROR] Aborting

没有创建这个目录，需要手工创建

mkdir -p /var/lib/mysql/data/binlog

File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 13 - Permission denied)

开启binlog后，重启过程中碰到的问题二

2020-01-28T01:12:43.463345Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-01-28T01:12:43.467768Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.29-log) starting as process 115213 ...
mysqld: File '/var/lib/mysql/data/binlog/mysql-bin.index' not found (Errcode: 13 - Permission denied)
2020-01-28T01:12:43.472386Z 0 [ERROR] Aborting

上一步用root 建的 /var/lib/mysql/data/binlog 目录，需要改成mysql用户，修改用户和用户组

chown -R mysql /var/lib/mysql/data/
chgrp -R mysql /var/lib/mysql/data/

[ERROR] Unable to setup unix socket lock file.

开启binlog后，重启过程中碰到的问题三

2020-01-28T01:21:37.069836Z 0 [Note] Server socket created on IP: '::'.
2020-01-28T01:21:37.069929Z 0 [ERROR] Could not open unix socket lock file /var/lib/mysql/mysql.sock.lock.
2020-01-28T01:21:37.069944Z 0 [ERROR] Unable to setup unix socket lock file.
2020-01-28T01:21:37.069955Z 0 [ERROR] Aborting

mysql.sock.lock的用户和用户组改成mysql的

[root@artisan mysql]# chown mysql:mysql /var/lib/mysql/mysql.sock.lock

can’t read dir of ‘./artisan’ (errcode 13 - permission denied)

开启binlog后，新建artisan数据库，连接artisan,碰到的问题

调整用户和用户组权限

[root@artisan mysql]# chown -R mysql:mysql artisan/

重启mysql

[root@artisan etc]# ps -ef|grep mysqld |grep -v grep 
root       7369 115008  0 14:30 pts/2    00:00:00 tail -f /var/log/mysqld.log
mysql      7419      1  0 14:31 ?        00:00:04 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
[root@artisan etc]# 
[root@artisan etc]#

观察日志 /var/log/mysqld.log 启动正常。

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

创建复制账户的时候，密码策略问题

参考：MySQL – ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

主从测试

测试主从同步

主节点，建表插入数据，测试自动同步

从节点，查看表和数据，

成功。

问题记录

Slave_SQL_Running: No

瞎倒腾了一下，结果把SQL线程搞挂了。

看MYSQL的日志,从节点的表不存在

2020-01-28T13:18:21.874935Z 6 [ERROR] Slave SQL for channel '': Error executing row event: 'Table 'artisan.t_order' doesn't exist', Error_code: 1146
2020-01-28T13:18:21.875184Z 6 [Warning] Slave: Table 'artisan.t_order' doesn't exist Error_code: 1146
2020-01-28T13:18:21.875207Z 6 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000045' position 1308.

是这样的，我同步的那个pos ,并不包含我建库的脚本，所以报错是正常的。

解决办法：重新设置了同步关系，重启线程

Master重新查看下

slave节点重新设置同步关系

重新查看

MySQL-主从架构的搭建

生猛干货

一主一从的搭建

前置工作

操作步骤

配置 master主服务器

Master的数据库中建立一个复制帐户并授权

配置 slave从服务器

启动从服务器(slave)的复制线程

查看从服务器状态

binlog相关的命令

问题记录

File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 2 - No such file or directory)

File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 13 - Permission denied)

[ERROR] Unable to setup unix socket lock file.

can’t read dir of ‘./artisan’ (errcode 13 - permission denied)

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

主从测试

测试主从同步

问题记录

Slave_SQL_Running: No

搞定MySQL

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL-主从架构的搭建

生猛干货

一主一从的搭建

前置工作

操作步骤

配置 master主服务器

Master的数据库中建立一个复制帐户并授权

配置 slave从服务器

启动从服务器(slave)的复制线程

查看从服务器状态

binlog相关的命令

问题记录

File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 2 - No such file or directory)

File ‘/var/lib/mysql/data/binlog/mysql-bin.index’ not found (Errcode: 13 - Permission denied)

[ERROR] Unable to setup unix socket lock file.

can’t read dir of ‘./artisan’ (errcode 13 - permission denied)

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

主从测试

测试主从同步

问题记录

Slave_SQL_Running: No

搞定MySQL

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像