@TOC

一、部署前准备

1.开放3306端口或直接关闭防火墙

因为MySQL主从之间需要通信,所以必须开放各自的端口,默认情况下是3306端口

①查看防火墙状态

firewall-cmd --state

②开放3306端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

③重新加载防火墙规则

firewall-cmd --reload

④查看已开放端口

firewall-cmd --list-ports

2.下载并上传MySQL安装包到服务器

下载地址:https://downloads.mysql.com/archives/community/
在这里插入图片描述
在这里插入图片描述

⑥创建MySQL安装目录并解压

我这边是在公司服务器上安装,考虑到数据库需要的存储空间比较大,所以挂载了一块磁盘,将整块盘都给MySQL用了,所以安装目录是根目录下的/mysql,各位读者可以随意在哪建目录都行
解压:tar -zxvf mysql-5.7.31-el7-x86_64.tar.gz -C /mysql

3.添加MySQL用户和组

userdel mysql   
groupadd mysql
useradd -g mysql mysql
passwd mysql 
mkdir -p /mysql/data   # 数据目录
chown -R mysql:mysql /mysql

二、开始部署

切换到mysql用户下部署,当然用root用户也可以

su - mysql
cd /mysql

1.创建my.cnf文件

!!!!
主从的配置文件唯一区别就是server-id
我这里设置主为1,从为2

vim my.cnf

[client]
socket=/mysql/data/mysql.sock
[mysqld]
server-id=1
port = 3306
basedir=/mysql/mysql
datadir=/mysql/data
socket=/mysql/data/mysql.sock
log-error=/mysql/data/mysqld.log
pid-file=/mysql/data/mysqld.pid
log-bin=/mysql/data/binlog
slow_query_log_file = /mysql/data/slow.log
log_slave_updates
expire_logs_days=7
binlog_format=ROW
#default_authentication_plugin=mysql_native_password    //修改密码加密方式
gtid-mode = on  
enforce-gtid-consistency = 1  
skip_slave_start=1  
innodb_buffer_pool_size   = 1000M                           
innodb_buffer_pool_instances = 2                          
innodb_log_file_size      = 100M                           
innodb_log_files_in_group = 3                             
innodb_log_buffer_size    = 24M                          
innodb_flush_log_at_trx_commit = 1                        
innodb_file_per_table     = 1                             
innodb_flush_method       = O_DIRECT                      
innodb_io_capacity        = 200                           
innodb_io_capacity_max    = 600                           
innodb_thread_concurrency = 0                             
innodb_autoinc_lock_mode  = 2                             
innodb_lock_wait_timeout  = 60                            
innodb_read_io_threads    = 4                 
innodb_write_io_threads   = 4                                  
innodb_max_dirty_pages_pct = 80                                
innodb_autoextend_increment = 512                              
innodb_checksum_algorithm = NONE   
max_connect_errors        = 1000                            
max_connections           = 500                                
connect_timeout           = 300                                
wait_timeout              = 86400                              
interactive_timeout       = 86400

2.配置环境

# 用root用户
rm -rf /etc/my.cnf
ln -s /mysql/my.cnf  /etc/my.cnf
ln -s /mysql/mysql-5.7.31-el7-x86_64 /mysql/mysql
cp /mysql/mysql/support-files/mysql.server /etc/init.d/mysql
# 令service可以启动mysql
chkconfig --add mysql
# mysql开机启动
chkconfig mysql on 

# 切换到mysql用户
# 配置环境变量
vim ~/.bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs
MYSQL_HOME=/mysql/mysql
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin

export PATH

:wq
#保存后刷新配置
source ~/.bash_profile

3.初始化MySQL

mysqld --defaults-file=/mysql/my.cnf --user=mysql --basedir=/mysql/mysql/ --datadir=/mysql/data/ --initialize

4.启动MySQL

mysqld_safe --defaults-file=/mysql/my.cnf --user=mysql &

登录MySQL

[mysql@192 mysql]$ grep "password" /mysql/data/mysqld.log 
2021-03-04T13:22:10.881586Z 1 [Note] A temporary password is generated for root@localhost: z8h-MF,lx!0L
[mysql@192 mysql]$ mysql -u root -p 
Enter password: # 输入上面root@localhost: 后的临时密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31-log

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 > set password for root@'localhost' = '你的密码';    
# 创建远程用户
mysql > create user 'root'@'%' identified by '你的密码';   
#设置授权
mysql > grant all privileges on *.* to 'root'@'%' with grant option;  

5.配置主库

# 创建用于主从复制的用户  这里'从服务器ip地址'也可以是'%'代表所有ip
mysql> CREATE USER 'repl'@'从服务器ip地址' IDENTIFIED WITH mysql_native_password BY '密码';
# 赋权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从服务器ip地址'; 
# 刷新权限
mysql> flush PRIVILEGES; 
# 查看master状态
mysql> show master status; 
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |     1471 |              |                  | a35148ef-7cec-11eb-8e8e-000c29c5e6ab:1-6 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

记下binlog.0000021471

6.配置从库

# 连接主库
mysql> CHANGE MASTER TO
    -> MASTER_HOST='主服务器ip地址',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='密码',
    -> MASTER_LOG_FILE='binlog.000002',
    -> MASTER_LOG_POS=1471;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
# 开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
# 查看从库状态,两个yes代表部署成功
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.19.36
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1471
               Relay_Log_File: 192-relay-bin.000002
                Relay_Log_Pos: 317
        Relay_Master_Log_File: binlog.000002
             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: 1471
              Relay_Log_Space: 522
              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: a35148ef-7cec-11eb-8e8e-000c29c5e6ab
             Master_Info_File: /mysql/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: 9fd95dd6-7cec-11eb-8f02-000c29927484:1-3
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

mysql> 

7.测试主从

# 主库执行
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

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

# 从库执行
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

成功!

三、管理MySQL

查看状态

service mysql status

关闭MySQL

service mysql stop

开启MySQL

第一次启动需要用mysqld_safe --defaults-file=/mysql/my.cnf --user=mysql &
后面都可以用service mysql start

四、其他故障

mysql -uroot -p登录输入密码之后有可能会提示
Can't connect to local MySQL server through socket '/tmp/mysql.sock'
这时可以用root用户建个软链接
ln -s /mysql/data/mysql.sock /tmp/mysql.sock

其他问题欢迎留言

Q.E.D.