MySQL学习笔记MySQL选择标准
MySQL : 企业现存版本最多的 5.6 > 5.7 > 5.5 > 8.0 建议 MySQL 8.0.17以后的版本 MySQL 5.7.26 选择最新的双数版本（5.7以后的选择标准）
获取MySQL软件
源码 : 看源码 , 二次开发 C++ 二进制: 解压就能用 YUM : 提供yum源 ,需要网络 一般不选择yum,数据库产品yum安装不安全 下载地址：https://downloads.mysql.com/archives/community/ 选择8.0.18版本 官方文档:https://dev.mysql.com/doc/refman/8.0/en/
MySQL 8.0.18 二进制版本企业级部署
确认支持系统列表
https://www.mysql.com/support/supportedplatforms/database.html https://www.mysql.com/support/eol-notice.html
创建数据库管理用户
[root@db04 ~]# useradd mysql
创建数据和日志目录
[root@db04 ~]# mkdir -p /data/mysql/data_3306 [root@db04 ~]# mkdir -p /data/mysql/binlog_3306
上传软件MD5验证并解压
[root@db04 ~]# cd /usr/local
rz waiting to receive.
[root@db04 local]# ll
-rw-r--r-- 1 root root 644869837 Oct 12 03:56 mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
-rw-r--r-- 1 root root 503854832 Oct 12 04:00 mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
[root@db04 local]# tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@db04 local]# tar xf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
MD5验证软件包
[root@db04 local]# md5sum mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
[root@db04 local]# md5sum mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
制作软连接
[root@db04 local]# ln -s mysql-5.7.26-linux-glibc2.12-x86_64 mysql57 [root@db04 local]# ln -s mysql-8.0.18-linux-glibc2.12-x86_64 mysql8
环境变量设置
[root@db04 local]# vim /etc/profile export PATH=/usr/local/mysql57/bin:$PATH [root@db04 local]# source /etc/profile [root@db04 local]# mysql -V mysql Ver 14.14 Distrib 5.7.26, for linux-glibc2.12 (x86_64) using EditLine wrapper
权限设置
[root@db04 local]# chown -R mysql.mysql /data /usr/local/mysql*
初始化
[root@db04 local]# yum install -y libaio-devel [root@db04 local]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3306 [root@db04 local]# cd /data/mysql [root@db04 mysql]# ll total 0 drwxr-xr-x 2 mysql mysql 6 Oct 31 15:48 binlog_3306 drwxr-xr-x 5 mysql mysql 147 Oct 31 16:06 data_3306 初始化方式区别对比： mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3306 ---------------------------------------- mysqld --initialize --user=mysql --basedir=/usr/local/mysql57 --datadir=/data/mysql/data_3306 1. --initialize-insecure : 不安全的 , 没有密码,没有密码策略 2. --initialize: 安全的模式,没次初始化完成都会生成临时密码,只能登陆数据库,需要人为修改后才能正常管理数据库 [Note] A temporary password is generated for root@localhost: )k0idgBRqq>; 修改密码： [root@db04 local]# mysqladmin -uroot -p password Oguooo@88 Enter password:)k0idgBRqq> 3. 5.6 中版本 /usr/local/mysql56/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql56 --datadir=/data/mysql/data_3306
启动数据库
原始MariaDB删除 [root@db04 mysql]# yum remove -y mariadb-libs -------------------------------- 配置文件 cat > /etc/my.cnf <<EOF [mysqld] user=mysql basedir=/usr/local/mysql57 datadir=/data/mysql/data_3306 socket=/tmp/mysql.sock server_id=6 log_bin=/data/mysql/binlog_3306 port=3306 [mysql] socket=/tmp/mysql.sock EOF -------------------------------- [root@db04 mysql]# cd /usr/local/mysql57/support-files/ [root@db04 support-files]# ll total 24 -rw-r--r-- 1 mysql mysql 773 Apr 13 2019 magic -rwxr-xr-x 1 mysql mysql 1061 Apr 13 2019 mysqld_multi.server -rwxr-xr-x 1 mysql mysql 894 Apr 13 2019 mysql-log-rotate -rwxr-xr-x 1 mysql mysql 10576 Apr 13 2019 mysql.server -------------------------------- [root@db04 support-files]# cp mysql.server /etc/init.d/mysqld [root@db04 support-files]# service mysqld start Starting MySQL.Logging to '/data/mysql/data_3306/db04.err'. SUCCESS! -------------------------------- [root@db04 support-files]# chkconfig --add mysqld [root@db04 support-files]# systemctl stop mysqld [root@db04 support-files]# systemctl start mysqld
下载目标版本的mysqlsh
5.6,5.7升级8.0是比较平滑升级的，5.5升级相对有些问题，应该先升级至5.7，再升级至8.0 [root@db04 local]# tar xf mysql-shell-8.0.18-linux-glibc2.12-x86-64bit.tar.gz [root@db04 local]# ln -s mysql-shell-8.0.18-linux-glibc2.12-x86-64bit mysql-sh [root@db04 mysql-sh]# pwd /usr/local/mysql-sh [root@db04 mysql-sh]# ll total 0 drwxr-xr-x 2 7161 31415 58 Sep 13 2019 bin drwxr-xr-x 3 7161 31415 21 Sep 13 2019 lib drwxr-xr-x 3 7161 31415 21 Sep 13 2019 share [root@db04 mysql-sh]# ll bin total 39456 -rwxr-xr-x 1 7161 31415 7785608 Sep 13 2019 mysql-secret-store-login-path -rwxr-xr-x 1 7161 31415 32613064 Sep 13 2019 mysqlsh [root@db04 mysql-sh]# vim /etc/profile export PATH=/usr/local/mysql57/bin:/usr/local/mysql-sh/bin:$PATH [root@db04 local]# source /etc/profile
使用mysqlsh检查原版本兼容情况
在5.7 数据库授权用户 mysql> grant all on *.* to root@'10.0.0.%' identified by '123'; 检查并处理不兼容的事项 root@db04 mysql-sh]# mysqlsh root:123@10.0.0.204:3306 -e "util.checkForServerUpgrade()" >/tmp/upgrade.log
修改配置文件
[root@db04 mysql-sh]# vim /etc/my.cnf [mysqld] user=mysql #basedir=/usr/local/mysql57 basedir=/usr/local/mysql8 datadir=/data/mysql/data_3306 socket=/tmp/mysql.sock server_id=6 log_bin=/data/mysql/binlog_3306 port=3306 [mysql] socket=/tmp/mysql.sock
改环境变量
vim /etc/profile #export PATH=/usr/local/mysql57/bin:/usr/local/mysql-sh/bin:$PATH export PATH=/usr/local/mysql8/bin:/usr/local/mysql-sh/bin:$PATH source /etc/profile
关闭原有数据库并cp备份
pkill mysqld [root@db04 mysql]# pwd /data/mysql [root@db04 mysql]# cp data_3306 data_3306.bak -r 注意：生产中迁移升级，备份恢复
启动配置登陆
[root@db04 mysql]# cd /usr/local/mysql8/support-files/
[root@db04 support-files]# ll
total 20
-rwxr-xr-x 1 mysql mysql 1061 Sep 20 2019 mysqld_multi.server
-rwxr-xr-x 1 mysql mysql 2048 Sep 20 2019 mysql-log-rotate
-rwxr-xr-x 1 mysql mysql 10576 Sep 20 2019 mysql.server
[root@db04 support-files]# cp mysql.server /etc/init.d/mysqld
cp: overwrite ‘/etc/init.d/mysqld’? y
[root@db04 support-files]# /etc/init.d/mysqld start
Starting MySQL....... SUCCESS!
-----------------------------------
[root@db04 support-files]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.18 MySQL Community Server - GPL