master in mysql

 

Install Mysql:

• sudo apt update
• sudo apt install mysql-server

Update Mysql Pass:

• mysql -u root -p -e "ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';"

Create Mysql User & Give It To Database Access:

CREATE database db_name; #Create Database

CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'user_password'; #Create User

GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'localhost'; #Single DB Access

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost'; #All DB Access

FLUSH PRIVILEGES; #Reload PRIVILEGES Table

EXIT;

DROP USER 'user_name'@'localhost'; #Delete User

MySQL Commands:

mysql -u root -p #Login

mysql -h server_ip -u user_name -p #Connect Mysql Remotely

show databases; #Show Databases

use db_name; #Enter Into Database

show tables; #Show Tables

select * from table_name; #Show Table Data

describe table_name; #Show Table Creted Format

DROP database db_name; #Delete Database

DROP table table_name; #Delete Table

CREATE database db_name; #Create Database

/etc/mysql/my.cnf #Mysql Conf File Path

/etc/mysql/mysql.cnf #Mysql Conf File Path

/etc/mysql/mysql.conf.d/mysqld.cnf #Mysql Conf File Path

mysqldump -u root -p --all-databases > backup.sql #Full Backup

mysqdump -u root -p db_name > db.sql #Mysql Dump

mysql -u root -p db_name < db.sql #Mysql Dump Restore

SOURCE database.sql; #Import DB Inside Mysql (Skips Erros)

sudo mysqlcheck -u admin -p --auto-repair --check --all-databases #Repair Databases

mysql -u root -p -e "show databases;" #Execute Any Query From Linux Cmd Line

1️⃣ MySQL Configuration Tweaks for High Performance

• Edit /etc/mysql/my.cnf:

[mysqld]

max_connections = 500

innodb_buffer_pool_size = 1G

query_cache_size = 128M

• sudo systemctl restart mysql

2️⃣ Use MySQLTuner - that helps optimize MySQL performance

👉 https://tech2towards.blogspot.com/2021/01/how-to-implement-mysql-tuner-in-linux.html

3️⃣ Enable Slow Query Logs - help identify queries that take too long to execute

👉 https://tech2towards.blogspot.com/2020/09/how-to-enable-slow-query-log-in-mysql.html