MYSQL主从复制搭建和报错解决

.大橙子.

已于 2022-08-18 14:12:03 修改

阅读量1.9k

点赞数 1

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

于 2022-08-18 14:03:17 首次发布

本文链接：https://blog.csdn.net/weixin_42333478/article/details/126404480

版权

mysql 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL主从复制的企业应用场景（普通-需要记录 mysql-bin.000001 | 345）

MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Slave），在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和I/O线程）在Slave端，另外一个线程（I/O线程）在Master端。要实现MySQL的主从复制，

1）修改主库的配置文件。执行vi /data/3306/my.cnf,编辑多实例3006的my.cnf配置文件，按如下内容修改两个参数： (3307为server_id=3因为不能重复)

因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后再在 Slave上以相同顺序执行获取的binlog日志中所记录的各种SQL操作。要打开MySQL的binlog记录功能，可通过在MySQL的配置文件my.cnf中的mysqld 模块（[mysqld]标识后的参数部分）增加“log-bin”参数选项来实现

下面简单描述MySQL Replication的复制原理过程

1）在Slave服务器上执行start slave命令开启主从复制开关，开始进行主从复制

2）此时，Slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求连接Master 服务器，并请求从指定binlog日志文件的指定位置（日志文件名和位置就是在配置主从复制服务时执行change master命令指定的）之后开始发送binlog日志内容。

3）Master服务器接收到来自Slave服务器的I/O线程的请求后，其上负责复制的I/O线程会根据Slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息，然后返回给Slave端的I/O线程。返回的信息中除了binlog日志内容外，还有在Master 服务器端记录的新的binlog文件名称，以及在新的binlog中的下一个指定更新位置。

4）当Slave服务器的I/O线程获取到Master服务器上I/O线程发送的日志内容，日志文件及位置点后，会将binlog日志内容依次写到Slave端自身的Relay Log（即中继日志）文件（MySQL-relay-bin.xxxx）的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取Master端新binlog日志时能够告诉Master服务器从新binlog日志的指定文件及位置开始请求新的binlog日志内容。

5）Slave服务器端的SQL线程会实时检测本地Relay Log中I/O线程新增加的日志内容，然后及时地把Relay Log文件中的内容解析成SQL语句，并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这些SQL语句，并在relay-log.info中记录当前应用中继日志的文件名及位置点。

1）在主库Master上执行操作配置（从库把3306直接改为3307 && server_id=3）

vi /my.cnf

#客户端相关配置

[client]

#客户端连接端口

port=3306

#客户端连接socket,必须与服务端的socket使用同一个

socket=/data/mysql/mysql3306/mysql.sock

[mysqld]

user = mysql

port = 3306

mysqlx_port = 33060 #这个会显示到端口处（ss -lnput | grep 330*）,如果不写那启动3307时候，也会出现33060端口

server-id = 1 #用于同步的每台机器或实例server-id都不能相同 ,3307为server_id=3因为不能重复)

log-bin = /data/mysql/mysql3306/mysql-bin #binlog日志的位置

basedir = /usr/local/mysql

datadir = /data/mysql/mysql3306/data

tmpdir = /data/mysql/mysql3306/tmp

socket = /data/mysql/mysql3306/mysql.sock

log_error = /data/mysql/mysql3306/error.logvi

#skip-grant-tables #这个恶心，写上后，启动实例，有mysql进程，但端口无法显示，好处是允许不输入密码登录

2）登陆数据库，检查参数的更改情况，如下：

mysql -uroot -p123123 -S /data/mysql/mysq3306/mysql.sock

mysql> show variables like 'server_id'; #配置的server_id为1

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 1 |

+---------------+-------+

1 row in set (0.01 sec)

mysql> show variables like 'log_bin'; #binlog功能已开启

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set (0.00 sec)

3）在主库上建立用于主从复制的账号

根据主从复制的原理，从库要想和主库同步，必须有一个可以连接主库的账号，并且这个账号是主库上创建的，权限是允许主库的从库连接并同步数据。

建立用于从库复制的账号yunjisuan，命令如下：

create user 'yunjisuan'@'192.168.31.%' identified by 'yunjisuan123';

授权

grant replication slave on *.* to 'yunjisuan'@'192.168.31.%';

刷新权限，生效

flush privileges;

#语句说明：

1）replication slave为mysql同步的必须权限，此处不要授权all权限

2）*.* 表示所有库所有表，也可以指定具体的库和表进行复制。例如yunjisuan.test中，y unjisuan为库名，test为表名

3）'yunjisuan'@'192.168.0.%' yunjisuan为同步账号。192.168.0.%为授权主机网段，使用了%表示允许整个192.168.0.0网段可以用yunjisuan这个用户访问数据库

4）identified by 'yunjisuan123'; yunjisuan123为密码，实际环境下设置的复杂些为好

5）with gran option 表示该用户可给其它用户赋予权限，但不可能超过该用户已有的权限

比如 a 用户有 select,insert 权限，也可给其它用户赋权，但它不可能给其它用户赋 delete 权限，除了 select,insert 以外的都不能,在授权后面加：'192.168.31.%' with gran option

4）查询用户

select user,host from mysql.user;

select * from mysql.user where user='yunjisuan'; #where 是SQL查询语句的条件

show grants for yunjisuan@'192.168.31.%'; #查看账号的授权情况

+--------------------------------------------------------------------------------+

| Grants for yunjisuan@192.168.31.% |

+--------------------------------------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO `yunjisuan`@`192.168.31.%` WITH GRANT OPTION |

+--------------------------------------------------------------------------------+

1 row in set (0.00 sec)

格外补充：

删除用户

drop user 'yunjisuan'@'192.168.31.%';

修改密码

Alter user 'yunjisuan'@'192.168.31.%' identified by '新密码';

5）从库连接主库配置

CHANGE MASTER TO 2. MASTER_HOST='192.168.31.134', #这里是主库的IP

MASTER_PORT=3306, #这里是主库的端口，从库端口可以和主库不同

MASTER_USER='yunjisuan', #这里是主库上建立的用于复制的用户 yunjisuan

MASTER_PASSWORD='yunjisuan123', #这里是yunjisuan用户的密码

MASTER_LOG_FILE='mysql-bin.000001', #这里是show master status时查看到的二进制日志文件名称，注意不能多空格

MASTER_LOG_POS=533; #这里是show master status时查看到的二进制日志偏移量，注意不能多空格

#提示：字符串用单引号括起来，数值不用引号，注意内容前后不能有空格。

#登录从库

mysql -uroot -p123123 -S /data/mysql/mysq3307/mysql.sock

#提示：这个步骤的参数一定不能错，否则，数据库复制配置会失败

mysql> CHANGE MASTER TO MASTER_HOST='192.168.31.134',MASTER_PORT=3306,MASTER_USER='yunjisuan',MASTER_PASSWORD='yunjisuan123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=533;

6）启动从库同步开关，测试主从复制配置情况

#启动开关

start slave;

#查询情况（需加 ; 不然会报错：最后面：ERROR: No query specified）

show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

Last_Errno: 0

Last_Error:

********************************************主从复制的问题****************************************************

（1）报错问题：

Last_IO_Errno: 2061

Last_IO_Error: error connecting to master 'yunjisuan@192.168.31.134:3306' - retry-time: 60 retries: 4 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

解决方法：

目前上面我这么搭建的是，新开一个窗口，然后mysql登录到创建用户的数据库内

mysql -uyunjisuan -pyunjisuan123 -h 192.168.31.134 -S /data/mysql/mysql3307/mysql.sock

从库操作：stop slave-start slave-show slave status\G，如果还不行就多来几次，或者看下show master status;

（2）报错问题：

•Last I0 Errno: 13117

Last I0 Error: Fatal error: The slave I/0 thread stops because master and slave have equal

MySQL server UUIDs; these UUIDs must be different for replication to work.

解决方法：

这个是由于从库服务器采用的Vmware克隆产生的，mysql的唯一标识和主库一致

解决：删除UUID,重启服务自动生成新UUID

rm -f /data/mysql/mysql3307/data/auto.cnf

cd /data/mysql/mysql3307/

./mysql stop

./mysql start

（3）报错问题：

Last_IO_Errno: 13114

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not open log file'

解决方法：

binlog位置索引处的问题，查询主库show master status;

（4）报错问题：

Last_IO_Error: error connecting to master 'rep@192.168.31.134:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.31.134' (111)

解决方法：

登录mysql控制台，执行select host, user, password_expired from mysql.user; 看一下显示的数据有没有127.0.0.1，如果没有，插入一条host为127.0.0.1的记录，其他值跟localhost那条记录一样。

注：如果没有mysql.user表，表示数据库没有安装成功，缺少mysql用户信息

******************************************************************************************************************

######以下为主库数据过大的备份操作（前提：主从复制之前主库已经有了50G的数据）######

1）对主数据库锁表只读（当前窗口不要关掉）的命令如下：

mysql -uroot -p123123 -S /data/mysql/mysq3306/mysql.sock

mysql> flush table with read lock;

提示：在引擎不同的情况下，这个锁表命令的时间会受下面参数的控制。锁表时，如果超过设置时间不操作会自动解锁。默认情况下自动解锁的时长参数值如下：

mysql> show variables like '%timeout%';

+-------------------------------------+----------+

| Variable_name | Value |

+-------------------------------------+----------+

| connect_timeout | 10 |

| delayed_insert_timeout | 300 |

| have_statement_timeout | YES |

| innodb_flush_log_at_timeout | 1 |

| innodb_lock_wait_timeout | 50 |

| innodb_rollback_on_timeout | OFF |

| innodb_semaphore_wait_timeout_debug | 600 |

| interactive_timeout | 28800 | #自动解锁时间受本参数影响

| lock_wait_timeout | 31536000 |

| mysqlx_connect_timeout | 30 |

| mysqlx_idle_worker_thread_timeout | 60 |

| mysqlx_interactive_timeout | 28800 |

| mysqlx_port_open_timeout | 0 |

| mysqlx_read_timeout | 30 |

| mysqlx_wait_timeout | 28800 |

| mysqlx_write_timeout | 60 |

| net_read_timeout | 30 |

| net_write_timeout | 60 |

| rpl_stop_slave_timeout | 31536000 |

| slave_net_timeout | 60 |

| wait_timeout | 28800 #自动解锁时间受本参数影响

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 345 | | |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

或者新开一个命令行窗口，用如下命令查看锁表后的主库binlog位置点信息：

[root@localhost ~]# mysql -uroot -p123123 -S /data/mysql/mysql3306/mysql.sock -e " show master status"

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 533 | | |

+------------------+----------+--------------+------------------+

2）锁表后，一定要单开一个新的SSH窗口，导出数据库的所有数据，如果数据量很大（50GB 以上），并且允许停机，可以停库直接打包数据文件进行迁移，那样更快。

mkdir -p /server/backup #创建备份目录

mysqldump -uroot -p123123 -S /data/mysql/mysql3306/mysql.sock --events -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz

-A表示备份所有库

-B表示增加use DB和 drop 等（导库时会直接覆盖原有的）

#为了确保导出数据期间，数据库没有数据插入，导库完毕可以再次检查主库状态信息，结果如下：

mysql -uroot -p123123 -S /data/mysql/mysql3306/mysql.sock -e " show master status"

2. +------------------+----------+--------------+------------------+

4. +------------------+----------+--------------+------------------+

5. | mysql-bin.000001 | 533 | | |

6. +------------------+----------+--------------+------------------+

3）#解表

unlock tables;

提示：若无特殊情况，binlog文件及位置点和锁表后导出数据前是一致的，即没有变化。

#导出数据完毕后，解锁主库，恢复可写，命令如下.因为主库还要对外提供服务，不能一直锁定不让用户访问。锁表后的binlog位置问题，实际上做从库前，无论主库更新了多少数据，最后从库都可以从上面show master status的位置很快赶上主库的进度。

4）把主库导出的MySQL数据迁移到从库

下面主要讲解单数据库多实例的主从配置，也就是说，mysqldump备份的3306实例的数据和要恢复的3307实例在一台机器上，因此无需异地复制拷贝。想查看主库导出的数据（server_id是不是为3），如下

mysql -uroot -p123123 -S /data/mysql/mysql3307/mysql.sock

mysql> show variables like 'server_id'; #配置的server_id为3

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 3 |

+---------------+-------+

1 row in set (0.01 sec)

mysql> show variables like 'log_bin'; #binlog功能已开启

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

1 row in set (0.00 sec)

mysql> exit

bye

5）备份数据导入从库

#备份目录

cd /server/backup/

#解压备份文件

gzip -d mysql_bak.2022-03-28.sql.gz

#这是把数据还原到3307实例的命令

mysql -uroot -p123123 -S /data/mysql/mysql3307/mysql.sock <mysql_bak.2022-03-28.sql.gz

#提示：

如果备份时使用了-A参数，则在还原数据到3307实例时，登陆3307实例的密码也会和3306主库的一致，因为3307实例的授权表MySQL也被覆盖了。

.大橙子.

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
打赏
0
评论
MYSQL主从复制搭建和报错解决

msyql主从搭建和排错
复制链接

扫一扫

专栏目录

MYSQL主从复制搭建和报错解决

“相关推荐”对你有帮助么？