mysql 修改data目錄後無法使用指令碼啟動薦

一、公司Mysql環境

#1. 目前公司Mysql只做了主從

#2. Mysql資料庫版本：Percona-Server-server-56-5.6.20-rel68.0.el6.x86_64.rpm

二、Mysql從庫的問題

Mysql從庫一直都用命令啟動：mysqld_safe –defaults-file=/etc/my.cnf & 。用/etc/init.d/mysql啟動指令碼一直報錯。最鬱悶的是在錯誤日誌裡沒有記錄啟動錯誤資訊。

我忍受了大半年的命令啟動，終於不想再忍下去了。

之前一直以為/etc/my.cnf有問題，再嘗試大半天時間後沒有解決。我決定還是解除安裝之前的mysql，重新安裝mysql

解除安裝準備工作：

tar zcvf mysql.tar.gz /data/mysql # 備份mysql資料目錄

mv /etc/my.cnf /etc/my.cnf.ori

刪除

rm -rf /data/mysql

rpm -e Percona-Server-server-56-5.6.20

rpm -e Percona-Server-client-56-5.6.20

rpm -e Percona-Server-shared-56-5.6.20

安裝

rpm -ivhPercona-Server-client-56-5.6.20-rel68.0.el6.x86_64.rpm Percona-Server-server-56-5.6.20-rel68.0.el6.x86_64.rpm Percona-Server-shared-56-5.6.20-rel68.0.el6.x86_64.rpm

#############################################

我以為會重新生成預設的/etc/my.cnf，發現沒有。又找了一臺伺服器安裝了mysql，匯出預設的/etc/my.cnf

###################################

重灌之後，在不修改預設配置是可以正常啟動的。但只要匯入生產/etc/my.cnf配置後又是錯誤

[root@###### ~]# service mysql start

Starting MySQL (Percona Server).The server quit without upd[FAILED]D file (/home/mysql/mysql.pid).

再改回預設的/etc/my.cnf正常。只要修改預設配置就出錯。（真心折騰啊）

[mysqld]
datadir=/data/mysql      # 預設/var/lib/mysql
socket=/data/mysql/mysql.sock  # 預設/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/data/mysqld.log   # 預設/var/log/mysqld.log
pid-file=/data/mysqld/mysqld.pid   #/var/run/mysqld/mysqld.pid

幸運的是此次有了錯誤日誌

[root@###### data]# !tail

tail -20 /var/log/mysqld.log

160524 14:07:20 mysqld_safe Starting mysqlddaemon with databases from /data/mysql

2016-05-24 14:07:20 13213 [Warning] Can`t create test file/data/mysql/mfs-data3.lower-test

2016-05-24 14:07:20 13213 [Warning] Can`tcreate test file /data/mysql/mfs-data3.lower-test

/usr/sbin/mysqld: File`/data/mysql/mysql-bin.index` not found (Errcode: 13 – Permission denied)

2016-05-24 14:07:20 13213 [ERROR] Aborting

花了點時間找到該錯誤是由selinux引起的。關閉selinux

setenforce 0

sed -i “s#enforcing#disabled#g” /etc/selinux/config

再次啟動

[root@###### mysql]# service mysql start

Starting MySQL (Percona Server)..The server quit without up[FAILED]ID file (/home/mysql/mysql.pid).

[root@yyqdb02 mysql]# cat log/mysql-error.log

160524 14:31:37 mysqld_safe Starting mysqld daemon with databases from /home/mysql/data/

2016-05-24 14:31:38 6632 [Warning] Can`t create test file /data/mysql/data/*.lower-test

/usr/sbin/mysqld: Can`t change dir to `/data/mysql/data/` (Errcode: 2 – No such file or directory)

2016-05-24 14:31:38 6632 [ERROR] Aborting

思路：建立所需的目錄

mkdir /data/mysql/{data,log} -p

發現問題依舊。後來仔細檢查了生產的/etc/my.cnf

datadir = /data/mysql/data/

終於找到原因了：

之前建立新例項命令是：mysql_install_db –user=mysql –datadir=/home/mysql

改為 mysql_install_db –user=mysql –datadir=/data/mysql/data

再次使用/etc/init.d/mysql restart終於成功了。

[root@###### data]# service mysql restart

Shutting down MySQL (Percona Server).. SUCCESS!

Starting MySQL (Percona Server).. SUCCESS!

三、恢復從庫身份

之前主庫一直有備份，所以不用擔心在主庫工作時間做主從同步了。

將主備份拷貝到從

[root@##### tmp]# scp 192.168.2.105:/data/bak/all_2016-05-22.sql.gz ./

root@172.30.1.10`s password:

all_2016-05-22.sql.gz 100% 21MB 20.9MB/s 00:00

[root@##### tmp]# ls

all_2016-05-22.sql.gz

[root@yyqdb02 tmp]# gzip -d all_2016-05-22.sql.gz

[root@yyqdb02 tmp]# less all_2016-05-22.sql

— MySQL dump 10.13 Distrib 5.6.20-68.0, for Linux (x86_64)

—

— Host: localhost Database:

— ——————————————————

— Server version 5.6.20-68.0-log

/*!40101 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE=`+00:00` */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0 */;

/*!40014 SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE=`NO_AUTO_VALUE_ON_ZERO` */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0*/;

—

— Position to start replication or point-in-timerecovery from

—

— CHANGE MASTER TO MASTER_LOG_FILE=`mysql-bin.000042`, MASTER_LOG_POS=142703;

—

— Current Database: `decorate`

—

[root@##### tmp]# service mysql status # mysql伺服器執行著

MySQL (Percona Server) running (17022) [ OK ]

[root@yyqdb02 tmp]#mysql -uroot -p < all_2016-05-22.sql

Enter password:

[root@##### tmp]# mysql -uroot -p # 由於沒有flush privileges，密碼為空。害我輸了好幾次匯入後的root密碼

Enter password:

ERROR 1045 (28000): Access denied for user`root`@`localhost` (using password: YES)

[root@###### tmp]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 5

Server version: 5.6.20-68.0-log Percona Server (GPL),Release 68.0, Revision 656

Oracle is a registered trademark of Oracle Corporationand/or its

affiliates. Other names may be trademarks of theirrespective

owners.

Type `help;` or `h` for help. Type `c` to clear thecurrent input statement.

mysql> flushprivileges; # 重新整理下

Query OK, 0 rows affected (0.00 sec)

mysql> changemaster tomaster_host=`192.168.2.105`,master_port=3306,master_user=`rep`,master_password=`password`,MASTER_LOG_FILE=`mysql-bin.000042`,MASTER_LOG_POS=142703; # 此命令已經可以盲打了

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> showslave statusG

*************************** 1. row***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.30.1.10

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000042

Read_Master_Log_Pos: 302454

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 160034

Relay_Master_Log_File: mysql-bin.000042

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 302454

Relay_Log_Space: 160201

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: c73c4373-34fd-11e4-a1bd-b82a72cf2c9f

Master_Info_File: /home/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for theslave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

####至此，全部搞定。主從2分鐘搞定，而且沒出錯，太順利了，讓我無法接受啊。

mysql 修改data目錄後 無法使用指令碼啟動薦

相關文章

mysql 修改data目錄後無法使用指令碼啟動薦