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.000005
         Position: 156
     Binlog_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 event
                  Master_Host: master
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 156
               Relay_Log_File: slave1-relay-bin.000006
                Relay_Log_Pos: 371
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test_db
          Replicate_Ignore_DB: mysql
           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: 156
              Relay_Log_Space: 628
              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: 2
                  Master_UUID: 7ff3e0e4-584f-11ef-baa2-0242ac110013
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica 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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_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再响应，需要安装插件来实现