务必先优化最大打开文件数：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 # 把数据也删了。