Centos 7 安装Mysql8 主从同步复制
环境：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://dev.mysql.com/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://dev.mysql.com/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; 无用一样出现；
三，解决需注意密码复试性跟长度；
以上测试，如配置出现问题 正常同步成功
