mysql主从搭建
mysql主从搭建
summerking
发布于 2022-10-27 13:36:48
发布于 2022-10-27 13:36:48
代码可运行
运行总次数：0
代码可运行
# 1.原理
- master的I/O线程将数据写入binlog中；
- slave的I/O线程从master的binlog中读取数据，写入自己的Relay_Log_File日志中；
- slave的SQL线程从Relay_Log_File日志中解析sql，完成数据的复制。
# 2.应用场景
- 从服务器作为主服务器的实时数据备份
- 主从服务器实现读写分离（主写从读），从服务器实现负载均衡
- 把多个从服务器根据业务重要性进行拆分访问（从服务器根据业务进行拆分）
# 3.master主库配置
- 修改my.cnf
[root@localhost mysql]# cat my.cnf
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
port=3306- 创建日志目录并赋予权限
mkdir /var/log/mysql
chown -R mysql:mysql /var/log/mysql- 重启数据库后，查看以下配置是否生效
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.02 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%skip_networking%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> - 在主库上建立用于主从复制的账号
mysql> CREATE USER 'rep1'@'%';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep1'@'%' identified by 'coolsummermoon';- 查看主库的二进制日志的名称 这里的Position和File后面会用到
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
mysql> # 4.slave从库配置
- 在从库上面测试之前建立的复制账号是否可以连接主库
[root@localhost mysql]# mysql -u rep1 -h 192.168.0.66 -P 3306 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1036
Server version: 5.7.21-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, 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> - 修改my.cnf
[root@localhost mysql]# cat my.cnf
[mysqld]
server-id=2
port=3306- 重启数据库后，查看以下配置是否生效
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.01 sec)
mysql> - 配置复制参数
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.66',MASTER_USER='rep1',MASTER_PASSWORD='coolsummermoon',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=154;
mysql> start slave;- 查看slave的状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.66
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1566853
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 1567019
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes- 若出错，则清理掉之前的配置，执行以下命令
mysql> stop slave;
mysql> reset slave all;# 5.验证
主上创建数据库插入数据等操作，从上能同步即可
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2020-11-20，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
