🔥🍺 MySQL cheat sheet

mysql-cheat-sheet.md

📘 LAMP 📕 MySQL 📘 Terminal 📘 htaccess

---

FIND + REPLACE

UPDATE `table_name` SET `mycolumn` = REPLACE(`mycolumn`,'target','replacement');

UPDATE USER PASSWORD MANUALLY

> my
> use mysql
UPDATE mysql.user SET Password=PASSWORD(..) WHERE USER='username' AND Host='localhost';

UPLOAD LARGE SQL FILES

> my
> use db_name;
> source /var/www/mydatabaseexport.sql

CREATE USERS IN MYSQL

CREATE USER 'davidsword'@'localhost' IDENTIFIED BY '**********';
GRANT ALL PRIVILEGES ON databasename.* TO 'davidsword'@'localhost';
FLUSH PRIVILEGES;

WORDPRESS UNABLE TO INSERT, AUTO_INCREMENT ERROR

# if IDs dont matter 
ALTER TABLE `problem_table` DROP PRIMARY KEY;
ALTER TABLE `problem_table` DROP COLUMN `id`;
ALTER TABLE `problem_table` ADD  `id` INT( 11 ) NOT NULL  PRIMARY KEY AUTO_INCREMENT FIRST;


# if IDs matter
ALTER TABLE `problem_table` DROP PRIMARY KEY;
DELETE FROM problem_table WHERE id = 0;
ALTER TABLE problem_table MODIFY id int NOT NULL PRIMARY KEY AUTO_INCREMENT;

DROP

drop table if exists tableName;