数据库安装（Linux版本 -mysql5.7）

一、下载安装包

主数据库和备用数据库都按照以上步骤进行安装单节点：单节点安装步骤一到六

1.1下载安装包

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz

1.2上传

将安装包上传到/usr/local/

二、基础参数设置

常用

  • vim [文件名] 创建与编辑文件
  • i 进入编辑状态 esc 退出
  • :wq 保存并退出
  • :q! 不保存推出

2.1创建并编辑sysctl.conf

/etc/sysctl.conf

执行命令

sudo vim /etc/sysctl.conf

输入配置

vm.swappiness = 0
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_fin_timeout = 30
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 65535
net.core.wmem_default = 87380
net.core.wmem_max = 16777216
net.core.rmem_default = 87380
net.core.rmem_max = 16777216

2.2创建并编辑limits.conf

/etc/security/limits.conf

执行命令

sudo vim /etc/security/limits.conf

输入配置

mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 20480
mysql hard nproc 65535

三、操作系统新增MySQL用户

3.1添加用户

sudo groupadd -g 505 mysql
sudo useradd -u 505 mysql -d /home/mysql -g mysql -s /sbin/nologin

四、创建数据目录

  • 重点：数据服务必须挂在独立的数据盘，且数据盘挂载目录为data

4.1创建数据目录并赋权限

sudo mkdir /data/mysql
sudo chown -R mysql:mysql /data/mysql

五、安装数据库

5.1解压安装包并赋值权限

cd /usr/local
sudo tar xzvf mysql-5.7.42-linux-glibc2.12-x86_64.tar.gz
sudo mv /usr/local/mysql-5.7.42-linux-glibc2.12-x86_64 /usr/local/mysql
sudo chown -R mysql. /usr/local/mysql

5.2编辑配置文件my.cnf

vim /etc/my.cnf

输入命令

sudo vim /etc/my.cnf

插入参数

注意:这里配置参数server_id 主库和从库请更换成不同的值。

[client]
port = 3306
socket = /data/mysql/mysql.sock
[mysqld]
server_id=10
port = 3306
user = mysql
character-set-server = utf8
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 3
default_storage_engine = innodb
log_timestamps = SYSTEM
socket = /data/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysqld.pid
lower_case_table_names=1
max_connections = 5000
max_connect_errors = 1000
table_open_cache = 1024
max_allowed_packet = 128M
open_files_limit = 65535
expire_logs_days=3
innodb_buffer_pool_size = 4096M
innodb_file_per_table = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 2
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 30
innodb_data_file_path=ibdata1:10M:autoextend
log_error = /data/mysql/error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
sql_mode=STRICT_TRANS_TABLES

5.3初始化MySQL数据库

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/src/mysql-5.7.42 --datadir=/data/mysql

5.4添加MySQL路径到系统环境变量

sudo echo "export PATH=$PATH:/usr/local/mysql/bin" | sudo tee -a /etc/profile

5.5编辑环境变量（忽略步骤）

sudo nano /etc/profile       保存：ctrl+o 然后回车    退出：ctrl+x

5.6使新的环境变量立即生效

source /etc/profile

5.7复制MySQL服务脚本并启动MySQL服务

复制操作
sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
启动操作
sudo /etc/init.d/mysqld start

六、进入Mysql

6.1登录密码

mysql 密码存在/data/mysql/error.log 中的 root@localhost: XXXX，其中 xxx 指代具体的信息；

6.2ubuntu 20.4 安装 libncurses5

执行命令：
sudo apt install apt-file
sudo apt-file update
sudo apt install libncurses5
sudo apt install libncuress*

6.3登录 mysql

mysql -uroot -p

6.4首次登录后无法使用用户操作，需要修改密码

ALTER USER USER() IDENTIFIED BY '密码';

七、配置主库（主数据库操作）

7.1创建用于主从复制的用户

CREATE USER 'repl'@'从库IP地址' IDENTIFIED WITH mysql_native_password BY '密码（自定义）';

7.2赋权

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'从库IP地址';

7.3刷新权限

FLUSH PRIVILEGES;

7.4查看主服务器状态

SHOW MASTER STATUS;

输出结果

+------------------+----------+--------------+------------------+-----------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set           |
+------------------+----------+--------------+------------------+-----------------------------+
| bin-log.000002   | 1471     |              |                  |                             |
+------------------+----------+--------------+------------------+-----------------------------+
1 row in set (0.00 sec)

八、配置从库（备用数据库操作）

8.1连接主库

CHANGE MASTER TO
MASTER_HOST='主库IP地址',
MASTER_USER='repl',
MASTER_PASSWORD='密码', --对应步骤7.1密码
MASTER_LOG_FILE='mysql-bin.000001',  -- 对应7.4的File
MASTER_LOG_POS=154;  -- 对应7.4的Position

8.2开启主从复制

START SLAVE;

8.3查看从库状态

命令： 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   -（注意：这里必须为YES才代表链接成功）
            Slave_SQL_Running: Yes	 -（注意：这里必须为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)

九、测试主从

9.1主库执行

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)

9.2从库执行

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

十、百旺Mysql数据库安装要求

1.最大连接数：max_connections 3000
2.表名不区分大小写：lower_case_table_names 参数值得为1
最近更新：:
Contributors: yanpeng_