MariaDB Socket from Path XAMPP for VSCODE on MAC OS

Run the following command in terminal:

ps -ax|grep mysql

FInd the output like this:

--socket=/Applications/XAMPP/xamppfiles/var/mysql/mysql.sock --port=3306

Then use this path without quote in Socket path

"Applications/XAMPP/xamppfiles/var/mysql/mysql.sock"

Then click Save and connect. It will show the success message.

Join Multiple table with subquery

SELECT DISTINCT

    e.card_id,

    e.photo_link,

    c.full_name,

    c.mobile_no,

    c.email,

    c.image_path,

    s.institute_name,

    p.profession_name,

    p.institute_name AS company, 

    i.village_road_present,

    i.post_office_present,

    i.thana_upazila_present,

    i.district_present,

    i.blood_group

FROM EVENT

    e

JOIN(

    SELECT DISTINCT

        id,

        full_name,

        mobile_no,

        email,

        image_path

    FROM

        cualumni

    WHERE

        softdeleted = 'No'

) AS c

ON

    c.id = e.user_id

JOIN(

    SELECT DISTINCT

        user_id,

        institute_name

    FROM

        education

    WHERE

        user_id IS NOT NULL

) AS s

ON

    s.user_id = e.user_id

JOIN(

    SELECT DISTINCT

        user_id,

        profession_name,

        institute_name

    FROM

        profession

    WHERE

        user_id IS NOT NULL

) AS P

ON

    P.user_id = e.user_id

JOIN(

    SELECT DISTINCT

        *

    FROM

        personal_info

    WHERE

        user_id IS NOT NULL

) AS i

ON

    i.user_id = e.user_id

Update columns values with column of another table based on condition in SQL command

UPDATE table1 SET price=(SELECT price FROM table2 WHERE table1.id=table2.id);

or,
UPDATE table1 SET table1.price=(SELECT table2.price FROM table2 WHERE table2.id=table1.id AND table2.item=table1.item);

or,
UPDATE event INNER JOIN cualumni ON event.user_id = cualumni.eid SET event.user_id = cualumni.id;


Or,

UPDATE table1 INNER JOIN table2 ON table1.id = table2.id SET table1.Price = table2.price

Reinstall MySQL in virtualmin

sudo apt purge mysql-server mysql-client mysql-common
sudo apt autoremove

sudo mv -iv /var/lib/mysql /var/tmp/mysql-backup

sudo rm -rf /var/lib/mysql*

Then goto webmin dashboard>Server>MySQL Database Server>Install

Find duplicates in value using SQL command

 SELECT

    username,

    email,

    COUNT(*)

FROM

    users

GROUP BY

    username,

    email

HAVING

    COUNT(*) > 1

or,

SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

Find All Rows Containing Duplicates using SQL CMD

SELECT

    a.*

FROM

    users a

JOIN(

    SELECT

        username,

        email,

        COUNT(*)

    FROM

        users

    GROUP BY

        username,

        email

    HAVING

        COUNT(*) > 1

) b

ON

    a.username = b.username AND a.email = b.email

ORDER BY

    a.email

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

Reset MySQL Root Password on Red Hat Enterprise Linux 8

sudo systemctl stop mysqld

sudo systemctl set-environment MYSQLD_OPTS=”--skip-grant-tables”

sudo systemctl start mysqld

sudo mysql -u root

mysql> FLUSH PRIVILEGES;

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewRootPassw0rd!'; 

mysql> FLUSH PRIVILEGES;

mysql> QUIT;

sudo systemctl stop mysqld

sudo systemctl unset-environment MYSQLD_OPTS

sudo systemctl start mysqld

sudo mysql -u root -p

mysql> QUIT;

sudo mysql -u root -p

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'ChangeRootPassw0rd!';

mysql> ALTER USER 'shola'@'localhost' IDENTIFIED BY 'ChangeSholaPassw0rd!';

mysql> FLUSH PRIVILEGES;

mysql> QUIT;

Now login again with new root password !!

Decrease product price by 10% using SQL command in Woocommernce from Phpmyadmin

 Query-1:

UPDATE

   wp_wc_product_meta_lookup

SET

    min_price =(min_price - min_price * .13)

WHERE 1;

Query-2;

 UPDATE

   wp_wc_product_meta_lookup

SET

    max_price =(max_price - max_price * .13)

WHERE 1;

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

Create mysql user with all privileges

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

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

How to install Fully Featured Mail Server using Postal on Ubuntu 16.04

Prerquisites:

Ruby 2.3 or higher

MySQL or MariaDB database server

Erlang

RabbitMQ server

Node.js (for javascript compilation)

Git (for accessing the repository)

Nginx

Installing dependencies:

Install Ruby

sudo apt install software-properties-common

sudo apt-add-repository ppa:brightbox/ruby-ng

sudo apt update

sudo apt install ruby2.3 ruby2.3-dev build-essential

Install MySQL

sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8

sudo add-apt-repository 'deb [arch=amd64,i386,ppc64el] http://mirrors.coreix.net/mariadb/repo/10.1/ubuntu xenial main'

sudo apt update

sudo apt install mariadb-server libmysqlclient-dev

Some additional configuration may be required to optimise your MySQL server. This is outside the scope of this documentation.

Install Erlang

wget https://packages.erlang-solutions.com/erlang-solutions_1.0_all.deb 

sudo dpkg -i erlang-solutions_1.0_all.deb

sudo apt-get update sudo apt-get install erlang

sudo apt-get update sudo apt-get install esl-erlang

If there is any issue while installing Erlang then use the following command:

sudo apt-get purge elixir
sudo apt-get purge erlang

sudo apt-get install esl-erlang

Install RabbitMQ

curl -s https://packagecloud.io/install/repositories/rabbitmq/rabbitmq-server/script.deb.sh | sudo bash

sudo apt update

sudo apt-get install apt-transport-https

sudo apt install rabbitmq-server

Install Node.js

sudo apt install nodejs

Install Git

sudo apt install git

There is documentation for installing the core prerequisites if needed.

Preparing the database

Postal has a single main MySQL database that contains all configuration and each mail server you create will have its own database that stores its messages and other mail-server specific content. No configuration is stored in the mail server databases.

mysql -u root -p -h 127.0.0.1

Begin by creating your main database. Run the commands below to create a new database. Replace XXX with a suitable password.

CREATE DATABASE `postal` CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

GRANT ALL ON `postal`.* TO `postal`@`127.0.0.1` IDENTIFIED BY "XXX";

Postal will handle the creation of databases for your mail servers but you need to give it access to do this. Run the following to add a grant that permits a user to manage all databases that are prefixed with postal-.

GRANT ALL PRIVILEGES ON `postal-%` . * to `postal`@`127.0.0.1`  IDENTIFIED BY "XXX";

Setting up RabbitMQ

Postal requires its own RabbitMQ vhost and user to connect with. You can create these using the following commands:

sudo rabbitmqctl add_vhost /postal

sudo rabbitmqctl add_user postal XXX

sudo rabbitmqctl set_permissions -p /postal postal ".*" ".*" ".*"

Preparing your operating system

Postal should be executed as its own user on your server. Run the following commands to create a new user with a home directory in /opt/postal.

sudo useradd -r -m -d /opt/postal -s /bin/bash postal

Postal needs to be able to listen on privileged ports (for example 80, 443 and 25). To enable this, we will allow ruby to listen on these ports. On a shared server this would be a security risk however on a server dedicated to Postal, this shouldn't cause any issues.

sudo setcap 'cap_net_bind_service=+ep' /usr/bin/ruby2.3

Installing system-wide gems

To run Postal, you'll need to have bundler and procodile installed on your system. Install them both:

sudo gem install bundler

sudo gem install procodile

Cloning the repository

Now, we're ready to get the repository cloned onto your server. Run the clone command as your postal user and clone into the /opt/postal/app directory.

sudo -i -u postal git clone https://github.com/atech/postal /opt/postal/app

The master branch (which is cloned by default) will contain the most stable version of the software. If you wish to work with a less stable branch, use the devel branch.

Postal provides a single binary that allows you to interact with it from any directory on your server. To make this available everywhere, simply symlink it into the /usr/bin/postal directory.

sudo ln -s /opt/postal/app/bin/postal /usr/bin/postal

Install Ruby dependencies

This will install all the required dependencies required to run the application.

postal bundle /opt/postal/app/vendor/bundle

Setting up configuration files

Before you can do anything, you'll need to generate the default configuration. By default, configuration is kept outside of the repository file structure and is stored in /opt/postal/config. Generate the initial configuration with the following command:

postal initialize-config

This will create a number of files for your configuration, including various private keys & certificates as well as a default postal.yml configuration file.

Configuring

Open up your new postal.yml configuration file from /opt/postal/config. Follow the comments in the example file and update items as appropriate based on your installation.

Initialize database & assets

postal initialize

Create your initial admin user

postal make-user

This will guide you through setting up a new admin user for your installation. You'll be prompted for an e-mail address, name & password. You can then use these when you first login.

Starting the application

To start the application, just run the following command. This will run postal in the foreground. This is only useful for testing and development. You'll run it in the background when you're sure everything is working.

postal run

If everything looks OK, you can just CTRL+C this and continue. If there are any errors, you should investigate these before continuing.

Now, you can run postal in the background on your server:

postal start

You can look at the status at any time using postal status.

Configuring Nginx

The Postal management interface and API should sit behind nginx. Install it and configure it as appropriate.

sudo apt install nginx

Once installed, you can change the default site configuration to match something suitable. The repository contains an example config file that you can copy into place.

sudo cp /opt/postal/app/resource/nginx.cfg /etc/nginx/sites-available/default

You can create a self signed SSL certificate if you need to (otherwise put your own

key & cert in /etc/nginx/ssl/postal.crt and postal.key)

sudo mkdir /etc/nginx/ssl/

sudo openssl req -x509 -newkey rsa:4096 -keyout /etc/nginx/ssl/postal.key -out /etc/nginx/ssl/postal.crt -days 365 -nodes

Make the appropriate changes for your domain and SSL certificate and then restart

sudo nano -w /etc/nginx/sites-available/default

sudo /etc/init.d/nginx restart

This might be a good time to set up Let's Encrypt rather than using a self-signed certificate. CertBot is a good way to go about setting this up.

Accessing the web interface

You can now navigate to your domain and you should see the Postal web interface asking for a login. You can login using the credentials you created earlier.

You can start by creating your own organization & mail server and exploring the system.

By default, the system is not configured to use any IP pools that are configured. Messages will be sent from the main IP on the server. Configuring multiple IP pools will be covered in other documentation.

Configuring Postal SMTP

The Postal system itself needs to be able to send e-mails. This is something that needs to be configured in your postal.yml configuration file. You can send these messages using Postal itself. Just create a new mail server and use its credentials in your config file. Postal sends emails when servers are approaching limits, new users are invited, password resets and more.

Once you have added the configuration for this, you can test the configuration using the command below. Replace the e-mail address with one of your own.

postal test-app-smtp youraddress@domain.com

Complete Installation with Git Repo:

https://gist.githubusercontent.com/amisalabir/5fd99cd156b2eba220ae826ef8348f89/raw/44bd3f5314e116c9134456aecae3258f15f10479/ubuntu1604.sh

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

How to concatenate self column in mysql

By using the following sql command we can edit a column with the value of same column: UPDATE tblclients SET phonenumber = concat('880',RIGHT(phonenumber,10));