最近系統(tǒng)漏洞掃描，掃出來很多MySQL的漏洞，沒有什么好的辦法，先升級到最新版本。在梳理MySQL數(shù)據(jù)庫信息時發(fā)現(xiàn)有一套zabbix用的MySQL版本是5.1.71的，現(xiàn)在的最新版本是5.7.17，版本跨越的太大了，不知道直接升級是否可行。庫的數(shù)據(jù)量大概15G，于是就想先備份，備份完后用mysql_upgrade做升級。如果實在不行就再新創(chuàng)建一套庫把數(shù)據(jù)導進去。
創(chuàng)新互聯(lián)是一家專注于成都網(wǎng)站制作、成都做網(wǎng)站與策劃設計,拱墅網(wǎng)站建設哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設十余年,網(wǎng)設計領域的專業(yè)建站公司;建站業(yè)務涵蓋:拱墅等地區(qū)。拱墅做網(wǎng)站價格咨詢:18980820575
下面是在虛擬機上測試的升級步驟，中間也遇到了一些問題，不過好在最后還是升級成功了，說明這條路還是可行了。
操作系統(tǒng)版本RHEL 6.7 舊的MySQL版本5.1.71，新的MySQL版本5.7.17。
1、查看舊的MySQL版本，并創(chuàng)建測試數(shù)據(jù)庫和測試表
mysql> select version(); +------------------+ | version() | +------------------+ | 5.1.71-community | +------------------+ mysql> create database zx; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | | zx | +--------------------+ 4 rows in set (0.00 sec) mysql> use zx; Database changed mysql> create table test_upgrade (id int); Query OK, 0 rows affected (0.07 sec) ...... mysql> select count(*) from test_upgrade; +----------+ | count(*) | +----------+ | 33554432 | +----------+ 1 row in set (0.00 sec)
現(xiàn)在test_upgrade表里有33554432條數(shù)據(jù)。
2、停止舊MySQL，并備份數(shù)據(jù)
[root@rhel6 mysql]# service mysql stop Shutting down MySQL.. SUCCESS! #這里直接用cp備份數(shù)據(jù) [root@rhel6 lib]# cp -R mysql mysql_bak [root@rhel6 lib]# du -sk mysql* 250880 mysql 250880 mysql_bak
3、安裝新Msql軟件，這里下載的是編譯好的解壓就可以使用
[root@rhel6 ~]# tar -zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz #解壓完后移動到/opt目錄下 [root@rhel6 ~]# mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql-5.7.17 [root@rhel6 ~]# mv mysql-5.7.17 /opt [root@rhel6 ~]# cd /opt [root@rhel6 opt]# chown -R mysql:mysql mysql-5.7.17/ [root@rhel6 opt]# ll total 4 drwxr-xr-x. 9 mysql mysql 4096 Mar 22 21:07 mysql-5.7.17
4、修改my.cnf
主要修改basedir參數(shù)：
[root@rhel6 opt]# vi /etc/my.cnf basedir=/opt/mysql-5.7.17
5、啟動新MySQL
[root@rhel6 mysql-5.7.17]# ./bin/mysqld_safe &
1)啟動報錯1：
2017-03-22T13:12:39.131998Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file './ibdata1' is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!2017-03-22T13:12:39.132028Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error 2017-03-22T13:12:39.733953Z 0 [ERROR] Plugin 'InnoDB' init function returned error. 2017-03-22T13:12:39.733986Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 2017-03-22T13:12:39.733994Z 0 [ERROR] Failed to initialize plugins. 2017-03-22T13:12:39.734000Z 0 [ERROR] Aborting
解決方法在my.cnf中的[mysqld]部分添加如下參數(shù)
innodb_data_file_path = ibdata1:10M:autoextend
再次啟動
2)啟動報錯2：
2017-03-22T13:16:03.374717Z 0 [ERROR] unknown option '--skip-locking' 2017-03-22T13:16:03.374735Z 0 [ERROR] Aborting
解決方法從my.cnf中注釋掉相關參數(shù)skip-locking
再次啟動
3)啟動報錯3：
2017-03-22T13:18:20.278752Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade. 2017-03-22T13:18:20.278954Z 0 [ERROR] Aborting
5.7無法讀取5.1的mysql.user表，解決方法使用--skip-grant-tables參數(shù)跳過授權驗證
再次啟動
[root@rhel6 mysql-5.7.17]# ./bin/mysqld_safe --skip-grant-tables&
啟動成功
2017-03-22T13:20:23.919677Z 0 [Note] ./bin/mysqld: ready for connections. Version: '5.7.17' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
6、執(zhí)行升級
[root@rhel6 mysql-5.7.17]# ./bin/mysql_upgrade -uroot 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.host 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. sys.sys_config OK zx.test_upgrade OK Upgrade process completed successfully. Checking if update is needed.
7、重啟MySQL，并驗證
[root@rhel6 mysql-5.7.17]# ./bin/mysqladmin -uroot -p shutdown [root@rhel6 mysql-5.7.17]# ./bin/mysqld_safe & mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.17 | +-----------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | | zx | +--------------------+ 6 rows in set (0.00 sec) mysql> select count(*) from zx.test_upgrade; +----------+ | count(*) | +----------+ | 33554432 | +----------+ 1 row in set (0.01 sec)
驗證通過后，刪除舊版本的MySQL軟件。
重慶分公司
重慶分公司
