本帖最后由 tangenxin 于 2016-5-24 16:56 编辑
一、公司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@##### 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
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@##### tmp]# gzip -d all_2016-05-22.sql.gz
[root@##### 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@yyqdb02 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
Copyright (c) 2009-2014 Percona LLC and/or itsaffiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates.All rights reserved.
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 status\G
*************************** 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环境
#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@##### 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
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@##### tmp]# gzip -d all_2016-05-22.sql.gz
[root@##### 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@yyqdb02 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
Copyright (c) 2009-2014 Percona LLC and/or itsaffiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates.All rights reserved.
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 status\G
*************************** 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分钟搞定，而且没出错，太顺利了，让我无法接受啊。
编辑回复