二进制安装MySQL5.5.39，磁盘空间不足导致MySQL无法启动_MySQL

--添加用户和组 [root@zlm local]# groupadd
[root@zlm local]# useradd -g mysql -s /sbin/nologin -d /opt/msyql mysql
--创建目录 [root@zlm local]# mkdir /data/mysql/mysql_3306/{data,logs,tmp} -p
--做软链接到/usr/local/mysql [root@zlm local]# ln -s /opt/mysql/mysql-5.5.39-linux2.6-i686/ mysql [root@zlm local]# ll -l mysql lrwxrwxrwx 1 root root 38 Sep 24 00:12 mysql -> /opt/mysql/mysql-5.5.39-linux2.6-i686/

--赋权限 [root@zlm local]# chown -R mysql:mysql /data/mysql/ [root@zlm local]# chown -R mysql:mysql /usr/local/mysql/
--添加环境变量 [root@zlm local]# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile [root@zlm local]# cd mysql
--初始化数据库 [root@zlm mysql]# ./scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf Installing MySQL system tables... 140924 0:12:43 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log_slow_queries is not set OK Filling help tables... 140924 0:12:44 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log_slow_queries is not set OK
To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands:
/usr/local/mysql/bin/mysqladmin -u root password 'new-password' /usr/local/mysql/bin/mysqladmin -u root -h zlm password 'new-password'
Alternatively you can run: /usr/local/mysql/bin/mysql_secure_installation
which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with: cd /usr/local/mysql ; /usr/local/mysql/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl cd /usr/local/mysql/mysql-test ; perl mysql-test-run.pl
Please report any problems at http://bugs.mysq?l.com/
--复制mysql.server文件到/etc/init.d/下并改名为mysql [root@zlm mysql]# cp support-files/mysql.server /etc/init.d/mysql cp: overwrite `/etc/init.d/mysql'? yes
--启动MySQL [root@zlm mysql]# service mysql start Starting MySQL...............The server quit without updating PID file (/data/mysql/mysql_3306/data/zlm.pid). [FAILED]
--查看错误日志error.log（如未在/etc/my.cnf中定义，默认格式为hostname.err） [root@zlm local]# cd /data/mysql/mysql_3306/data/
[root@zlm data]# ll total 403024 -rw-rw---- 1 mysql mysql 2824 Sep 24 00:13 error.log -rw-rw---- 1 mysql mysql 104857600 Sep 24 00:13 ibdata1 -rw-rw---- 1 mysql mysql 104857600 Sep 24 00:13 ib_logfile0 -rw-rw---- 1 mysql mysql 104857600 Sep 24 00:13 ib_logfile1 -rw-rw---- 1 mysql mysql 97685504 Sep 24 00:13 ib_logfile2 drwx------ 2 mysql root 4096 Sep 24 00:12 mysql drwx------ 2 mysql mysql 4096 Sep 24 00:12 performance_schema drwx------ 2 mysql root 4096 Sep 24 00:12 test [root@zlm data]# cat error.log 140924 00:13:33 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mysql_3306/data 140924 0:13:33 [Warning] options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log_slow_queries is not set 140924 0:13:33 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead. 140924 0:13:33 [Note] Plugin 'FEDERATED' is disabled. 140924 0:13:33 InnoDB: The InnoDB memory heap is disabled 140924 0:13:33 InnoDB: Mutexes and rw_locks use InnoDB's own implementation 140924 0:13:33 InnoDB: Compressed tables use zlib 1.2.3 140924 0:13:33 InnoDB: Using Linux native AIO 140924 0:13:33 InnoDB: Initializing buffer pool, size = 128.0M 140924 0:13:33 InnoDB: Completed initialization of buffer pool InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 140924 0:13:33 InnoDB: Setting file ./ibdata1 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 140924 0:13:40 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 140924 0:13:43 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 140924 0:13:44 InnoDB: Log file ./ib_logfile2 did not exist: new to be created InnoDB: Setting log file ./ib_logfile2 size to 100 MB InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB:140924 0:13:45 InnoDB: Error: Write to file ./ib_logfile2 failed at offset 0 97517568. InnoDB: 1048576 bytes should have been written, only 167936 were written. InnoDB: Operating system error number 2. InnoDB: Check that your OS and file system support files of this size. InnoDB: Check also that the disk is not full or a disk quota exceeded. InnoDB: Error number 2 means 'No such file or directory'. InnoDB: Some operating system error numbers are described at InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html InnoDB: Error in creating ./ib_logfile2: probably out of disk space 140924 0:13:45 [ERROR] Plugin 'InnoDB' init function returned error. 140924 0:13:45 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 140924 0:13:45 [ERROR] Unknown/unsupported storage engine: InnoDB 140924 0:13:45 [ERROR] Aborting
140924 0:13:45 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
140924 00:13:45 mysqld_safe mysqld from pid file /data/mysql/mysql_3306/data/zlm.pid ended [root@zlm data]# perror 2 --perror是用于查询Linux错误代码的命令 OS error code 2: No such file or directory [root@zlm data]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 7.7G 7.3G 0 100% / --磁盘空间使用率100%，没有剩余空间了 /dev/sda1 99M 12M 82M 13% /boot tmpfs 506M 0 506M 0% /dev/shm
--关机添加磁盘 [root@zlm data]# init 0 [root@zlm ~]# rm -rf /data --先删除掉现有的/data目录中全部数据，因为重新挂载后也会丢失的，提前先释放掉这部分空间 [root@zlm ~]# fdisk -l
Disk /dev/sda: 10.7 GB, 10737418240 bytes 255 heads, 63 sectors/track, 1305 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System /dev/sda1 * 1 13 104391 83 Linux /dev/sda2 14 1305 10377990 8e Linux LVM
Disk /dev/sdb: 5368 MB, 5368709120 bytes --增加了1个5G的磁盘 255 heads, 63 sectors/track, 652 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdb doesn't contain a valid partition table --还未分区
--对新添加的磁盘分区 [root@zlm ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel Building a new DOS disklabel. Changes will remain in memory only, until you decide to write them. After that, of course, the previous content won't be recoverable.
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n Command action e extended p primary partition (1-4) p Partition number (1-4): 1 First cylinder (1-652, default 1): Using default value 1 Last cylinder or +size or +sizeM or +sizeK (1-652, default 652): Using default value 652
Command (m for help): w The partition table has been altered!
Calling ioctl() to re-read partition table. Syncing disks.
--查看磁盘信息 [root@zlm ~]# fdisk -l
Disk /dev/sda: 10.7 GB, 10737418240 bytes 255 heads, 63 sectors/track, 1305 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System /dev/sda1 * 1 13 104391 83 Linux /dev/sda2 14 1305 10377990 8e Linux LVM
Disk /dev/sdb: 5368 MB, 5368709120 bytes 255 heads, 63 sectors/track, 652 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes
Device Boot Start End Blocks Id System /dev/sdb1 1 652 5237158+ 83 Linux --分完区了
--格式化ext3文件系统 [root@zlm ~]# mkfs.ext3 /dev/sdb1
mke2fs 1.39 (29-May-2006) Filesystem label= OS type: Linux Block size=4096 (log=2) Fragment size=4096 (log=2) 655360 inodes, 1309289 blocks 65464 blocks (5.00%) reserved for the super user First data block=0 Maximum filesystem blocks=1342177280 40 block groups 32768 blocks per group, 32768 fragments per group 16384 inodes per group Superblock backups stored on blocks: 32768, 98304, 163840, 229376, 294912, 819200, 884736
Writing inode tables: done Creating journal (32768 blocks): done Writing superblocks and filesystem accounting information: done
This filesystem will be automatically checked every 21 mounts or 180 days, whichever comes first. Use tune2fs -c or -i to override. [root@zlm ~]# mkdir /data/mysql/mysql_3306/{data,logs,tmp} -p [root@zlm ~]# mount /dev/sdb1 /data [root@zlm ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/VolGroup00-LogVol00 7.7G 6.9G 398M 95% / --可以看到，重启完以后又释放了一部分空间 /dev/sda1 99M 12M 82M 13% /boot tmpfs 506M 0 506M 0% /dev/shm /dev/sdb1 5.0G 139M 4.6G 3% /data --/data目录已经挂载到新增的5G磁盘上去了
--为了使挂载持久化，重启后依然不会丢失，需要修改/etc/fstab [root@zlm ~]# vi /etc/fstab /dev/VolGroup00/LogVol00 / ext3 defaults 1 1 /dev/VolGroup00/LogVol00 / ext3 defaults 1 1 LABEL=/boot /boot ext3 defaults 1 2 tmpfs /dev/shm tmpfs defaults 0 0 devpts /dev/pts devpts gid=5,mode=620 0 0 sysfs /sys sysfs defaults 0 0 proc /proc proc defaults 0 0 /dev/VolGroup00/LogVol01 swap swap defaults 0 0 /data /dev/sdb1 ext3 defaults 0 0 --在文件末尾加入这一行，指定挂载的目录与磁盘的对应关系
--重新创建/data下面的相关目录 [root@zlm ~]# cd /data/ [root@zlm data]# ll total 1
drwx------ 2 root root 16384 Sep 24 00:32 lost+found
[root@zlm data]# rm -rf lost+found [root@zlm data]# ll total 0
[root@zlm data]# mkdir /data/mysql/mysql_3306/{data,logs,tmp} -p
--重新赋权限后初始化数据库 [root@zlm mysql]# pwd /usr/local/mysql [root@zlm mysql]# chown -R mysql:mysql /data/mysql/
[root@zlm mysql]# ./scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf

注意必须是先给/data目录赋予权限后再初始化，否则会报错，因为/data目录是重新创建并挂载到sdb1磁盘分区上的 如： Installation of system tables failed! Examine the logs in /data/mysql/mysql_3306/data for more information.
--再次重启MySQL服务 [root@zlm mysql]# service mysql start Starting MySQL........... [ OK ] [root@zlm mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(testing)root@localhost [(none)]> exit Bye [root@zlm mysql]# ps aux|grep mysql root 2744 0.4 0.1 4528 1276 pts/0 S 00:54 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/mysql_3306/data --pid-file=/data/mysql/mysql_3306/data/zlm.pid mysql 3452 3.2 3.4 319580 35472 pts/0 Sl 00:55 0:03 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql_3306/data/error.log --open-files-limit=8192 --pid-file=/data/mysql/mysql_3306/data/zlm.pid --socket=/tmp/mysql.sock --port=3306 root 3493 0.0 0.0 3912 672 pts/0 R+ 00:56 0:00 grep mysql [root@zlm mysql]# mysqladmin shutdown [root@zlm mysql]# ps aux|grep mysql root 3501 0.0 0.0 3912 668 pts/0 R+ 00:56 0:00 grep mysql [root@zlm mysql]#
第2种启动方式： [root@zlm mysql]# /etc/init.d/mysql start Starting MySQL...... [ OK ] [root@zlm mysql]#
第3种启动方式： [root@zlm mysql]# mysqld_safe --defaults-file=/etc/my.cnf & [1] 4612 [root@zlm mysql]# Starting mysqld daemon with databases from /data/mysql/mysql_3306/data STOPPING server from pid file /data/mysql/mysql_3306/data/zlm.pid 140924 01:09:02 mysqld ended
这里启动失败了，查看error.log，提示说xxx参数设置得不合理，如： [root@zlm mysql]# cat error.log 140924 01:03:00 mysqld started
140924 1:03:00 [ERROR] /usr/libexec/mysqld: unknown variable 'slow_query_log_file=slow.log'
140924 01:03:00 mysqld ended
140924 01:04:54 mysqld started 140924 1:04:54 [ERROR] /usr/libexec/mysqld: unknown variable 'slow_query_log=0'
140924 01:04:54 mysqld ended
140924 01:05:39 mysqld started Unknown suffix '.' used for variable 'long_query_time' (value '0.5') 140924 1:05:39 [ERROR] /usr/libexec/mysqld: Error while setting value '0.5' to 'long_query_time'
140924 01:05:39 mysqld ended
140924 01:06:26 mysqld started 140924 1:06:26 [ERROR] /usr/libexec/mysqld: unknown variable 'min_examined_row_limit=100'
140924 01:06:26 mysqld ended
140924 01:09:02 mysqld started 140924 1:09:02 [ERROR] /usr/libexec/mysqld: unknown variable 'general_log_file=general.log'
140924 01:09:02 mysqld ended
造成这个问题的原因其实是我之前在安装RHEL 5.3操作系统的时候，选了系统盘里自带的那个mysql-5.0.45-7.el5 rpm包引起的，即便已经删除掉了。这个rpm包安装后会拷贝mysqld到/usr/libexec/目录下，很多在MySQL 5.5中可以识别的参数，在MySQL 5.0中是不认的，所以造成启动失败，一直提示"unknown variable"，比方说以下这些： table_definition_cache table_open_cache slow_query_log_file
slow_query_log
long_query_time
min_examined_row_limit ...
解决办法：
--用新版本的mysqld可执行文件去覆盖掉原来/usr/libexec下的mysqld以后，就能用mysqld_safe & 的方式启动了
[root@zlm mysql]# cp /usr/local/mysql/bin/mysqld /usr/libexec/mysqld
cp: overwrite `/usr/libexec/mysqld'? yes
[root@zlm mysql]# mysqld_safe --defaults-file=/etc/my.cnf &
[1] 4687 Starting mysqld daemon with databases from /data/mysql/mysql_3306/data
这里会一直停在这一行，按ctrl+c可以重新返回到shell命令行
--查看mysql进程 [root@zlm mysql]# ps aux|grep mysql root 4687 0.0 0.1 4484 1152 pts/0 S 01:10 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf mysql 4721 0.0 3.1 329828 32704 pts/0 Sl 01:10 0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --user=mysql --pid-file=/data/mysql/mysql_3306/data/zlm.pid --skip-external-locking --port=3306 --socket=/tmp/mysql.sock root 4768 0.0 0.0 3912 668 pts/0 R+ 01:25 0:00 grep mysql
可以看到，用mysqld_safe方式，也已经启动成功了，注意，用这种方式启动，比单纯用mysqld方式启动要更佳，即使去kill掉mysqld的进程后，mysqld_safe可以保证mysqld进程重启
[root@zlm mysql]# ps aux | grep mysql root 5134 0.5 0.1 4484 1152 pts/0 S 01:32 0:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf mysql 5171 2.4 3.1 319584 32708 pts/0 Sl 01:32 0:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --user=mysql --pid-file=/data/mysql/mysql_3306/data/zlm.pid --skip-external-locking --port=3306 --socket=/tmp/mysql.sock root 5193 0.0 0.0 3912 668 pts/0 R+ 01:32 0:00 grep mysql [root@zlm mysql]# kill -9 5171 [root@zlm mysql]# /usr/bin/mysqld_safe: line 388: 5171 Killed nohup /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --user=mysql --pid-file=/data/mysql/mysql_3306/data/zlm.pid --skip-external-locking --port=3306 --socket=/tmp/mysql.sock >> error.log 2>&1
Number of processes running now: 0 140924 01:32:54 mysqld restarted 按ctrl+c返回到shell命令行
[root@zlm mysql]# ps -ef | grep mysql root 5134 2531 0 01:32 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf mysql 5205 5134 0 01:32 pts/0 00:00:00 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/mysql_3306/data --user=mysql --pid-file=/data/mysql/mysql_3306/data/zlm.pid --skip-external-locking --port=3306 --socket=/tmp/mysql.sock root 5229 2531 0 01:33 pts/0 00:00:00 grep mysql
mysqld_safe进程依然还是5134，但刚才被kill掉的mysqld进程5171已经变成5205这个新的进程了，其实mysqld_safe就是mysqld的守护进程，用来保证mysqld的正常运行的，当我们执行mysqld_safe &来启动MySQL的时候，其实最终还是去调用mysqld这个命令的