Mysql主从复制

前言：

  主从复制原理就是基于二进制日志实现的，主库的二进制日志一定要开启；从库服务器IO会将主库的二进制日志拷贝到自身的中继日志上，然后数据库SQL的IO从中继日志中知晓主库做了什么操作，从库一比一复制主库的操作，从而达到数据的一致性。

主、从服务器各自安装数据库

#环境清理
[root@localhost src]# rpm -qa | grep -i mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@localhost src]# rpm -qa | grep -i mysql
[root@localhost src]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps
[root@localhost src]# rm -rf /etc/my.cnf

#解压安装包
[root@localhost src]# tar zxvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# mv mysql-5.7.39-linux-glibc2.12-x86_64 /usr/local/mysql5.7

#创建mysql用户、目录并授权目录
[root@localhost src]# useradd -r -s /sbin/nologin mysql
[root@localhost src]# mkdir -p /data/mysql57/data
[root@localhost src]# mkdir -p /data/mysql57/log
[root@localhost src]# chown -R mysql.mysql /data
[root@localhost src]# chown -R mysql.mysql /usr/local/mysql5.7/

#进入安装目录，创建配置文件
[root@localhost src]# cd ..
[root@localhost local]# cd mysql5.7/
[root@localhost mysql5.7]# vim my.cnf
[client]
port=3306
socket=/tmp/mysql57.sock

[mysqld]
socket=/tmp/mysql57.sock
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3306
basedir=/usr/local/mysql5.7/
datadir=/data/mysql57/data
tmpdir=/tmp
log-error=/data/mysql57/log/mysqld57.log
pid-file=/data/mysql57/mysqld57.pid

#初始化数据库
[root@localhost mysql5.7]# /usr/local/mysql5.7/bin/mysqld --defaults-file=/usr/local/mysql5.7/my.cnf --initialize --user=mysql --basedir=/usr/local/mysql5.7/ --datadir=/data/mysql57/data

#修改启动脚本（根据你实际情况，写入mysql的数据目录、安装目录、配置文件路径）
[root@localhost mysql5.7]# vim /usr/local/mysql5.7/support-files/mysql.server
#安装目录
basedir='/usr/local/mysql5.7'
#数据目录
datadir='/usr/local57/data'
#配置文件路径
conf=/usr/local/mysql5.7/my.cnf

#拷贝启动脚本
[root@localhost mysql5.7]# cp -a ./support-files/mysql.server /etc/init.d/mysql57
[root@localhost mysql5.7]# /etc/init.d/mysql57 start
Starting MySQL. SUCCESS! 

#获取数据库的临时root密码
[root@localhost mysql5.7]# cat /data/mysql57/log/mysqld57.log  | grep password
2022-10-25T02:04:02.438103Z 1 [Note] A temporary password is generated for root@localhost: g&Us;SGfu9RL

[root@localhost mysql5.7]# ln -s /usr/local/mysql5.7/bin/mysql /usr/local/bin/

#报错
[root@localhost mysql5.7]# mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

#需要指定mysql.sock的路径,输入临时密码
[root@localhost mysql5.7]# mysql -S /tmp/mysql57.sock -uroot -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39
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> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye

#检验密码是否修改成功
[root@localhost mysql5.7]# mysql -S /tmp/mysql57.sock -uroot -p 
Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.39 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> quit
Bye

mysql指定sock优化

#新建别名，添加以下一句话alias mysql57='mysql -S /tmp/mysql57.sock'
[root@localhost ~]# vim .bashrc 
[root@localhost ~]# cat .bashrc 
# .bashrc
# User specific aliases and functions
alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'
alias mysql57='mysql -S /tmp/mysql57.sock'
# Source global definitions
if [ -f /etc/bashrc ]; then
    . /etc/bashrc
fi

[root@localhost ~]# source .bashrc

#无需再手动指定sock路径
[root@localhost ~]# mysql57 -uroot -p
Enter password: 
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.

二、主库相关配置

主库配置文件：

#主从配置
#开启二进制日志
log-bin=/data/mysql57/log/bin/mybin.log

#设置二进制日志使用内存大小
binlog_cache_size=1M

#设置使用二进制的日志格式
binlog_format=row

#二进制日志过期清理时间
expire_logs_days=7

#跳过主从复制遇中遇到的1062问题，1062错误是指一些主键重复，1032是主从数据库数据不一致导致的
slave_skip_errors=1062

#mysql服务器ID，需保证唯一
server-id=1

#是否只读，1代表只读，0代表读写
read-only=0

#管理员账户也只能只读
#super-read-only=1

#不需要备份的数据库
binlog-ignore-db=mysql

#指定同步的数据库
#binlog-do-db=数据库名

主库创建远程连接用户，并授予相关主从复制权限

#新建一个slave用户，运行在192.168.140的网段连接
mysql> create user 'slave'@'192.168.140.%' identified by "Qwe.12345";
Query OK, 0 rows affected (0.00 sec)

#授予slave用户的主从复制相关权限
mysql> grant replication slave on *.* to 'slave'@'192.168.140.%';
Query OK, 0 rows affected (0.01 sec)

查看主库的pos节点（记录下来，后续需要用到，非常重要）

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mybin.000003 |      879 |              | mysql            |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

三、从库相关的配置

从库的配置文件

[root@localhost relay]# cat /usr/local/mysql5.7/my.cnf 
[client]
port=3306
socket=/tmp/mysql57.sock
[mysqld]
socket=/tmp/mysql57.sock
character-set-server=utf8
collation-server=utf8_general_ci
skip-name-resolve
user=mysql
port=3306
basedir=/usr/local/mysql5.7/
datadir=/data/mysql57/data
tmpdir=/tmp
log-error=/data/mysql57/log/mysqld57.log
pid-file=/data/mysql57/mysqld57.pid

#主从配置
#mysql服务ID，整个集群唯一性
server-id=2

#是否只读,1代表只读,0代表读写
read-only=1

#开启中继日志
relay-log=/data/mysql57/log/relay/myrelay.log

#保证了relay-log的完整性
relay-log-recovery=1

#binlog日志写入系统缓存，保证安全性
sync-relay-log=1

重启数据库：

[root@localhost relay]# /etc/init.d/mysql57 restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

mysql8.0.23数据库操作：

CHANGE REPLICATION SOURCE TO SOURCE_HOST='主库地址', SOURCE_USER='连接主库的用户', SOURCE_PASSWORD='密码', SOURCE_LOG_FILE='二进制日志文件名', SOURCE_LOG_POS=二进制日志文件的pod点;
START REPLICA;
SHOW REPLICA STATUS\G;

#进入数据库添加以下语句
mysql> change master to master_host='192.168.140.130',master_user='slave',master_password='QWE.12345',master_log_file='mybin.000003',master_log_pos=879;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.140.130
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mybin.000003
          Read_Master_Log_Pos: 879
               Relay_Log_File: localhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mybin.000003
             Slave_IO_Running: Connecting
            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: 879
              Relay_Log_Space: 154
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'slave@192.168.140.130:3306' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 0
                  Master_UUID: 
             Master_Info_File: /data/mysql57/data/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: 221025 22:50:25
     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)

ERROR: 
No query specified

显示正在连接中，主库的防火墙和selinux是否关闭？请确认！（生成环境下请放行）

     Slave_IO_Running: Connecting
     Slave_SQL_Running: Yes

手动连接测试；显示成功

[root@localhost ~]# mysql57 -h 192.168.140.130 -uslave -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
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.

关闭防火墙和selinux，最终测试还是不行？还是显示正在连接中！怎么办？莫慌，去查看日志！

从库错误日志；没有发现什么有价值的线索！

[root@localhost log]# tail -5 /data/mysql57/log/mysqld57.log 
2022-10-26T03:10:32.842608Z 1 [ERROR] Slave I/O for channel '': error connecting to master 'slave@192.168.140.130:3306' - retry-time: 60  retries: 11, Error_code: 1045

主库错误日志；怎么拒绝从服务器访问呢？密码不对吗？查看之前步骤，还真是密码大小写问题！（服了）

[root@localhost log]# tail -5 /data/mysql57/log/mysqld57.log
2022-10-26T02:54:25.374182Z 4 [Note] Access denied for user 'slave'@'192.168.140.132' (using password: YES)

重新配置salve

#停掉slave
mysql> stop slave;Query OK, 0 rows affected (0.00 sec)

#删除slave之前的配置
mysql> reset master;Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host='192.168.140.130',master_user='slave',master_password='Qwe.12345',master_log_file='mybin.000003',master_log_pos=879;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)


#最终查看
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.140.130
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mybin.000003
          Read_Master_Log_Pos: 879
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 316
        Relay_Master_Log_File: mybin.000003
             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: 879
              Relay_Log_Space: 527
              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: 4bbdd90f-5409-11ed-8f4a-000c2976723d
             Master_Info_File: /data/mysql57/data/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)

ERROR: 
No query specified

两个都是显示yes代表成功：          

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

关于中继日志，原本主从复制必须要有中继日志，很多人都没有指定开启，这次特意没有主动指定开启中继日志，但是它会自己生成中继日志

Relay_Log_File: localhost-relay-bin.000002

得知中继日志名字，查找日志所在路径

[root@localhost log]# find / -name 'localhost-relay-bin.000002'
/data/mysql57/data/localhost-relay-bin.000002
#所以为了方便管理，请手动开启并指定中继日志文件路径以及名字

四、最终测试

从库查询：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

主库建库建表，插入数据：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ldx                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

#创建数据库
mysql> create database student charset utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use student;
Database changed
mysql> create table stu (
    -> id int primary key auto_increment,
    -> sname varchar(10) not null default '',
    -> gender char(1) not null default '',
    -> company varchar(20) not null default '',
    -> salary decimal(6,2) not null default 0.00
    -> )engine myisam charset utf8;
Query OK, 0 rows affected (0.01 sec)

#查看表结构：
mysql> desc stu;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| sname   | varchar(10)  | NO   |     |         |                |
| gender  | char(1)      | NO   |     |         |                |
| company | varchar(20)  | NO   |     |         |                |
| salary  | decimal(6,2) | NO   |     | 0.00    |                |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.06 sec)

#插入数据
mysql> insert into stu 
    -> values
    -> (01,'张三','男','百度',8888.88);
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu 
    -> values
    -> (02,'李四','女','新浪',7000.98),
    -> (03,'刘备','男','皇族后裔',2500);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into stu 
    -> (sname,gender,company)
    -> values
    -> ('孙策','男','江东集团');
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+----+--------+--------+--------------+---------+
| id | sname  | gender | company      | salary  |
+----+--------+--------+--------------+---------+
|  1 | 张三   | 男     | 百度         | 8888.88 |
|  2 | 李四   | 女     | 新浪         | 7000.98 |
|  3 | 刘备   | 男     | 皇族后裔     | 2500.00 |
|  4 | 孙策   | 男     | 江东集团     |    0.00 |
+----+--------+--------+--------------+---------+
4 rows in set (0.00 sec)

最终结果图：

主库图：

从库图：