Percona 5.5.30转投MariaDB 5.5.30小记

数据库 其他数据库 MySQL MariaDB
本文将介绍从Percona 5.5.30迁移到MariaDB 5.5.30的小记录。供各位开发者参考,期间会在启动时报错,敬请注意。

Mariadb 5.5.30 用  percona 5.5.30 的mysql系统库启动时会报错

  1. [ERROR] Incorrect definition of table mysql.event: expected column ‘sql_mode’ at position 14 to have type set …… 
  2. [ERROR] mysqld: Table ‘./mysql/tables_priv’ is marked as crashed and should be repaired 
  3. [Warning] Checking table: ‘./mysql/tables_priv’ 
  4. [ERROR] mysql.tables_priv: 1 client is using or hasn’t closed the table properly 
  5. mysql.proc ….. 

Mariadb和percona的mysql.event此表结构有点不同

diff -u  Mariadb.event.txt  percona.event.txt

— event.txt   2013-05-04 10:35:25.939778373 -0400

+++ event2.txt  2013-05-04 10:36:12.221966298 -0400

@@ -12,7 +12,7 @@

   `ends` datetime DEFAULT NULL,

   `status` enum(‘ENABLED’,'DISABLED’,'SLAVESIDE_DISABLED’) NOT NULL DEFAULT ‘ENABLED’,

   `on_completion` enum(‘DROP’,'PRESERVE’) NOT NULL DEFAULT ‘DROP’,

  1. -  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'
  2. NOT NULL DEFAULT ''
  3.   
  4. +  `sql_mode` set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')
  5.  NOT NULL DEFAULT ''

   `comment` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ”,

   `originator` int(10) unsigned NOT NULL,

   `time_zone` char(64) CHARACTER SET latin1 NOT NULL DEFAULT ‘SYSTEM’,

导致event无法使用, 在咨询P.linux兄后,解决办法如下

装一个纯净的mariadb,

然后从percona导出mysql库

mysqldump  -B mysql  –opt  –triggers=true  –events  -u root -p > mmmmmysql.sql

注释掉mysql.event, mysql.proc 表的建表语句(留下insert),然后手工导mmmmmysql.sql 进Mariadb 5.5.30

再启动Mariadb就没报错了 ,能正常运行!

原文链接:http://www.mysqlops.com/2013/05/08/percona-5-5-30-%E8%BD%AC%E6%8A%95-mariadb-5-5-30-%E5%B0%8F%E8%AE%B0.html

 

【编辑推荐】

  1. MariaDB 5.3将支持ALTER TABLE的进度提示
  2. MySQL创始人打造MariaDB 全面兼容MySQL 5.1
  3. MariaDB 2周年了
  4. 教你五步优化你的MongoDB
  5. MariaDB 5.3.4性能测试

 

责任编辑:彭凡 来源: MySQLOPS
相关推荐

2015-07-16 17:00:15

MySQLMariaDBPercona

2017-10-09 18:36:54

MariaDB阿里巴巴

2009-03-04 09:30:55

FlockFirefoxChrome

2013-09-18 09:57:01

Android开源雅虎

2023-03-10 10:32:44

Vanilla OSUbuntuDebian Sid

2015-04-16 11:04:13

ICT渠道华为

2012-12-04 09:11:55

2019-07-24 10:46:41

iPhone华为苹果

2012-04-24 09:44:16

Windows Pho网秦

2020-06-30 14:25:28

MavenGradle Spring

2012-05-26 23:32:54

webOS

2009-02-17 20:24:18

MOTO微软手机操作系统

2012-10-16 09:54:21

MapRHortonworksCloudera

2011-01-13 09:17:44

2009-07-29 09:35:07

谷歌离职

2009-05-19 16:47:54

LinuxWindows微软

2012-09-17 14:42:28

Uinx云计算IBM

2009-12-01 09:09:58

谷歌Digg

2011-06-28 09:22:46

黑莓RIMAndroid

2012-11-02 14:22:13

华为Juniper
点赞
收藏

51CTO技术栈公众号