【MySQL】ubuntu18.04 安装与配置 Mysql8.0（dpkg方式）

ubuntu18.04安装MySQL8.0（dpkg方式）

---

环境

系统：ubuntu18.04

mysql版本：MySQL8.0

清除已有的MySQL

1 sudo apt-get autoremove --purge mysql-server-5.7 
2 sudo apt-get remove mysql-server         
3 sudo apt-get autoremove mysql-server  
4 sudo apt-get remove mysql-common
5 sudo rm -rf /etc/mysql/  /var/lib/mysql    #很重要
# 清理残留数据
7 dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P  
8 sudo apt autoremove
9 sudo apt autoclean
---------
# 更新apt—get
10 sudo apt-get update
11 sudo apt-get upgrade

下载deb格式文件

在官网 https://dev.mysql.com/downloads/repo/apt/ 下载

或者

通过命令下载

wget -c https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb

安装

1、将下载好的文件放入 /usr/server 文件夹下

2、安装lsb-release

dpkg需要依赖lsb_release，不过一般ubuntu都会自带。

3、dpkg方式设置MySQL安装

sudo dpkg -i mysql-apt-config_0.8.13-1_all.deb

然后需要选择两次，分别是产品和产品版本，第一次选择mysqlserver，第二次选择mysql8.0，然后esc退出设置并输入：

sudo apt-get update 
sudo apt-get upgrade

4、安装

sudo apt install mysql-server

输入root的密码并选择加密方式，在Select default authentication plugin，注意选第二项，即mysql5.x版本的加密方式。

如果安装的过程中没有出现加密方式和设置密码的弹窗，则在安装完成后，可以通过

sudo vim /etc/mysql/debian.cnf

查看默认的用户名和密码

登录MySQL

mysql -u root -p 密码

启动、重启、关闭MySQL服务

service mysql start
service mysql restart
service mysql stop

修改MySQL数据存储路径

vim /etc/mysql/mysql.conf.d mysqld.cnf

修改datadir 的路径

mv /var/lib/mysql /data/mysqldata -R
chown mysql:mysql /data/mysqldata -R
chmod 750 /data/mysqldata -R
vim /etc/apparmor.d/usr.sbin.mysqld
#datadir access
改成 /data/mysqldata

ubuntu18.04安装mysql5.7.20（安装包）

---

下载

1、下载方式

官网：https://dev.mysql.com/downloads/mysql/

2、下载后将安装包放入/usr/server

3、赋予可执行权限并借用哇

sudo chmod +x mysql5.7.20.tar.gz

tar -xf mysql5.7.20.tar.gz

4、解压后ls查看有如下文件：

libmysqlclient20_5.7.10-1ubuntu14.04_amd64.deb
 
libmysqlclient-dev_5.7.20-1ubuntu14.04_amd64.deb
libmysqld-dev_5.7.20-1ubuntu14.04_amd64.deb
mysql-client_5.7.20-1ubuntu14.04_amd64.deb
mysql-common_5.7.20-1ubuntu14.04_amd64.deb
mysql-community-client_5.7.20-1ubuntu14.04_amd64.deb
mysql-community-server_5.7.20-1ubuntu14.04_amd64.deb
mysql-community-source_5.7.20-1ubuntu14.04_amd64.deb
mysql-community-test_5.7.20-1ubuntu14.04_amd64.deb
mysql-server_5.7.20-1ubuntu14.04_amd64.deb
mysql-testsuite_5.7.20-1ubuntu14.04_amd64.deb
mysql-community_5.7.20-1ubuntu14.04_amd64.changes

5、更新安装源并安装libaio1依赖：

sudo apt-get update
sudo apt-get upgrade
 
apt-get install libaio1

6、安装：

sudo dpkg -i mysql-common_5.7.20-1ubuntu14.04_amd64.deb
sudo dpkg-preconfigure mysql-community-server_5.7.20-1ubuntu14.04_amd64.deb

# 输入两次root用户的密码

sudo dpkg -i libmysqlclient20_5.7.20-1ubuntu14.04_amd64.deb
sudo dpkg -i libmysqlclient-dev_5.7.20-1ubuntu14.04_amd64.deb
sudo dpkg -i libmysqld-dev_5.7.20-1ubuntu14.04_amd64.deb
sudo dpkg -i mysql-community-client_5.7.20-1ubuntu14.04_amd64.deb
sudo dpkg -i mysql-client_5.7.20-1ubuntu14.04_amd64.deb
sudo dpkg -i mysql-common_5.7.20-1ubuntu14.04_amd64.deb

7、安装依赖包：

sudo apt-get -f install
sudo apt-get -f install libmecab2

8、安装mysql-server

sudo dpkg -i mysql-community-server_5.7.20-1ubuntu14.04_amd64.deb
sudo dpkg -i mysql-server_5.7.20-1ubuntu14.04_amd64.deb

9、验证安装：

mysql -u root -p

登录成功即可验证安装成功

基本设置

---

打开远程访问权限：

mysql -u root -p
password
use mysql;
GRANT ALL ON *.* TO 'root'@'%'  IDENTIFIED BY 'password' WITH GRANT OPTION;
#or grant all privileges on *.* to root@"%" identified by "Zkst@019" ;

flush privileges;
exit

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
#bind 127.0.0.1  （加 井号 注释掉这一行）

service mysql restart

优化mysql配置：

---

1、读写优化

innodb_buffer_pool_size： 缓冲池，多次访问相同表数据时减少磁盘I/O

query_cache_size ：缓存 select 的结果，下次执行同样查询不用再去磁盘读

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
log-error	= /var/log/mysql/error.log

# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

max_allowed_packet = 524288000
back_log = 400
innodb_thread_concurrency = 16
innodb_buffer_pool_size = 28G
thread_cache_size = 64
innodb_log_file_size = 8G
innodb_log_buffer_size = 64M
innodb_flush_method = O_DIRECT
table_open_cache = 1000
max_connections = 2000
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=256M
transaction_isolation=READ-COMMITTED
default-time-zone = '+08:00'