Mysql5.7.15轉MariaDB10.1教學

建立一個 mariadb.repo
cd /etc/yum.repos.d/
vi mariadb.repo

# MariaDB 10.1 CentOS repository list - created 2017-01-02 00:27 UTC 
# http://downloads.mariadb.org/mariadb/repositories/ 
[mariadb] 
name = MariaDB 
baseurl = http://yum.mariadb.org/10.1/centos6-amd64 
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB 
gpgcheck=1

2.備份原本的mysql
/etc/init.d/mysqld stop
mv /var/lib/mysql /var/lib/mysql.bak

3.移除原本的mysql571x
yum remove mysql mysql-devel mysql-community-server mysql-community-common*

4.安裝 MariaDB Server
yum install MariaDB-server MairaDB-client MariaDB-devel*

5.還原資料庫
rm -Rf /var/lib/mysql
mv /var/lib/mysql.bak /var/lib/mysql

6.還原設定檔
cp my.cnf.rpmsave my.cnf

7.升級資料表
/etc/init.d/mysql start
mysql_upgrade -p -uroot  #單套修復
mysql_upgrade -p -uking -Smysql02.sock #多套修復8.測試主從跟隨正常

mysql5610的mysql資料表可以延用在10.1上
mysql5715的mysql資料表必須要重建不能用在10.1
========故障排除==============

mysql無法啟動出現
2017-01-02 9:02:39 140617881475840 [ERROR] mysqld: Can't create/write to file '/var/lib/mysql/' (Errcode: 21 "Is a directory")
2017-01-02 9:02:39 140617881475840 [ERROR] Can't start server: can't create PID file: Is a directory

解決方式
vi /etc/my.cnf
#pid-file=/var/lib/mysql/

mysql啟動log出現
2017-01-02 9:13:08 140425056434944 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1286: Unknown storage engine 'InnoDB'

解決方式
登入mysql
use mysql;
DROP TABLE gtid_slave_pos;
DROP TABLE innodb_index_stats;

CREATE TABLE `gtid_slave_pos` ( 
  `domain_id` int(10) unsigned NOT NULL, 
  `sub_id` bigint(20) unsigned NOT NULL, 
  `server_id` int(10) unsigned NOT NULL, 
  `seq_no` bigint(20) unsigned NOT NULL, 
  PRIMARY KEY (`domain_id`,`sub_id`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';

CREATE TABLE `innodb_index_stats` ( 
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL, 
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL, 
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL, 
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL, 
  `stat_value` bigint(20) unsigned NOT NULL, 
  `sample_size` bigint(20) unsigned DEFAULT NULL, 
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL, 
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`) 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;