After installing MySQL, log in to it via terminal with $sudo mysql -u root Once inside MySQL, we will need to change the default plugin authentication to mysql_native_password and set a password for root. mysql> ALTER USER [email protected] IDENTIFIED WITH mysql_native_password BY ‘password’; mysql> exit You can now login in MySQL as root using the […]
Let say that you wanted to create a MySQL user for example johnpili and assign privileges. You can do that using the simple code snippet below: mysql> CREATE USER ‘johnpili’@’localhost’ IDENTIFIED BY ‘Pass2Word3r’; mysql> GRANT ALL PRIVILEGES ON *.* TO ‘johnpili’@’localhost’; mysql> FLUSH PRIVILEGES; […]
Perhaps you may need a ledger type query that can display a running total like the one on a bank statement. SELECT a.*, (@running_balance:[email protected]_balance+(a.credit-a.debit)) AS ‘balance’ FROM ledger a JOIN (SELECT @running_balance := 0) b WHERE a.id_user = ? AND a.account_type = ? AND a.year = ? […]
Suppose you want to retrieve a list of products that are not in promotion. SELECT * FROM product t1 LEFT JOIN promo_product t2 ON t1.idProduct = t2.product_idProduct WHERE t2.idPromoProduct IS NULL […]
One of the easiest way to upgrade MySQL installation is by first backing up the existing database and removing all traces of MySQL from the system. sudo apt remove –purge mysql* sudo apt autoremove sudo apt autoclean sudo apt remove dbconfig-mysql […]
In MySQL, If you have an existing table and you wanted to have unique index across multiple columns. This is very useful in implementing unique constraints. Please checkout the code snippet below. ALTER TABLE `prefix_counter` ADD UNIQUE `unique_prefix_counter`(`prefix`, `year`); […]
Building a public website with user registration and email verification is typical. It will require users to validate their email by clicking a unique generated URL link. Sometimes, user registration with pending verification will grow exponentially and needs an automated cleanup of records. In this example, I created a simple MySQL stored procedure to delete […]
