如果线上的mysql升级怎样做_MySQL升级问题

今天做了MySQL升级,从 5.1.26-rc 升级 到 5.5.15,都是从官网下载的源码包进行编译的,其中MySQL 5.5.15是由我的同事提供的

由于现有数据库采用innodb的引擎,故采用mysqldump导出备份,然后再导入备份的方式进行升级

升级大致步骤准备:

1、对现有数据库做备份

2、对现有的MySQL程序重命名(防止升级失败还能恢复)

3、对现有权限库做单独备份

4、替换现有MySQL程序

5、启动MySQL 5.5.15,导入备份

注:特别注意mysql日志提示

在升级后启动MySQL 5.5.15的过程中遇到很多提示错误,根据错误提示加以修改,很快解决问题。

a、我所安装升级的MySQL 5.5.15,编译目录选择的是/usr/local/mysql,但是我们线上的数据库程序所在目录为/usr/local/mysql5,我按照线上的进行升级,也将程序按照线上服务部署,故在启动的过程中提示如下错误:

120717 11:19:41 mysqld_safe The file /usr/local/mysql/bin/mysqld

does not exist or is not executable. Please cd to the mysql installation

directory and restart this script from there as follows:

./bin/mysqld_safe&

See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information

解决方法:将程序目录更改为/usr/local/mysql

b、配置文件中有[mysqld]选项段有如下选项--log-slow-queries,这个选项属于老版本的选项,根据mysql日志文件GY_ZGC_97.err错误提示加以修改:

120717 11:21:32 mysqld_safe Starting mysqld daemon with databases from /home/mysql/data

120717 11:21:32 [Warning] The syntax '--log-slow-queries' is deprecated and will be removed in a future release. Please use '--slow-query-log'/'--slow-query-log-file' instead.

120717 11:21:32 InnoDB: The InnoDB memory heap is disabled

120717 11:21:32 InnoDB: Mutexes and rw_locks use InnoDB's own implementation

120717 11:21:32 InnoDB: Compressed tables use zlib 1.2.3

120717 11:21:33 InnoDB: Initializing buffer pool, size = 1000.0M

120717 11:21:33 InnoDB: Completed initialization of buffer pool

解决方法:将配置文件中的log-slow-queries替换为slow-query-log

c、mysql启动有个选项--datadir,该选项指定的目录没有权限问题:

120717 11:21:33  InnoDB: Operating system error number 13 in a file operation.

InnoDB: The error means mysqld does not have the access rights to

InnoDB: the directory.

InnoDB: File name ./ibdata1

InnoDB: File operation call: 'create'.

InnoDB: Cannot continue operation.

120717 11:21:33 mysqld_safe mysqld from pid file /home/mysql/data/GY_ZGC_97.pid ended

解决方法:将mysql数据目录属主更改为mysql用户:chown -R mysql.mysql data

d、mysql配置文件有如下选项:default-character-set=utf8

120717 11:22:46 InnoDB: 1.1.8 started; log sequence number 0

120717 11:22:46 [ERROR] /usr/local/mysql/bin/mysqld: unknown variable 'default-character-set=utf8'

120717 11:22:46 [ERROR] Aborting

120717 11:22:46  InnoDB: Starting shutdown...

120717 11:22:47  InnoDB: Shutdown completed; log sequence number 1595675

120717 11:22:47 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete

120717 11:22:47 mysqld_safe mysqld from pid file /home/mysql/data/GY_ZGC_97.pid ended

解决方法:注释掉该选项

e、mysql权限库直接用的原来的权限库,有如下错误提示:

120717 11:25:00 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it

120717 11:25:00 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure

120717 11:25:00 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure

解决方法:根据提示只需要运行bin/mysql_upgrade,类似如下命令(特别注意:本地在启动多个数据库的情况下,需要通过--sock制定要访问的数据库)

[root@245 mysql]# bin/mysql_upgrade --sock=/tmp/mysql_log.sock -p

Enter password:

Looking for 'mysql' as: ./mysql

Looking for 'mysqlcheck' as: ./mysqlcheck

Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql_log.sock'

Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql_log.sock'

mysql.columns_priv                                 OK

mysql.db                                           OK

mysql.event                                        OK

mysql.func                                         OK

mysql.general_log                                  OK

mysql.help_category                                OK

mysql.help_keyword                                 OK

mysql.help_relation                                OK

mysql.help_topic                                   OK

mysql.host                                         OK

mysql.ndb_binlog_index                             OK

mysql.plugin                                       OK

mysql.proc                                         OK

mysql.procs_priv                                   OK

mysql.servers                                      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

Running 'mysql_fix_privilege_tables'...

OK

对于MySQL升级有很多种方法,我个人选择了导入导出的方法,如果有读者对其他的方式感兴趣,可以做相关尝试。

以上是升级过程中的相关问题,做了简单总结,适合初学者。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值