How to remove database in a safe way<\/a>
\nWhen you have to drop a large database, you\u2019ll encounter some problems, mainly replication\u2026<\/p>\n
\nWhat happens when you issue a DROP TABLE command? The table has to be removed from the table dictionary \u2013 which is a fast, atomic operation \u2013 and has to be removed from file system too. If you use an older version than 5.5.10 you have to calculate with a huge amount of time if your buffer pool is big, because the server will scan through the pages there, checking if anything is in memory from that table; between 5.5.10 and 5.5.30 you can use the \u2018innodb_lazy_drop_table\u2019 variable to adjust this behaviour \u2013 when you set it to \u201c1\u201d this check will happen in the background -, but after 5.5.30 you don\u2019t have to deal with this.<\/p>\n
Okay, so you have two atomic operations, but last time you dropped a huge table it took seconds, why? It takes a constant amount of time to unlink a single block, but files beyond the size of a single block consist of multiple blocks linked together, and the larger the file the larger the number of blocks that are linked.<\/p>\n
To solve this, we have to know about one more thing, called hard links. On UNIX every file has an inode where the file begins, and if you create a hard link, then it will look like you have a new file, but that will be the same as the old one. (Technically all the files are hard links to inodes.)<\/p>\n
[root@db-dev.xyz \/var\/lib\/mysql\/kinja]# ls -i event.ibd\n17433044 event.ibd\n[root@db-dev.xyz \/var\/lib\/mysql\/kinja]# ln event.ibd event.ibd_link\n[root@db-dev.xyz \/var\/lib\/mysql\/kinja]# stat event.ibd*\nFile: `event.ibd'\nSize: 13023313920 Blocks: 25436168 IO Block: 4096 regular file\nDevice: 801h\/2049d Inode: 17433044 Links: 2\nAccess: (0640\/-rw-r-----) Uid: ( 496\/ mysql) Gid: ( 497\/ mysql)\nAccess: 2015-01-13 03:13:52.193601140 -0500\nModify: 2015-01-13 01:04:55.000000000 -0500\nChange: 2015-01-13 05:51:32.762582905 -0500\nFile: `event.ibd_link'\nSize: 13023313920 Blocks: 25436168 IO Block: 4096 regular file\nDevice: 801h\/2049d Inode: 17433044 Links: 2\nAccess: (0640\/-rw-r-----) Uid: ( 496\/ mysql) Gid: ( 497\/ mysql)\nAccess: 2015-01-13 03:13:52.193601140 -0500\nModify: 2015-01-13 01:04:55.000000000 -0500\nChange: 2015-01-13 05:51:32.762582905 -0500\n<\/code><\/pre>\nTo remove a file on UNIX (like systems) we can use the unlink() (man 2 unlink) C function, which decreases the link count by 1 on a hard link (file), and removes the inode when link count equals to 0. To check this see how MySQL handles file deletion!<\/p>\n
The following source code snippet is taken from MySQL-5.6.22 source code. (\/storage\/innobase\/os\/os0file.cc)<\/p>\n
\/***********************************************************************\/\/**\n1807 Deletes a file if it exists. The file has to be closed before calling this.\n1808 @return TRUE if success *\/\n1809 UNIV_INTERN\n1810 bool\n1811 os_file_delete_if_exists_func(\n1812 \/*==========================*\/\n1813 const char* name) \/*!< in: file path as a null-terminated\n1814 string *\/\n1815 {\n1816 #ifdef __WIN__\n1817 bool ret;\n1818 ulint count = 0;\n1819 loop:\n[... SOME UNIMPORTANT WINDOWS STUFF :) ...]\n1853 #else\n1854 int ret;\n1855\n1856 ret = unlink(name);\n1857\n1858 if (ret != 0 && errno != ENOENT) {\n1859 os_file_handle_error_no_exit(name, \"delete\", FALSE);\n1860\n1861 return(false);\n1862 }\n1863\n1864 return(true);\n1865 #endif \/* __WIN__ *\/\n1866}\n<\/code><\/pre>\nYou can see that as it was expected, the file will be removed with the unlink() function! So far, so good. What we can expect, when we create a hard link to a database table file? Nothing at all. That will be not shown up in the table dictionary, and the link count increase will be handled by the file system.<\/p>\n
So the hacky way to remove table fast is the following: create a hard link to that table on file system, and drop the table inside mysql. The unlink part will run fast and removes the original file, however, the link still exists, but MySQL will not know about that file, which you can remove manually. YAY!<\/p>\n","protected":false},"excerpt":{"rendered":"
I showed in an earlier post how to drop a whole database in a very safe way (no replication lag at all) and that technique could be used to drop a single table too, but cleaning up a table can take hours if not days to finish, so this is not the most comfortable way […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[134],"tags":[144,126],"class_list":["post-45","post","type-post","status-publish","format-standard","hentry","category-tech-en","tag-mysql-en","tag-oldblog-en"],"_links":{"self":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/45","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=45"}],"version-history":[{"count":1,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/45\/revisions"}],"predecessor-version":[{"id":434,"href":"https:\/\/banyek.net\/index.php?rest_route=\/wp\/v2\/posts\/45\/revisions\/434"}],"wp:attachment":[{"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=45"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=45"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/banyek.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=45"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}