Notes

MySQL notes

author: Paul Kim

categories: mysql

tags: mysql

Install MySQL

  • Ubuntu

    # add MySQL repository and install latest version of MySQL
cd ~/Downloads
curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.13-1_all.deb
sudo dpkg -i mysql-apt-config*
sudo apt update
rm mysql-apt-config*
sudo apt install mysql-server mysql-workbench
sudo mysql_secure_installation

# switch to another major release version
sudo dpkg-reconfigure mysql-apt-config
sudo apt update

  • Mac

    brew info mysql
brew install mysql
mysql_secure_installation
brew cask info mysqlworkbench
brew cask install mysqlworkbench

  • Windows

    See Install MySQL on Windows

Uninstall MySQL

  • Ubuntu

    # stop mysql
sudo service mysql stop

# remove mysql
sudo apt purge mysql-*
sudo apt autoremove
sudo apt autoclean

  • Mac

    mysql.server stop
brew cask uninstall mysqlworkbench
brew uninstall mysql
rm -rf /usr/local/var/mysql
rm -rf /usr/local/etc/my.cnf

Install sakila database for MySQL

# download and extract sakila database
cd ~/Downloads
curl -OL https://downloads.mysql.com/docs/sakila-db.tar.gz
tar xf sakila-db.tar.gz

# login mysql
mysql -u root -p

# import sakila database
SOURCE ~/Downloads/sakila-db/sakila-schema.sql;
SOURCE ~/Downloads/sakila-db/sakila-data.sql;
USE sakila;
SHOW TABLES;

See Sakila Sample Database and Sakila Installation

Start/Stop/Restart MySQL

  • Ubuntu

    # start mysql
sudo systemctl start mysql.service
sudo service mysql start

# restart mysql
sudo systemctl restart mysql.service
sudo service mysql restart

# stop mysql
sudo systemctl stop mysql.service
sudo service mysql stop

# mysql status
sudo systemctl status mysql.service
sudo service mysql status

  • Mac

    # start mysql
mysql.server start

# restart mysql
mysql.server restart

# stop mysql
mysql.server stop

# mysql status
mysql.server status

# have launchd start mysql as a background service
brew services start mysql

# stop mysql background service
brew services stop mysql

Connect to MySQL

Connect locally:

mysql -u testuser -p
testpass

Connect remotely on port 3306:

mysql -h 123.123.123.123 -P 3306 -u testuser -p
testpass

Adding Accounts, Assigning Privileges, and Dropping Accounts

Adding Accounts, Assigning Privileges, and Dropping Accounts

Creating Accounts and Granting Privileges

CREATE USER Syntax and GRANT Syntax - create account and assign privileges to account

Grant privileges at the global level:

# used only when connecting from localhost
CREATE USER 'finley'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'finley'@'localhost' WITH GRANT OPTION;

# used to connect from any host
CREATE USER 'finley'@'%' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'finley'@'%' WITH GRANT OPTION;

# used to connect from any host in the example.com domain
CREATE USER 'finley'@'%.example.com' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'finley'@'%.example.com' WITH GRANT OPTION;

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
# grant the global RELOAD and PROCESS admin privileges
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';

Grant privileges at a lower level, to specific databases or objects within databases:

CREATE USER 'custom'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON bankaccount.* TO 'custom'@'localhost';

CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON expenses.* TO 'custom'@'host47.example.com';

CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'password';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customer.addresses TO 'custom'@'%.example.com';

Checking Account Privileges and Properties

SHOW GRANTS Syntax – show the privileges for an account

# display the privileges granted to a specific user
SHOW GRANTS FOR 'root'@'localhost';

# display the privileges granted to the current user
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

SHOW CREATE USER Syntax – show nonprivilege properties for an account

SET print_identified_with_as_hex = ON;
# show the CREATE USER statement that creates the named user
SHOW CREATE USER 'root'@'localhost'\G

Revoking Account Privileges

REVOKE Syntax - revoke privileges from account

Revoke global privileges:

REVOKE ALL ON *.* FROM 'finley'@'%.example.com';

REVOKE RELOAD ON *.* FROM 'admin'@'localhost';

Revoke database-level privileges:

REVOKE CREATE,DROP ON expenses.* FROM 'custom'@'host47.example.com';

Revoke table-level privileges:

REVOKE INSERT,UPDATE,DELETE ON customer.addresses FROM 'custom'@'%.example.com';

Dropping Accounts

DROP USER Syntax - remove account

DROP USER 'finley'@'localhost';
DROP USER 'finley'@'%';
DROP USER 'finley'@'%.example.com';
DROP USER 'admin'@'localhost';
DROP USER 'custom'@'localhost';
DROP USER 'custom'@'host47.example.com';
DROP USER 'custom'@'%.example.com';

Password Management

ALTER USER Syntax

-- change the current user's password:
ALTER USER USER() IDENTIFIED BY 'auth_string';
ALTER USER USER() IDENTIFIED BY 'auth_string' REPLACE 'current_auth_string';

-- change a named user's password:
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'auth_string';
ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'auth_string' REPLACE 'current_auth_string';

Note: ALTER USER Syntax is the preferred statement for account alterations, including assigning passwords

SET PASSWORD Syntax

-- sets the password for the current user
SET PASSWORD = 'auth_string';

-- sets the password for 'jeffrey'@'localhost'
SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';

Important: Under some circumstances, SET PASSWORD Syntax may be recorded in server logs onr history files, which means that cleartext passwords may be read by anyone having read access to that information.

MySQL commands

SHOW VARIABLES Syntax

# get mysql version
SHOW VARIABLES LIKE "version";

# get max_allowed_packet value
SHOW VARIABLES LIKE "max_allowed_packet";

SHOW DATABASES Syntax

# show databases
SHOW DATABASES;

USE Syntax

# access mysql database called 'my_database'
USE my_database;

CREATE DATABASE Syntax

# create a database called dbname
CREATE DATABASE dbname;

SHOW TABLES Syntax

# show all tables in 'my_database'
SHOW TABLES;

EXPLAIN Syntax

The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

EXPLAIN SELECT * FROM table_name WHERE conditions \G

list mysql users

DESCRIBE mysql.user;
SELECT user from mysql.user;

get mysql version

mysqladmin -u root -p version

# get mysql version
SHOW VARIABLES LIKE "version";

check if column has duplicate values

SELECT my_column, COUNT(1) as count
FROM my_table
GROUP BY my_column
HAVING COUNT(1) > 1
Source

ChromeEdgeFirefoxOpera

© 2021 paulkode.com. All rights reserved.