openstack基于mysql的问题修复
同事一台openstack单节点服务器由于未知原因,无法正常工作。
远程引导同事检查故障。
[root@localhost ~]# nova-manage service list
出现错误:SQL connection failed 。貌似没有连接数据库,看来可能是服务或数据库有问题。
引导同事检测openstack服务及数据库:[root@sxun init.d]# /etc/init.d/openstack-keystone status
keystone (pid 12593) is running...
[root@sxun init.d]# /etc/init.d/openstack-nova-api status
openstack-nova-api (pid 7446) is running...
[root@sxun init.d]# /etc/init.d/openstack-nova-compute status
openstack-nova-compute (pid 11728) is running...
[root@sxun init.d]# /etc/init.d/openstack-nova-conductor status
openstack-nova-conductor (pid 7704) is running...
[root@sxun init.d]# /etc/init.d/mysqld statusmysql is stoppd
[root@sxun init.d]# /etc/init.d/mysqld start
starting mysqld: [FAILED]
restart也是如此,看来是mysqld服务没有启动的问题。为什么没有启动了,找到了mysqld的日志查看:
tail -100 /var/lib/mysql/sxun.err
错误日志报大致错误如下:
170420 15:00:20 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
......
##############以下引用#############130508 16:30:53 mysqld_safe Starting mysqlddaemon with databases from /data/mysql
130508 16:30:54 [Warning] The syntax'--log-slow-queries' is deprecated and will be removed in a future release.Please use '--slow-query-log'/'--slow-query-log-file' instead.
130508 16:30:54 InnoDB: The InnoDB memoryheap is disabled
130508 16:30:54 InnoDB: Mutexes andrw_locks use GCC atomic builtins
130508 16:30:54 InnoDB: Compressed tablesuse zlib 1.2.3
130508 16:30:54 InnoDB: Initializing bufferpool, size = 500.0M
130508 16:30:54 InnoDB: Completedinitialization of buffer pool
130508 16:30:54 InnoDB: highest supportedfile format is Barracuda.
InnoDB: Log scan progressed past thecheckpoint lsn 27219795678
130508 16:30:54 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information fromthe .ibd files...
InnoDB: Restoring possible half-writtendata pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to logsequence number 27219928522
##############引用,中间省略#############
解决办法:删除掉ibdata* ,
ib_logfile* ,所有的日志。重启后可启动数据库,因数据库是非正常关闭引起的。所有会造成数据丢失。要做好备份工作
##############以上引用#############
[root@sxun mysql]# ll
total 225356
drwx------ 2 mysql mysql 4096 Feb 15 16:06 cinder
drwx------ 2 mysql mysql 4096 Oct 1 2016 glance
-rw-rw---- 1 mysql mysql 220200960 Apr 20 18:08 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Apr 20 18:08 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Apr 20 18:08 ib_logfile1
drwx------ 2 mysql mysql 4096 Oct 1 2016 keystone
drwx------ 2 mysql mysql 4096 Oct 1 2016 mysql
srwxrwxrwx 1 mysql mysql 0 Feb 16 14:52 mysql.sock
drwx------ 2 mysql mysql 4096 Oct 1 2016 nova
drwx------ 2 mysql mysql 4096 Oct 1 2016 ovs_neutron
-rw-r----- 1 mysql root 38548 Feb 16 14:52 sxun.err
drwx------ 2 mysql mysql 4096 Oct 1 2016 test
生产环境不敢删除,先移动其它地方备份记[root@sxun init.d]#mv ibdata* /root/
[root@sxun init.d]#mv ib_logfile* /root/
[root@sxun init.d]# /etc/init.d/mysqld start
starting mysqld: [OK]
服务器的数据库已经是启动起来了,也生成了新的ib*文件。测试一下keystone和nova服务,但是:[root@sxun instances]# nova-manage service list
CRITICAL nova[req-97fd8f67-8a06-4ab7-b421-01d36bc6ad02 None None] (ProgrammingError) (1146,"Table 'nova.services' doesn't exist") 'SELECT services.created_at ASservices_created_at, services.updated_at AS services_updated_at,services.deleted_at AS services_deleted_at, services.deleted ASservices_deleted, services.id AS services_id, services.host AS services_host,services.`binary` AS services_binary, services.topic AS services_topic,services.report_count AS services_report_count, services.disabled ASservices_disabled, services.disabled_reason AS services_disabled_reason \nFROMservices \nWHERE services.deleted = %s' (0,)
[root@sxun instances]# keystone user-list
Authorization Failed: An unexpected errorprevented the server from fulfilling your request. (ProgrammingError) (1146,"Table 'keystone.domain' doesn't exist") 'SELECT domain.id ASdomain_id, domain.name AS domain_name, domain.enabled AS domain_enabled,domain.extra AS domain_extra \nFROM domain \nWHERE domain.id = %s' ('default',)(HTTP 500)
mysqld起来了,但由于ib*文件已经更换,数据不一样了。nova等各项服务已经出错了, 尝试reboot重启了一下服务器,仍然不行,ib*得还原,但还原又启动不了mysqld了。
直接抄袭主义不行,关键还得想办法:InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
问题在于这个错误,数据库非正常关机,然后数据库重启后有一个恢复操作,恢复不了就挂了。
后续的解决方案:
1、还原ib*文件,
2、在配置文件加入 不强制恢复数据的 关掉 #innodb_force_recover = 6。
3、启动数据库,mysqldump将数据库导出。
4、删除数据库文件,mysql启动完成后重新导入mysql数据库
同事操的刀,具体操作如下:[root@sxun01 mysql(keystone_admin)]#mysqldump -uroot --all-database >> my_mysql.dump
-- Warning: Skipping the data of tablemysql.event. Specify the --events option explicitly.
[root@sxun01 mysql(keystone_admin)]#/etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@sxun01 mysql(keystone_admin)]# rm -rfibdata1 ib_logfile0 ib_logfile1
[root@sxun01 mysql(keystone_admin)]# vi/etc/my.cnf
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld_safe]
socket = /var/lib/mysql/mysql.sock
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld_safe]
socket = /var/lib/mysql/mysql.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
skip-external-locking
#innodb_force_recover = 6
bind-address = 0.0.0.0
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysqld.log
expire_logs_days = 10
max_binlog_size = 100M
default-storage-engine = InnoDB
[mysqldump]
quick
quote-names
"/etc/my.cnf" 43L, 811C written
[root@sxun01 mysql(keystone_admin)]# mkdirdatabase_bak
[root@sxun01 mysql(keystone_admin)]# mvcinder/ glance/ keystone/ nova/ ovs_neutron/ database_bak/
[root@sxun01 mysql(keystone_admin)]# lldatabase_bak/
total 20
drwx------ 2 mysql mysql 4096 Oct 25 15:08cinder
drwx------ 2 mysql mysql 4096 Oct 25 15:08glance
drwx------ 2 mysql mysql 4096 Oct 25 15:08keystone
drwx------ 2 mysql mysql 4096 Oct 25 15:08nova
drwx------ 2 mysql mysql 4096 Oct 25 15:08ovs_neutron
[root@sxun01 mysql(keystone_admin)]# ll
total 29648
drwxr-xr-x 2 root root 4096 Apr 20 15:15 aaa
drwxr-xr-x 2 root root 4096 Apr 20 15:33 bbb
drwxr-xr-x 7 root root 4096 Apr 20 15:47 database_bak
-rw-r--r-- 1 root root 29610746 Apr 20 15:45 my_mysql.dump
drwx------ 2 mysql mysql 4096 Oct 25 15:05 mysql
-rw-r----- 1 mysql root 82317 Apr 20 15:46 sxun01.err
-rw-r----- 1 mysql root 637765 Apr 20 15:35 sxun01.err--
drwx------ 2 mysql mysql 4096 Oct 25 15:05 test
[root@sxun01 mysql(keystone_admin)]#
[root@sxun01 mysql(keystone_admin)]#
[root@sxun01 mysql(keystone_admin)]#/etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@sxun01 mysql(keystone_admin)]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql> quit
Bye
[root@sxun01 mysql(keystone_admin)]# mysql-uroot
[root@sxun01 mysql(keystone_admin)]#
[root@sxun01 mysql(keystone_admin)]#
[root@sxun01 mysql(keystone_admin)]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 93
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql> use nova ;
Reading table information for completion oftable and column names
You can turn off this feature to get aquicker startup with -A
Database changed
mysql> select * from instances;
mysql> quit
Bye
[root@sxun01 mysql(keystone_admin)]#nova-manage service list
Binary Host Zone Status State Updated_At
nova-consoleauth sxun01 internal enabled XXX 2017-04-20 05:31:48
nova-scheduler sxun01 internal enabled XXX 2017-04-20 05:31:54
nova-conductor sxun01 internal enabled :-) 2017-04-20 07:49:07
nova-cert sxun01 internal enabled XXX 2017-04-20 05:31:55
nova-compute sxun01 nova enabled XXX 2017-04-20 05:31:53
[root@sxun01 mysql(keystone_admin)]# reboot
[root@sxun01 mysql(keystone_admin)]#
Broadcast message from root@sxun01
(/dev/pts/0) at 15:49 ...
The system is going down for reboot NOW!
Last login: Thu Apr 20 15:28:26 2017
[root@sxun01 ~]# nova-manage service list
Binary Host Zone Status State Updated_At
nova-consoleauth sxun01 internal enabled :-) 2017-04-20 07:53:28
nova-scheduler sxun01 internal enabled :-) 2017-04-20 07:53:28
nova-conductor sxun01 internal enabled :-) 2017-04-20 07:53:28
nova-cert sxun01 internal enabled :-) 2017-04-20 07:53:28
nova-compute sxun01 nova enabled :-) 2017-04-20 07:53:24
最终结果,可喜可贺。