####Centos7系统mysql5.7主从复制搭建######

定义

主从复制，是指建立一个和主数据库完全一样的数据库环境（称为从数据库），并将主库的操作行为进行复制的过程：将主数据库的DDL和DML的操作日志同步到从数据库上，然后在从数据库上对这些日志进行重新执行，来保证从数据库和主数据库的数据的一致性。

主服务器IP：192.168.100.30
从服务器IP：192.168.100.31

主服务器IP：192.168.100.30主操作：

首先要保证主从两台数据库的版本，数据库表等一致, 本教程是基于5.7， 64位的mysql数据库， 系统是centos 7， 主从均为虚拟机

Server version: 5.7.37

安装MySQL数据库
yum install mysql-community-server.x86_64  -y

主节点配置:

编译/etc/my.cnf文件的[mysqld]下加入如下配置
[root@localhost ~]# vim /etc/my.cnf

[mysqld]

server_id = 1                         #主数据库端ID号
log-bin = mysql-bin                   #开启二进制日志
binlog-do-db = mytest                 #需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
auto_increment_offset = 1             #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_increment = 1          #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
log_bin_trust_function_creators = 1   #将函数复制到slave

安装完mysql 

##在mysql的配置文件末尾添加以下内容 意为：去掉mysql数据库密码复杂权限
vim /etc/my.cnf 
plugin-load=validate_password.so
validate-password=OFF
character-set-server=utf8
innodb_file_per_table=1
保存退出

##重启mysql服务
systemctl restart mysqld

查看初始密码
grep "password" /var/log/mysqld.log

##进入mysql更改密码

[root@localhost ~]# mysql -uroot -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-log

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> set password for root@localhost = password('admin'); 
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

###配置安装从mysql数据库，登录的用户密码,权限等， slave 为登录用户名， 192.168.100.31 为从库的ip，slpass 为从服务器登录的密码，

mysql> CREATE USER 'slave'@'192.168.100.31' IDENTIFIED BY 'slpass';      ##创建用户
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.100.31';       ##分配权限
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;                                                ###刷新权限
Query OK, 0 rows affected (0.00 sec)

mysql> 

查看主节点状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |     1171 | mytest       |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> 

下来开始从节点(192.168.100.31)配置

编译/etc/my.cnf文件的[mysqld]下加入如下配置

vim /etc/my.cnf 
server_id = 2 #从节点的ID号,不能和主节点的一样
relay_log = relay-log #开启中继日志
read_only = ON #只读开启,实现读写分离


##在mysql的配置文件末尾添加以下内容 意为：去掉mysql数据库密码复杂权限
vim /etc/my.cnf 
plugin-load=validate_password.so
validate-password=OFF
character-set-server=utf8
innodb_file_per_table=1
保存退出

##重启mysql服务
systemctl restart mysqld

查看初始密码
grep "password" /var/log/mysqld.log

##进入mysql更改密码
mysql -uroot -p 

mysql> set password for root@localhost = password('admin'); 
Query OK, 0 rows affected, 1 warning (0.00 sec)

#在从库添加：设置复制的主节点数据库, 从库链接主从 。 其中mysql-bin.000004', MASTER_LOG_POS=1171;来源于主库（show master status;）

mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.30', MASTER_USER='slave', MASTER_PASSWORD='slpass', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=1171;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

开启主从复制并查看状态: （注从库执行）
登录mysql后执行：START SLAVE;

状态查看  show slave status\G;

里面的 Slave_IO_Running 和 Slave_SQL_Running 均为 Yes ， 是已经配置成功的标识

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.100.30
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1171
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             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: 1171
              Relay_Log_Space: 521
              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: cfc312e2-f6af-11ec-bd30-000c2954213b
             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)

ERROR: 
No query specified

mysql>