Showing posts with label Database. Show all posts
Showing posts with label Database. Show all posts

Friday, October 21, 2022

How to install MySQL on Ubuntu 22.04

 Installing MySQL:

sudo apt update

sudo apt install mysql-server

sudo systemctl start mysql.service


Configuring MySQL:

sudo mysql_secure_installation



Creating a Dedicated MySQL User and Granting Privileges:

  sudo mysql

 mysql -u root -p

 CREATE USER 'mailapp'@'localhost' IDENTIFIED WITH authentication_plugin BY 'password';

 CREATE USER 'mailapp'@'localhost' IDENTIFIED BY 'password';

 CREATE USER 'mailapp'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

 ALTER USER 'mailapp'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

 GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT on *.* TO 'mailapp'@'localhost' WITH GRANT OPTION;

 GRANT ALL PRIVILEGES ON *.* TO 'mailapp'@'localhost' WITH GRANT OPTION;

 FLUSH PRIVILEGES;

 exit

 

 mysql -u mailapp -p

Friday, March 11, 2022

How to Delete All Products in WooCommerce using SQL Command?

  1. Log in to the phpMyAdmin portal
  2. Select the database that has all the products to be deleted. Make sure, that you backup your database before executing the SQL statement!
  3. Than select table {prefix}_posts and run the SQL statement as shown.


DELETE relations.*, taxes.*, terms.*

FROM metro4u_term_relationships AS relations

INNER JOIN metro4u_term_taxonomy AS taxes

ON relations.term_taxonomy_id=taxes.term_taxonomy_id

INNER JOIN metro4u_terms AS terms

ON taxes.term_id=terms.term_id

WHERE object_id IN (SELECT ID FROM metro4u_posts WHERE post_type IN ('product','product_variation'));


DELETE FROM metro4u_postmeta WHERE post_id IN (SELECT ID FROM metro4u_posts WHERE post_type IN ('product','product_variation'));

DELETE FROM metro4u_posts WHERE post_type IN ('product','product_variation');


Note: "metro4u" is a prefix. Set your Database Table prefix

Sunday, September 19, 2021

Create mysql user with all privileges

 CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';

Sunday, December 8, 2019

How to import large sql file using windows command line.

c:\xampp\mysql\bin>mysql -u root -p -h localhost 
Enter password: 
show databases;
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 28 Server version: 10.1.21-MariaDB mariadb.org binary distribution Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use database; source c:/xampp/mysql/bin/file_name.sql

Upload file to server using Power Shell Command

 scp file_name.zip username@server_ip/server_address:/directory_path