MySQL notes

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

© 2020 | Paul Kim