Mysql 安装

最新推荐文章于 2024-02-18 10:00:41 发布

LSZXZNJ

最新推荐文章于 2024-02-18 10:00:41 发布

阅读量441

点赞数

分类专栏： DB 数据存储相关文章标签： mysql linux 数据库

本文链接：https://blog.csdn.net/htydowd/article/details/117600465

版权

DB 同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

数据存储相关

10 篇文章 0 订阅

订阅专栏

linux安装mysql.

1. 官网下载 linux 版本的 mysql

mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

2. 创建用户组和用户

groupadd mysql

useradd mysql -r -g mysql

提示：一个目录下创建多个子目录，可以用父目录/{子目录1，子目录2} -p 创建

mkdir -p /home/mysql/program
mkdir -p /home/mysql/data/mysqldata1/{binlog,innodb_log,innodb_ts,\log,mydata,relaylog,slowlog,sock,tmpdir,undo}
yum install tree -y

[root@192 tar]# tree /home/mysql/
/home/mysql/
├── conf
├── data
│   └── mysqldata1
│       ├── binlog
│       ├── innodb_log
│       ├── innodb_ts
│       ├── log
│       ├── mydata
│       ├── relaylog
│       ├── slowlog
│       ├── sock
│       ├── tmpdir
│       └── undo
└── program

4. 解压

[root@192 tar]# tar xf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /home/mysql/\program/
[root@192 tar]# chown mysql.mysql /home/mysql -R

建立软连接并设置环境变量

注意：建软连接时，目标目录 mysql 不要提前创建

ln -s /home/mysql/program/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
export PATH=$PATH:/usr/local/mysql/bin/
echo 'export PATH=$PATH:/usr/local/mysql/bin/' >> /etc/profile

my.cnf文件

直接用/etc/my.cnf

注意：my.cnf中有需要用到的文件需要提前创建好

[root@192 conf]# cat my.cnf 
[client]
socket=/home/mysql/data/mysqldata1/sock/mysql.sock #sock文件所在路径

[mysqld]
user=mysql
basedir = /usr/local/mysql
socket=/home/mysql/data/mysqldata1/sock/mysql.sock #sock文件所在路径
pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid #pidk文件所在路径
datadir=/home/mysql/data/mysqldata1/mydata #数据文件路径
tmpdir=/home/mysql/data/mysqldata1/tmpdir #存放临时文件的路径
log-error=/home/mysql/data/mysqldata1/log/error.log
slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log
log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin
relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin
innodb_data_home_dir=/home/mysql/data/mysqldata1/innodb_ts
innodb_log_group_home_dir=/home/mysql/data/mysqldata1/innodb_log
innodb_undo_directory=/home/mysql/data/mysqldata1/undo/
explicit_defaults_for_timestamp=true
server-id=1

初始化

// 5.7 用这个命令， 这里需要指定好 启动的配置文件
mysqld  --defaults-file=/etc/my.cnf --initialize-insecure

[root@192 slowlog]# cp -ar /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 
[root@192 slowlog]# chmod +x /etc/init.d/mysqld 
[root@192 slowlog]# chkconfig mysqld on
[root@192 slowlog]# chkconfig --list mysqld

注意：该输出结果只显示 SysV 服务，并不包含原生 systemd 服务。SysV 配置数据可能被原生 systemd 配置覆盖。 
      如果您想列出 systemd 服务,请执行 'systemctl list-unit-files'。
      欲查看对特定 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld          0:关    1:关    2:开    3:开    4:开    5:开    6:关
[root@192 slowlog]# service mysqld start
Starting MySQL.. SUCCESS!

查看启动情况

[root@192 slowlog]# ps aux | grep mysqld
[root@192 slowlog]# netstat -ntupl | grep mysqld
[root@192 slowlog]# cat /home/mysql/data/mysqldata1/log/error.log 
......
//最后一行

Version: '5.7.34-log'  socket: '/home/mysql/data/mysqldata1/sock/mysql.sock'  port: 3306  MySQL Community Server (GPL)

[root@192 slowlog]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log 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> 

-- 修改 root 用户 密码，5.7版本的直接这样设置
mysql> set password for 'root'@'localhost' = 'admin'
    -> ;
Query OK, 0 rows affected (0.01 sec)

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

mysql> exit
Bye
[root@192 slowlog]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.34-log 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>

查看初始配置


mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.34-log |
+------------+
1 row in set (0.00 sec)
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> select * from mysql.db\G
*************************** 1. row ***************************
                 Host: localhost
                   Db: performance_schema
                 User: mysql.session
          Select_priv: Y
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
*************************** 2. row ***************************
                 Host: localhost
                   Db: sys
                 User: mysql.sys
          Select_priv: N
          Insert_priv: N
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: Y
2 rows in set (0.00 sec)

创建用户

mysql> create user 'xjw'@'%' identified by 'admin'; -- admin 为登录密码
Query OK, 0 rows affected (0.59 sec)
mysql> grant all on *.* to 'xjw'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@192 slowlog]# mysql -uxjw -p
Enter password:  -- 这里输入 admin
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.34-log 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> show grants;
+------------------------------------------------------------+
| Grants for xjw@%                                           |
+------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'xjw'@'%' WITH GRANT OPTION |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

[root@192 mydata]# tree /home/mysql/data/mysqldata1/* | grep -Ei '.frm|.myi|.myd|.ibd' | awk -F '.' '{print $2}' | sort |uniq -c
      2 
    219 frm
     20 ibd
     10 MYD
     10 MYI
[root@192 mydata]# tree /home/mysql/data/mysqldata1/* | grep -Eiv '.frm|.myi|.myd|.ibd'
/home/mysql/data/mysqldata1/binlog
├── mysql-bin.000001
├── mysql-bin.000002
└── mysql-bin.index
/home/mysql/data/mysqldata1/innodb_log
├── ib_logfile0
└── ib_logfile1
/home/mysql/data/mysqldata1/innodb_ts
├── ib_buffer_pool
└── ibtmp1
/home/mysql/data/mysqldata1/log
└── error.log
├── auto.cnf
├── ca-key.pem
├── ca.pem
├── client-cert.pem
├── client-key.pem
├── mysql
│   ├── db.opt
│   ├── general_log.CSM
│   ├── general_log.CSV
│   ├── slow_log.CSM
│   ├── slow_log.CSV
├── performance_schema
│   ├── db.opt
├── private_key.pem
├── public_key.pem
├── server-cert.pem
├── server-key.pem
└── sys
    ├── db.opt
    ├── sys_config_insert_set_user.TRN
    ├── sys_config.TRG
    ├── sys_config_update_set_user.TRN
/home/mysql/data/mysqldata1/relaylog
/home/mysql/data/mysqldata1/slowlog
└── slow-query.log
/home/mysql/data/mysqldata1/sock
├── mysql.pid
├── mysql.sock
└── mysql.sock.lock
/home/mysql/data/mysqldata1/tmpdir
/home/mysql/data/mysqldata1/undo

3 directories, 291 files

是否支持 performance_schema:
mysql> select * from information_schema.engines;
mysql> show engines;

是否开启performance_schema:
performance_schema=ON|OFF来设置，并在my.cnf中进行配置
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

开启设置：
mysql> update setup_instruments set enabled='YES',timed='YES' where name like 'wait%';
Query OK, 267 rows affected (0.01 sec)
Rows matched: 321  Changed: 267  Warnings: 0

mysql> update setup_consumers set enabled='YES' where name like '%wait%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select count(*) from events_waits_current;
+----------+
| count(*) |
+----------+
|        6 |
+----------+
1 row in set (0.00 sec)

mysql> select * from events_waits_current;	

连接mysql 失败解决：
[root@192 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@192 ~]# firewall-cmd --reload
success
[root@192 ~]# firewall-cmd --list-ports
3306/tcp
[root@192 ~]#

windows安装mysql:

my.ini配置：

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=E:\\message\\mysql-8.0.26-winx64
# 设置mysql数据库的数据的存放目录
datadir=E:\\message\\mysql-8.0.26-winx64\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

管理员运行CMD：

#初始化配置
E:\message\mysql-8.0.26-winx64\bin>mysqld --initialize --user=mysql --console
2021-09-06T06:09:33.618864Z 0 [System] [MY-013169] [Server] E:\message\mysql-8.0.26-winx64\bin\mysqld.exe (mysqld 8.0.26) initializing of server in progress as process 69108
2021-09-06T06:09:33.619774Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2021-09-06T06:09:33.653107Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-09-06T06:09:39.344275Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-09-06T06:09:51.201914Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-09-06T06:09:51.202535Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-09-06T06:09:51.283314Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: mRwtqB!xr8dR  // 注意，这里是生成的root 初始密码，登录的时候需要用


#安装
E:\message\mysql-8.0.26-winx64\bin>mysqld.exe -install
Service successfully installed.

#启动

E:\message\mysql-8.0.26-winx64\bin>net start mysql
MySQL 服务正在启动 ..
MySQL 服务已经启动成功。


#登录
E:\message\mysql-8.0.26-winx64\bin>mysql -u root -p
Enter password: ************  // 输入root用户 密码
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26

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>

# 修改初始密码
mysql> ALTER USER root@localhost IDENTIFIED with mysql_native_password  BY 'password123';
Query OK, 0 rows affected (0.08 sec)

mysql>