MariaDB – Adamhuan's Data Center – 【逻辑驱动数据】

MariaDB：升级（5.5.6 –> 10.4.1） on CentOS 7

首先，看看当前数据库的信息。

[root@oracleme ~]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-01-18 22:19:27 CST; 5s ago
  Process: 20862 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
  Process: 20830 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 20861 (mysqld_safe)
    Tasks: 21
   CGroup: /system.slice/mariadb.service
           ├─20861 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
           └─21227 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysq...

Jan 18 22:19:24 vesta.gds-services.com systemd[1]: Starting MariaDB database ...
Jan 18 22:19:25 vesta.gds-services.com mariadb-prepare-db-dir[20830]: Databas...
Jan 18 22:19:25 vesta.gds-services.com mysqld_safe[20861]: 190118 22:19:25 my...
Jan 18 22:19:25 vesta.gds-services.com mysqld_safe[20861]: 190118 22:19:25 my...
Jan 18 22:19:27 vesta.gds-services.com systemd[1]: Started MariaDB database s...
Hint: Some lines were ellipsized, use -l to show in full.
[root@oracleme ~]# 
[root@oracleme ~]# mysql -V
mysql  Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1
[root@oracleme ~]# 
[root@oracleme ~]# mysql -u root
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select version();
+----------------+
| version()      |
+----------------+
| 5.5.60-MariaDB |
+----------------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| admin_default      |
| mysql              |
| performance_schema |
| roundcube          |
| yuyihuan_itop      |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> select user,host from mysql.user;
+-----------------+-----------+
| user            | host      |
+-----------------+-----------+
| admin_default   | %         |
| yuyihuan_itroot | %         |
| admin_default   | localhost |
| root            | localhost |
| roundcube       | localhost |
| yuyihuan_itroot | localhost |
+-----------------+-----------+
6 rows in set (0.00 sec)

MariaDB [(none)]>

[root@oracleme ~]# service mariadb status

Redirecting to /bin/systemctl status mariadb.service

● mariadb.service - MariaDB database server

Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)

Active: active (running) since Fri 2019-01-18 22:19:27 CST; 5s ago

Process: 20862 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)

Process: 20830 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)

Main PID: 20861 (mysqld_safe)

Tasks: 21

CGroup: /system.slice/mariadb.service

├─20861 /bin/sh /usr/bin/mysqld_safe --basedir=/usr

└─21227 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysq...

Jan 18 22:19:24 vesta.gds-services.com systemd[1]: Starting MariaDB database ...

Jan 18 22:19:25 vesta.gds-services.com mariadb-prepare-db-dir[20830]: Databas...

Jan 18 22:19:25 vesta.gds-services.com mysqld_safe[20861]: 190118 22:19:25 my...

Jan 18 22:19:27 vesta.gds-services.com systemd[1]: Started MariaDB database s...

Hint: Some lines were ellipsized, use -l to show in full.

[root@oracleme ~]#

[root@oracleme ~]# mysql -V

mysql Ver 15.1 Distrib 5.5.60-MariaDB, for Linux (x86_64) using readline 5.1

[root@oracleme ~]#

[root@oracleme ~]# mysql -u root

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2

Server version: 5.5.60-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select version();

+----------------+

| version() |

+----------------+

| 5.5.60-MariaDB |

+----------------+

1 row in set (0.00 sec)

MariaDB [(none)]>

MariaDB [(none)]> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| admin_default |

| mysql |

| performance_schema |

| roundcube |

| yuyihuan_itop |

+--------------------+

6 rows in set (0.00 sec)

MariaDB [(none)]>

MariaDB [(none)]> select user,host from mysql.user;

+-----------------+-----------+

| user | host |

+-----------------+-----------+

| admin_default | % |

| yuyihuan_itroot | % |

| admin_default | localhost |

| root | localhost |

| roundcube | localhost |

| yuyihuan_itroot | localhost |

+-----------------+-----------+

6 rows in set (0.00 sec)

MariaDB [(none)]>

备份原库：

[root@oracleme mysql_data]# pwd
/backup/mysql_data
[root@oracleme mysql_data]# 
[root@oracleme mysql_data]# ls -ltr
total 0
[root@oracleme mysql_data]# 
[root@oracleme mysql_data]# mysqldump -u root --all-databases > mariadb_backup_data_all_db.sql
[root@oracleme mysql_data]# 
[root@oracleme mysql_data]# ls -ltr
total 520
-rw-r--r--. 1 root root 530379 Jan 18 22:21 mariadb_backup_data_all_db.sql
[root@oracleme mysql_data]# 
[root@oracleme mysql_data]# du -sh *
520K    mariadb_backup_data_all_db.sql
[root@oracleme mysql_data]# 

[root@oracleme mysql_data]# cp -rf /etc/my.cnf* .
[root@oracleme mysql_data]# ls -ltr
total 524
-rw-r--r--. 1 root root 530379 Jan 18 22:21 mariadb_backup_data_all_db.sql
drwxr-xr-x. 2 root root     67 Jan 18 22:23 my.cnf.d
-rw-r--r--. 1 root root    737 Jan 18 22:23 my.cnf
[root@oracleme mysql_data]# 
[root@oracleme mysql_data]# ls -ltr my.cnf.d/
total 12
-rw-r--r--. 1 root root 744 Jan 18 22:23 server.cnf
-rw-r--r--. 1 root root 232 Jan 18 22:23 mysql-clients.cnf
-rw-r--r--. 1 root root 295 Jan 18 22:23 client.cnf
[root@oracleme mysql_data]#

[root@oracleme mysql_data]# pwd

/backup/mysql_data

[root@oracleme mysql_data]#

[root@oracleme mysql_data]# ls -ltr

total 0

[root@oracleme mysql_data]#

[root@oracleme mysql_data]# mysqldump -u root --all-databases > mariadb_backup_data_all_db.sql

[root@oracleme mysql_data]#

[root@oracleme mysql_data]# ls -ltr

total 520

-rw-r--r--. 1 root root 530379 Jan 18 22:21 mariadb_backup_data_all_db.sql

[root@oracleme mysql_data]#

[root@oracleme mysql_data]# du -sh *

520K mariadb_backup_data_all_db.sql

[root@oracleme mysql_data]#

[root@oracleme mysql_data]# cp -rf /etc/my.cnf* .

[root@oracleme mysql_data]# ls -ltr

total 524

-rw-r--r--. 1 root root 530379 Jan 18 22:21 mariadb_backup_data_all_db.sql

drwxr-xr-x. 2 root root 67 Jan 18 22:23 my.cnf.d

-rw-r--r--. 1 root root 737 Jan 18 22:23 my.cnf

[root@oracleme mysql_data]#

[root@oracleme mysql_data]# ls -ltr my.cnf.d/

total 12

-rw-r--r--. 1 root root 744 Jan 18 22:23 server.cnf

-rw-r--r--. 1 root root 232 Jan 18 22:23 mysql-clients.cnf

-rw-r--r--. 1 root root 295 Jan 18 22:23 client.cnf

[root@oracleme mysql_data]#

配置YUM文件：

[root@oracleme ~]# ls -ltr /etc/yum.repos.d/
total 96
-rw-r--r--. 1 root root 1050 Oct  3  2017 epel-testing.repo
-rw-r--r--. 1 root root  951 Oct  3  2017 epel.repo
-rw-r--r--. 1 root root 5701 Nov 23 21:16 CentOS-Vault.repo
-rw-r--r--. 1 root root 1331 Nov 23 21:16 CentOS-Sources.repo
-rw-r--r--. 1 root root  630 Nov 23 21:16 CentOS-Media.repo
-rw-r--r--. 1 root root  314 Nov 23 21:16 CentOS-fasttrack.repo
-rw-r--r--. 1 root root  649 Nov 23 21:16 CentOS-Debuginfo.repo
-rw-r--r--. 1 root root 1309 Nov 23 21:16 CentOS-CR.repo
-rw-r--r--. 1 root root 1664 Nov 23 21:16 CentOS-Base.repo
-rw-r--r--. 1 root root  750 Dec 22 01:50 remi-safe.repo
-rw-r--r--. 1 root root 1314 Dec 22 01:50 remi-php73.repo
-rw-r--r--. 1 root root 1314 Dec 22 01:50 remi-php72.repo
-rw-r--r--. 1 root root 1314 Dec 22 01:50 remi-php71.repo
-rw-r--r--. 1 root root 1314 Dec 22 01:50 remi-php70.repo
-rw-r--r--. 1 root root  456 Dec 22 01:50 remi-php54.repo
-rw-r--r--. 1 root root  446 Dec 22 01:50 remi-glpi94.repo
-rw-r--r--. 1 root root  446 Dec 22 01:50 remi-glpi93.repo
-rw-r--r--. 1 root root  446 Dec 22 01:50 remi-glpi92.repo
-rw-r--r--. 1 root root  446 Dec 22 01:50 remi-glpi91.repo
-rw-r--r--. 1 root root 2605 Jan 18 17:16 remi.repo
-rw-r--r--. 1 root root   99 Jan 18 17:16 nginx.repo
-rw-r--r--. 1 root root  145 Jan 18 17:16 vesta.repo
-rw-r--r--. 1 root root   87 Jan 18 22:26 mariadb.repo
[root@oracleme ~]# 
[root@oracleme ~]# cat /etc/yum.repos.d/mariadb.repo 
[mariadb]
name=MariaDB
baseurl=http://yum.mariadb.org/10.4.1/centos7-amd64/
gpgcheck=0
[root@oracleme ~]# 
[root@oracleme ~]# vi /etc/yum.conf 
[root@oracleme ~]# 
[root@oracleme ~]# cat /etc/yum.conf | grep keepcache
keepcache=1
[root@oracleme ~]#

[root@oracleme ~]# ls -ltr /etc/yum.repos.d/

total 96

-rw-r--r--. 1 root root 1050 Oct 3 2017 epel-testing.repo

-rw-r--r--. 1 root root 951 Oct 3 2017 epel.repo

-rw-r--r--. 1 root root 5701 Nov 23 21:16 CentOS-Vault.repo

-rw-r--r--. 1 root root 1331 Nov 23 21:16 CentOS-Sources.repo

-rw-r--r--. 1 root root 630 Nov 23 21:16 CentOS-Media.repo

-rw-r--r--. 1 root root 314 Nov 23 21:16 CentOS-fasttrack.repo

-rw-r--r--. 1 root root 649 Nov 23 21:16 CentOS-Debuginfo.repo

-rw-r--r--. 1 root root 1309 Nov 23 21:16 CentOS-CR.repo

-rw-r--r--. 1 root root 1664 Nov 23 21:16 CentOS-Base.repo

-rw-r--r--. 1 root root 750 Dec 22 01:50 remi-safe.repo

-rw-r--r--. 1 root root 1314 Dec 22 01:50 remi-php73.repo

-rw-r--r--. 1 root root 1314 Dec 22 01:50 remi-php72.repo

-rw-r--r--. 1 root root 1314 Dec 22 01:50 remi-php71.repo

-rw-r--r--. 1 root root 1314 Dec 22 01:50 remi-php70.repo

-rw-r--r--. 1 root root 456 Dec 22 01:50 remi-php54.repo

-rw-r--r--. 1 root root 446 Dec 22 01:50 remi-glpi94.repo

-rw-r--r--. 1 root root 446 Dec 22 01:50 remi-glpi93.repo

-rw-r--r--. 1 root root 446 Dec 22 01:50 remi-glpi92.repo

-rw-r--r--. 1 root root 446 Dec 22 01:50 remi-glpi91.repo

-rw-r--r--. 1 root root 2605 Jan 18 17:16 remi.repo

-rw-r--r--. 1 root root 99 Jan 18 17:16 nginx.repo

-rw-r--r--. 1 root root 145 Jan 18 17:16 vesta.repo

-rw-r--r--. 1 root root 87 Jan 18 22:26 mariadb.repo

[root@oracleme ~]#

[root@oracleme ~]# cat /etc/yum.repos.d/mariadb.repo

[mariadb]

name=MariaDB

baseurl=http://yum.mariadb.org/10.4.1/centos7-amd64/

gpgcheck=0

[root@oracleme ~]#

[root@oracleme ~]# vi /etc/yum.conf

[root@oracleme ~]#

[root@oracleme ~]# cat /etc/yum.conf | grep keepcache

keepcache=1

[root@oracleme ~]#

刷新YUM：[……]

发表于： 2019年1月19日 2019年1月19日
作者： Adamhuan
分类： MariaDB, MySQL
标签： 10.4.1, 5.5.6, 7, centos, centos7, Linux, MariaDB, MySQL, Update, Upgrade, 升级
发表评论: MariaDB：升级（5.5.6 –> 10.4.1） on CentOS 7

MariaDB Galera – error：Job for mariadb.service failed because the control process exited with error code.

报错如题所示，具体如下：

[root@mariadb-cluster-3 ~]# service mysql start
Starting mysql (via systemctl):  Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
                                                           [FAILED]
[root@mariadb-cluster-3 ~]#

[root@mariadb-cluster-3 ~]# service mysql start

Starting mysql (via systemctl): Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

[FAILED]

[root@mariadb-cluster-3 ~]#

日志：

Dec 22 10:02:41 mariadb-cluster-3 systemd: Starting MariaDB database server...
Dec 22 10:02:43 mariadb-cluster-3 sh: WSREP: Failed to start mysqld for wsrep recovery: '2017-12-22 10:02:41 140276076894336 [Note] /usr/sbin/mysqld (mysqld 10.2.11-MariaDB) starting as process 20861 ...
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Uses event mutexes
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Compressed tables use zlib 1.2.7
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Using Linux native AIO
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Number of pools: 1
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Using SSE2 crc32 instructions
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Completed initialization of buffer pool
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140275306231552 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [ERROR] InnoDB: Header page consists of zero bytes in datafile: ./ibdata1, Space ID:0, Flags: 0. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile './ibdata1' could not be found in the doublewrite buffer.
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [Note] InnoDB: Starting shutdown...
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Plugin 'InnoDB' init function returned error.
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [Note] Plugin 'FEEDBACK' is disabled.
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Unknown/unsupported storage engine: InnoDB
Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Aborting'
Dec 22 10:02:43 mariadb-cluster-3 systemd: mariadb.service: control process exited, code=exited status=1
Dec 22 10:02:43 mariadb-cluster-3 systemd: Failed to start MariaDB database server.
Dec 22 10:02:43 mariadb-cluster-3 systemd: Unit mariadb.service entered failed state.
Dec 22 10:02:43 mariadb-cluster-3 systemd: mariadb.service failed.

Dec 22 10:02:41 mariadb-cluster-3 systemd: Starting MariaDB database server...

Dec 22 10:02:43 mariadb-cluster-3 sh: WSREP: Failed to start mysqld for wsrep recovery: '2017-12-22 10:02:41 140276076894336 [Note] /usr/sbin/mysqld (mysqld 10.2.11-MariaDB) starting as process 20861 ...

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Uses event mutexes

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Compressed tables use zlib 1.2.7

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Using Linux native AIO

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Number of pools: 1

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Using SSE2 crc32 instructions

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [Note] InnoDB: Completed initialization of buffer pool

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140275306231552 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [ERROR] InnoDB: Header page consists of zero bytes in datafile: ./ibdata1, Space ID:0, Flags: 0. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile './ibdata1' could not be found in the doublewrite buffer.

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:41 140276076894336 [ERROR] InnoDB: Plugin initialization aborted with error Data structure corruption

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [Note] InnoDB: Starting shutdown...

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Plugin 'InnoDB' init function returned error.

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [Note] Plugin 'FEEDBACK' is disabled.

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Unknown/unsupported storage engine: InnoDB

Dec 22 10:02:43 mariadb-cluster-3 sh: 2017-12-22 10:02:42 140276076894336 [ERROR] Aborting'

Dec 22 10:02:43 mariadb-cluster-3 systemd: mariadb.service: control process exited, code=exited status=1

Dec 22 10:02:43 mariadb-cluster-3 systemd: Failed to start MariaDB database server.

Dec 22 10:02:43 mariadb-cluster-3 systemd: Unit mariadb.service entered failed state.

Dec 22 10:02:43 mariadb-cluster-3 systemd: mariadb.service failed.

原因：配置文件写的有问题。

先移动文件：

[root@mariadb-cluster-3 mysql]# mkdir /backup/mysql_20171222
[root@mariadb-cluster-3 mysql]# 
[root@mariadb-cluster-3 mysql]# pwd
/var/lib/mysql
[root@mariadb-cluster-3 mysql]# ll
total 159764
-rw-rw---- 1 mysql mysql    16384 Dec 22 10:02 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 Dec 22 10:02 aria_log_control
-rw-rw---- 1 mysql mysql 12582912 Dec 22 01:29 ibdata1
-rw-r----- 1 mysql mysql 50331648 Dec 22 01:49 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Dec 22 01:29 ib_logfile1
-rw-rw---- 1 mysql mysql 50331648 Dec 22 01:29 ib_logfile101
-rw-rw---- 1 mysql mysql        0 Dec 22 01:21 sst_in_progress
[root@mariadb-cluster-3 mysql]# 
[root@mariadb-cluster-3 mysql]# ls -ltr ib*
-rw-rw---- 1 mysql mysql 50331648 Dec 22 01:29 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 Dec 22 01:29 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Dec 22 01:29 ib_logfile101
-rw-r----- 1 mysql mysql 50331648 Dec 22 01:49 ib_logfile0
[root@mariadb-cluster-3 mysql]# 
[root@mariadb-cluster-3 mysql]# mv ib* /backup/mysql_20171222/
[root@mariadb-cluster-3 mysql]#

[root@mariadb-cluster-3 mysql]# mkdir /backup/mysql_20171222

[root@mariadb-cluster-3 mysql]#

[root@mariadb-cluster-3 mysql]# pwd

/var/lib/mysql

[root@mariadb-cluster-3 mysql]# ll

total 159764

-rw-rw---- 1 mysql mysql 16384 Dec 22 10:02 aria_log.00000001

-rw-rw---- 1 mysql mysql 52 Dec 22 10:02 aria_log_control

-rw-rw---- 1 mysql mysql 12582912 Dec 22 01:29 ibdata1

-rw-r----- 1 mysql mysql 50331648 Dec 22 01:49 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 Dec 22 01:29 ib_logfile1

-rw-rw---- 1 mysql mysql 50331648 Dec 22 01:29 ib_logfile101

-rw-rw---- 1 mysql mysql 0 Dec 22 01:21 sst_in_progress

[root@mariadb-cluster-3 mysql]#

[root@mariadb-cluster-3 mysql]# ls -ltr ib*

-rw-rw---- 1 mysql mysql 50331648 Dec 22 01:29 ib_logfile1

-rw-rw---- 1 mysql mysql 12582912 Dec 22 01:29 ibdata1

-rw-rw---- 1 mysql mysql 50331648 Dec 22 01:29 ib_logfile101

-rw-r----- 1 mysql mysql 50331648 Dec 22 01:49 ib_logfile0

[root@mariadb-cluster-3 mysql]#

[root@mariadb-cluster-3 mysql]# mv ib* /backup/mysql_20171222/

[root@mariadb-cluster-3 mysql]#

[……]

Galera error – WSREP: Failed to start mysqld for wsrep recovery: ‘2017-12-22 1:19:58 140304668907648 [Note] /usr/sbin/mysqld (mysqld 10.2.11-MariaDB) starting as process 3777 …

具体日志如下：

Dec 22 01:19:49 mariadb-cluster-3 systemd: Starting MariaDB database server...
Dec 22 01:20:01 mariadb-cluster-3 sh: WSREP: Failed to start mysqld for wsrep recovery: '2017-12-22  1:19:58 140304668907648 [Note] /usr/sbin/mysqld (mysqld 10.2.11-MariaDB) starting as process 3777 ...
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:19:59 140304668907648 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:19:59 140304668907648 [Note] InnoDB: Uses event mutexes
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:19:59 140304668907648 [Note] InnoDB: Compressed tables use zlib 1.2.7
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:19:59 140304668907648 [Note] InnoDB: Using Linux native AIO
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:19:59 140304668907648 [Note] InnoDB: Number of pools: 1
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:19:59 140304668907648 [Note] InnoDB: Using SSE2 crc32 instructions
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [Note] InnoDB: Completed initialization of buffer pool
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140303903000320 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [Note] InnoDB: Starting shutdown...
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [ERROR] Plugin 'InnoDB' init function returned error.
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [Note] Plugin 'FEEDBACK' is disabled.
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:00 140304668907648 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:01 140304668907648 [ERROR] Unknown/unsupported storage engine: InnoDB
Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22  1:20:01 140304668907648 [ERROR] Aborting'
Dec 22 01:20:01 mariadb-cluster-3 systemd: mariadb.service: control process exited, code=exited status=1
Dec 22 01:20:01 mariadb-cluster-3 systemd: Failed to start MariaDB database server.
Dec 22 01:20:01 mariadb-cluster-3 systemd: Unit mariadb.service entered failed state.
Dec 22 01:20:01 mariadb-cluster-3 systemd: mariadb.service failed.
Dec 22 01:20:01 mariadb-cluster-3 systemd: Started Session 71 of user root.
Dec 22 01:20:01 mariadb-cluster-3 systemd: Starting Session 71 of user root.

Dec 22 01:19:49 mariadb-cluster-3 systemd: Starting MariaDB database server...

Dec 22 01:20:01 mariadb-cluster-3 sh: WSREP: Failed to start mysqld for wsrep recovery: '2017-12-22 1:19:58 140304668907648 [Note] /usr/sbin/mysqld (mysqld 10.2.11-MariaDB) starting as process 3777 ...

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:19:59 140304668907648 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:19:59 140304668907648 [Note] InnoDB: Uses event mutexes

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:19:59 140304668907648 [Note] InnoDB: Compressed tables use zlib 1.2.7

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:19:59 140304668907648 [Note] InnoDB: Using Linux native AIO

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:19:59 140304668907648 [Note] InnoDB: Number of pools: 1

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:19:59 140304668907648 [Note] InnoDB: Using SSE2 crc32 instructions

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140304668907648 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140304668907648 [Note] InnoDB: Completed initialization of buffer pool

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140303903000320 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140304668907648 [ERROR] InnoDB: The innodb_system data file 'ibdata1' must be writable

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140304668907648 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140304668907648 [Note] InnoDB: Starting shutdown...

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140304668907648 [ERROR] Plugin 'InnoDB' init function returned error.

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140304668907648 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140304668907648 [Note] Plugin 'FEEDBACK' is disabled.

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:00 140304668907648 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:01 140304668907648 [ERROR] Unknown/unsupported storage engine: InnoDB

Dec 22 01:20:01 mariadb-cluster-3 sh: 2017-12-22 1:20:01 140304668907648 [ERROR] Aborting'

Dec 22 01:20:01 mariadb-cluster-3 systemd: mariadb.service: control process exited, code=exited status=1

Dec 22 01:20:01 mariadb-cluster-3 systemd: Failed to start MariaDB database server.

Dec 22 01:20:01 mariadb-cluster-3 systemd: Unit mariadb.service entered failed state.

Dec 22 01:20:01 mariadb-cluster-3 systemd: mariadb.service failed.

Dec 22 01:20:01 mariadb-cluster-3 systemd: Started Session 71 of user root.

Dec 22 01:20:01 mariadb-cluster-3 systemd: Starting Session 71 of user root.

文件系统权限的问题：【must be writable】

修正权限：

[root@mariadb-cluster-3 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@mariadb-cluster-3 ~]#

1 2	[root@mariadb-cluster-3 ~]# chown -R mysql.mysql /var/lib/mysql/ [root@mariadb-cluster-3 ~]#

然后再启动就没问题了。
———————————————[……]

MariaDB Galera – error：Job for mariadb.service failed because a timeout was exceeded.

在集群节点服务器上以集群的方式启动数据库可能出现如题所示的错误，具体如下：

[root@mariadb-cluster-2 ~]# service mysql start
Starting mysql (via systemctl):  
Job for mariadb.service failed because a timeout was exceeded. See "systemctl status mariadb.service" and "journalctl -xe" for details.
                                                           [FAILED]
[root@mariadb-cluster-2 ~]#

[root@mariadb-cluster-2 ~]# service mysql start

Starting mysql (via systemctl):

Job for mariadb.service failed because a timeout was exceeded. See "systemctl status mariadb.service" and "journalctl -xe" for details.

[FAILED]

[root@mariadb-cluster-2 ~]#

日志：

Dec 21 20:36:55 mariadb-cluster-2 systemd: Starting MariaDB database server...
Dec 21 20:38:25 mariadb-cluster-2 systemd: mariadb.service start-pre operation timed out. Terminating.
Dec 21 20:38:25 mariadb-cluster-2 systemd: Failed to start MariaDB database server.
Dec 21 20:38:25 mariadb-cluster-2 systemd: Unit mariadb.service entered failed state.
Dec 21 20:38:25 mariadb-cluster-2 systemd: mariadb.service failed.

Dec 21 20:36:55 mariadb-cluster-2 systemd: Starting MariaDB database server...

Dec 21 20:38:25 mariadb-cluster-2 systemd: mariadb.service start-pre operation timed out. Terminating.

Dec 21 20:38:25 mariadb-cluster-2 systemd: Failed to start MariaDB database server.

Dec 21 20:38:25 mariadb-cluster-2 systemd: Unit mariadb.service entered failed state.

Dec 21 20:38:25 mariadb-cluster-2 systemd: mariadb.service failed.

有可能是系统防火墙的干扰：
[crayon-5c96f03d0[……]

MariaDB Galera – error：[ERROR] WSREP: Failed to prepare for ‘xtrabackup-v2’ SST. Unrecoverable.

如题所示的错误，发生在，在集群节点服务器上以集群的方式启动数据库，具体如下：

[root@mariadb-cluster-2 ~]# service mysql start
Starting mysql (via systemctl):  Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.
                                                           [FAILED]
[root@mariadb-cluster-2 ~]#

[root@mariadb-cluster-2 ~]# service mysql start

Starting mysql (via systemctl): Job for mariadb.service failed because the control process exited with error code. See "systemctl status mariadb.service" and "journalctl -xe" for details.

[FAILED]

[root@mariadb-cluster-2 ~]#

日志：

Dec 21 19:57:47 mariadb-cluster-2 mysqld: WSREP_SST: [INFO] Streaming with xbstream (20171221 19:57:47.901)
Dec 21 19:57:47 mariadb-cluster-2 mysqld: WSREP_SST: [INFO] Using socat as streamer (20171221 19:57:47.904)
Dec 21 19:57:47 mariadb-cluster-2 mysqld: which: no socat in (/usr/sbin:/sbin:/usr//bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin)
Dec 21 19:57:47 mariadb-cluster-2 mysqld: WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/usr//bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin (20171221 19:57:47.908)
Dec 21 19:57:47 mariadb-cluster-2 mysqld: 2017-12-21 19:57:47 140258381383424 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.158.1.174' --datadir '/var/lib/mysql/'   --parent '5001'  ''
Dec 21 19:57:47 mariadb-cluster-2 mysqld: Read: '(null)'
Dec 21 19:57:47 mariadb-cluster-2 mysqld: 2017-12-21 19:57:47 140258381383424 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.158.1.174' --datadir '/var/lib/mysql/'   --parent '5001'  '' : 2 (No such file or directory)
Dec 21 19:57:47 mariadb-cluster-2 mysqld: 2017-12-21 19:57:47 140258602903296 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.
Dec 21 19:57:47 mariadb-cluster-2 mysqld: 2017-12-21 19:57:47 140258602903296 [ERROR] Aborting
Dec 21 19:58:01 mariadb-cluster-2 mysqld: Error in my_thread_global_end(): 1 threads didn't exit
Dec 21 19:58:01 mariadb-cluster-2 systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE
Dec 21 19:58:01 mariadb-cluster-2 systemd: Failed to start MariaDB database server.
Dec 21 19:58:01 mariadb-cluster-2 systemd: Unit mariadb.service entered failed state.
Dec 21 19:58:01 mariadb-cluster-2 systemd: mariadb.service failed.

Dec 21 19:57:47 mariadb-cluster-2 mysqld: WSREP_SST: [INFO] Streaming with xbstream (20171221 19:57:47.901)

Dec 21 19:57:47 mariadb-cluster-2 mysqld: WSREP_SST: [INFO] Using socat as streamer (20171221 19:57:47.904)

Dec 21 19:57:47 mariadb-cluster-2 mysqld: which: no socat in (/usr/sbin:/sbin:/usr//bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin)

Dec 21 19:57:47 mariadb-cluster-2 mysqld: WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/usr//bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin (20171221 19:57:47.908)

Dec 21 19:57:47 mariadb-cluster-2 mysqld: 2017-12-21 19:57:47 140258381383424 [ERROR] WSREP: Failed to read 'ready <addr>' from: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.158.1.174' --datadir '/var/lib/mysql/' --parent '5001' ''

Dec 21 19:57:47 mariadb-cluster-2 mysqld: Read: '(null)'

Dec 21 19:57:47 mariadb-cluster-2 mysqld: 2017-12-21 19:57:47 140258381383424 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.158.1.174' --datadir '/var/lib/mysql/' --parent '5001' '' : 2 (No such file or directory)

Dec 21 19:57:47 mariadb-cluster-2 mysqld: 2017-12-21 19:57:47 140258602903296 [ERROR] WSREP: Failed to prepare for 'xtrabackup-v2' SST. Unrecoverable.

Dec 21 19:57:47 mariadb-cluster-2 mysqld: 2017-12-21 19:57:47 140258602903296 [ERROR] Aborting

Dec 21 19:58:01 mariadb-cluster-2 mysqld: Error in my_thread_global_end(): 1 threads didn't exit

Dec 21 19:58:01 mariadb-cluster-2 systemd: mariadb.service: main process exited, code=exited, status=1/FAILURE

Dec 21 19:58:01 mariadb-cluster-2 systemd: Failed to start MariaDB database server.

Dec 21 19:58:01 mariadb-cluster-2 systemd: Unit mariadb.service entered failed state.

Dec 21 19:58:01 mariadb-cluster-2 systemd: mariadb.service failed.

原因，需要安装socat软件包。

该软件包默认Linux不支[……]