mysql主从复制
一、主从复制简述
MySQL主从复制是一种数据库复制技术，用于在主数据库（Master）和一个或多个从数据库（Slave）之间同步数据。这种技术允许数据从主数据库复制到从数据库，实现数据的冗余存储和读写分离，从而提高数据库的可用性和扩展性。
二、主从复制的优势
主从复制的优势包括：
数据热备：作为后备数据库，主数据库服务器故障后，可以切换到从数据库继续工作，避免数据丢失。
架构扩展：随着业务量增大，单台服务器可能无法满足需求。通过主从复制，可以降低磁盘I/O访问频率，提高性能。
读写分离：主数据库处理写操作，从数据库处理读操作，支持更大的并发，特别是在生成报表时，可以避免慢查询导致的锁表问题。
三、主从复制操作
1、数据库安装
Mysql版本：
最新版本：8
旧版本/前一个版本：5.7
安装： 在BASE和EPEL仓库中没有Mysql的RPM包，但是有Mariadb的RPM包
1.准备Mysql的yum仓库
[root@mysql-rpm ~]# yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm
2.修改安装版本,启用5.7版本
yum -y install yum-utils
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
3.安装
yum -y install mysql mysql-server
客户端：mysql-community-client
服务器端：mysql-community-server
初始化：
[root@mysql-rpm ~]# systemctl start mysqld
[root@mysql-rpm ~]# systemctl enable mysqld
修改密码：mariadb针对root用户没有初始密码，但是mysql针对root用户有初始密码，针对mysql在使用之前先修改初始密码
[root@mysql-rpm ~]# mysqladmin -uroot -p'uEe0vMb#kRHY' password 'Testing@123'
[root@mysql-rpm ~]# mysql -uroot -pTesting
2、实际操作
节点 master 192.168.67.128
slave 192.168.67.131
注意： 所有节点关闭防火墙和selinux
保证yum仓库可以使用
保证网络通畅
如果是克隆服务器需要修改每台数据库的server-uuid
修改主机名（所有节点）可选操作
[root@c7 ~]# hostname master
[root@c7 ~]# hostname slave
添加本地解析
cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.67.131 master
192.168.67.128 slave
scp /etc/hosts slave:/etc/hosts
Master部署
主服务器部署
[root@master ~]# vi /etc/my.cnf
log-bin = my1log
server-id = 1
创建授权用户
[root@master ~]# mysql -uroot -pTesting@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> grant all on *.* to 'slave'@'%' identified by 'Testing@123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
重启服务
[root@master ~]# systemctl restart mysqld
注意：
replication slave：
拥有此权限可以查看从服务器，从主服务器读取二进制日志
super权限：
允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句
reload权限：
必须拥有reload权限，才可以执行flush [tables | logs | privileges]
Slave部署
从服务器部署：
[root@slave ~]# vi /etc/my.cnf
log-bin = my2log
server-id = 2
重启服务：
[root@slave ~]# systemctl restart mysqld
获取主服务器信息：（主服务器操作）
[root@master ~]# mysql -uroot -pTesting@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44-log MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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> show binlog events;
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| my2log.000001 | 4 | Format_desc | 2 | 123 | Server ver: 5.7.44-log, Binlog ver: 4 |
| my2log.000001 | 123 | Previous_gtids | 2 | 154 | |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
指定主服务器信息：（从服务器操作）
[root@slave ~]# mysql -u root -pTesting@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> edit
-> ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
注意：edit中添加的内容
CHANGE MASTER TO
MASTER_HOST='master',
MASTER_USER='slave',
MASTER_PASSWORD='Testing@123',
MASTER_PORT=3306,
MASTER_LOG_FILE='my1log.000001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
参数解释：
CHANGE MASTER TO
MASTER_HOST='mysql-master-1.blackmed.cn/ip', //做过解析的可以直接用主机名，没有则用IP
MASTER_USER='slave', //主服务器用户
MASTER_PASSWORD='big', //主服务器的密码
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001', //日志文件
MASTER_LOG_POS=4, //日志位置
MASTER_CONNECT_RETRY=10; //默认尝试次数
获取参数：
mysql> help change master to
启动slave：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
注意：
stop slave；停止slave
reset master；删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件；用于第一次进行搭建主从库时，进行主库binlog初始化工作
reset slave；用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件
查看主从状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: my1log.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: my1log.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 154
Relay_Log_Space: 568
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: 85c6acc4-3db0-11ed-b302-000c29311164
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)
注意：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
验证：
主服务器创建数据：
create database nihao;
Query OK, 1 row affected (0.01 sec)
从服务器查看数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| nihao |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)