说明
MySQL在各个平台下的安装各不相同,WINDOWS最简单,这里介绍的是mysql在linux平台——ubuntu下的安装方法。
ubuntu下安装方法也有几种,这里介绍的是下载程序zip包并install的方法。这个方法也是非常的简单,相对于deb方法,一步到位,文件还比较集中。
1 安装 5.6.26
1.1 下载
http://mysql.linux.cz/Downloads/MySQL-5.6/这里能看到形形色色的安装包。我们选择的是类似mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz
的文件
1.2 执行安装
将文件拷贝到安装目录:
cp mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz /usr/local
解压:
tar -xvf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz
进入mysql的根目录,并执行安装命令:
scripts/mysql_install_db --user=root
在执行此命令时,或会提示:
Installing MySQL system tables..../bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
只需要安装依赖包:
apt-get install libaio1 libaio-dev
1.3 启动与关闭
(1). 启动(注意,必须进入mysql的根目录执行下列命令,inf配置文件对路径有依赖):
bin/mysqld_safe --user=root&
或会启动失败,查看错误日志有以下提示:
2016-11-04 10:16:45 11530 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2016-11-04 10:16:45 11530 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
查看一下内存情况:
root@ubuntu:/usr/local/mysql-5.6.33-linux-glibc2.5-x86_64# free -m
total used free shared buffers cached
Mem: 979 900 79 32 1 83
-/+ buffers/cache: 815 163
Swap: 1021 880 141
看到的内存不够默认的128M,加大内存(如果在虚拟机中,多分配点)。
(2). 关闭:
bin/mysqladmin -uroot -p shutdown
1.4 后设置
(1). 重设密码:
bin/mysqladmin -u root password 'newpassword'
(2). 开启远程访问:
/*创建一个新用户,指定访问端为%*/
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
/*使设置生效*/
flush privileges;
2 安装8.0.18
2.1 下载资源包
下载地址在这里
解压到/usr/local目录下,进入mysql目录
2.2 安装
给另一个用户:chown elk.elk mysql-8.0.18-linux-glibc2.12-x86_64 -R
初始化:
bin/mysqld --initialize-insecure --user=elk --basedir=/usr/local/mysql-8.0.18-linux-glibc2.12-x86_64 --datadir=/usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/data
提示:
2019-12-24T14:10:24.396562Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.18) initializing of server in progress as process 13833
2019-12-24T14:10:27.476315Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
这里进程并没有启动,data目录多了一些初始化的东西。
添加到自启:
cp support-files/mysql.server /etc/init.d/mysql.server
update-rc.d mysql.server defaults
修改相关路径:
使修改生效:systemctl daemon-reload
启动:service mysql.server start
如果启动失败,通过这个命令查看:systemctl status mysql.server
,比如显示:
Dec 24 22:21:41 iZ8vbj9qj1ctpijae0bxunZ mysql.server[14127]: /etc/init.d/mysql.server: 1:
/etc/init.d/mysql.server: my_print_defaults: not found
Dec 24 22:21:41 iZ8vbj9qj1ctpijae0bxunZ mysql.server[14127]: /etc/init.d/mysql.server: 259: cd: can't cd to /usr/local/mysql
Dec 24 22:21:41 iZ8vbj9qj1ctpijae0bxunZ mysql.server[14127]: Starting MySQL
Dec 24 22:21:41 iZ8vbj9qj1ctpijae0bxunZ mysql.server[14127]: * Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)
Dec 24 22:21:41 iZ8vbj9qj1ctpijae0bxunZ systemd[1]: Started LSB: start and stop MySQL
这种情况修改启动脚本,另外,需要root以外的用户来启动bin/mysqld,且是后台进程,遗留问题:上文设置的服务并无法生效。
下面是来自官方的安装方法,非常的简单,过程如下:
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar xvf /path/to/mysql-VERSION-OS.tar.xz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql-8.0.18-linux-glibc2.12-x86_64 --datadir=/usr/local/mysql-8.0.18-linux-glibc2.12-x86_64/data # 会生成一个临时密钥,且可登录,但是切换到mysql库,报:ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.,可以使用下方的更改密码的语句alter更改后,可访问 k
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
shell> ./mysql -uroot -p
shell> alter USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass123' # 修改了密码才能切换
shell> use mysql; # 切换库
shell> create user 'ggh_dba'@'%' IDENTIFIED WITH mysql_native_password BY 'pass123' # 建一个新用户
shell> grant all privileges on *.* to 'ggh_dba'@'%';
- 如果出现这种坑:Do you already have another mysqld server running on socket: /tmp/mysql.sock ?
清理一下类似文件:/tmp/mysql*,这是历史遗留产物。启动成功!
- 没密码登录不了
以完全模式启动:
mysqld_safe --skip-grant-tables --skip-networking &
,可免密登录, 设置密码:set password for 'root'@'localhost' = 'root'
,参考自官方文档
如果以上方法无法修改密码,因为加了skip-grant-tables的参数的话,那么可以试一下以下方法:
update user set authentication_string=password(‘123’) where user=‘root’;
flush privileges;
(以上在5.7.18版本上验证通过,用status命令查到的mysql版本号的)
创建新用户的方法:CREATE USER 'jeffrey'@'localhost' IDENTIFIED with mysql_native_password BY 'password'
,参考自官方文档
给用户授权:
grant insert,update on toc.toc_borrow_history to 'support'@'%';
grant insert,update on toc.toc_transaction to 'support'@'%';
grant select on toc.* to 'support'@'%';
2.3 填坑
- 坑1
报Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set
解决办法:增加配置spring.datasource.jpa.database-platform: org.hibernate.dialect.MySQLDialect ,更多资料见这里
- 坑2
报Unable to load authentication plugin 'caching_sha2_password'
从8开始,已经从mysql_native_password加密方法,改为caching_sha2_password,
解决办法:ALTER USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass123'
;