常规安装

1. 下载：地址https://dev.mysql.com/downloads/mysql/5.7.html#downloads
2. 选择对应5.7的版本，下载并解压到指定目录 tar -xvf mysql-5.7.32-linux-glibc2.12-x86_64.tar(在进一步-zxvf解压后面的文件) 我安装在：/data/util/mysql/mysql57

3. 创建mysql配置文件 vim /etc/my.cnf

   [mysqld]
   bind-address=0.0.0.0
   port=3306
   user=mysql
   #安装目录
   basedir=/data/util/mysql/mysql57
   #数据目录
   datadir=/data/util/mysql/mysqlData
   socket=/tmp/mysql.sock
   log-error=/data/util/mysql/mysqlData/mysql.err
   pid-file=/data/util/mysql/mysqlData/mysql.pid
   #character config
   character_set_server=utf8mb4
   symbolic-links=0
   explicit_defaults_for_timestamp=true

4. 创建mysql用户组和用户并修改权限

   groupadd mysql
   useradd -r -g mysql mysql
   chown mysql:mysql -R /data/util/mysql/mysqlData/

5. 进入mysql的bin目录 cd /data/util/mysql/mysql57/bin 初始化加载配置，

   ./mysqld --defaults-file=/etc/my.cnf --basedir=/data/util/mysql/mysql57 --datadir=/data/util/mysql/mysqlData --user=mysql --initialize

   此时便会在mysqlData文件夹中生成一堆文件
   

6. 查看密码 cat /data/util/mysql/mysqlData/mysql.err,将临时密码保存，后面在第一次登录修改密码的时候用的到

1. 因为mysql的默认安装地址是/usr/local/mysql，而我们的目录是在/data/util/mysql/mysql57,所以需要对mysql的安装目录个数据目录进行一个修改，cd /data/util/mysql/mysql57/support-files 启动文件的配置。

2. 先将mysql.server放置到/etc/init.d/mysql中

   cp /data/util/mysql/mysql57/support-files/mysql.server   /etc/init.d/mysql

3. 进入mysql的安装目录的bin目录启动

   service mysql start  #stop是通知

4. 启动成功后，对密码进行修改

   ./mysql -u root -p

   输完密码后看等否登录（show databases;看下是否有多个数据库名）
   备注：停止Mysql服务：进入/usr/bin 命令：./mysqladmin -uroot -p shutdown 输入密码 ```bash

SET PASSWORD = PASSWORD(‘123456’); ALTER USER ‘root’@’localhost’ PASSWORD EXPIRE NEVER; FLUSH PRIVILEGES;

use mysql #访问mysql库 update user set host = ‘%’ where user = ‘root’; #使root能再任何host访问 FLUSH PRIVILEGES;

如果是云服务器请在安全组开放其端口

参考链接地址：[https://blog.csdn.net/qq_37598011/article/details/93489404](https://blog.csdn.net/qq_37598011/article/details/93489404)
<a name="FcBp1"></a>
# docker-compose安装
1. 编写docker-compose.yml文件

version: ‘3.1’ services: mysql: image: mysql:5.7 #到dockerhub找到你要的镜像版本 container_name: mysql5.7 privileged: true #一定要设置为true，不然数据卷可能挂载不了，启动不起 ports:

 - 3306:3306
environment:
  MYSQL_USER: admin
  MYSQL_PASSWORD: 123456
  MYSQL_ROOT_PASSWORD: xxxx  #root用户的密码
  TZ: Asia/Shanghai
command:
  --character-set-server=utf8mb4
  --collation-server=utf8mb4_general_ci
  --explicit_defaults_for_timestamp=true
  --lower_case_table_names=1
  --max_allowed_packet=128M
  --sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"   
volumes:
  - ./data:/var/lib/mysql
  - ./conf/my.cnf:/etc/my.cnf
restart: always

version: “3” services: mysql-master: image: mysql:5.7 container_name: mysql_master ports:

  - 3307:3306
volumes:
  - ./volume/mysql-master:/var/lib/mysql
  - ./volume/etc/mysqld.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
environment:
  MYSQL_ROOT_PASSWORD: 123456
  MYSQL_USER: admin
  MYSQL_PASSWORD: 123456
restart: unless-stopped

2. 编写完成后，后台方式启动 docker-compose up -d
2. docker ps 找到容器id docker exec -it id bash 进入容器内
2. 执行以下脚本

mysql -u root -p #以root用户登陆进去，创建admin用户

create user ‘admin’@’%’ identified by ‘123456’; grant all privileges on . to ‘admin’@’%’ identified by ‘123456’; flush privileges;

################# 新建用户后，使用admin登陆

mysql -u admin -p 能成功连接即可

查看数据存储位置：show GLOBAL VARIABLES LIKE '%datadir%'   默认存放位置 /var/lib/mysql/
<a name="um00Y"></a>
### 时区问题
```bash
mysql> show variables like "%time_zone%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    |
| time_zone        | SYSTEM |
+------------------+--------+
mysql> select now(); # 查看时间是否与现在一致，不一致的话记得修改mysqld.cnf追加配置

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#追加的
default-storage-engine=INNODB
character_set_server=utf8mb4
innodb_default_row_format=DYNAMIC
innodb_large_prefix=ON
innodb_file_format=Barracuda
innodb_log_file_size=2G
sql_mode = NO_AUTO_VALUE_ON_ZERO
# 时区+8个小时
default-time_zone = '+8:00'

docker-compose安装主从数据库

1. 编写compose文件，配置相应的cnf和创建对应的挂在目录。启动 docker-compose up -d

   version: "3"
   services:
   mysql-master:
    image: mysql:5.7
    container_name: mysql_master
    ports:
      - 3307:3306
    volumes:
      - ./volume/mysql-master:/var/lib/mysql
      - ./volume/conf/mysql-ms/mysqld-master.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_DATABASE: BIG
      MYSQL_USER: admin
      MYSQL_PASSWORD: 123456
    restart: unless-stopped
   mysql-slave:
    image: mysql:5.7
    container_name: mysql_slave
    ports:
      - 3308:3306
    volumes:
      - ./volume/mysql-slave:/var/lib/mysql
      - ./volume/conf/mysql-ms/mysqld-slave.cnf:/etc/mysql/mysql.conf.d/mysqld.cnf
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_DATABASE: BIG
      MYSQL_USER: admin
      MYSQL_PASSWORD: 123456
    restart: unless-stopped

   mysqld-master.cnf

   [mysqld]
   #主数据库端ID号 唯一即可
   server_id = 1           
   #开启二进制日志                  
   log-bin = mysql-bin    
   #需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可                  
   binlog-do-db = nacos
   binlog-do-db = xxl-job
   binlog-do-db = simone
   binlog-do-db = order
   binlog-do-db = user
   #将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中                 
   log-slave-updates                        
   #控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失) 
   sync_binlog = 1                    
   #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
   auto_increment_offset = 1           
   #这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
   auto_increment_increment = 1            
   #二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除  
   expire_logs_days = 7                
   #将函数复制到slave  
   log_bin_trust_function_creators = 1

   mysqld-slave.cnf

   [mysqld]
   # 从数据库端ID号 唯一即可
   server_id = 2
   #开启二进制日志   
   log-bin = mysql-bin
   #将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中  
   log-slave-updates
   sync_binlog = 0
   #log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
   innodb_flush_log_at_trx_commit = 0 
   #指定slave要复制哪个库
   replicate-do-db = nacos
   replicate-do-db = xxl-job
   replicate-do-db = simone
   replicate-do-db = order
   replicate-do-db = user
   #MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据
   slave-net-timeout = 60
   #将函数复制到slave 
   log_bin_trust_function_creators = 1

   

2. 分别登陆主从数据库给admin赋权 ``` mysql -u admin -p #输入密码后登陆

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

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

3. 执行完成后，将我们148:3306的数据库和数据分别拷贝到新的数据库中。（148:3306是我们技术leader用的库，我自己的业务库自己维护），复制完成后，开始着手配置工作。
![image.png](https://cdn.nlark.com/yuque/0/2021/png/771792/1619143717614-3d831014-92c2-436c-b410-58c67ec5ed42.png#clientId=u73dcc51d-2d54-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=401&id=u4df1e1de&margin=%5Bobject%20Object%5D&name=image.png&originHeight=401&originWidth=329&originalType=binary&ratio=1&rotation=0&showTitle=false&size=15610&status=done&style=none&taskId=uca702ff6-0ea5-4841-a0d2-588ff2bd5a2&title=&width=329)
4. 获取mysql-master的binlog开始主从复制的地址（连接上主库输入）

mysql> show master status; +—————————+—————+———————————————————+—————————+—————————-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +—————————+—————+———————————————————+—————————+—————————-+ | mysql-bin.000005 | 154 | nacos_conf,xxl-job,simone,order,user | | | +—————————+—————+———————————————————+—————————+—————————-+ 1 row in set (0.00 sec)

5. 在从库上执行

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

mysql> change master to -> master_host=’192.168.1.148’, -> master_user=’root’, -> master_port=3307, -> master_password=’123456’, -> master_log_file=’mysql-bin.000005’, -> master_log_pos=154; Query OK, 0 rows affected, 2 warnings (0.04 sec)

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

```
mysql> show slave status\G; #查看从库的装填
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.148
                  Master_User: root
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: aa9cf29d7f05-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes #执行io线程必须为yes
            Slave_SQL_Running: Yes #执行sql线程必须为yes
              Replicate_Do_DB: nacos_conf,xxl-job,simone,order,user
          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: 154
              Relay_Log_Space: 534
              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: dcbc2673-a348-11eb-b069-0242c0a81003
             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

注意查看Slave_IO_Running | Slave_SQL_Running这两列必须为yes
本次用到的命令集合：

create user 'admin'@'%' identified by '123456';
grant all privileges on *.* to 'admin'@'%' identified by '123456';
flush privileges;
mysql -u admin -p
mysql -u root -p
grant replication slave on *.* to 'root'@'192.168.1.148' identified by '123456';
stop slave;
reset slave;
change master to 
    master_host='192.168.1.148',
    master_user='root',
    master_port=3307,
    master_password='123456',
    master_log_file='mysql-bin.000005',
    master_log_pos=154;
start slave;
show slave status\G;

至此，完成，后面每次修改、新增、删除（表/数据）都会同步到salve，以此来做备份。也可以用作主写从读来分摊主库的压力。

参考：
https://www.jianshu.com/p/40e8b7548f03
https://www.cnblogs.com/gl-developer/p/6170423.html

k8s

因为我目前开发环境是自己单节点部署了一台k8s，所以也就暂时没搞文件挂在比如NFS服务，直接类似于docker的方式挂在载宿主机上。ok直接进入部署的yml就好了

Deployment方式

Deployment

因为我这里使用了k8s中的密文传输，所以在此之前需要先建一个root账户的密码，与此同时再进行deploy之前也需要把对应宿主机conf的挂在文件放到指定位置，否则初始化容器的时候会报错说找不到对应文件

apiVersion: apps/v1                       #apiserver的版本
kind: Deployment                              #副本控制器deployment，管理pod和RS
metadata:
    namespace: prod
  name: mysql-master                                 #deployment的名称，全局唯一
spec:
  replicas: 1                                   #Pod副本期待数量
  selector:
    matchLabels:                              #定义RS的标签
      app: mysql-master                              #符合目标的Pod拥有此标签
  strategy:                                    #定义升级的策略
    type: RollingUpdate                         #滚动升级，逐步替换的策略
  template:                          #根据此模板创建Pod的副本（实例）
    metadata:
      labels:
        app: mysql-master                   #Pod副本的标签，对应RS的Selector
    spec:
      containers:                     #Pod里容器的定义部分
      - name: mysql-master            #容器的名称
        image: mysql:5.7              #容器对应的docker镜像
        volumeMounts:                 #容器内挂载点的定义部分
        - name: time-zone             #容器内挂载点名称
          mountPath: /etc/localtime   #容器内挂载点路径，可以是文件或目录
        - name: mysql-data
          mountPath: /var/lib/mysql   #容器内mysql的数据目录
        - name: mysql-logs
          mountPath: /var/log/mysql   #容器内mysql的日志目录
        - name: mysql-conf
          mountPath: /etc/mysql/mysql.conf.d/mysqld.cnf   #mysql配置文件          
        ports:
        - containerPort: 3306           #容器暴露的端口号
        env:                            #写入到容器内的环境容量
        - name: MYSQL_ROOT_PASSWORD     #定义了一个mysql的root密码的变量
          #value: "123456"  我这里使用了k8s中的secret方式密文显示
          valueFrom:
            secretKeyRef:
              name: mysecret-mysql
              key: mysql-root-pwd
      volumes:                          #本地需要挂载到容器里的数据卷定义部分
      - name: time-zone                 #数据卷名称，需要与容器内挂载点名称一致
        hostPath:
          path: /etc/localtime          #挂载到容器里的路径，将localtime文件挂载到容器里，可让容器使用本地的时区
      - name: mysql-data
        hostPath:
          path: /myvolume/mysql/master/data
          type: DirectoryOrCreate
      - name: mysql-logs
        hostPath:
          path: /myvolume/mysql/master/logs
          type: DirectoryOrCreate    
      - name: mysql-conf
        hostPath:
          path: /myvolume/mysql/master/conf/mysqld.cnf
          type: FileOrCreate

service

apiVersion: v1
kind: Service
metadata:
  labels:
    app: mysql-master
  name: mysql-master
  namespace: prod
spec:
  type: NodePort
  ports:
  - port: 3306
    nodePort: 30010 #NodePort默认范围是30000-32767，会映射道容器的对应端口
    protocol: TCP
    targetPort: 3306
  selector:
    app: mysql-master

Secret

我这里选择base64进行加密，当然因为这是可逆的也会导致不安全，我这里只是做个演示

# 配置secret
echo -n "root" | base64     ---> cm9vdA==
echo -n "123456" | base64  ---> MTIzNDU2
apiVersion: v1
kind: Secret
metadata:
  name: mysecret-mysql
  namespace: prod
type: Opaque
data:
  mysql-user-name: cm9vdA==
  mysql-root-pwd: MTIzNDU2

StatefulSet方式（推荐）

statefulset

apiVersion: apps/v1                      
kind: StatefulSet                            
metadata:
  name: mysql
  namespace: prod
spec:
  serviceName: mysql-headless
  replicas: 1                         
  selector:
    matchLabels:                            
      app: mysql                             
  template:                          
    metadata:
      labels:
        app: mysql                 
    spec:
      containers:                    
      - name: mysql-container           
        image: mysql:5.7    
        ports:
        - containerPort: 3306          
        volumeMounts:                 
        - name: time-zone             
          mountPath: /etc/localtime   
        - name: mysql-data
          mountPath: /var/lib/mysql   
        - name: mysql-logs
          mountPath: /var/log/mysql
        - name: mysql-conf
          mountPath: /etc/mysql/mysql.conf.d/mysqld.cnf   #mysql配置文件          
        env:                            #写入到容器内的环境容量
        - name: MYSQL_ROOT_PASSWORD     #定义了一个mysql的root密码的变量
          valueFrom:
            secretKeyRef:
              name: mysecret-mysql
              key: mysql-root-pwd
      volumes:                          #本地需要挂载到容器里的数据卷定义部分
      - name: time-zone                 #数据卷名称，需要与容器内挂载点名称一致
        hostPath:
          path: /etc/localtime   #挂载到容器里的路径，将localtime文件挂载到容器里，可让容器使用本地的时区
      - name: mysql-data
        hostPath:
          path: /root/project-server/backend/mysql/mysql-master/volume/data
          type: DirectoryOrCreate
      - name: mysql-logs
        hostPath:
          path: /root/project-server/backend/mysql/mysql-master/volume/logs
          type: DirectoryOrCreate    
      - name: mysql-conf
        hostPath:
          path: /root/project-server/backend/mysql/mysql-master/volume/conf/mysqld.cnf
          type: FileOrCreate

service

apiVersion: v1
kind: Service
metadata:
  labels:
    app: mysql
  name: mysql-headless
  namespace: prod
spec:
  type: NodePort
  ports:
  - port: 3306
    nodePort: 30001 #NodePort默认范围是30000-32767，会映射道容器的对应端口
    protocol: TCP
    targetPort: 3306
  selector:
    app: mysql

Secret

同上

CorbJob数据库备份

一般我们会部署mysql数据库集群以此来达到高可用，但是以防万一别人删库跑路，所以还是定时做个数据库备份最好，k8s为我们提供了定时任务的解决方案，我们直接拿来主义即可。
需要说到的就是因为我们这里需要mysqldump所有选择了mysql镜像来使用此命令，当然commond命令是在我们容器内部进行的所以需要将内部的mysql-xxx.sql挂在到我们宿主机的指定位置即可。

apiVersion: batch/v1
kind: CronJob
metadata:
  name: mysql-backup
  namespace: default
spec:
  schedule: "0 0 * * *"   
  jobTemplate:
    spec:
      completions: 1
      template:
        metadata: {}
        spec:
          #parallelism: 1 #标志并行运行的Pod的个数,默认为1,可以理解为同时运行的Pod数量.
          containers:
          - name: mysql-backup
            imagePullPolicy: IfNotPresent
            image: mysql:5.7
            env:
              - name: MYSQL_BACKUP_USER
                value: root
              - name: MYSQL_BACKUP_USER_PASSWORD
                valueFrom:
                  secretKeyRef:
                    name: mysecret-mysql
                    key: mysql-root-pwd 
            #command: ['bash','/backup/backup.sh']
            command:
            - /bin/sh
            - -c
            - |
              set -ex
              pwd &&
              ls &&
              if [ ! -d "/myvolume/mysql-backup/" ];then
                  mkdir -p /myvolume/mysql-backup/
              fi &&
              mysqldump --host=192.168.1.110 -P 30010 --user=$MYSQL_BACKUP_USER \
                        --password=$MYSQL_BACKUP_USER_PASSWORD \
                        --routines --databases order \
                        > /myvolume/mysql-backup/mysql-`date +"%Y%m%d_%H_%M"`.sql
            volumeMounts:                           #容器内挂载点的定义部分
            - name: mysql-backup
              mountPath: /myvolume/mysql-backup/   #容器内目录不存在              
          restartPolicy: OnFailure #Never
          volumes:                         
          - name: mysql-backup
            hostPath:
              path: /myvolume/mysql-backup/
              type: DirectoryOrCreate

定时任务博客参考：https://dylanyang.top/post/2020/07/10/%E4%BD%BF%E7%94%A8cronjob%E5%AE%9A%E6%97%B6%E5%A4%87%E4%BB%BDmysql%E6%95%B0%E6%8D%AE%E5%BA%93/https://segmentfault.com/a/1190000014966962 官方corn表达式规则参考：
https://kubernetes.io/docs/concepts/workloads/controllers/cron-jobs/