二進制安裝MySQL5.5.39，磁盤空間不足導致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這個命令的
