Linux 安装 mysql

原创已于 2023-03-28 14:58:42 修改 · 302 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#linux #mysql #服务器 #java

于 2023-01-13 16:45:04 首次发布

这篇博客介绍了在Linux(CentOS 7)上安装MySQL 8.0的详细步骤，包括下载安装包、卸载旧版本、创建用户和组、配置环境变量、初始化数据库、设置默认密码、配置服务以及测试远程登录。还提到了在安装过程中可能遇到的问题和解决方法，如禁用binlog、免密码登录以及修改root密码。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

菜鸟教程：Linux 命令大全
 Linux安装配置MySQL详细步骤
http://c.biancheng.net/view/7616.html
一，下载mysql
1.内网需要本地先下载安装包后上传至目标服务器：点击mysql官网下载
2.服务器命令直接下载
3.删除服务器的mysql或mariadb目录和文件和组件

[root@test ~]# find / -name mysql
/usr/locol/mariadb-libs-5.5.64-1.el7.x86_64
[root@test ~]# rm -rf /user/locol/mariadb-libs-5.5.64-1.el7.x86_64

rpm -ev ：删除组件

[root@test ~]# rpm -qa|grep mysql
[root@test ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.64-1.el7.x86_64
[root@test ~]# rpm -ev mariadb-libs-5.5.64-1.el7.x86_64

在卸载服务是遇到了组件服务无法卸载问题，提示缺少依赖postfix-2:2.10.1-7.el7.x86_64：

解决方法就是在 rpm -ev 加上–nodeps：–nodeps就是安装时不检查依赖关系

[root@test ~]# rpm -ev --nodeps mariadb-libs-5.5.64-1.el7.x86_64

4.解压压缩包
上传安装包至/softbak(mkdir创建文件夹)，解压到/usr/local/（tar -xzvf /softbak/text.tar.gz tarFileAddress不填默认当前文件夹，解压安装包），修改解压文件名称为mysql（mv）

[root@test ~]# mkdir /softbak
[root@test ~]# cd /softbak
[root@test softbak]# tar -xzvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz /usr/local/
[root@test softbak]# cd /usr/local/
[root@test local]# mv mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz/ mysql

或者上传安装包到/usr/local

[root@test ~]# cd /usr/local/ 
[root@test local]# tar -xzvf /usr/local/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
[root@test local]# mv mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz/ mysql

5.创建mysql用户和组

[root@test local]# cd /usr/local/mysql
[root@test mysql]# groupadd mysql
[root@test mysql]# useradd -r -g mysql -s /bin/false mysql

6.创建目录并授权

[root@test mysql]# mkdir /var/lib/mysql
[root@test mysql]# mkdir /usr/local/mysql/log
[root@test mysql]# mkdir /usr/local/mysql/data
[root@test mysql]# chown -R mysql:mysql /usr/local/mysql
[root@test mysql]# chown -R mysql:mysql /var/lib/mysql

7.添加环境变量

vim命令用不了试试vi
[root@test mysql]# vim ~/.bash_profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
[root@test mysql]#source ~/.bash_profile

Linux的环境变量保存在变量PATH中,通过shell命令 echo $PATH查看内容或者通过命令export查看。

Linux环境变量值之间是通过冒号进行隔开的( : )
格式为：PATH=$PATH:<PATH 1>:<PATH 2>:<PATH 3>

8.配置mysql参数

[root@test mysql]# vim /etc/my.cnf


[mysql]
default-character-set=utf8mb4
socket=/var/lib/mysql/mysql.sock
[mysqld]
port = 3306
socket=/var/lib/mysql/mysql.sock
basedir=/usr/local/mysql
character-set-server=utf8mb4
default-storage-engine=INNODB
innodb_buffer_pool_size = 200M
max_allowed_packet=16M
explicit_defaults_for_timestamp=1
log-output=FILE
general_log = 0
general_log_file=/usr/local/mysql/log/liandodb_general.err
slow_query_log = ON
slow_query_log_file=/usr/local/mysql/log/liandodb_query.err
long_query_time=10
log-error=/usr/local/mysql/log/liandodb_error.err
default-authentication-plugin=mysql_native_password
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
expire_logs_days=5

按a开始编辑，退出编辑esc,退出并保存:wq,退出不保存:q!

禁用binlog

MySQL-8.0之前的版禁用binlog
MySQL-8.0 之前的版本只要注释掉 binlog 相关的配置项 MySQL 就会自己关闭 binlog 相关的功能了。

[mysqld]
#log_bin = mysql-bin
MySQL-8.0版本禁用binlog
MySQL-8.0 专门加了一个 skip_log_bin 的参数，专门用来做这个事，在 MySQL-8.0.19 版本下这个参数要出现在 log_bin 参数之后才有效果。

[mysqld]
log_bin      = mysql-bin
skip_log_bin = ON       # 注意 skip_log_bin 要放在 log_bin 之后才能生效
另一个要注意的事skip_log_bin参数是不能通过 show global variables 语句查看的。

mysql -uroot -pxxxx -h127.0.0.1 -P3306 -e"show global variables like '%log_bin%';"
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | ON    |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+

9.初始化数据库

[root@test mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

10.查看初始化日志及初始化默认密码

[root@test mysql]# cd /usr/local/mysql/log/
[root@test log]# ls
liandodb_error.err  liandodb_query.err
[root@liandoyun log]# more liandodb_error.err 
2020-05-15T03:09:59.555661Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.20) initializing of server in progress as process1834
2020-05-15T03:09:59.623225Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-05-15T03:10:03.164817Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-05-15T03:10:08.144133Z 6 [Note] [MY-010454] [Server] A temporary password is generated for 
root@localhost: txu2t6_EGBH:

日志中的【2020-05-15T03:10:08.144133Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: txu2t6_EGBH:】txu2t6_EGBH是初始化默认登录密码

11.配置mysql服务

[root@test log]# vim /usr/lib/systemd/system/mysqld.service
 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 65536
LimitNPROC = 65536
 
[root@test log]# systemctl daemon-reload

12.测试服务

[root@test log]# systemctl stop mysqld
[root@test log]# systemctl start mysqld
[root@test log]# ps -ef | grep mysqld

netstat -tnlp | grep 3306
在这里插入图片描述

//设置开机启动
[root@test log]# systemctl enable mysqld
//查看服务状态
[root@test log]# systemctl status mysqld
//重新启动
[root@test log]#systemctl restart mysqld

手动启停命令

启动：
[root@test log]# nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
停止：
[root@test log]# mysqladmin -uroot -p shutdown -S /var/lib/mysql/mysql.sock

13.登录并修改root密码

[root@test log]# mysql -u root -p
Enter password: txu2t6_EGBH

输入mysql -u root -p出现错误：-bash: mysql: command not found
输入：alias mysql=/usr/local/mysql/bin/mysql
再重复mysql -u root -p，输入mysql口令即可

(1).使用默认密码登录错误
mysql 8.0 版本，由于增加了一些安全策略等限制。修改密码需要配置/etc/my.cnf 文件免密码登录mysql

vim /etc/my.cnf
# 增加这两行命令
skip-grant-tables
default-authentication-plugin=mysql_native_password

重启启动mysql

[root@test log]# systemctl restart mysqld

再次登录，enter跳过密码
（注意：登录成功后记得注释掉 /etc/my.cnf 中 skip-grant-tables）

<-- 进入数据库 -->
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
 
 <-- 修改root密码 -->
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.04 sec)
 
  <-- 刷新 -->
mysql> flush privileges;
Query OK, 0 rows affected (0.07 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> select host,user from user where user='root';
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)
 
  <-- 设置root远程登录 -->
mysql> create USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
Query OK, 0 rows affected (0.07 sec)
 
mysql> grant all privileges on *.* to 'root'@'%' with grant option;
Query OK, 0 rows affected (0.07 sec)
 
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;

14.测试远程登录

[root@test ~]# mysql -h 10.0.3.116 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.20 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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>

原创地址:centos7.7 安装 mysql8.0.20
如遇到mysql端口不正常参考:mysql查看端口为0

mysqldump

导出数据库

[root@test home]# mysqldump --socket=/var/lib/mysql/mysql.sock -uroot -pPassword database --default-character-set=utf8>/home/database .sql;

导出表

[root@test home]# mysqldump --socket=/var/lib/mysql/mysql.sock -uroot -pPassword database tables --default-character-set=utf8>/home/database .sql;

执行sql文件

[root@test log]# mysql -u root -p
mysql> create DATABASE test_database;

mysql> use test_database;

mysql> set names utf8;

mysql> source /home/database.sql;

sql语句

修改字段为空

ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 数据类型 NOT NULL;
alter table topic modify state int(4) null;