Hello everyone. There are times when the need arises delete MySql database... Why delete and not edit? They just unknowingly piled up a bunch of tables, or don't know how to identify an error. Easier to demolish and create a new database. The best optionwill of course produce. Basically, every self-respecting administrator should know how to delete a MySql database.
PhpMyAdmin login
Let's consider working with MySql databases in phpMyAdmin, which is built into (this is not necessary at all, it is better to use it for training and training). After installing the local server on your computer, you will have access to phpMyAdmin and the ability to create databases.
Login to phpMyAdmin at local server you can follow the link: http://localhost./Tools/phpmyadmin/index.php.
Deleting MySql Database
In the admin panel on the left, select the database you are going to remove.
In the next window, at the top, the button Operations... You click.
And the last step ⇒ press Drop database (DROP).
How to delete MySql database from command line
The database can be erased using the command in command line... Sign in account to the database server: Z: \\ usr \\ local \\ mysql-5.5 \\ bin \\ mysql.exe -u root.
The command to delete a database looks like this: DROP DATABASE database_name,
-where database_name denotes the name of the database.
For example, DROP DATABASE moyblog - denotes to drop the database named moyblog. 
Press Enter to run the command. The system will display a message about the completion of the command and the elapsed time.
Cleaning and Editing MySql Database
Database deletion is a radical method. In many cases, it will be enough to remove a specific table, clear the table rows, some records - that is, edit the MySql database.
Dropping tables in a database
Enter the required database. Check the boxes you want and click Delete... If you need to erase only rows without deleting the table - press Clear.
Where did I say that plugins leave behind a bunch of garbage in the database? So, in addition to this heap, there is other garbage that gradually clogs our MySQL database, preventing the blog from developing calmly. Today I want to tell you how to get rid of all this garbage generated by plugins and more! Read on to find out how to clean up your MySQL database.
I must say right away that before cleaning, be sure to make a copy of MySQL! If something goes wrong, you can recover in seconds. How do I clear my MySQL database? Take a look at the short list below - all our actions:
- Why bother cleaning it from slag
- What needs to be cleaned
- How to clean up a database without hands
- How often do you need to clean up MySQL
Did what I asked you to do - create backup database data? Yes, let's go then ...
Based on my experience, I know that many reading this article do not even think to do something. If only because they saw my warning about creating a copy of the database. Now they think: "I will read it, save it to bookmarks, and then somehow I will solve this problem." They may also have a fear of cleaning.
Let's be honest! I have already written more than one article to promote the sites of my readers, but I did not see the result in many. That is, not all bloggers who read me follow my recommendations on their blogs. Or they could increase the attendance of their resource to at least 300 unique visitors per day. So, if you just read the post now, don't clean up the database, then there can be no question of any growth!
The next category of bloggers may simply be afraid to do some kind of manipulation on their blog, just so as not to damage anything. The point is, it's okay! I have not met such people who would like to ditch their offspring. Therefore, I wrote: "Make a backup of the MySQL database." If suddenly something goes wrong, you can immediately restore the blog. Although it is unlikely that something will go wrong, because we will be working with a plugin that has never crashed.
Still, it is better to overdo it than to miss it! What do you think? As a last resort, you can hire a freelancer who will do everything in in the best possible way... But why then, I am writing this article about cleaning up the database? That's right, not for him! A freelancer already receives money for his work, and in order to save money, you can do everything yourself, the more everything will be described in detail later.
How frightened you need to clean your MySQL database
Of course, not everyone's database grows very quickly. If the blog is set up perfectly inside and out, you did not install plugins, and then you turned them off, removed them as unnecessary, deleted revisions of articles, or maybe they were turned off altogether in the settings, did not receive SPAM comments, then your base grows only from new articles and normal comments ...
I have no doubt that your blog is set up in the best way, because even if you are not a layout designer, programmer or IT, the Internet is full of courses on this topic and I think you did all the settings perfectly. Could disable revisions or remove them with a plugin. Even from SPAM you have. But it cannot be that some plugin is not tried, and then it was deleted! So let's get down to business.
What needs to be cleaned in the MySQL database is mandatory
The list I have compiled is not large, but that does not mean that it is the same in your database! For an illustrative example, I will show you a screenshot of my database.
- Post revisions (WordPress engine makes copies of pages and posts by default just in case)
- Spam comments (all blogs and websites receive such comments, such as black SEO)
- Unnecessary tables (they are created by plugins, some plugins clean up garbage, but not all)
- Pinkbacks (when a blogger has the setting "Try to notify the blogs mentioned in the article" and he puts a link on your resource, then a record is entered into your database)
Now take a look at my MySQL database for an example:
Special plugin for cleaning the MySQL database from slag
Before cleaning up your database, take a look at it first. This can be done on the hosting in the phpMyAdmin section, but be sure to select the database you want to check on the left side. I wanted to show you how to find it step by step, but all hosting accounts are different and I think you can figure it out without me. As a last resort, write support by asking: "Where is my MySQL database".
Are you here? Okay! Now you can see all the tables in your database. There, at the end of each line, it is indicated how much space this or that table takes. We are not interested in KB, so look at MB. Here is a list of my most weighty tables:
- Wp-comments
- Wp-posts + wp_postmeta
- Wp_options
- Wp_blc_instances
- Wp_blc_links
- Wp_blc_synch
- Wp_wppage_conversion
- Wp_wppage_conversion_log
- Wp_wppage_stats
Your tables will most likely differ from mine, because we all use plugins according to our personal needs and desires. I will describe each table in order.
Wp-comments is responsible for comments, that is, all information about the comments left on your blog is entered there. It may be easy, but if there are a lot of SPAM comments left that were even sent to the trash, they still take up a lot of space. Most likely you have it very large, like mine. After all, there are almost 6,000 comments on my blog. Nothing wrong! We will clear the whole thing further.
Now check the Comments / Spam tab in your blog admin panel. Look if there is a lot of spam, delete them.
Wp_posts + wp_ppostmeta logs all blog posts and all their metadata. If you write voluminous posts, then this table will be large.
Wp_options stores our WordPress blog settings. There may be a small size, but not a fact! Are you using plugins? And where do you think their settings are written? Exactly, there. Optimize DB or Clean Options are used to reduce the size.
The last plugin is very good, it often helped me clean up the jambs behind other plugins that I removed. Clean Options pulls a lot of slag from the wp_options table. As usual, the old plugins settings remain in this database table. Why would they blog? Exactly, they need to be removed!
Only this plugin must not be abused! Use it if the wp_options table is really huge. If the size is 1-2 mb, it is better not to touch it.
Wp_blc_instances, wp_blc_links, wp_blc_synch + wp_wppage_conversion, wp_wppage_conversion_log, wp_wppage_stats, these tables are left by the Broken Link Checker and Wppage plugins. The first one, I use often - I checked it and turned it off so that there was less load on the blog, and the second one was installed some time ago, then deleted, but its tables remained.
So tables from Broken Link Checker need to be cleared, and Wppage should be deleted once and for all! We will do these actions now.
To do this, go to your phpMyAdmin, open the blog database, select the tables you need with checkmarks and select clear below.
So good, the DB is much smaller. Now we need to remove unnecessary tables from old plugins. In my case, this is Wppage.
Here, too, you need to select the necessary tables for cleaning with checkmarks, only at the bottom select - delete.
Only in no case should you rush! In a hurry, you can select the wrong tables.
Now let's move on to the WP-Cleanup plugin for semi-automatic cleaning of the MySQL database. Download it from here or in the admin panel on the "Plugins / add new" tab, write its name in the search. Install and activate.
To go go to "Settings / WP-Cleanup". Remove all slag by pressing one button:
- Post revisions
- Spam comments
- Unapproved comments
- Unused tags
- Unused META data
- Unused part of the database
Select the lines you want to clear and click the "Cleanup the selected items" button.
The database has been cleared!
Database cleaning is of course a necessary procedure, but in addition I recommend doing database optimization. I use WP-Optimize plugin for this.
After everything is done, disable WP-Cleanup. We don't need him anymore. By the way, I advise you to disable all plugins that are needed for one-time use. At least in order not to load the server once again.
How often do you need to clean up the database?
Do the cleaning depending on the activity on your blog, that is, if you installed plugins, updated articles, received SPAM comments, then it is better to clean the database right after that. To prevent SPAM from coming, revisions can be disabled, and plugins must be cleared anyway. Generally check the size of your database about once a month. I can't say for sure, everything depends only on you.
I hope you have done all the work and now know how to clean up the garbage from a MySQL database for the future. Subscribe to my blog updates, so as not to miss the most interesting and useful. See you!
From the author: do you think you have cleaned up everywhere? The house was cleaned, the car was washed, the children were "ripped out" to shine. And they forgot to clean up on their DBMS server! Can't you? It's not scary! Now I will teach you how to clean up a MySQL database.
Why clean up?
So that there is no debris and dust. In the case of databases, this means "digital" garbage. If your resource works on the basis of a CMS, then even in this case, various "scraps" remain in the tables after the work of plugins, the engine system. Well, maybe you also forgot the line somewhere. Gradually, all this "dust" accumulates in huge layers, which then negatively affect the speed of the entire resource. And this negatively affects the usability of the site. In general, it is still necessary and necessary to clean.
In addition, the base "grown" by leaps and bounds can take up a lot of virtual disk space, as a result of which the hoster will start to get nervous and fill you up with a message about exceeding the limit. If you don’t do anything (don’t switch to an expensive tariff in which large “areas” for the site are provided), then it will simply “unplug” your resource. Therefore, it is better to learn how to clean a MySQL database than to spend money on a new one. tariff plan or moving to "new rails".
Also, sometimes it is necessary to delete only the contents of the table and preserve its structure. We will consider all this in today's article devoted to the correct cleaning on a DBMS. Let's start!
Clearing the table
Often it is necessary not to delete a table, but only to clear it of all rows, but at the same time preserve its entire structure: column data types, indexes, primary keys, and its other parameters. Of course, you can always drop the entire table using the DROP TABLTE command, but then you have to create it and re-write all the attributes. And this is not very convenient, so MySQL has a special command for deleting table rows. Its syntax is:
TRUNCATE TABLE name_table;
TRUNCATE TABLE name_table; |
An example of how to clean up a MySQL database:
TRUNCATE TABLE `db1`.`user_animal`;
TRUNCATE TABLE `db1`. `user_animal`; |
We look into the table, and we see that the "Browse" tab is inactive, and the automatically generated select query returned an empty result. That is, they got what they wanted:
There is another SQL command that can produce a similar result - DELETE FROM. Usage example:
DELETE FROM `db1`.`animal`;
DELETE FROM `db1`. `animal`; |
Thus, we have cleared all the "cells" of the animal table from animals. The structure of the table was not affected, only records were deleted. We end up with the same result:
Before we clean up the MySQL database again, let's figure out how these two commands differ. More precisely than TRUNKATE is preferred for use:
Unlike DELETE FROM, this command does not erase all records line by line, but deletes and re-creates the table. This operation takes less time.
Doesn't return the number of rows deleted, which also saves resources.
The ability to recreate the table even with damaged indexes or data file.
Shell tools
But you don't have to worry about writing boring queries and use the built-in phpMyAdmin functionality to clean up tables. We go into the program, select the desired base on the left. After that, in the section of the main menu, select the table, and in the drop-down list, mark the action that should be performed with the table. We are interested in the "Clear" command. And do not forget to click "Ok" in the bottom right corner.
As you can see, the application executed the same command (TRUNCATE) and erased all records from the specified table.
Before we clean up the MySQL database completely, let's take a look at the Overview tab to make sure there is no data left in the table. Everything is fine, the automatically generated sample returned a zero result.
Easy cleaning
But this is not all the possibilities that this program provides. Its built-in tools let you tidy up a bit. More precisely, to optimize the work of the selected table, to put everything in its place. To do this, again select the desired table from the database, but in the drop-down list of commands, select "Optimize table".
The program system automatically generates a request to the DBMS server and sends it for execution. The kernel, in turn, returns a result and a report on the execution of the request.
This operation can be performed instead of "light cleaning" of the table, or after a certain period of time after thoroughly cleaning the MySQL database. But still, I wanted to know what exactly the OPTIMIZE TABLE command does:
Recovers deleted or incomplete records in a table.
Sorts index pages (tidies indexes).
Refreshes statistics for a table.
I think it is clear that this is a necessary command, which sometimes should be executed in order to maintain the "health" of tables in good shape.
Garbage bins
As for popular engines, MySQL cannot help you with them. Well, how can the DBMS know which records in the tables are "garbage" and which are not. For this (for each CMS), specialized extensions are developed. Here are some examples of such WordPress plugins:
WP-Cleanup. It is reported that it hasn't been updated for 2 years, but in fact it even works on latest versions engine.
Hope you find out how to clean MySQL database. The main thing is to understand that it is necessary to clean not only in the apartment, car, but also on the DBMS server. Otherwise, your site will be "cluttered" so that not a single visitor will "fit" into it. And this must not be allowed!
Every aspiring WordPress webmaster will sooner or later face slower website loading times. One of the reasons may be a bloated database. Especially to solve this problem, this article was written on how to clean WordPress. In it we will figure out why to clean the database, how to clean it of garbage and how often it needs to be done.
This article is presented from, which you can read on my blog, and arm yourself not only with the method described in this article, but also with other useful tips.
Page navigation:
Why clean your WordPress database?
To understand the meaning of cleaning the database, you need to figure out what is in it. And all our settings, posts, comments, meta data, copies and revisions of articles are in our database. The picture below shows the basic (standard) tables in the WordPress database.
As you can see, we have 13 tables designed for different purposes. Almost every one of these tables can contain "junk" data that slows down the loading of our pages. How does this happen? The point is that when "creating" any page, each element is loaded from the database. They are searched for by keys, for example, by id, and the more records we have in each of the tables, the more the program has to go through the data to find the data we need at the moment.
In short, if we have 1000 records and each has 5 comments, then after receiving a request to the database to display the page we need, we go through 1000 records and 5000 thousand comments in the search for exactly those that are required. The search takes place a little differently, but we will not go into details, this is not the article about this.
Each of the records can have multiple copies and autosaves, this will further aggravate the situation with the waiting time for a response and page loading from the server.
It turns out that by clearing our database from garbage, we will significantly reduce the amount of data that will be sorted out and, accordingly, it will take less time.
This is the main task, to clean WordPress and its databases from unnecessary information. Based on this statement, we will continue and analyze how to clear our database.
How do I clear the WordPress database?
There are two ways to clear the database of unnecessary rows:
- manual cleaning;
- cleaning with plugins.
For first cleaning method access to our database on the server will be required. You will also need to know what to delete and what not. You will also need to enter SQL queries for cleaning and understanding their main purpose. This method can be good for nerves of those users and site owners on WordPress who are embarrassed by any manual intervention in the site code, and even more so in the database. Therefore, we will consider both manual cleaning and cleaning using a plugin.
For second cleaning method we need an appropriate one that will help us in solving this problem. There are a huge number of such plugins and they differ mainly in authors, rating and appearance... In this article, we will look at using the plugin WP Clean Up,who can easily cope with the task. In addition, plugins of this type are not used all the time. This means that after using the plugin, you can deactivate it or delete it altogether, in other words, using the plugin will not slow down the download.
In order to clear our database in manual mode, first let's go to our phpMyAdminfrom the server where our site is located. Next, go to the SQL tab and enter the requests we need to delete records.
After we went to the required place, enter the required requests into the form:
- In order to clear copies (revisions) of our records, you need to enter the following line: DELETE FROM wp_posts WHERE post_type \u003d 'revision';which would mean "Delete all revisions in the post table".
- To clear comment spam, you need the following line: DELETE FROM wp_comments WHERE comment_approved \u003d 'spam';
- Clear cache from RSS blog: DELETE FROM wp_options WHERE option_name LIKE ('_transient% _feed_%');
After entering the required line, click on the "OK" button.
After that, we once again confirm our action.
If the query is successfully executed, our Mysql will tell us the result of the work done, and how many rows we managed to clear.
We repeat the same actions for other tables.
These are the main points you will need to know to manually clean up your database. Now let's look at cleaning with a plugin.
Cleaning the database from garbage using a plugin
As I mentioned earlier, we need to download and install the plugin. WP Clean Up.What are we going to do first and foremost. How to install the plugin can be read separately.
Ilya Zhuravlev is in touch,after reading this article you will learn how to clean up and optimize the database data wordpress... Over time, a lot of unnecessary garbage accumulates in the database, which can affect, for example, the speed of opening your site. The database is loaded not only by the installed plugins, but also when you delete the plugin, it may leave unnecessary code, tables, rows after it. Not many people know about post revision, when you write an article, wordpress automatically saves a draft of the entry in the database every time a post is changed. Imagine how many of these drafts are saved when writing one article.
Those who are not engaged in optimization, then their garbage in the database is 2, or even 3, 4, 5 times the size of the main content of the database. Imagine that your database weighs 90 megabytes, but the necessary and basic content of the database weighs only 30 megabytes, 60 megabytes is a load of 200, that is, dead weight, unnecessary garbage. Remove this load and your site will fly like a falcon!
Let's start cleaning up and optimizing the database.
First, let's clean up the database using a plugin - WP Clean Up, a very simple and straightforward plugin, does not need any settings, click on the button and that's it, the best of its kind. You can install this plugin directly from the WordPress admin panel. Go to the tab: Plugins - Add New, enter the name of the plugin in the search form, press Enter, install and activate the plugin that opens.
To configure the plugin, go to the tab: Settings - WP Clean Up.
On the plugin page, the first field will display the types of database tables that can be cleared. At the bottom, click on the - Delete All button to clear everything at once. Attention! If your site has the drafts you need, then when you clear all the elements at the same time, they will also be deleted. To save drafts, delete all elements individually, except for the Draft element, by clicking on the - Delete button opposite the value.
In the second field, you will have all the available tables in your database and their size displayed. After cleaning, you will need to optimize the databases, that is, update. Click on the button - Optimize. In the Total field, you can see how much your database has changed in size.
As you can see from the screenshot, in the second table, only 11 main database tables are displayed. You can have much more tables, from 50 to 100. In addition to 11 main tables, there are tables related to installed plugins and remote ones. Next, I will show you how to clear the database of unnecessary tables of remote plugins. After cleaning the database, you can deactivate the plugin - WP Clean Up. Periodically, once every 3-6 months, activate the plug-in and clean again.
