【task01】--sql进击小队-环境搭建、初识数据库

1.MySQL安装

  • 基于Linux平台,有3种安装方式

1.rpm包安装
2.二进制安装(常用,本次教程以此模式)
3.源码安装(繁琐,不太推荐)

  • 卸载与MySQL互斥的依赖包
[root@master01 ~]# rpm -qa|egrep "msyql|mariadb"
mariadb-libs-5.5.60-1.el7_5.x86_64

[root@master01 ~]# rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64
error: Failed dependencies:
	libmysqlclient.so.18()(64bit) is needed by (installed) postfix-2:2.10.1-7.el7.x86_64
	libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) postfix-2:2.10.1-7.el7.x86_64

# --nodeps 以跳过验证包的依赖关系,进而完成强制卸载

或者:
yum remove mariadb-libs-5.5.60-1.el7_5.x86_64

1.1.二进制包获取

  • MySQL官网
https://dev.mysql.com/downloads/mysql/

1.1.登录到Linux的机器

wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz

1.2.创建mysql用户

useradd -M -s /sbin/nologin -r mysql

1.3.解压

tar xf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.26-linux-glibc2.12-x86_64 /usr/local/
cd /usr/local/
ln -s mysql-8.0.26-linux-glibc2.12-x86_64/ mysql

1.4.目录规范

mkdir /data/mysql_3306/data/{data,log,tmp} -pv

1.5.编辑配置文件(这个配置我只是百度的,待完善)

mv /etc/my.cnf /etc/my.cnf.bak

[root@node01 local]# more /etc/my.cnf
[mysqld]
lower_case_table_names          = 1
user                            = mysql
server_id                       = 1
port                            = 3306
 
default-time-zone = '+08:00'
enforce_gtid_consistency        = ON
gtid_mode                       = ON
binlog_checksum                 = none
default_authentication_plugin   = mysql_native_password
datadir                         = /data/mysql_3306/data/data
pid-file                        = /data/mysql_3306/data/tmp/mysqld.pid
socket                          = /data/mysql_3306/data/tmp/mysqld.sock
tmpdir                          = /data/mysql_3306/data/tmp/
skip-name-resolve               = ON
open_files_limit                = 65535
table_open_cache                = 2000
 
#################innodb########################
innodb_data_home_dir            = /data/mysql_3306/data/data
innodb_data_file_path           = ibdata1:512M;ibdata2:512M:autoextend
innodb_buffer_pool_size = 12000M
innodb_flush_log_at_trx_commit = 1
innodb_io_capacity = 600
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 85
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 32
innodb_file_per_table
innodb_rollback_on_timeout
 
innodb_undo_directory           = /data/mysql_3306/data/data
innodb_log_group_home_dir       = /data/mysql_3306/data/data
 
###################session###########################
join_buffer_size = 8M
key_buffer_size = 256M
bulk_insert_buffer_size = 8M
max_heap_table_size = 96M
tmp_table_size = 96M
read_buffer_size = 8M
sort_buffer_size = 2M
max_allowed_packet = 64M
read_rnd_buffer_size = 32M
 
############log set###################
log-error                       =  /data/mysql_3306/data/log/mysqld.err
log-bin                         = /data/mysql_3306/data/log/binlog
log_bin_index                   = /data/mysql_3306/data/logbinlog.index
max_binlog_size                 = 500M
slow_query_log_file             = /data/mysql/log/slow.log
slow_query_log                  = 1
long_query_time                 = 10
log_queries_not_using_indexes   = ON
log_throttle_queries_not_using_indexes  = 10
log_slow_admin_statements       = ON
log_output                      = FILE,TABLE
master_info_file                = /data/mysql_3306/data/log/master.info

1.6.修改目录权限

chown -R mysql.mysql /data/mysql_3306/

1.7.初始化

cd /usr/local/mysql 
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql_3306/data/data

1.8.配置环境变量

vim /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH

# 使配置生效
. /etc/profile.d/mysql.sh

1.9.启动(后期用systemctl管理,待完善)

cd /usr/local/mysql/bin
mysqld_safe --defaults-file=/etc/my.cnf &

1.10.获取初始化密码

[root@node01 bin]# more /data/mysql_3306/data/log/mysqld.err |grep  generated
2021-08-16T14:48:03.408250Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: =iTl_eOic1#0

1.11.登录到MySQL

mysql -S /data/mysql_3306/data/tmp/mysqld.sock -p'=iTl_eOic1#0'

1.12.测试,并修改root密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'admin@123';
Query OK, 0 rows affected (0.01 sec)

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

1.13.设置mysql可以远程

# 查看
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

# 修改可以远程连接MySQL
mysql> update mysql.user set host='%' where user="root" and host="localhost";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 查看
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.01 sec)

1.13.再次登录

[root@node01 bin]#  mysql -S /data/mysql_3306/data/tmp/mysqld.sock -p'admin@123'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> 

2.客户端软件安装

  • 安装Navicat,步骤省略,如果不知道安装的小伙伴可以联系我获取软件包