Tutorial _ MySQL

Source: Internet
Author: User
This article describes how to solve common startup failures and backup failures in MySQL. The example environment is based on the Linux system. For more information, see Startup failed
After the server is restarted --> restart the Application Service (Confluence) --> An error is reported, indicating that the database connection fails (mysql is set to boot automatically) --> view the mysql database Status:

[root@fisheye ~]# ps -ef | grep mysqlroot   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 cocould not be found! [Failed] Starting MySQL ...... ERROR! The server quit without updating PID file (/mydata/data/fisheye... pid). [failed]

View error logs:

[root@fisheye data]# tail -100 fisheye.err

InnoDB: Last MySQL binlog file position 0 337403929, file name ./mysql-bin.000016141013 1:13:28 InnoDB: Waiting for the background threads to start141013 1:13:29 InnoDB: 5.5.33 started; log sequence number 100664715217:13:29 UTC - mysqld got signal 11 ;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully helpdi141013 01:13:29 mysqld_safe mysqld from pid file /mydata/data/fisheye.pid ended

No obvious error prompt is found, so try to manually create a pid file

[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 local MySQL server through socket '/tmp/mysql.sock' (2)

I suddenly thought of the articles that I have read about this type of error. remember that mysql cannot be started due to insufficient disk space.

[root@fisheye data]# df -h

(File system capacity in use available % mount point)

/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/apptmpfs         1.3G   0 1.3G  0% /dev/shm

As a result, the following lists some solutions to similar problems (cannot be started:
1. it may be that the partitions in the datadir directory are full (df-h)
Solution: Open the configuration file/etc/my. cnf: Re-specify the data directory (datadir) under [mysqld], and migrate the original data directory to the newly created data directory.
About Migration: (strong); (3) If you move to another server, ensure that the mysql version is consistent.

2. it may be that the/mydata/data/fisheye. pid file has no write permission.
Solution: Grant permissions, execute "chown-R mysql: mysql/mydata/data/", and restart mysqld!

3. the mysql process may already exist in the process.
Solution: Run "ps-ef | grep mysqld" to check whether the mysqld process exists. if the "kill-9 process number" is used to kill the process and restart mysqld!

4. mysql may be installed on the machine for the second time. the service startup may be affected due to residual data.
Solution: go to the mysql data directory/data to see, if there is a mysql-bin.index, just remove it, it is the culprit.

5. skip-federated field problem (ERROR message: [ERROR]/mydata/data/mysql/libexec/mysqld: unknown option '-- skip-federated ')
Solution: Check whether the skip-federated field has been commented out in the/etc/my. cnf file. if so, comment out the field immediately.

6. selinux. if it is a centos system, selinux is enabled by default.
Solution: close it, open/etc/selinux/config, change SELINUX = enforcing to SELINUX = disabled, and then save the disk and restart the machine.

Backup failed
Description
When mysqldump is executed, a table cannot be found and the execution is interrupted? And the problem record after the lock table is extended!
The problem and solution are as follows:
Error Meaage: when mysqldump is executed, a table cannot be found and the execution is interrupted.

[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 try the lock table. 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_suggest_report`': Table 'fx.user_suggest_report' doesn't exist (1146)

Log on to the server and check whether the table exists.

[Root @ test100 data] # mysql-h127.0.0.1-D fx mysql> show tables; # View all tables --> It is found that the table exists.

+--------------------------------+| Tables_in_fx          |+--------------------------------+| user_suggest_report      |+--------------------------------+80 rows in set (0.00 sec)

Delete this table

Mysql> drop table user_suggest_report;

ERROR 1064 (42000): You have an 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 line 1

Go to the mysql storage directory and move or delete the data table.

[root@test100 data]# cat /etc/my.cnf | grep datadirdatadir=/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

Backup again

[Root @ test100 data] # mysqldump fx> fx.150109. SQL # operation successful
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.