TypechoJoeTheme

运维茶馆

统计
登录
用户名
密码

ubuntu1804安装MySQL8

掌柜博主
2021-09-22
/
0 评论
/
1,923 阅读
/
395 个字
/
百度已收录
09/22
本文最后更新于2023年07月07日,已超过515天没有更新。如果文章内容或图片资源失效,请留言反馈,我会及时处理,谢谢!

务必先优化最大打开文件数:Ubuntu优化最大打开文件数

安装MySQL8

打开这里 ,打开之后页面有个download按钮,点击它下载deb包。拿到下载链接,到服务器上使用wget下载到服务器。

## 以下操作使用 root 进行。
# wget https://repo.mysql.com//mysql-apt-config_0.8.19-1_all.deb
# dpkg -i mysql-apt-config_0.8.19-1_all.deb # 安装mysql8 apt源
# cp /etc/apt/sources.list /etc/apt/sources.-bak
# > /etc/apt/sources.list
cat /etc/apt/sources.list # 添加下面 163 源。不用国内源,下载 mysql 可能失败。
deb http://mirrors.163.com/ubuntu/ bionic main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ bionic-security main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ bionic-updates main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ bionic-proposed main restricted universe multiverse
deb http://mirrors.163.com/ubuntu/ bionic-backports main restricted universe multiverse

# apt install mysql-server -y
## 输入两次密码,OK。OK,然后选择 MySQL 5.x,使用兼容老的认证方式。

启动服务

# systemctl enable mysql
# systemctl start mysql
# systemctl status mysql
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Wed 2021-09-22 07:17:35 UTC; 1min 43s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 8963 (mysqld)
   Status: "Server is operational"
    Tasks: 37 (limit: 4629)
   CGroup: /system.slice/mysql.service
           └─8963 /usr/sbin/mysqld

Sep 22 07:17:35 zk systemd[1]: Starting MySQL Community Server...
Sep 22 07:17:35 zk systemd[1]: Started MySQL Community Server.

# 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 MySQL Community Server - GPL

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.

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

mysql> flush privileges;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> exit
Bye

以上对mysql的简单安装已经完成。

表名不区分大小写

lower_case_table_names 是 mysql 一个大小写敏感设置的属性,此参数不可以动态修改。
默认值:

  • unix,linux 下 默认值为 0
  • Windows 下默认值是 1
  • Mac OS 下默认值是 2

mysql8 安装好后,默认是0,即区分大小写。而且不支持,在配置添加lower_case_table_names=1,改成不区分大小写,5.x版本是支持的。。。mysql8 必须在初始化的时候,设置好值为1。下面配置、并手动初始化一下:

root@opstea:~#  mkdir /data/db/mysql8/data -p
root@opstea:~# chown -R mysql.mysql /data/db/mysql8/
root@opstea:~# vim /etc/mysql/mysql.conf.d/mysqld.cnf 
root@opstea:~# egrep -v "^#|^$" /etc/mysql/mysql.conf.d/mysqld.cnf 
[mysqld_safe]
socket                = /var/run/mysqld/mysqld.sock
nice                = 0
[mysqld]
lower_case_table_names=1 # 添加这行
user                = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket                = /var/run/mysqld/mysqld.sock
port                = 3306
basedir                = /data/db/mysql8
datadir                = /data/db/mysql8/data
tmpdir                = /tmp
lc-messages-dir        = /usr/share/mysql
skip-external-locking
bind-address                = 192.168.23.130
key_buffer_size                = 16M
max_allowed_packet        = 16M
thread_stack                = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit        = 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
expire_logs_days        = 10
max_binlog_size   = 100M

不指定数据目录的话,你也可以将/var/lib/mysql/*下的数据删了,再手动初始化。

手动初始化

# 由于自定义了数据目录,需要:
root@opstea:~# vim /etc/apparmor.d/usr.sbin.mysqld
# 找到:
# Allow data dir access
  /lvmdata/data/ r,
  /lvmdata/data/** rwk,
# 改成:
  /data/db/mysql8/ r,
  /data/db/mysql8/** rwk,
 # 然后重启服务
 root@opstea:~# service apparmor restart
 root@opstea:~# /usr/sbin/mysqld --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --initialize --user=mysql --lower-case-table-names=1

启动服务

root@opstea:~# systemctl enable mysql
root@opstea:~# systemctl start mysql
root@opstea:~# systemctl status mysql

进入数据库

root@opstea:~# grep password /var/log/mysql/error.log # 查看密码
root@opstea:~# 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 MySQL Community Server - GPL
mysql> show databases;
ERROR 1018 (HY000): Can't read dir of '.' (errno: 13 - Permission denied)

# 报错解决:
root@opstea:/data/db# chown -R mysql:mysql mysql8/
root@opstea:/data/db# chmod -R 755 mysql8/
root@opstea:/data/db# systemctl restart mysql

# 再次查看
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
# 需要先修改密码
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> flush privileges;

mysql> exit
Bye

设置远程连接

# 查看用户信息,来源主机,用户名,认证插件、认证加密字符串
mysql> use mysql;
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+---------------+-----------------------+-------------------------------------------+
| host      | user          | plugin                | authentication_string                     |
+-----------+---------------+-----------------------+-------------------------------------------+
| localhost | root          | mysql_native_password | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
| localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-----------------------+-------------------------------------------+
mysql> update user set host='%' where user='root'; # 现在密码是前面设置的12345678
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+---------------+-----------------------+-------------------------------------------+
| host      | user          | plugin                | authentication_string                     |
+-----------+---------------+-----------------------+-------------------------------------------+
| %         | root          | mysql_native_password | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
| localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)

# 如果还想改密码:
mysql> alter user 'root'@'%' identified with mysql_native_password by '12345678abc';
mysql> flush privileges;

卸载MySQL

# apt autoremove mysql-server # 只删服务,不删数据,重新安装的话,数据还在
# apt autoremove --purge mysql-server # 把数据也删了。

赞(0)
赞赏
感谢您的支持,我会继续努力哒!
版权属于:

运维茶馆

本文链接:

https://opstea.com/archives/9.html(转载时请注明本文出处及文章链接)

评论 (0)