一、公司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.rpmPercona-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 serverquit without upd[FAILED]D file (/home/mysql/mysql.pid).
再改回默认的/etc/my.cnf正常。只要修改默认配置就出错。(真心折腾啊)
1 [mysqld]
2 datadir=/data/mysql # 默认/var/lib/mysql
3 socket=/data/mysql/mysql.sock # 默认/var/lib/mysql/mysql.sock
4 user=mysql
5 #Disabling symbolic-links is recommended to prevent assorted security risks
6 symbolic-links=0
7 [mysqld_safe]
8 log-error=/data/mysqld.log # 默认/var/log/mysqld.log
9 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 Startingmysqlddaemon with databases from /data/mysql
2016-05-24 14:07:20 13213 [Warning] Can'tcreate 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 serverquit without up[FAILED]ID file (/home/mysql/mysql.pid).
[root@yyqdb02 mysql]# catlog/mysql-error.log
160524 14:31:37 mysqld_safe Starting mysqlddaemon with databases from /home/mysql/data/
2016-05-24 14:31:38 6632 [Warning] Can'tcreate test file /data/mysql/data/*.lower-test
2016-05-24 14:31:38 6632 [Warning] Can'tcreate 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]# scp192.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 -dall_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
/*!40101SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101SET@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 */;
/*!40014SET@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 orpoint-in-timerecovery from
--
-- CHANGE MASTER TOMASTER_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 flushprivileges; # 刷新下
Query OK, 0 rows affected (0.00 sec)
mysql> changemastertomaster_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.01sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> showslave status\G
*************************** 1.row***************************
Slave_IO_State: Waiting formaster 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 fortheslave 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)
编辑回复