mysql 使用中 怎么解决_MySQL使用中遇到的问题及解决办法

【问题一】在安装配置MySQL多实例时，出现以下问题

................................................................

[root@localhost 3308]# cat mysql_qiuuuu3306.err

2017-08-18 04:36:55 11343 [ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 768 pages (rounded down to MB) than specified in the .cnf file: initial 8192 pages, max 0 (relevant if non-zero) pages!

2017-08-18 04:36:55 11343 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!

2017-08-18 04:36:55 11343 [ERROR] Plugin 'InnoDB' init function returned error.

2017-08-18 04:36:55 11343 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2017-08-18 04:36:55 11343 [ERROR] /application/mysql/bin/mysqld: unknown variable 'table_cache=614'

2017-08-18 04:36:55 11343 [ERROR] Aborting

....................................................................

【解决办法】

idata1与my.cnf innodb_data_file_path = ibdata1:XX:autoextend不一致造成的错误

[root@localhost 3306]# cd /data/3308/data/

[root@localhost data]# ll -h

total 109M

-rw-rw----. 1 mysql mysql  12M Aug 18 04:43 ibdata1

-rw-rw----. 1 mysql mysql  48M Aug 18 04:43 ib_logfile0

-rw-rw----. 1 mysql mysql  48M Aug 18 03:40 ib_logfile1

drwx------. 2 mysql mysql 4.0K Aug 18 03:40 mysql

drwx------. 2 mysql mysql 4.0K Aug 18 04:43 performance_schema

drwx------. 2 mysql mysql    6 Aug 18 03:40 test

此时发现ibdata1的大小为12M，需要将/data/3308/my.cnf中的

innodb_data_file_path = ibdata1: 12M: autoextend的大小改为12M

【问题二】在上面的基础上面，任然报错如下

[root@localhost 3308]# cat mysql_qiuuuu3306.err

....................................

2017-08-18 05:18:21 23534 [Note] Plugin 'FEDERATED' is disabled.

2017-08-18 05:18:21 23534 [ERROR] InnoDB: syntax error in innodb_data_file_path or size specified is less than 1 megabyte

2017-08-18 05:18:21 23534 [ERROR] Plugin 'InnoDB' init function returned error.

2017-08-18 05:18:21 23534 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2017-08-18 05:18:21 23534 [ERROR] Unknown/unsupported storage engine: InnoDB

2017-08-18 05:18:21 23534 [ERROR] Aborting

......................................

【解决办法】

[root@localhost 3308]# vim /data/3308/my.cnf

innodb_data_file_path = ibdata1: 12M: autoextend 发现路径中包含有空格，将空格删除就能正常启动！

[root@localhost 3308]# /data/3308/mysql start

starting mysql…

[root@localhost 3308]# netstat -luntp|grep 330

tcp6       0      0 :::3308                 :::*                    LISTEN      24358/mysqld

【问题三】初始化mysql时，出现一下报错信息

[root@bogon support-files]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql --datadir=/application/mysql/data --user=mysql

FATAL ERROR: please install the following Perl modules before executing /application/mysql/scripts/mysql_install_db:

Data::Dumper

【解决办法】

[root@bogon support-files]# yum -y install autoconf

【问题四】开机，开启mysql是出现的能够启动mysql，但是不能进入mysql

[root@test /]# /etc/init.d/mysqld start

Starting MySQL                                             [  OK  ]

[root@test /]# /etc/init.d/mysqld status

Multiple MySQL running but PID file could not be found

【解决办法】

使用kill -9 PID 杀死mysql的两个进程，然后重新启动mysql