Common boot failures and backup failures under Mysql tutorial _mysql

Startup failed
Restart the server after--> restart the application service (confluence)--> error, database connection failed (MySQL set up from boot)--> view MySQL database status:

[Root@fisheye ~]# Ps-ef | grep MySQL
root   25555 21974 0 11:28 pts/0  00:00:00 grep mysql

Start the MySQL server

[Root@fisheye data]# service MySQL start

MySQL server PID file could not is found! [Failed]
Starting MySQL ....... ... error! The server quit without updating PID file (/mydata/data/fisheye. PID). Failed

To view the error log:

[Root@fisheye data]# tail-100 Fisheye.err

Innodb:last MySQL binlog file position 0 337403929, file name./mysql-bin.000016 141013 1:13:28 innodb:waiting for
t He background threads to start
141013 1:13:29 innodb:5.5.33 started; log sequence number 1006647152
17:13:29 UTC -Mysqld got signal;
This could is because you hit a bug. It is also possible so this binary
or one of the libraries it being linked against IS corrupt, improperly built,
o R misconfigured. This error can also is caused by malfunctioning hardware.
We'll try our best to scrape up some info that'll hopefully help
di141013 01:13:29 mysqld_safe mysqld from PID fil E/mydata/data/fisheye.pid Ended

No obvious error prompted, so manually create a PID file try

[Root@fisheye data]# Touch/mydata/data/fisheye.pi

Restart the service again:

[Root@fisheye data]# service MySQL restart

ERROR 2002 (HY000): Can ' t connect to the local MySQL server through socket '/tmp/mysql.sock ' (2)

Suddenly think of the previous read such an error article, remember that there may be insufficient disk space caused by MySQL can not start.

[Root@fisheye data]# Df-h

(File system capacity has been used available with% mount points)

/DEV/SDA1       9.5G 9.5G 0 100%/
/dev/sda4       5.5G 1.3G 4.0G 24%/mnt/backup
/dev/mapper/ ihuilianvg-ihuilianlv00
            22G 4.2G  17G 20%/var/www/app
tmpfs         1.3G   0 1.3G  0%/dev/shm

Sure enough, here's a list of solutions for similar problems (which cannot be started):
1. The DataDir directory may be full of partitions (DF-H)
Workaround: Open the configuration file/etc/my.cnf, reassign the Data directory (DATADIR) at the [Mysqld] section, and migrate the original data directory to the redefined data directory
About migration: (1), CP or tar must be brought to the right, however, in order to prevent the accidental recommendation again; (2), the data is relatively large must first compress and then migrate, to ensure integrity, especially when the SCP to other machines may time out so be sure to compress (tar.gz); (3), If you move to another server, be sure to keep the MySQL version consistent.

2. May be the/mydata/data/fisheye.pid file does not write permission
Workaround: Give permission, execute "chown-r mysql:mysql/mydata/data/" and restart mysqld!

3. There may already be a MySQL process in progress
WORKAROUND: Use the command "Ps-ef|grep mysqld" to see if there is a mysqld process that is killed with the kill-9 process number and then restarted mysqld!

4. It may be the second time that MySQL is installed on the machine, with residual data affecting the startup of the service.
Solution: Go to MySQL Data directory/data Look, if there is mysql-bin.index, quickly remove it, it is the culprit.

5.skip-federated field problem (Error message: [ERROR]/mydata/data/mysql/libexec/mysqld:unknown option '--skip-federated ')
Workaround: Check to see if there are any skip-federated fields in the/etc/my.cnf file that are not commented out, and comment them out immediately.

6.selinux trouble, if it is CentOS system, the default will open SELinux
Solution: Close it, open the/etc/selinux/config, change the selinux=enforcing to selinux=disabled after the disk to restart the machine to try.

Backup failed
description
Cannot find a certain tables and break execution when performing mysqldump? and lock the table after the extension of the problem record!
Problems and programmes are as follows
Error Meaage: Unable to find a tables and interrupt execution while executing mysqldump

[root@test100 data]# mysqldump FX > Fx.sql

Mysqldump:got error:1146:table ' user_suggest_report ' doesn ' t exist when using LOCK TABLES

Consider adding--skip-lock-tables or R to lock the table to try, also not, the information is as follows

[root@test100 data] #mysqldump--skip-lock-tables FX > Fx.sql

Error:couldn ' t read status information for Table Vote_results () mysqldump:couldn ' t execute ' show create TABLE ' User_sug Gest_report ': Table ' fx.user_suggest_report ' doesn ' t exist (1146)

Login server to see if this table exists

 [root@test100 data] #mysql-h127.0.0.1-d FX
 mysql> show tables;      #查看所有的表--> found the table exists.

+--------------------------------+
| Tables_in_fx          |
+--------------------------------+
| user_suggest_report      |
+--------------------------------+
rows in Set (0.00 sec)

Delete this table

mysql> drop table User_suggest_report;      #既然是存在的, but the system is determined that there is no explanation of the problem, simply want to delete try

Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' user_suggest_report ' at l INE 1

Move or delete the data table in the MySQL storage directory

[Root@test100 data]# cat/etc/my.cnf | grep datadir
datadir=/var/lib/mysql
[root@test100 data]# cd/var/lib/mysql/fx/
[root@test100 fx]# MV User _suggest_report.frm/data

Restart the MySQL server

[root@test100 fx]# Service mysqld Restart

Re-backup operation

[root@test100 data]# mysqldump FX > Fx.150109.sql  #操作成功