修订记录
| 时间 | 编辑人员 | 编辑情况 |
| 2020-9-2 | 章瀚中 | 首次编译编辑 |
目的
mysql5.6的编译安装和升级mysql5.7 形成操作知识文档。
系统信息
| 操作系统 | 配置 | IP地址 | 软件版本 |
| Centos 7.6 | 4c4g | ---.---.---.114 | myqsl5.6.49（5.6的最新版）、myqsl5.7.9 |
mysql5.6.49编译耗时20分钟，
mysql5.7编译安装耗时29分钟，
bin目录下二进制文件替换升级耗时4分钟完成
myqldump备份导入导出升级方式取决于数据量的大小
注意事项
本次操作均在 /usr/local/mysqltest 下进行，且在后续新建mysql用户和组后将本目录归属者、归属组设置为mysql
项目最后目录结构，文中出现的目录名称与此结构对应。
/usr/local/mysqltest
├── mysql-5.7.9.tar.gz
├── mysql-5.6.49.tar.gz
├── mysql-5.7.9
├── mysql-5.6.49
├── mysql57
├── mysql56
├── mysql_data57
├── mysql_data56
├── mysql56to57.sql
└── msyql56all.sql各目录含义
mysql-5.7.9.tar.gz #mysql5.7的压缩包
mysql-5.6.49.tar.gz #mysql5.6的压缩包
mysql-5.7.9 #mysql5.6的解压后的目录，用于cmake编译和make构建安装命令执行
mysql-5.6.49 #mysql5.6解压后的目录，用于cmake编译和make构建安装命令执行
mysql57 #muysql5.6的基础目录，cmake时指定的构建安装目录，make命令将会在此目录生成mysql5.7的基础文件,初始化在这里运行
mysql56 #muysql5.6的基础目录，cmake时指定的构建安装目录，make命令将会在此目录生成mysql5.6的基础文件，初始化命令在这里运行
mysql_data57 #初始化时指定的mysql5.7数据目录
mysql_data56 #初始化时指定的mysql5.6数据目录
mysql56to57.sql #在进行逻辑升级时的全量备份
msyql56all.sql #下文中第一次全备的目录Mysql5.6 的源码安装（自定义目录）和升级
下载源码包也可以执行命令
wget https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.49.tar.gz
1.源码安装
安装必要的支持包
yum -y install make bison-devel ncures-devel libaio perl-Data-Dumper net-tools bison bison-devel gcc gcc-c++ cmake ncurses ncurses-developenssl openssl-devel
创建mysql用户
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql解压压缩包
shell> mkdir /usr/local/mysqltest
shell> cd /usr/local/mysqltest
将压缩包上传到此目录下
shell> tar -xvf mysql-5.6.49.tar.gz
shell> cd mysql-5.6.49进行编译，常用的编译语句
-DBUILD_CONFIG=mysql_release: Configure the source with the same build options used by Oracle to produce binary distributions for official MySQL releases.-DCMAKE_INSTALL_PREFIX=*dir_name*: Configure the distribution for installation under a particular location. 我们这里只要指定安装路径就好啦-DCPACK_MONOLITHIC_INSTALL=1: Cause make package to generate a single installation file rather than multiple files.-DWITH_DEBUG=1: Build the distribution with debugging support.
其它编译选项
[root@localhost mysqltest]# mkdir mysql56
[root@localhost mysql-5.6.49]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysqltest/mysql56如有报错后请仔细阅读错误内容
system (use the OS openssl library), yes (synonym for system),
</path/to/custom/openssl/installation>
CMake Error at cmake/ssl.cmake:66 (MESSAGE):
Please install the appropriate openssl developer package.Call Stack (most recent call first):
cmake/ssl.cmake:260 (FATAL_SSL_NOT_FOUND_ERROR)
CMakeLists.txt:483 (MYSQL_CHECK_SSL)```读一下是因为没有openssl 开发包下载一下，
yum -y install openssl openssl-devel
然后删除文件夹下的文件 rm CMakeCache.txt 重新编译即可
rm -f CMakeCache.txt
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysqltest/mysql56
一般，这里遇到的问题就是缺少软件，缺什么就下载什么
开始构建安装
make&&make install
创建其数据目录并且赋予权限
mkdir /data
chown mysql.mysql /data
然后到安装好mysqld的目录里去初始化mysql，这时里面的文件所属组和所属人都是root
修改里面修改文件的权限
chown -R mysql.mysql mysql56/
[root@localhost mysql56]# ll
total 244
drwxr-xr-x 2 mysql mysql 4096 Sep 2 08:15 bin
drwxr-xr-x 3 mysql mysql 18 Sep 2 08:15 data
drwxr-xr-x 2 mysql mysql 55 Sep 2 08:15 docs
drwxr-xr-x 3 mysql mysql 4096 Sep 2 08:15 include
drwxr-xr-x 3 mysql mysql 291 Sep 2 08:15 lib
-rw-r--r-- 1 mysql mysql 219891 Jun 2 13:32 LICENSE
drwxr-xr-x 4 mysql mysql 30 Sep 2 08:15 man
-rw-r--r-- 1 mysql mysql 1189 Sep 2 13:37 my.cnf
drwxr-xr-x 10 mysql mysql 4096 Sep 2 09:00 mysql-test
-rw-r--r-- 1 mysql mysql 587 Jun 2 13:32 README
drwxr-xr-x 2 mysql mysql 30 Sep 2 08:15 scripts
drwxr-xr-x 28 mysql mysql 4096 Sep 2 08:16 share
drwxr-xr-x 4 mysql mysql 4096 Sep 2 08:16 sql-bench
drwxr-xr-x 2 mysql mysql 136 Sep 2 08:16 support-filesMysql的基础目录，一般的mysql会包含如下目录
| 目录名称 | 文件目录说明 | | ------------- | ----------------------------------------------------------- | | bin | mysqld server，cluent and utility programs | | docs | mysql manual in info format | | man | unix manual pages | | include | include(header) files | | lib | libraries | | share | error messages,dictionary,and sql for database installaiton | | support-files | miscellaneous support files |
将mysql加入环境变量
[root@localhost usr]# echo 'export PATH=/usr/local/mysqltest/mysql56/bin:$PATH' >> /etc/profile
[root@localhost usr]# source /etc/profile初始化mysqld，注意这里初始化时指定的mysql基础目录和数据目录官方文档
这时是进入到安装目录的scripts目录中
[root@localhost scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysqltest/mysql56 --datadir=/data
当我们初始化完成后 ，屏幕会输出
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/mysqltest/mysql56/bin/mysqladmin -u root password 'new-password'
/usr/local/mysqltest/mysql56/bin/mysqladmin -u root -h localhost password 'new-password'
Alternatively you can run:
/usr/local/mysqltest/mysql56/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd . ;/usr/local/mysqltest/mysql56/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysql.com/
The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
New default config file was created as //usr/local/mysqltest/mysql56/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings
WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server从输出信息获知：
1.启动前需要将support-files/mysql.server 复制到正确的位置，
需要先修改这个文件里面的 basedir 和datadir
[root@localhost mysql]# cp support-files/mysql.server /etc/init.d/myqsld
[root@localhost mysql]# chmod 755 /etc/init.d/myqsld
[root@localhost mysql]# chkconfig mysqld on
[root@localhost mysql]# systemctl daemon-reload2.启动前需要将默认的/etc/my.cnf 删除，然后修改自己文件夹找下的my.cnf 默认读取此文件
因为是自建目录，所以需要在配置文件里写明
[mysqld_safe]
#指定mysql的pid 日志位置
err-log=/var/log/mysqld.log
pid-file=/usr/local/mysqltest/mysql56/mysqld_safe.pid
[mysqladmin]
#指定mysqladmin运行时连接mysql的socket位置
socket =/usr/local/mysqltest/mysql56/mysql56.socket
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
datadir = /usr/local/mysqltest/mysql56/
basedir = /usr/local/mysqltest/mysql56/
socket = /usr/local/mysqltest/mysql56/mysql56.socket
log-error=/var/log/mysqld-error.log
general_log_file=/var/log/general_log.log
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
[mysql]
socket = /usr/local/mysqltest/mysql56/mysql56.socket3.以守护进程启动mysqld cd . ; /usr/local/mysqltest/mysql56/bin/mysqld_safe --defaults-file=/usr/local/mysqltest/mysql56/my.cnf & 这种方法启动需要使用kill -15 <mysql-pid>关闭
也可以 systemctl start mysqld 这种启动就用systemctl正常管理
要使用命令修改root的密码,方便连接。
4.在启动mysqld后需要使用命令修改root的密码,方便连接。
/usr/local/mysqltest/mysql56/bin/mysqladmin --defaults-file=/usr/local/mysqltest/mysql56/my.cnf -u root -h localhost password '********'
2.连接mysql5.6
bin/mysql --defaults-file=/usr/local/mysqltest/mysql56/my.cnf -u root -p'**********'
可以在这里修改密码：
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
#修改密码语句
mysql> update user set password = password('Root-123') where user = 'root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#查看版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.49 |
+-----------+
1 row in set (0.00 sec)2.1 创建用户和表mysql基本操作
mysql> create user 'hanzhongtest'@'%' identified by 'Hanzhongtest';
Query OK, 0 rows affected (0.00 sec)
mysql> create database hanzhongtestdatabase ;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on hanzhongtestdatabase.* to 'hanzhongtest';
Query OK, 0 rows affected (0.00 sec)此时建库表为了看升级之后是否数据丢失等
mysql> create database hanzhongtestdatabase;
mysql> use hanzhongtestdatabase;
mysql> create table message(
name varchar(20) not null,
id int(10) not null ) ;3.备份数据
备份整个mysql5.6，方便后续操作误删后的恢复
[root@localhost mysqltest]# mysql/bin/mysqldump -S /usr/local/mysqltest/mysql/mysql56.socket -u root -p'Root-123' --all-databases > msyql56all.sql4.1 升级到5.7
4.1 首先编译构建mysql5.7
[root@localhost mysqltest]# tar -xvf mysql-5.7.9.tar.gz
[root@localhost mysqltest]# mkdir mysql57
[root@localhost mysqltest]# cd mysql-5.7.9
[root@localhost mysql-5.7.9]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysqltest/mysql57出错后认真阅读
CMake Error at cmake/boost.cmake:76 (MESSAGE):
You can download it with -DDOWNLOAD_BOOST=1 -DWITH_BOOST=<directory>
This CMake script will look for boost in <directory>. If it is not there,
it will download and unpack it (in that directory) for you.
If you are inside a firewall, you may need to use an http proxy:
export http_proxy=http://example.com:80
Call Stack (most recent call first):
cmake/boost.cmake:228 (COULD_NOT_FIND_BOOST)
CMakeLists.txt:435 (INCLUDE)
MySQL在5.7版本及以后，都需要boots 库，所以需要先安装boots
1.在/usr/local下创建 名为boots的目录
mkdir -p /usr/local/boots
2.进入该目录，然后下载boots
wget https://sourceforge.net/projects/boost/files/boost/1.74.0/boost_1_74_0.tar.bz2
3.解压
tar -xvf boost_1_74_0.tar.bz2
缺少bzip2 就去yum -y install bzip2此后cmake编译主要加上 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost/boost_1_74_0
继续cmake
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysqltest/mysql57 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/usr/local/boost/boost_1_74_0
此后进行构建安装
make && make install
4.2 初始化mysql5.7
使用替换bin目录方式升级，无需做俩！行之间的操作
！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
为了后续方便进行逻辑升级，我们需要将数据目录和生成日志都要与mysql5.6进行区分,将此次自己写的my.cnf 拷贝为/etc/my.cnf
注意目录#./bin/mysqld --defaults-file=/usr/local/mysqltest/mysql57/my.cnf --basedir=/usr/local/mysqltest/mysql57/ --datadir=/usr/local/mysqltest/mysql_data57/ --user=mysql --initialize然后到 my.cnf里规定的日志处 看初始密码,启动mysqld(此处需要注意，如果你的mysql5.6正在运行直接看！下面的内容)
[root@localhost mysql57]# /usr/local/mysqltest/mysql57/bin/mysqld_safe &
[root@localhost mysql57]# /usr/local/mysqltest/mysql57/bin/mysql -u root -p‘刚才看的初始密码’
mysql> set password = password('Root-123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
4.3 升级注意事项
做之前先做镜像方便后续快速恢复操作
- 仅在一般可用性（GA）版本之间支持升级。
- 从MySQL 5.6升级到5.7。建议先升级到最新版本，再升级到下一版本。例如，在升级到MySQL 5.7之前，先升级到最新的MySQL 5.6版本。
- 不支持跳过版本的升级。例如，不支持从MySQL 5.5直接升级到5.7。
其次需要阅读 升级mysql5.7须知 ，下面是对于升级的注意事项概述
一、5.6升级5.7时如果有YEAR(2) 数据类型的列 ，需要改为 4位数的year 官方手册修改方式
二、对于5.6.4表中时间格式中 ‘秒’ 如果没有精确到小数， avoid_temporal_upgrade 开启mysql_upgrade 将不会升级这个表， avoid_temporal_upgrade 禁用将有利于升级，且 avoid_temporal_upgrade 被禁后 REOAIR TABLE 将会升级表。
三、MySQL 5.7.2开始，服务器要求mysql.user系统表中的帐户行具有非空plugin列值，并禁用具有空值的帐户。这要求您升级mysql.user表时需要填写所有 plugin值。
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.00 sec)
mysql> select User,plugin from user;
+--------------+-----------------------+
| User | plugin |
+--------------+-----------------------+
| root | mysql_native_password |
| root | mysql_native_password |
| root | mysql_native_password |
| | mysql_native_password |
| hanzhongtest | mysql_native_password |
+--------------+-----------------------+
5 rows in set (0.00 sec)
mysql>
#检查发现都是有值，所以不用理会4.4 mysql_upgrade
每次升级都应运行查看兼容情况
官方文档
- It upgrades the system tables in the
mysqlschema so that you can take advantage of new privileges or capabilities that might have been added. - It upgrades the Performance Schema and
sysschema. - It examines user schemas.
检查到有错误它会自动修复，修复失败了看官网这里
4.5 bin升级方法：使用原数据目录升级
注意上文写的关闭方式
1.Stop the old (MySQL 5.6) server，此时需要考虑到事务的提交，
配置慢速关机mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0" ,这样会使得innodb进行完全清除和更改缓冲区，确保当俩版本数据文件不同，可以充分准备数据。
最后执行mysqladmin -u root -p shutdown 关闭mysql。
2.Upgrade the MySQL binaries in place by replacing the old binaries with the new ones
将mysql5.6的bin全部替换为mysql5.7的。
[root@localhost bin]# cp -f mysql57/bin/* ../mysql56/bin/3.Start the MySQL 5.7 server normally (no special options)
此时需要mysqld_safe启动一次才可以systemctl启动
/usr/local/mysqltest/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysqltest/mysql/my.cnf &
[root@localhost mysqltest]# mysql -u root -p'Root-123'
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.9 |
+-----------+
1 row in set (0.00 sec)
mysql> use hanzhongtestdatabase
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------------------------+
| Tables_in_hanzhongtestdatabase |
+--------------------------------+
| message |
+--------------------------------+
1 row in set (0.00 sec)
mysql> desc message
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| id | int(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)此时我们简单的表还可以正常访问，剩下就是弄一下万一有数据类型复杂的表我们该怎么办。
4.Run mysql_upgrade to upgrade the system tables
#mysql_upgrade -S /usr/local/mysqltest/mysql/mysql56.socket -u root -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading the sys schema.
Checking databases.
hanzhongtestdatabase.message OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.6.Restart the MySQL 5.7 server
# kill -15 <mysqld-pid>
# /usr/local/mysqltest/mysql/bin/mysqld_safe --defaults-file=/usr/local/mysqltest/mysql/my.cnf &4.6逻辑升级方法：使用载入备份文件进行升级
此时需要恢复未升级的状态
1.To generate the dump file, run mysqldump with the --add-drop-table option and without the --flush-privileges option #其中 --add-drop-table的意义为：在每一条创建表语句前加入删除表的声明
[root@localhost mysqltest]# mysqldump -u root -p'Root-123' -S /usr/local/mysqltest/mysql/mysql56.socket --all-databases --add-drop-table > mysql56to57.sql2.Stop the old (MySQL 5.6) server
3.Upgrade the MySQL binaries in place (replace the old binaries with the new ones)
mysqladmin -u root -p shutdown
4.Start the MySQL 5.7 server normally (no special options)
/usr/local/mysqltest/mysql57/bin/mysqld_safe &，详见 4.2 初始化mysql5.7
5.Reload the dump file (mysql < dump_file)
/usr/local/mysqltest/mysql57/bin/mysql -u root -p'Root-123'< mysql56to57.sql
6.Run mysql_upgrade to upgrade the system tables
[root@localhost mysqltest]# mysql57/bin/mysql_upgrade -S /usr/local/mysqltest/mysql57/mysql57.socket -u root -p'Root-123'
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
The sys schema is already up to date (version 1.5.0).
Found 0 sys functions, but expected 21. Re-installing the sys schema.
Upgrading the sys schema.
Checking databases.
hanzhongtestdatabase.message OK
sys.sys_config OK
Upgrade process completed successfully.
Checking if update is needed.7.Restart the MySQL 5.7 server
# kill -15 <mysqld-pid>
# /usr/local/mysqltest/mysql57/bin/mysqld_safe --defaults-file=/usr/local/mysqltest/mysql57/my.cnf &
/usr/local/mysqltest/mysql57/bin/mysql -u root -p'Root-123'
进入mysql后发现
mysql> use hanzhongtestdatabase
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+--------------------------------+
| Tables_in_hanzhongtestdatabase |
+--------------------------------+
| message |
+--------------------------------+
1 row in set (0.00 sec)
mysql> desc message;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| id | int(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)升级完成！
升级完成！
总结
总体来说手动进行编译、升级、执行步骤缓慢，争取写成脚本一键执行。
对于升级过程来说
| bin二进制文件替换升级 | mysqldump备份导入导出升级 |
| 整体速度快 | 升级速度主要取决于数据量大小、备份花费的时间 |
263
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
