Zabbix-server Migrating databases

Today landed Zabbix, found that the Zabbix-server disk has reached 80%, issued an alarm,

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/20/4bb3b3971c3d830bbf9fa09978c17de8.png "title=" 1.png " alt= "4bb3b3971c3d830bbf9fa09978c17de8.png"/>

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/20/96be50916b6449b67d120bfc74f25950.png "title=" 1.png " alt= "96be50916b6449b67d120bfc74f25950.png"/>

Log in to the server, using the #ll-shil found that the following tables are too large to occupy disk space:

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/20/c9ffa5361b2d200616b1f5d90b8176a2.png "title=" 1.png " alt= "C9ffa5361b2d200616b1f5d90b8176a2.png"/>

Check it out in MySQL (Zabbix's databases is called Zabbix):

mysql> select table_name,  (data_length+index_length)/1024/1024 as total_mb,  Table_rows from information_schema.tables where table_schema= ' Zabbix '; +------------------- ---------+---------------+------------+| table_name                  | total_mb      |  table_rows |+----------------------------+---------------+------------+| events                      |  2876.00000000 |   23659702 | |  history                     | 3005.60937500 |   36816179 | |  history_uint                | 2762.26562500 |   35895354 | |  trends_uint                 | 1189.60937500 |   16612396 | |  trends                      |  831.59375000 |   11548652 |+-------------- --------------+---------------+------------+113 rows in set  (0.08 sec)

The above is a large table of data, so our focus is on them. Because the amount of data is too large, it is almost impossible to delete the data in the normal way. So decide to use TRUNCATE table directly to quickly empty the data of these tables, then use mysqldump to export the data, delete the shared tablespace data file, and re-import the data.

So we stopped Zabbxi-server first.

Systemctl stop Zabbix-server systemctl stop httpd

Then log in to MySQL and clear the historical data:

[[Email protected] ~] # mysql-uroot-pmysql > Use zabbix;database changedmysql > TRUNCATE TABLE history; Query OK, 123981681 rows affected (0.23 sec) MySQL > Optimize table history;1 row in Set (0.02 sec) mysql > truncate t Able History_uint; Query OK, 57990562 rows affected (0.12 sec) MySQL > Optimize table history_uint;1 row in Set (0.03 sec)

The original database is then backed up,#mysqldump-uroot-p password Zabbix >/home/zabbix_db.sql .

After the backup is complete, you can # SYSTECMTL stop mariadb to shut down MySQL and delete the shared tablespace data file,#rm-rf/var/lib/mysql/ib*.

Then prepare a larger disk, such as the new disk is called Zabbixdb, and then create a DB folder inside. The/zabbixdb/db group and user are then changed to MySQL, the statement is:# CHOWN-VR mysql:mysql/zabbixdb/db.

After the change is over, give 700permissions:# CHMOD-VR 700/etc/zabbixdb/db.

Then the whole/var/lib/mysql* content is imported into the zabbixdb/db:#cp-av/var/lib/mysql*/zabbixdb/db.

To modify MY.CNF, add a sentence in [mysqld]: innodb_file_per_table=1, which is to modify the InnoDB to stand-alone tablespace mode, each table of each database generates a data space. Also modify the database storage directory:

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/20/10109693859979ee599849948a269afd.png "title=" 1.png " alt= "10109693859979ee599849948a269afd.png"/>

This time can be # systemctl start mariadb Restart the MySQL service, after starting to see the "Standalone table space" function just set in my.cnf OK, check the statement is show variables like '%per_table% '; if you see "on", that means it's turned on:

650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/20/07f6c768b6590694105a9040b5edd929.png "title=" 1.png " alt= "07f6c768b6590694105a9040b5edd929.png"/>

You can then restore the database:

[Email protected] zabbix]# Mysql-uroot Zabbix 

If the error occurs at this time, there are errors like this:
650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/20/4e1b2903d176a7b8792e58600012a67c.png "title=" 1.png " alt= "4e1b2903d176a7b8792e58600012a67c.png"/>
This may be caused by the database cache, this time can be used in the database #flush TABLES; , but it will probably not.

So this time, go to the new MySQL directory folder, that is,/zabbixdb/db, and then into the database Zabbix, found that the folder has a lot of files, but each of the files are both one. IBD has another. frm, and this "Globalmacro" It's only IBD and not. frm, so this time we can move this globalmacro.ibd to somewhere else and then re-execute
# Mysql-uroot Zabbix 
You can restore the database.

Last boot Zabbix-server:
Systemctl start Zabbix-serversystemctl start httpd

Finally, check the disk space situation:
650) this.width=650; "src=" Https://s2.51cto.com/oss/201711/20/8dd602ef3980638dbb38e3be1310bd94.png "title=" 1.png " alt= "8dd602ef3980638dbb38e3be1310bd94.png"/>

found that the entire disk operation is OK ~, at this point the entire Zabbix database migration is complete.

[Reference]https://stackoverflow.com/questions/17914446/ Mysqldump-problems-with-restore-error-please-discard-the-tablespace-before-imp
This article is from "Life is waiting for Gordo" blog, please make sure to keep this source http://chenx1242.blog.51cto.com/10430133/1983612
Zabbix-server Migrating databases