WordPress, como la gran mayoria de las aplicaciones web minimamente complejas, se basa en una base de datos. Salvo el «tema» y los archivos (generalmetne imágenes) subidos, TODO se encuentra ahí: post, paginas, links, configuración… Saber movernos por la base de datos es fundamental, y aqui vamos a contaros unas pocas sentencias SQL para usar desde el phpmyadmin que os serán de gran utilidad.
Lo primero de todo: BACKUP – BACKUP – BACKUP – BACKUP – BACKUP – BACKUP – BACKUP – BACKUP – BACKUP – BACKUP – BACKUP – BACKUP de la propia base de datos.
Hay diversos plugins que te facilitan este paso, pero si quieres hacerlo manual:
- Login en tu phpMyAdmin
- Escoge la base de datos donde esta tu wordpress
- Click en Export en lo alto de la navegación
- Seleciona todas las tablas
- Selecciona SQL para exportar como un archivo .sql
- Marca la casilla «Save as file»
- Click Go y una ventana de dialogo emergerá para preguntarte donde guarda el archivo.
Cambiar Siteurl & Homeurl
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';
Cambiar GUID
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com')
Cambiar URL en Contenidos
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');
Actualizar Post Meta
Updating Post Meta works almost the same way as updating the URL in post content. If you have stored extra URL data for each post, you can use the follow query to change all of them.
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://www.oldsiteurl.com','http://www.newsiteurl.com');
Cambiar el nombre por defecto de «Admin»
Every default WordPress installation will create an account with a default Admin username. This is wide spread knowledge, everyone who uses WordPress knows this. However, this can be a security issue because a hacker can brutal force your WordPress admin panel. If you can change your default “Admin” username, you will give your WordPress admin panel additional security.
UPDATE wp_users SET user_login = 'Your New Username' WHERE user_login = 'Admin';
Resetear Password
Ever wanted to reset your password in WordPress, but cannot seem to use the reset password section whatever the reason?
UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';
Asignar todos los articulos del autor A al autor B
If you want to transfer the articles under Author B to merge with those under Author A, it will be very time consuming if you do it article by article. With the following SQL query, you can easily go through all the records and assign articles by Author B to go under Author A.
You will first need to obtain the author ID of both authors by going to your Author & User page in your WordPress admin panel. Click on the author’s name to view their profile. At the address bar, look for «user_id». That is the author ID information we require.
UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';
Borrar revisiones
When you are editing an article in WordPress, there will be many revision copies being saved. This is a waste of resources because excessive revision records can increase the burden of the database. Over time, when you have thousands of entries, your database will have grown significantly. This will increase loop iterations, data retrieval and will affect the page loading time.
DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'
Source from: Lester chan
Note: Bear in mind that all revisions from each post will be deleted, including all of its meta data.
Borrar Post Meta
Installing or removing plugins is a very common task for WordPress. Some of the plugins make use of the post meta to store data pertaining to the plugin. After you have removed the plugin, those data are still left inside the post_meta table, which will no longer be needed. Run the following query to clean up the unused post meta value. This will help to speed up and reduce the size of your database.
DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';
Exportar todo los emails de los comentarios sin duplicados
Over a period of time, your blog will have received many comments. These comments will include the email addresses left by the commenter. You can retrieve all these emails for your mailing list without any duplicate.
SELECT DISTINCT comment_author_email FROM wp_comments;
Once you have the result, under Query results operations, select export to export all the emails in phpMyAdmin.
Borrar todos los Pingback
Popular articles receive plenty of pingback. When this happens, the size of your database increases. In order to reduce size of the database, you can try removing all the pingbacks.
DELETE FROM wp_comments WHERE comment_type = 'pingback';
Borrar todos los comentarios Spam
If you have plenty of spam comments, going through each page to delete spam can be tedious and frustrating. With the following SQL query, even if you have to face deleting 500 over spam comments, it will be a breeze.
DELETE FROM wp_comments WHERE comment_approved = 'spam';
0 = Comment Awaiting Moderation
1 = Approved Comment
spam = Comment marked as Spam
Identificar Tags sin usar
In a WordPress database, if you run a query to delete old posts manually from MySQL, the old tags will remain and appear in your tag cloud/listing. This query allows you to identify the unused tags.
SELECT * From wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;
Actualizacion
Desactivar todos los plugins de una sola vez
A veces pasa que por algun plugin con fallas, o por anguna incompatibilidad dejas de poder entrar alpanel de coltrol del sitio. En ese caso, hay 2 formas de atacar al problma. Si sabes exactametne cual es el plguin que está causando problemas, puedes ir a la carpeta por FTP y borrar el archivo/carpeta causante. Al entrar al panel arrojará un error (porque no se encuentra) pero podrás entrar. Pero si no sabes cual de todos es el que esta dando problemas, no vas a borrarlos todos… lo mejors es desactivarlos todos desde la base de datos y luego ya podras ir activadolos uno a uno.
Esta query sirve para ello: desctivar todos los plugins de una sola vez.
UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';
Fuente: http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/
Comentarios