Important mysql queries

I written article for my personal purpose. I am using some mysql query on daily. I created the list of my Important mysql queries.

Important mysql queries

Taking backup of all mysql databases

>mysqldump -uroot -p --all-databases >> all_database_dump.sql;

for single database

mysqldump -uroot -p public_wordpressmu wp_1_posts > one.sql

database import from sql file

mysql –verbose -uroot -p public_wordpressmu wp_1_posts < one.sql

Creating Trigger

create trigger
DROP TRIGGER wordpress.after_wp_1_post_update;

delimiter //

CREATE TRIGGER wordpressmu.after_wp_1_post_update

AFTER UPDATE ON wordpressmu.wp_1_posts

FOR EACH ROW

IF new.post_status = ‘publish’ THEN

INSERT INTO .article_posts (id,ref_id, title, status,
comments_on, created_at, updated_at) values (new.id,new.id,
new.post_title, new.post_status, 1, new.post_date, new.post_date);

END IF;

//

delimiter ;

Insert from different table (migration)

INSERT INTO database_anme.TableName(id, ref_id, title, status, comments_on, created_at, updated_at) select id, id, post_title, post_status, 1, post_date,post_date from wordpress.wp_1_posts

Update multiple rows in through Sql query

Update user set status=”ok” where id in (2 ,3 ,4 ,5,6)

changing all post path and image path
UPDATE wp_1_posts SET post_content = REPLACE(post_content, ‘test.siwan.com’, ‘www.siwan.com’);

WordPress -fetching category through sql queryfrom post

SELECT wp_1_term_taxonomy.taxonomy, wp_1_term_relationships.object_id, wp_1_terms.name, wp_1_posts.post_title
FROM wp_1_posts INNER JOIN ((wp_1_terms INNER JOIN wp_1_term_taxonomy ON wp_1_terms.term_id = wp_1_term_taxonomy.term_id) INNER JOIN wp_1_term_relationships ON wp_1_term_taxonomy.term_taxonomy_id = wp_1_term_relationships.term_taxonomy_id) ON wp_1_posts.ID = wp_1_term_relationships.object_id
WHERE wp_1_term_taxonomy.taxonomy=’category’ and wp_1_term_relationships.object_id=’217′
ORDER BY wp_1_term_relationships.object_id ;

CREATE DATABASE DB-NAME;
CREATE USER ‘DB-NAME’@’localhost’ IDENTIFIED BY ‘DB-NAME@123’;
grant all on USER-NAME.* to ‘DB-NAME’@’%’ identified by ‘DB-NAME@123’;
grant all on USER-NAME.* to ‘DB-NAME’@’localhost’ identified by ‘DB-NAME@123’;
FLUSH PRIVILEGES;

Add a Comment

Your email address will not be published. Required fields are marked *