The Zabbix database is located on a full partition

after: nothing to open before colleagues do Zabbix page found found reported MySQL error, no time

After restarting the MySQL database, it's been a few minutes.

After a while, there's no graphic display.

Enter the database, show processlist; We found a lot of errors with the session delay.

After half a day of troubleshooting, found that the space to store data is full, resulting in MySQL is not linked to the

MySQL data is stored by default (sweat ~ Not I do/give 60g/home to 930G data actually saved to/inside)

So decide on the data migration

1. Modify the directory where MySQL data is stored

To modify two places, one is to modify the DataDir in the/etc/my.cnf file:

　

  datadir=/yinzhengjie/data/mysql/

Change it to:

Datadir=/home/mysql

Also to modify /etc/init.d/mysqld the file, change to datadir=  :

Datadir=/home/mysql

2. Stop the MySQL service

/etc/init.d/mysqld stop

3. Create a new data storage directory

Mkdir/home/mysql

4. Move the data to the new database storage directory

Mv/yinzhengjie/data/mysql/*  /home/mysql

To add a little bit of knowledge here, the InnoDB engine and the MyISAM engine have different data files.

For the MyISAM engine, the data files are "*.frm", "*". MYD "," *. MYI "Three files are stored in the corresponding database folder in the"/var/lib/mysql "directory. Move these files directly into the new data store directory, and you can.

For the InnoDB engine, the data is stored in the $innodb_data_home_dir” 的ibdata1 file (typically) and the structure file exists in table_name.frm.

5. Modify MySQL Database directory permissions and configuration files

Chown mysql:mysql/home/mysql/-R

6. Modify the socket

Modification of SOCKET,/ETC/MY.CNF in two places

Socket=/home/mysql/mysql.sock

Create a connection file to/var/lib/mysql/mysql.sock

Ln-s/home/mysql/mysql.sock/var/lib/mysql/mysql.sock

7. Restart the MySQL service

Perform:

/etc/init.d/mysqld start

Problem found:

It probably means that the permissions are globally writable and can be written by any user. MySQL is concerned that this file has been maliciously modified by other users, so ignore this configuration file.

644 /HOME/MYSQL/MY.CNF

Restart Normal:

Special case: (I have not met here)

Start reading and writing to the database and find that the problem is:

Write:

ERROR 1146 (42S02): Table * * doesn ' t exist

To create a table:

Workaround:

In the/data/mysql directory, delete the ib_logfile* file so that the InnoDB engine's table is normal.

Re-execute

This process can be slow for larger tables of data volume. After execution, the MyISAM engine's table is OK.

8. Now that MySQL is normal and database data are available, we should see Zabbix.

Restart Zaibbix:

　

/etc/init.d/zabbix_server restart

Website Access error:

Database error
Error connecting to database:no such file or directory

Workaround:

Ln-s/var/lib/mysql/mysql.sock/tmp/

Vim/usr/local/php/etc/php.ini

Mysqli.default_socket =/var/lib/mysql/mysql.sock

./php-fpm

Visit again: (normal)

　

　

The Zabbix database is located on a full partition