mysql安装（linux环境）

mysql安装（linux环境）

1 下载

http://dev.mysql.com/downloads/mysql/

在这里插入图片描述

选择所需系统及版本的包，此次环境以CenOS7 64位安装8.0.26版本的mysql为例。

2 安装&设置

2.1检测是否已经安装了mysql

rpm -qa | grep mysql*

2.2安装&启动

  1. 将下载的安装包mysql-8.0.26-linux-glibc2.12-x86_64.tar上传/usr/local目录下

  2. 解压压缩包，并将目录修改为mysql

    [root@localhost local]# tar -xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar 
mysql-test-8.0.26-linux-glibc2.12-x86_64.tar.xz
mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
mysql-router-8.0.26-linux-glibc2.12-x86_64.tar.xz
[root@localhost local]# tar -xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
[root@localhost local]# mv mysql-8.0.26-linux-glibc2.12-x86_64 mysql

  3. 在mysql目录下创建data

    [root@localhost local]# cd mysql
[root@localhost mysql]# mkdir data

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

    [root@localhost mysql]# groupadd mysql
[root@localhost mysql]# useradd -r -g mysql mysql
[root@localhost mysql]# chown mysql:mysql -R /usr/local/mysql

  5. 进入/usr/local/mysql/bin目录下并初始化，初始化后会在data目录下生成相关文件

    [root@localhost mysql]# cd /usr/local/mysql/bin
[root@localhost bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --lower-case-table-names=1

    注意： MySQL8.0及以上版本, 只能在初始化的时候设置lower-case-table-names参数, 无法通过修改my.cnf实现，故初始化时增加不区分大小写设置。

    初始化成功生成临时密码

    [root@localhost bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2021-10-24T12:11:39.956567Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2021-10-24T12:11:39.957339Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.26) initializing of server in progress as process 65258
2021-10-24T12:11:39.987701Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-10-24T12:11:41.718478Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-10-24T12:11:44.011550Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-10-24T12:11:44.012414Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-10-24T12:11:44.063645Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: js..L)Yp25a6

  6. 修改/etc/my.cnf配置文件
    在这里插入图片描述

  7. 创建快捷ln

    [root@localhost ~]# ln -s /usr/local/mysql/bin/ /usr/bin/

  8. 启动mysql服务

    [root@localhost bin]# pwd
/usr/bin
[root@localhost data]# servie mysqld start
bash: servie: command not found...
[root@localhost data]# cd ../bin
[root@localhost bin]# service mysqld start
Starting MySQL.Logging to '/usr/local/mysql/data/mysql.log'.
.. SUCCESS!

    常用启停命令

    # 启动服务器
service mysqld start
# 重启
service mysqld restart
# 停止
service mysqld stop
# 查看状态
service mysqld status

2.3修改密码

  1. 使用root账号登录mysql

    [root@localhost bin]# ./mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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.

  2. 修改root密码

    alter user 用户@ip identified by '密码';

    mysql> alter user root@localhost identified by 'root';
Query OK, 0 rows affected (0.08 sec)

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

2.4开启远程访问

  1. 设置权限时报错

    mysql> grant all privileges on *.* to 'root'@'%' ;
ERROR 1410 (42000): You are not allowed to create a user with GRANT

    授权失败的原因是mysql 数据库中user 表中的特定用户(root) 的host 的属性值为localhost。

  2. 解决方法如下：

    # 使用mysql数据库
mysql> use mysql;
# 特定用户的host修改
mysql> update user set host='%' where user='root';
# 刷新权限，使配置生效
mysql> flush privileges;
# 设置授权
mysql> grant all privileges on *.* to 'root'@'%' ;

  3. CentOS 7 防火墙开启3306接口

    # 启动friewall
[root@localhost ~]# systemctl start firewalld
# 查看firewall启动状态
[root@localhost ~]# systemctl status firewalld
# 开启3306端口
[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
# 重启
[root@localhost ~]# firewalld   # firewall-cmd --reload
# 查看3306端口是否开启，no为开启
[root@localhost ~]# firewall-cmd --query-port=3306/tcp
no

  4. 本地客户端连接测试
    在这里插入图片描述

2.5设置环境变量

  1. 打开/etc/profile文件，在最下面添加如下内容，PATH替换成mysql安装目录

    export PATH=/usr/local/mysql/bin:$PATH

  2. 使用source命令，使环境变量立即生效

    [root@localhost ~]# source /etc/profile

2.6设置系统启动时自动启动mysql服务

  1. MySQL的服务脚本放到系统服务中

    [root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

  2. 设置可执行权限

    [root@localhost ~]# chmod +x /etc/init.d/mysqld

  3. 添加mysql服务

    [root@localhost ~]# chkconfig --add mysqld

  4. 查看服务列表

    [root@localhost ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
netconsole      0:off   1:off   2:off   3:off   4:off   5:off   6:off
network         0:off   1:off   2:on    3:on    4:on    5:on    6:off

    注：如果看到mysql的服务，并且3,4,5都是开的话则成功，如果是关，则重新打开

    [root@localhost ~]# chkconfig --level 345 mysqld on

2.7设置不区分大小写

  1. 通过命令查看mysql是否是区分大小

    mysql> show variables like '%case_table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.03 sec)

    lower_case_table_names=1（说明是不区分大小写的）

    lower_case_table_names=0（如上图为0说明区分大小写的）

  2. 重新初始化

    MySQL8.0及以上版本, 只能在初始化的时候设置lower-case-table-names参数, 无法通过修改my.cnf实现，修改后重启服务会报以下错误
    在这里插入图片描述

    所以，需要停止服务，删除已初始化的数据库, 即删除data_dir目录，然后重新初始化

    [root@localhost data]# pwd
/usr/local/mysql/data
[root@localhost data]# rm -rf *
[root@localhost data]# ll
total 0
[root@localhost data]# 
[root@localhost data]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --lower-case-table-names=1
2021-10-24T14:01:24.183143Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2021-10-24T14:01:24.183247Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.26) initializing of server in progress as process 5654
2021-10-24T14:01:24.201807Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-10-24T14:01:25.172002Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-10-24T14:01:26.302893Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-10-24T14:01:26.303335Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-10-24T14:01:26.391330Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ipYfphEl!6fP

  3. 设置/etc/my.cnf的lower-case-table-names属性值，使其值与初始化值保持一致，否则启动服务还会报错
    在这里插入图片描述

  4. 启动mysql，再次查看mysql是否是区分大小

    [root@localhost data]# service mysqld start 
Starting MySQL.Logging to '/usr/local/mysql/data/mysql.log'.
. SUCCESS! 
mysql> show variables like '%case_table%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.01 sec)

    2.8查看数据库编码

    mysql> show variables like "character%";
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8mb4                          |
| character_set_connection | utf8mb4                          |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | utf8mb4                          |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8mb3                          |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)