掌柜
ubuntu1804安装MySQL8
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 # 把数据也删了。