mysql下载安装
yum安装mysql
查看mysql yum安装包
[ruoyi@vm10-0-0-3 ~]$ yum list mysql*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
MySQL-python.x86_64 1.2.5-1.el7 base
MySQL-zrm.noarch 3.0-17.el7 epel
mysql++.x86_64 3.1.0-12.el7 epel
mysql++-devel.x86_64 3.1.0-12.el7 epel
mysql++-manuals.x86_64 3.1.0-12.el7 epel
mysql-connector-java.noarch 1:5.1.25-3.el7 base
mysql-connector-odbc.x86_64 5.2.5-8.el7 base
mysql-connector-python.noarch 1.1.6-1.el7 epel
mysql-mmm.noarch 2.2.1-15.el7 epel
mysql-mmm-agent.noarch 2.2.1-15.el7 epel
mysql-mmm-monitor.noarch 2.2.1-15.el7 epel
mysql-mmm-tools.noarch 2.2.1-15.el7 epel
mysql-proxy.x86_64 0.8.5-2.el7 epel
mysql-proxy-devel.x86_64 0.8.5-2.el7 epel
mysql-utilities.noarch 1.3.6-1.el7 epel
mysqlreport.noarch 3.5-11.el7 epel
mysqltuner.noarch 1.7.17-2.git.f18a3ef.el7 epel
使用yum安装mysql
[root@vm10-0-0-3 ~]# yum install mysql* -y
卸载mysql
[root@vm10-0-0-3 ~]# yum list install mysql*| awk '{print $1}' | xargs rpm -e --nodeps
下载mysql安装包
点击下载，拿到下载连接https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
[root@vm10-0-0-3 local]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
安装过程
解包
[root@vm10-0-0-3 local]# tar -zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
重命名
[root@vm10-0-0-3 local]# mv mysql-5.7.29-linux-glibc2.12-x86_64 mysql
创建data目录
[root@vm10-0-0-3 mysql]# mkdir data
新建mysql用户及用户组
[root@vm10-0-0-3 mysql]# useradd mysql
[root@vm10-0-0-3 mysql]# passwd mysql
修改目录权限
[root@vm10-0-0-3 local]# chown -R mysql:mysql mysql
编译运行初始化
[root@vm10-0-0-3 bin]# ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql
执行报错
./mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
安装报错类库
[root@vm10-0-0-3 bin]# yum install libaio.so.1
[root@vm10-0-0-3 bin]# yum -y install numactl
重新执行还是同样的报错
[root@vm10-0-0-3 bin]# ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql
./mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
查看安装的版本，默认安装的32位的，但实际应使用64位的
[root@vm10-0-0-3 bin]# yum list libaio*
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Installed Packages
libaio.i686 0.3.109-13.el7 @base
Available Packages
libaio.x86_64 0.3.109-13.el7 base
libaio-devel.i686 0.3.109-13.el7 base
libaio-devel.x86_64 0.3.109-13.el7 base
[root@vm10-0-0-3 bin]# yum install libaio.x*
### 再次执行成功
[root@vm10-0-0-3 bin]# ./mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql
2020-06-16T09:16:13.553039Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-06-16T09:16:13.903569Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-06-16T09:16:13.951441Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-06-16T09:16:14.007676Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 06b96e14-afb2-11ea-b836-fa163e0d9114.
2020-06-16T09:16:14.008595Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-06-16T09:16:14.876493Z 0 [Warning] CA certificate ca.pem is self signed.
2020-06-16T09:16:15.009329Z 1 [Note] A temporary password is generated for root@localhost: HUe9dgUlet&q
启动服务
[root@vm10-0-0-3 ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/usr/local/mysql/data/vm10-0-0-3.err'.
[ OK ]
添加软连接并重启
[root@vm10-0-0-3 ~]# ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
[root@vm10-0-0-3 ~]# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
[root@vm10-0-0-3 ~]# service mysql restart
登录数据库报错
[root@vm10-0-0-3 ~]# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
修改my.cnf文件
[mysqld]
datadir=/usr/local/mysql/data
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
max_connections=400
innodb_file_per_table=1
#表名大小写不明感，敏感为
lower_case_table_names=1
#skip-grant-tables
socket=/usr/local/mysql/data/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/usr/local/mysql/data/error.log
pid-file=/usr/local/mysql/data/mysql.pid
[client]
port=3306
socket=/usr/local/mysql/data/mysql.sock
[root@vm10-0-0-3 ~]# service mysql restart
重启后再次登录
[root@vm10-0-0-3 ~]# mysql -u root -p
Enter password:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
密码过期问题my.cnf中添加
skip-grant-tables
重启再次登录
[root@vm10-0-0-3 ~]# service mysql restart
[root@vm10-0-0-3 ~]# mysql -u root -p
设置密码不过期
MySQL [(none)]> 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
MySQL [mysql]> update user set password_expired='N' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MySQL [mysql]> quit
设置可以远程登录
MySQL [mysql]> use mysql;
Database changed
MySQL [mysql]> update user set user.Host='%' where user.User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [mysql]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
去除my.cnf中新加的
skip-grant-tables
开启云服务器3306端口访问权限
远程访问mysql成功
设置开机启动，拷贝到开机启动目录下
[root@vm10-0-0-3 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
赋予执行权限
[root@vm10-0-0-3 ~]# chmod +x /etc/init.d/mysqld
添加服务检查
[root@vm10-0-0-3 ~]# chkconfig --add mysqld
[root@vm10-0-0-3 ~]# chkconfig --list
创建用户并赋予权限
[root@vm10-0-0-3 ~]# mysql -uroot -p
mysql> create user 'admin'@'120.92.151.50' identified by 'admin';
mysql> grant all on *.* to 'admin'@'120.92.151.50';
[root@vm10-0-0-3 ~]# mysql -h120.92.151.50 -uadmin -p
Enter password:
修改用户密码
方法1： 用SET PASSWORD命令
登录MySQL。
格式：mysql> set password for 用户名@localhost = password('新密码');
例子：mysql> set password for root@localhost = password('123');
方法2：用mysqladmin
格式：mysqladmin -u用户名 -p旧密码 password 新密码
例子：mysqladmin -uroot -p123456 password 123
方法3：用UPDATE直接编辑user表
登录MySQL。
mysql> use mysql;
mysql> update user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges;
rpm安装mysql
上传解压mysql tar包
-rw-r--r-- 1 root root 544174080 Dec 13 19:44 mysql-5.7.10-1.el7.x86_64.rpm-bundle.tar
-rw-r--r-- 1 7155 wheel 25649868 Nov 30 2015 mysql-community-client-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 275520 Nov 30 2015 mysql-community-common-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 3898152 Nov 30 2015 mysql-community-devel-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 44647736 Nov 30 2015 mysql-community-embedded-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 23829536 Nov 30 2015 mysql-community-embedded-compat-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 122766532 Nov 30 2015 mysql-community-embedded-devel-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 2307512 Nov 30 2015 mysql-community-libs-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 2115668 Nov 30 2015 mysql-community-libs-compat-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 47982608 Nov 30 2015 mysql-community-minimal-debuginfo-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 148556300 Nov 30 2015 mysql-community-server-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 13533604 Nov 30 2015 mysql-community-server-minimal-5.7.10-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 wheel 108596824 Nov 30 2015 mysql-community-test-5.7.10-1.el7.x86_64.rpm
查找删除原有包
[root@localhost ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
[root@localhost ~]# yum remove mariadb*
Configuration file /etc/yum/pluginconf.d/product-id.conf not found
Unable to find configuration file for plugin product-id
Configuration file /etc/yum/pluginconf.d/search-disabled-repos.conf not found
Unable to find configuration file for plugin search-disabled-repos
Configuration file /etc/yum/pluginconf.d/subscription-manager.conf not found
Unable to find configuration file for plugin subscription-manager
Loaded plugins: aliases, fastestmirror, protectbase
Resolving Dependencies
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.56-2.el7 will be erased
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64
--> Running transaction check
---> Package postfix.x86_64 2:2.10.1-6.el7 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
按顺序安装
[root@localhost ~]# rpm -ivh mysql-community-common-5.7.10-1.el7.x86_64.rpm
warning: mysql-community-common-5.7.10-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-common-5.7.10-1.e################################# [100%]
[root@localhost ~]# rpm -ivh mysql-community-libs-5.7.10-1.el7.x86_64.rpm
warning: mysql-community-libs-5.7.10-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-libs-5.7.10-1.el7################################# [100%]
[root@localhost ~]# rpm -ivh mysql-community-client-5.7.10-1.el7.x86_64.rpm
warning: mysql-community-client-5.7.10-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-client-5.7.10-1.e################################# [100%]
[root@localhost ~]# rpm -ivh mysql-community-server-5.7.10-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.10-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:mysql-community-server-5.7.10-1.e################################# [100%]
初始化mysql并查看初始密码
[root@localhost ~]# mysqld --initialize
[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log
2022-12-13T12:00:58.500951Z 1 [Note] A temporary password is generated for root@localhost: 43W7-_uosNty
启动mysql 并查看状态
[root@localhost ~]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: failed (Result: start-limit) since Tue 2022-12-13 20:03:29 CST; 14s ago
Process: 8061 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
Process: 8045 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead)
查看报错
[root@localhost ~]# vi /var/log/mysqld.log
2022-12-13T12:02:48.233420Z 0 [Note] InnoDB: PUNCH HOLE support available
2022-12-13T12:02:48.233462Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2022-12-13T12:02:48.233468Z 0 [Note] InnoDB: Uses event mutexes
2022-12-13T12:02:48.233473Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2022-12-13T12:02:48.233478Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2022-12-13T12:02:48.233482Z 0 [Note] InnoDB: Using Linux native AIO
2022-12-13T12:02:48.233795Z 0 [Note] InnoDB: Number of pools: 1
2022-12-13T12:02:48.233945Z 0 [Note] InnoDB: Using CPU crc32 instructions
2022-12-13T12:02:48.244560Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2022-12-13T12:02:48.253744Z 0 [Note] InnoDB: Completed initialization of buffer pool
2022-12-13T12:02:48.255893Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2022-12-13T12:02:48.265976Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2022-12-13T12:02:48.266017Z 0 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
2022-12-13T12:02:48.266038Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2022-12-13T12:02:48.866703Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2022-12-13T12:02:48.866778Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2022-12-13T12:02:48.866799Z 0 [ERROR] Failed to initialize plugins.
2022-12-13T12:02:48.866813Z 0 [ERROR] Aborting
登录mysql 修改密码
[root@localhost /]# systemctl start mysqld
[root@localhost /]# mysql -uroot -p43W7-_uosNty
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 2
Server version: 5.7.10
Copyright (c) 2000, 2015, 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> set password for 'root'@'localhost'=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> exit
Bye
修改远程可链接
[root@localhost /]# mysql -uroot -p123456
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 5
Server version: 5.7.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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> GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
设置mysql开机启动
[root@localhost /]# systemctl enable mysqld
[root@localhost /]# systemctl is
is-active is-enabled is-failed isolate is-system-running
[root@localhost /]# systemctl is-enabled mysqld
enabled
安装常见错误解决
错误1：allowed_packet过小
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'sysJobServiceImpl': Invocation of init method failed; nested exception is org.quartz.JobPersistenceException: Couldn't store job: Packet for query is too large (2,223 > 1,024). You can change this value on the server by setting the 'max_allowed_packet' variable. [See nested exception: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (2,223 > 1,024). You can change this value on the server by setting the 'max_allowed_packet' variable.]
my.cnf中添加
max_allowed_packet=20M
错误2：登录失败
Access denied for user 'root'@'localhost' (using password: YES)
my.cnf 添加skip-grant-tables
登录后修改密码
[root@vm10-0-0-3 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.29 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> 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 [mysql]> update user set authentication_string=password('root') where user="root";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
修改root密码
MySQL [mysql]> update user set authentication_string=password('root') where user="root";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
错误3：远程连接未开启
连接失败！null, message from server: “Host ‘xxxx‘ is not allowed to connect to this MySQL server“
连接数据库
mysql -u root -p
use mysql ;
select user,host from user;//可以看到user为root，host为localhost的话，说明mysql只允许本机连接，那么外网，本地软件客户端就无法连接了。
update user set host = ‘%’ where user=‘root’;
flush privileges;//刷新权限
错误4：本地登录连接通道错误
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
my.cnf中新增配置
[mysqld]
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
socket=/var/lib/mysql/mysql.sock（跟这个socket路径一样）
user=mysql
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[client]
port=3306
socket=/var/lib/mysql/mysql.sock（新增socket路径与mysqld一致）
错误5：工具远程连接出错
ERROR 2013 (HY000): Lost connection to MySQL server during query
添加ssh通道连接
错误6 [ERROR] InnoDB: The innodb_system data file ‘ibdata1’ must be writable
仔细检查my.cnf中的错误
扫一扫
7万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
