日志
Mysql5.1升级到5.7及报错处理
|||
1、查看旧的MySQL版本，并创建测试数据库和测试表
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)
现在test_upgrade表里有33554432条数据。
2、停止旧MySQL，并备份数据
[root@rhel6 mysql]# service mysql stop
Shutting down MySQL.. SUCCESS!
或者 /etc/init.d/mysqld |stop|status|restart|condrestart|try-restart|reload|force-reload}
#这里直接用cp备份数据目录下的数据 ，默认/var/lib
[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参数：
[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]部分添加如下参数
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中注释掉相关参数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参数跳过授权验证
4)启动报错：升级mysql找不到mysql.sock报错
Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
<1>chmod 777 /var/lib/mysql
然后
service mysql restart
service mysqld restart
或者我用如下方式：
service mysqld stop
service mysqld start
<2>ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
红色字体灵活变动，有时候是mysqld.sock
详情参考https://blog.csdn.net/hjf161105/article/details/78850658
权限修改，启发颇大
5)启动报错：
2017-01-28T09:32:23.944029Z mysqld_safe Logging to '/var/log/mysqld.log'.
2017-01-28T09:32:23.983215Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2017-01-28T09:32:23.994323Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
解决：需要删除/var/lib/mysql下几个ib_logfile，然后重启数据库（对我无显著作用）有时候虽然报错如上，但可按4）中解决方法尝试解决。
详情参考
https://blog.csdn.net/ly_feng/article/details/7917500/
https://blog.csdn.net/hjf161105/article/details/78850658
6）启动报错error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or distory
直接运行：yum -y install numactl.x86_64就可以了
7）
没事的时候删几个数据文件/var/lib/mysql下的 ib_logfile* 的日志文件，目前对我没有很大帮助。详情参考：
再次启动
[root@rhel6 mysql-5.7.17]# ./bin/mysqld_safe --skip-grant-tables&
启动成功,日志中会有很多ERROR，是无法识别旧表，暂时略过，出现以下字样即可：
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、执行升级
[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)
8.升级完成后在控制台或者使用navicate连接都是新升级后的数据库，但是mysql --version 的时候现实的仍是旧版本，需要将新版本mysql包的bin中执行文件替换掉/usr/bin下的执行文件，我只替换了一个mysql，再执行mysql --version就显示新版本了，可见视情况备份而后改。
/2 
