mysql数据库安装
1.首先官网下载压缩包 mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz
2.解压到/usr/local 目录下,解压完成后修改文件名 mv mysql-5.7.24**** mysql
[root@localhost local]# ls
mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz redis-4.0.6 tomcat8
mongodb4.0.0 mysql-5.7.24-linux-glibc2.12-x86_64 nginx redis-4.0.6.tar.gz
[root@localhost local]# mv mysql-5.7.24-linux-glibc2.12-x86_64 mysql
[root@localhost local]# ls
mysql mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz redis-4.0.6 tomcat8
mongodb4.0.0 nginx redis-4.0.6.tar.gz
3.由于CentOS 7 版本将MySQL数据库软件从默认的程序列表中移除,用mariadb代替了。要先卸载mariadb,除非不安装mysql,而直接使用mariadb,请参考以下方法:来自centos7 mysql数据库安装和配置 - starof - 博客园
安装mariadb
MariaDB数据库管理系统是MySQL的一个分支,主要由开源社区在维护,采用GPL授权许可。开发这个分支的原因之一是:甲骨文公司收购了MySQL后,有将MySQL闭源的潜在风险,因此社区采用分支的方式来避开这个风险。MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。
安装mariadb,大小59 M。
[root@yl-web yl]# yum install mariadb-server mariadb
mariadb数据库的相关命令是:
systemctl start mariadb #启动MariaDB
systemctl stop mariadb #停止MariaDB
systemctl restart mariadb #重启MariaDB
systemctl enable mariadb #设置开机启动
所以先启动数据库
[root@yl-web yl]# systemctl start mariadb
然后就可以正常使用mysql了
[root@yl-web yl]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.41-MariaDB MariaDB Server
Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]>
安装mariadb后显示的也是 MariaDB [(none)]> ,可能看起来有点不习惯。
以下是继续安装压缩版mysql:
#卸载系统自带的Mariadb
[root@localhost ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@localhost ~]# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64
#删除etc目录下的my.cnf文件
[root@localhost ~]# rm /etc/my.cnf
rm: cannot remove ?etc/my.cnf? No such file or directory
#检查mysql是否存在
[root@localhost ~]# rpm -qa | grep mysql
[root@localhost ~]#
#检查mysql组和用户是否存在,如无创建
[root@localhost ~]# cat /etc/group | grep mysql
[root@localhost ~]# cat /etc/passwd | grep mysql
#创建mysql用户组
[root@localhost ~]# groupadd mysql
#创建一个用户名为mysql的用户并加入mysql用户组
[root@localhost ~]# useradd -g mysql mysql
在创建用户组和group分组时,由于此前手残,把文件删除了,一直报错,这时要复制他们的备份文件
[root@localhost mysql]# groupadd mysql
groupadd:无法打开 /etc/group
[root@localhost mysql]# cd ../../../
[root@localhost /]# groupadd mysql
groupadd:无法打开 /etc/group
[root@localhost /]# lsattr /etc/passwd
lsattr: 没有那个文件或目录 当尝试对/etc/passwd进行stat调用时
[root@localhost /]# cp /etc/passwd- /etc/passwd
[root@localhost /]# cp /etc/group- /etc/group
[root@localhost /]# groupadd mysql
[root@localhost /]# useradd -g mysql mysql
以上cp 就是解决办法
更改所属的组和用户
#更改所属的组和用户
[root@localhost local]# chown -R mysql mysql/
[root@localhost local]# chgrp -R mysql mysql/
[root@localhost local]# cd mysql
#创建data文件夹
[root@localhost mysql]# mkdir data
[root@localhost mysql]# ls
bin COPYING data docs include lib man README share support-files
安装和初始化
在etc目录下创建my.cnf文件,编辑文件
[root@localhost mysql]# touch /etc/my.cnf
[root@localhost mysql]# vim /etc/my.cnf
添加以下内容
①,普通版
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
skip-name-resolve
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
②,支持表情包的 emoji
[mysql]
# 设置mysql默认字符集
# default-character-set=utf8
# 设置mysq默认使用字符集 utf8mb4 支持emoji 表情包
default-character-set=utf8mb4
[client]
# 设置mysql客户端默使用认字符集 支持emoji
default-character-set=utf8mb4
[mysqld]
skip-name-resolve
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
# character-set-server=utf8
# 以下内容配置 是为了使数据库支持 emoji 表情
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
# 支持配置结束
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
然后初始化
[root@localhost mysql]# bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
2019-01-03 10:54:13 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2019-01-03 10:54:28 [WARNING] The bootstrap log isn't empty:
2019-01-03 10:54:28 [WARNING] 2019-01-03T02:54:13.085298Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead
在初始化时碰到了一个问题:
2019-02-16 16:46:17 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2019-02-16 16:46:17 [ERROR] Child process: /usr/local/mysql/bin/mysqldterminated prematurely with errno= 32
2019-02-16 16:46:17 [ERROR] Failed to execute /usr/local/mysql/bin/mysqld --bootstrap --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US --basedir=/usr/local/mysql
-- server log begin --
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
-- server log end --
解决办法:
yum install -y libaio
2020-07-02 09:41:28 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2020-07-02 09:41:28 [ERROR] Child process: /usr/local/mysql/bin/mysqldterminated prematurely with errno= 32
2020-07-02 09:41:28 [ERROR] Failed to execute /usr/local/mysql/bin/mysqld --bootstrap --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US --basedir=/usr/local/mysql
-- server log begin --
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory
-- server log end --
解决办法:
yum -y install numactl
安装后再进行初始化就行了
拷贝启动文件到etc/init.d/mysqld下,赋予其权限,启动mysql
[root@localhost mysql]# cp ./support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chown 777 /etc/my.cnf
[root@localhost mysql]# chmod +x /etc/init.d/mysqld
[root@localhost mysql]# /etc/init.d/mysqld restart
启动时如果报以下错,说明可能已经启动过了,端口被用了
[root@localhost mysql]# /etc/init.d/mysqld restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
SUCCESS!
可以通过命令查看,执行完后在重启,不会报如上错误,启动成功,再查看进程号
[root@localhost mysql]# ps -ef|grep mysqld
root 31544 1 0 11:05 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.localdomain.pid
mysql 31743 31544 0 11:05 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --port=3306
root 31959 20119 0 11:07 pts/0 00:00:00 grep --color=auto mysqld
[root@localhost mysql]# kill -9 31544
[root@localhost mysql]# kill -9 31743
[root@localhost mysql]# /etc/init.d/mysqld restart
ERROR! MySQL server process #31743 is not running!
Starting MySQL. SUCCESS!
[root@localhost mysql]# ps -ef|grep mysqld
root 32067 1 0 11:07 pts/0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.localdomain.pid
mysql 32268 32067 1 11:07 pts/0 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --port=3306
root 32338 20119 0 11:08 pts/0 00:00:00 grep --color=auto mysqld
其它情况参考: MySQL server PID file could not be found!_「已注销」的博客-CSDN博客
启动完成后设置开机自启动
[root@localhost mysql]# chkconfig --level 35 mysqld on
[root@localhost mysql]# chmod +x /etc/rc.d/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# service mysqld status
SUCCESS! MySQL running (32649)
获取mysql初始密码
[root@localhost mysql]# cat /root/.mysql_secret
# Password set for user 'root@localhost' at 2019-01-03 10:54:13
8pw0u8dayNm.
登录mysql,用 ./mysql -uroot -p
[root@localhost mysql]# cd bin
[root@localhost bin]# mysql -uroot -p
-bash: mysql: 未找到命令
[root@localhost bin]# ./mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24
Copyright (c) 2000, 2018, 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>
修改密码,flush privileges; 很重要,否则无效
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
然后 exit 退出,用新密码重新登录一次测试
修改mysql可以远程连接,把user为root的账号的host改为%
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 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 user,host,password from user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> select user,host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> update user set host="%" where user="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select user,host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> exit
Bye
[root@localhost bin]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
修改完重启,否则不生效
2.重置密码
登录时报错 :ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES),排除其他原因后重置密码
1.重置密码的第一步就是跳过MySQL的密码认证过程,如下:
#vim /etc/my.cnf(注:windows下修改的是my.ini)
在末尾添加 :
skip-grant-tables
然后保存文档并退出
2.重启MySQL:如上安装时重启命令:/etc/init.d/mysqld restart
3.重启之后输入./mysql进入
4.接下来就是用sql来修改root的密码:
use mysql;
update mysql.user set authentication_string=password('123456') where user='root';mysql> flush privileges;
(注:mydql5.7 密码字段password 改为了 authentication_stringmysql)
exit
5.如果root用户误删了,则新建root用户
insert into user(user,host,authentication_string,ssl_type,ssl_cipher,x509_issuer,x509_subject) values('root','localhost',PASSWORD('123456'),'','','','');
如图:
重置密码完成,去掉my.cnf的内容,重启mysql,用密码登录即可