亲宝软件园·资讯

展开

Centos 7 安装Mysql8 主从同步复制

Yee.Liu 人气:1

环境:Centos 7 

软件:Mysql8 

安装方式:Yum

1.从官网下载最新yum 源对应Cenots 7 版本安装;

[root@DataNode-03 ~]# yum -y localinstall mysql80-community-release-el7-3.noarch.rpm 
Loaded plugins: fastestmirror
Examining mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch
Marking mysql80-community-release-el7-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-3 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================================================================================================
 Package                                        Arch                        Version                      Repository                                                    Size
============================================================================================================================================================================
Installing:
 mysql80-community-release                      noarch                      el7-3                        /mysql80-community-release-el7-3.noarch                       31 k

Transaction Summary
============================================================================================================================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-3.noarch                                                                                                                   1/1 
  Verifying  : mysql80-community-release-el7-3.noarch                                                                                                                   1/1 

Installed:
  mysql80-community-release.noarch 0:el7-3                                                                                                                                  

Complete!
[root@DataNode-03 ~]# 

2,更新本地yum 源,由于我刚安装更新过,所有此处未重新更新;

[root@DataNode-03 ~]# yum -y update
Loaded plugins: fastestmirror
Determining fastest mirrors
base                                                                                                                                                 | 3.6 kB  00:00:00     
epel                                                                                                                                                 | 5.3 kB  00:00:00     
extras                                                                                                                                               | 2.9 kB  00:00:00     
mysql-connectors-community                                                                                                                           | 2.5 kB  00:00:00     
mysql-tools-community                                                                                                                                | 2.5 kB  00:00:00     
mysql80-community                                                                                                                                    | 2.5 kB  00:00:00     
updates                                                                                                                                              | 2.9 kB  00:00:00     
(1/10): epel/x86_64/updateinfo                                                                                                                       | 1.0 MB  00:00:00     
(2/10): epel/x86_64/group_gz                                                                                                                         |  90 kB  00:00:00     
(3/10): base/7/x86_64/primary_db                                                                                                                     | 6.0 MB  00:00:00     
(4/10): base/7/x86_64/group_gz                                                                                                                       | 165 kB  00:00:00     
(5/10): epel/x86_64/primary_db                                                                                                                       | 6.7 MB  00:00:00     
(6/10): mysql-connectors-community/x86_64/primary_db                                                                                                 |  53 kB  00:00:00     
(7/10): updates/7/x86_64/primary_db                                                                                                                  | 6.7 MB  00:00:00     
(8/10): mysql-tools-community/x86_64/primary_db                                                                                                      |  69 kB  00:00:00     
(9/10): mysql80-community/x86_64/primary_db                                                                                                          |  97 kB  00:00:00     
(10/10): extras/7/x86_64/primary_db                                                                                                                  | 159 kB  00:00:00     
No packages marked for update
[root@DataNode-03 ~]#

3.安装Mysql8,选择安装server ,client ,libs,devel,common

[root@DataNode-03 ~]# yum -y install mysql-community-{common,libs,libs-compat,client,server,devel}
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Package mysql-community-common-8.0.19-1.el7.x86_64 already installed and latest version
Package mysql-community-libs-8.0.19-1.el7.x86_64 already installed and latest version
Package mysql-community-libs-compat-8.0.19-1.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:8.0.19-1.el7 will be installed
---> Package mysql-community-devel.x86_64 0:8.0.19-1.el7 will be installed
---> Package mysql-community-server.x86_64 0:8.0.19-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

============================================================================================================================================================================
 Package                                          Arch                             Version                                Repository                                   Size
============================================================================================================================================================================
Installing:
 mysql-community-client                           x86_64                           8.0.19-1.el7                           mysql80-community                            41 M
 mysql-community-devel                            x86_64                           8.0.19-1.el7                           mysql80-community                           7.1 M
 mysql-community-server                           x86_64                           8.0.19-1.el7                           mysql80-community                           436 M

Transaction Summary
============================================================================================================================================================================
Install  3 Packages

Total download size: 484 M
Installed size: 2.2 G
Downloading packages:
(1/3): mysql-community-devel-8.0.19-1.el7.x86_64.rpm                                                                                                 | 7.1 MB  00:00:00     
(2/3): mysql-community-client-8.0.19-1.el7.x86_64.rpm                                                                                                |  41 MB  00:00:05     
(3/3): mysql-community-server-8.0.19-1.el7.x86_64.rpm                                                                                                | 436 MB  00:00:38     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                        12 MB/s | 484 MB  00:00:38     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-client-8.0.19-1.el7.x86_64                                                                                                               1/3 
  Installing : mysql-community-server-8.0.19-1.el7.x86_64                                                                                                               2/3 
  Installing : mysql-community-devel-8.0.19-1.el7.x86_64                                                                                                                3/3 
  Verifying  : mysql-community-server-8.0.19-1.el7.x86_64                                                                                                               1/3 
  Verifying  : mysql-community-client-8.0.19-1.el7.x86_64                                                                                                               2/3 
  Verifying  : mysql-community-devel-8.0.19-1.el7.x86_64                                                                                                                3/3 

Installed:
  mysql-community-client.x86_64 0:8.0.19-1.el7             mysql-community-devel.x86_64 0:8.0.19-1.el7             mysql-community-server.x86_64 0:8.0.19-1.el7            

Complete!
[root@DataNode-03 ~]#

 

4.配置主服务器my.cnf 文件,注意由于是自己设置数据目录与日志目录,需要自己创建文件夹与配置文件夹权限

[root@DataNode-03 ~]# mkdir /var/log/mysql /Data/mysql/ -p
[root@DataNode-03 ~]# chown mysql:mysql /var/log/mysql /Data/mysql/ -R
[root@DataNode-03 ~]#
[root@DataNode-03 ~]#more /etc/my.cnf
# For advice on how to change settings please see
# http:/https://img.qb5200.com/download-x/dev.mysql.comhttps://img.qb5200.com/download-x/doc/refman/8.0/en/server-configuration-defaults.html


[client]
port = 3306
socket=/Data/mysql/mysql.sock

[mysqld]
port = 3306
socket=/Data/mysql/mysql.sock
datadir=/Data/mysql/
pid-file=/var/run/mysqld/mysqld.pid
user = mysql
bind-address = 0.0.0.0

#配置主从
server-id = 1 #表示是本机的序号为1,一般来讲就是master的意思
log-bin=mysql-bin
log_bin_index=master-bin.index
binlog_do_db=testdb
sync_binlog=1
#binlog_ignore_db 指定不记录二进制日志的数据库,即不需要复制的数据库名,如果有多个数据库,重复设置这个选项即可。

5.启动主服务器mysql,与重置root 密码;

[root@DataNode-03 ~]# cat /var/log/mysqld.log | grep password      
2020-03-01T08:46:29.290171Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: F33er/Gl>8X1
[root@DataNode-03 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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 'rZShG8*J%$0S&*@$7!y*$w4Z';
Query OK, 0 rows affected (0.01 sec)

mysql> 

6.配置主服务器同步使用的账号密码,此处有坑后面讲,

mysql> create user 'top_slave'@'%' identified by 'rZShG8*J%$0S&*@$7!y*$w4Z';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'top_slave'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

 

7. 查看主服务器master 状态,稍等从服务器同步使用使用到;

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

mysql> 

8. 主从服务器开通防火墙;

[root@DataNode-03 ~]# systemctl restart firewalld
[root@DataNode-03 ~]#  firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@DataNode-03 ~]# systemctl restart firewalld                                
[root@DataNode-03 ~]# 

9.从服务器my.cnf 需要配置,但运行目录文件夹需要创建跟主服务器,重置root 密码方式一样;

[root@tophad-Manager-02 ~]# more /etc/my.cnf
# For advice on how to change settings please see
# http:/https://img.qb5200.com/download-x/dev.mysql.comhttps://img.qb5200.com/download-x/doc/refman/8.0/en/server-configuration-defaults.html


[client]
port = 3306
socket=/Data/mysql/mysql.sock

[mysqld]
port = 3306
socket=/Data/mysql/mysql.sock
datadir=/Data/mysql/
pid-file=/var/run/mysqld/mysqld.pid
user = mysql
bind-address = 0.0.0.0

#配置主从
server-id = 2 #表示是本机的序号为1,一般来讲就是master的意思
log-bin=mysql-bin
log_bin_index=master-bin.index
replicate_do_db=topdb
replicate_do_db=mysql
replicate_do_db=sys
sync_binlog=1

 

10.配置从服务器同步主服务器信息;

mysql> change master to
    -> master_host='172.31.209.147',
    -> master_port=3306,
    -> master_user='top_slave',
    -> master_password='rZShG8*J%$0S&*@$7!y*$w4Z',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=1183,
    -> MASTER_SSL=1,
    -> master_delay=0;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.209.147
                  Master_User: top_slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2035
               Relay_Log_File: tophad-Manager-02-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: topdb,mysql,sys
          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: 2035
              Relay_Log_Space: 542
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           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: a9796edf-5b82-11ea-9db0-00163e083c39
             Master_Info_File: mysql.slave_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: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql>

 

11.以上同步成功,测试创建一个数据库就好了,主服务器创建数据库,我这里就只查从服务器信息

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

mysql>

 

踩坑报告解决信息;

12. 坑 的信息,Last_IO_Error: error connecting to master 'top_slave@172.31.209.151:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication 

#二个解决方式;
一,从服务服务器同步更换成:
change master to
master_host='172.31.209.151',
master_port=3306,
master_user='top_slave',
master_password='rZShG8*J%$0S&*@$7!y*$w4Z',
master_log_file='mysql-bin.000002',
master_log_pos=1183,
MASTER_SSL=1,   #增加ssl
master_delay=0;

二,在主服务器更换密码认证插件,因为Mysql 认证与5.7 不一样,使用ssl 认证

ALTER USER 'top_slave'@'%' IDENTIFIED WITH mysql_native_password BY 'rZShG8*J%$0S&*@$7!y*$w4Z'; 

 

13 . Last_IO_Error: error connecting to master 'top_slave@172.31.209.147:3306' - retry-time: 60 retries: 1 message: Access denied for user 'top_slave'@'172.31.139.94' (using password: YES)

此错误信息需注意了:短测试错误

测试更换:

一,使用短密码没用,无法一样报这个错;

二,更换密码安全级别,set global validate_password.policy=LOW;  无用一样出现;

三,解决需注意密码复试性跟长度;

 

以上测试,如配置出现问题  正常同步成功

 

加载全部内容

相关教程
猜你喜欢
用户评论