[系统运维]CentOS7下MySQL5.7 安装及主从复制搭建

1.下载mysql安装包（采用rpm方式安装）

https://dev.mysql.com/downloads/mysql/

2. 压缩包拷贝到Linux服务器

# 解压缩
[root@localhost ~]# tar -xvf mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar 
mysql-community-client-5.7.36-1.el7.x86_64.rpm
mysql-community-common-5.7.36-1.el7.x86_64.rpm
mysql-community-devel-5.7.36-1.el7.x86_64.rpm
mysql-community-embedded-5.7.36-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.36-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.36-1.el7.x86_64.rpm
mysql-community-libs-5.7.36-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.36-1.el7.x86_64.rpm
mysql-community-server-5.7.36-1.el7.x86_64.rpm
mysql-community-test-5.7.36-1.el7.x86_64.rpm

# CentOS7默认会安装Mariadb,需要先删除，具体操作如下
[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@localhost ~]# rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps
# 再查询，确认已被删除
[root@localhost ~]# rpm -qa | grep mariadb
[root@localhost ~]# 

3. 单台安装（主从服务器都按照同样的方式安装）

# 解压后的rpm包如下
[root@localhost ~]# ll
总用量 1066152
-rw-------. 1 root root       1407 11月  8 07:07 anaconda-ks.cfg
-rw-r--r--. 1 root root  545863680 12月  9 07:57 mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar
-rw-r--r--. 1 7155 31415  26664808 9月   8 08:34 mysql-community-client-5.7.36-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415    317808 9月   8 08:34 mysql-community-common-5.7.36-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   4118740 9月   8 08:34 mysql-community-devel-5.7.36-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  47760636 9月   8 08:34 mysql-community-embedded-5.7.36-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415  23316608 9月   8 08:34 mysql-community-embedded-compat-5.7.36-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 132191192 9月   8 08:34 mysql-community-embedded-devel-5.7.36-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   2473272 9月   8 08:34 mysql-community-libs-5.7.36-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415   1263988 9月   8 08:34 mysql-community-libs-compat-5.7.36-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 182267476 9月   8 08:34 mysql-community-server-5.7.36-1.el7.x86_64.rpm
-rw-r--r--. 1 7155 31415 125479900 9月   8 08:35 mysql-community-test-5.7.36-1.el7.x86_64.rpm

按以下顺序依次安装rpm包
# 通用组件包
[root@localhost ~]# rpm -ivh mysql-community-common-5.7.36-1.el7.x86_64.rpm 
# lib包
[root@localhost ~]# rpm -ivh mysql-community-libs-5.7.36-1.el7.x86_64.rpm 
# lib补丁包
[root@localhost ~]# rpm -ivh mysql-community-libs-compat-5.7.36-1.el7.x86_64.rpm 
# 客户端
[root@localhost ~]# rpm -ivh mysql-community-client-5.7.36-1.el7.x86_64.rpm 
# 服务器端
[root@localhost ~]# rpm -ivh mysql-community-server-5.7.36-1.el7.x86_64.rpm 
# 工具包
[root@localhost ~]# rpm -ivh mysql-community-devel-5.7.36-1.el7.x86_64.rpm 
# 安装完成后，执行以下命令初始化数据库
[root@localhost ~]# mysqld --initialize --user=mysql

# 初始化后，查看初始的临时密码（最后一行，5%C6B3,ju87/就是root用户的临时密码）
[root@localhost ~]# cat /var/log/mysqld.log 
2021-12-09T00:36:27.798664Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-12-09T00:36:29.458439Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-12-09T00:36:29.674325Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-12-09T00:36:29.766320Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0cf29288-5888-11ec-a067-000c29396f60.
2021-12-09T00:36:29.767094Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-12-09T00:36:30.284076Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-12-09T00:36:30.284110Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-12-09T00:36:30.286158Z 0 [Warning] CA certificate ca.pem is self signed.
2021-12-09T00:36:30.328119Z 1 [Note] A temporary password is generated for root@localhost: 5%C6B3,ju87/

# 启动mysql服务
[root@localhost ~]# systemctl start mysqld.service
# 查看启动状态
[root@localhost ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 四 2021-12-09 08:46:17 CST; 1min 56s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 4455 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 4437 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 4458 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─4458 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

# 服务启动后，本地通过客户端连接（密码就是上述查询出来的临时密码）
[root@localhost ~]# mysql -uroot -p5%C6B3,ju87/

# 进入后，先修改下密码，修改为：123456
mysql> set password=password('123456');

# 修改完退出
mysql> exit
Bye
# 使用新密码登录（密码可以-p，回车后再输入）
[root@localhost ~]# mysql -uroot -p123456

# 正常进入，表明密码已经修改成功，退出mysql客户端，关闭并禁用防火墙
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# systemctl disable firewalld.service

# 至此本台服务器安装完成，其余机器也按照上述方式安装；

4. 主从复制–>主库配置

# 1.主库配置文件配置
[root@localhost ~]# cd /etc
[root@localhost etc]# vim my.cnf

# 在配置文件中的[mysqld]节点下增加如下内容(最少配置)
[mysqld]
# 开启binlog，文件名为：mysql-logbin
log_bin=mysql-logbin
# 设置server-id，主从不能重复（可设置1,2..）
server-id=1

# 2.修改完成，保存退出vim，重启mysql服务
[root@localhost etc]# systemctl restart mysqld

3.登录mysql，主库给从库授权，执行如下命令
mysql> grant replication slave on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

# 配置完成，查看下主库的状态（配置中没有指定同步哪些库，默认是对所有的库进行同步）
mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| mysql-logbin.000001 |      869 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5. 主从复制–>从库配置

# 1.从库配置文件配置
[root@localhost ~]# vim /etc/my.cnf
# 在配置文件中的[mysqld]节点下增加如下内容
[mysqld]
# 设置server-id，主从不能重复（主库设置为了1，从库设置为2）
server-id=2
# relay-log的名称，如果不指定，则会使用默认值
relay_log=mysql-relaylog
# 设置从库只读
read_only=1

# 2.修改完成，保存退出vim，重启mysql服务
[root@localhost ~]# systemctl restart mysqld

# 3.登录mysql，执行如下命令
# master_log_file 和 master_log_pos 就是上述主库show master status查询出来的值；
mysql> change master to master_host='192.168.2.136',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-logbin.000001',master_log_pos=869;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

# 查看下从库状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.2.136
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-logbin.000001
          Read_Master_Log_Pos: 869
               Relay_Log_File: mysql-relaylog.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-logbin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
			下面太多省略。。。。
看到：Slave_IO_Running: Yes，Slave_SQL_Running: Yes，表明一主一从配置成；