Msyql 主从同步

前言

为什么要进行主从复制呢？

• 数据备份
• 读写分离，减轻主库压力

搭建

主库

• 准备mysql安装包

	mysql-8.0.26.tar.gz

• 解压安装包

 tar -zxvf mysql-8.0.26.tar.gz

• 添加环境变量

# 编辑环境变量文件
sudo vim /etc/profile#在末尾添加内容 
export MYSQL_HOME=/softwares/mysql-8.0.26/
export PATH=$PATH:$MYSQL_HOME/bin#将环境变量生效
source /etc/profile

• 创建文件夹

sudo mkdir -p /softwares/mysql-8.0.26/data
sudo mkdir -p /softwares/mysql-8.0.26/binlog

• 创建用户

sudo groupadd mysql
sudo useradd -g mysql mysql
sudo chown -R mysql:mysql /softwares/mysql-8.0.26

• 修改my.cnf 配置文件

[mysqld]
basedir=/softwares/mysql-8.0.26
datadir=/softwares/mysql-8.0.26/data
socket=/softwares/mysql-8.0.26/mysql.sock
log-error=/softwares/mysql-8.0.26/mysqld.log
symbolic-links=0 
secure_file_priv=''
server-id=2 # 服务id，必填，且跟从库不一样
log-bin=/softwares/mysql-8.0.26/binlog/mysql-bin
binlog-ignore-db=mysql # 忽略需要记录的数据库
binlog-do-db=test_db # 指定需要记录的数据库[mysqld_safe]
log-error=/softwares/mysql-8.0.26/mysqld.log
pid-file=/softwares/mysql-8.0.26/mysqld.pid [client]
port=3306
socket=/softwares/mysql-8.0.26/mysql.sock

• 初始化数据库

sudo su - root -c '/softwares/mysql-8.0.26/bin/mysqld --initialize --user=mysql --basedir=/softwares/mysql-8.0.26/ --datadir=/softwares/mysql-8.0.26/data/'

• 启动服务

sudo su - mysql -c '/softwares/mysql-8.0.26/bin/mysqld_safe --user=mysql &'

• 查看并修改初始密码

# 查看初始密码
sudo cat /softwares/mysql-8.0.26/mysqld.log |grep "temporary password" |awk -F "root@localhost: " '{print $2}'
# 登录，输入上一步获取的密码
mysql -uroot -p# 修改密码
mysql> alter user root@localhost identified by 'Bigdata_123';
mysql> create user 'root'@'%' identified by 'Bigdata_123';
mysql> GRANT ALL PRIVILEGES on *.* to 'root'@'%';
mysql> GRANT ALL PRIVILEGES on *.* to 'root'@'localhost';
mysql> flush privileges;

从库

与主库基本一致，主要在my.cnf 上有一点不一样

[mysqld]
basedir=/softwares/mysql-8.0.26
datadir=/softwares/mysql-8.0.26/data
socket=/softwares/mysql-8.0.26/mysql.sock
log-error=/softwares/mysql-8.0.26/mysqld.log
symbolic-links=0 
secure_file_priv=''
server-id=9 # 与主库不一致
replicate-do-db=test_db # 需要同步的数据库
replicate-ignore-db=mysql # 不需要同步的数据库
sync_binlog=1
slave-net-timeout=60 # 当收不到master数据时，重新发起连接，时间默认是60s[mysqld_safe]
log-error=/softwares/mysql-8.0.26/mysqld.log
pid-file=/softwares/mysql-8.0.26/mysqld.pid [client]
port=3306
socket=/softwares/mysql-8.0.26/mysql.sock

测试

主库

• 准备数据库

# 创建数据库
create database test_db character set utf8;
# 选择数据库
use test_db;
# 创建表
CREATE TABLE grade(
gradeid INT(10) PRIMARY KEY AUTO_INCREMENT, 
gradename VARCHAR(50) NOT NULL
);
# 插入数据
insert into grade (GradeID,GradeName) values (1,'大一'),(2,'大二'),(3,'大三'),(4,'大四');
# 查看数据
select * from grade

• dump数据

mysqldump -uroot -pBigdata_123 -h127.0.0.1 --all-databases --single-transaction --routines --events --triggers --master-data=2 --hex-blob --default-character-set=utf8mb4 --flush-logs --quick > all.sql

• 查看当前binlog信息

mysql> show master status\G;
*************************** 1. row ***************************File: mysql-bin.000005Position: 156Binlog_Do_DB: Binlog_Ignore_DB: mysql
Executed_Gtid_Set: 
1 row in set (0.00 sec)

• 复制all.sql数据到从库，目标文件夹是/tmp

scp all.sql root@slave:/tmp/

从库

• 恢复dump数据

# 登录数据库
mysql -uroot -pBigdata_!23
# 导入sql数据
source /tmp/all.sql

• 连接主库

# 登录客户端，通过该方式可避免复制时的错误
mysql -uroot -pBigdata_123 -h slave1 -P3306 --get-server-public-key# 错误信息：
Last_IO_Errno:2061
Last_IO_Error:error connecting to mater 'root@master:3306' - retry-time:60 retries:1 massage:Authentication plugin 'caching_sha2_password' reported error:Authentication require secure connection.# 停止同步
stop slave
# 连接到主库
change master to master_host="master",master_user="root",master_password="Bigdata_123",master_log_file="mysql-bin.000005" ,master_log_pos=156;
# 开启同步
start slave

• 查看同步信息

mysql> show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: masterMaster_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 156Relay_Log_File: slave1-relay-bin.000006Relay_Log_Pos: 371Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: test_dbReplicate_Ignore_DB: mysqlReplicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 156Relay_Log_Space: 628Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2Master_UUID: 7ff3e0e4-584f-11ef-baa2-0242ac110013Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace: 
1 row in set, 1 warning (0.01 sec)

问题

是推还是拉

slave先主动去拉，发送binlog文件名和pos位置，master推送该位置之后的数据，
同时建立好长链接，当master有数据更新时，推送给slave

链接断了怎么办

slave会主动重新建立连接，超时时间默认60s，可配置

复制策略有哪些

• 异步复制
  效率上相对最高，也是mysql默认方式，master只管写入binlog就返回
• 半同步复制
  等到有一个salve返回同步成功后，master再响应，需要安装插件来实现
• 同步复制
  等全部salve返回成功后，master再响应，需要安装插件来实现