MySQL Practice

MySQL Documentation

MySQL Reference Manual (5.6, 5.7)

Indexes (5.6, 5.7)

Example Databases

Employees Sample Database

https://github.com/datacharmer/test_db

shell< mysql < employees.sql

Sakila Sample Database

Chapter 2 Installing and Upgrading MySQL (5.6, 5.7)

2.5 Installing MySQL on Linux (5.6, 5.7)

2.5.1 Installing MySQL on Linux Using the MySQL Yum Repository (5.6, 5.7)

Download MySQL Yum Repository

A Quick Guide to Using the MySQL Yum Repository

install-mysql-yum-centos.sh

./install-mysql-yum-centos.sh centos6 mysql56

2.5.3 Installing MySQL on Linux Using the MySQL APT Repository (5.6, 5.7)

Download MySQL APT Repository

A Quick Guide to Using the MySQL APT Repository

## Ubuntu 14.04/16.04/17.04, Debian 7.11/8.9/9.1 ##
$ wget https://repo.mysql.com//mysql-apt-config_0.8.7-1_all.deb
$ sudo dpkg -i mysql-apt-config_0.8.7-1_all.deb

## Configuring mysql-apt-config ##
# Ubuntu 16.04/17.04 only has MySQL >=5.7

$ sudo apt-get update
$ sudo apt-get install -y mysql-server

## Configuring mysql-community-server ##

## Switch to another supported major release series at any time ##
$ sudo dpkg-reconfigure mysql-apt-config
$ sudo apt-get update

2.10 Postinstallation Setup and Testing (5.6, 5.7)

2.10.1 Initializing the Data Directory (5.6, 5.7)

MySQL 5.6

## CentOS 6.9/7.3 ##
mysql_install_db --user=mysql

mysql_install_db — Initialize MySQL Data Directory

MySQL 5.7

2.10.1.1 Initializing the Data Directory Manually Using mysqld

## CentOS 6.9/7.3 ##
$ mysqld --initialize-insecure --user=mysql

# Check whether the data directory is initialiazed.
$ ls -al /var/lib/mysql

2.10.1.2 Initializing the Data Directory Manually Using mysql_install_db

2.10.2 Starting the Server (5.6, 5.7)

Start the server

## CentOS 6.9, Ubuntu 14.04, Debian 7.11/8.9(MySQL 5.6)/9.1(MySQL 5.6) ##
mysqld_safe --user=mysql &

## CentOS 6.9, Ubuntu 14.04/16.04/17.04, Debian 7.11/8.9/9.1 ##
service mysql start

## CentOS 7.3 ##
systemctl start mysqld

## Ubuntu 16.04/17.04, Debian 8.9/9.1 ##
systemctl start mysql

Shut down the server

## CentOS 6.9, Ubuntu 14.04/16.04/17.04, Debian 7.11/8.9/9.1 ##
mysqladmin -u root shutdown

## CentOS 6.9, Ubuntu 14.04/16.04/17.04, Debian 7.11/8.9/9.1 ##
service mysql stop

## CentOS 7.3 ##
systemctl stop mysqld

## Ubuntu 16.04/17.04, Debian 8.9/9.1 ##
systemctl stop mysql

mysqld_safe — MySQL Server Startup Script (5.6, 5.7)

2.10.3 Testing the Server (5.6, 5.7)

Simple tests to check whether the server is up and responding to connections

## CentOS 6.9/7.3, Ubuntu 14.04/16.04/17.04, Debian 7.11/8.9/9.1 ##
$ mysqladmin version
$ mysqladmin variables

Verify that you can retrieve information from the server

## CentOS 6.9/7.3, Ubuntu 14.04/16.04/17.04, Debian 7.11/8.9/9.1 ##
$ mysqlshow
$ mysqlshow mysql
$ mysql -e "SELECT User, Host, plugin FROM mysql.user" mysql

2.10.4 Securing the Initial MySQL Accounts (5.6, 5.7)

## CentOS 6.9/7.3, Ubuntu 14.04/16.04/17.04, Debian 7.11/8.9/9.1 ##
$ mysql_secure_installation

mysql_secure_installation — Improve MySQL Installation Security (5.6, 5.7)

Chapter 3 Tutorial (5.6, 5.7)

3.1 Connecting to and Disconnecting from the Server (5.6, 5.7)

$ mysql -h host -u user -p

If you are logging in on the same machine that MySQL is running on, you can omit the host, and simply use the following:

$ mysql -u user -p

After you have connected successfully, you can disconnect any time by typing QUIT (or \q) at the mysql> prompt:

mysql> QUIT

3.4 Getting Information About Databases and Tables (5.6, 5.7)

You have previously seen SHOW DATABASES, which lists the databases managed by the server. To find out which database is currently selected, use the DATABASE() function:

mysql> SELECT DATABASE();

mysql> SHOW TABLES;

mysql> DESCRIBE pet;

3.5 Using mysql in Batch Mode (5.6, 5.7)

$ mysql -h host -u user -p < batch-file

mysql> source filename;
mysql> \. filename

Chapter 4 MySQL Programs (5.6)

4.2 Using MySQL Programs (5.6)

4.2.2 Connecting to the MySQL Server (5.6)

$ mysql --host=localhost --user=myname --password mydb
$ mysql -h localhost -u myname -p mydb

You can specify connection parameters in the [client] section of an option file. The relevant section of the file might look like this:

[client]
host=host_name
user=user_name
password=your_pass

4.2.6 Using Option Files (5.6)

To determine whether a program reads option files, invoke it with the --help option. (For mysqld, use --verbose and --help.) If the program reads option files, the help message indicates which files it looks for and which option groups it recognizes.

Option Files Read on Unix and Unix-Like Systems

4.2.7 Command-Line Options that Affect Option-File Handling (5.6)

Most MySQL programs that support option files handle the following options. Because these options affect option-file handling, they must be given on the command line and not in an option file. To work properly, each of these options must be given before other options.

• --defaults-extra-file=file_name

• --defaults-file=file_name

• --no-defaults

4.2.8 Using Options to Set Program Variables (5.6)

Many MySQL programs have internal variables that can be set at runtime using the SET statement.

Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options.

$ mysql --max_allowed_packet=16777216
$ mysql --max_allowed_packet=16M

4.3 MySQL Server and Server-Startup Programs (5.6)

4.3.1 mysqld — The MySQL Server (5.6)

mysqld, also known as MySQL Server, is the main program that does most of the work in a MySQL installation. MySQL Server manages access to the MySQL data directory that contains databases and tables. The data directory is also the default location for other information such as log files and status files.

The mysqld program has many options that can be specified at startup. For a complete list of options, run this command:

$ mysqld --verbose --help

4.3.2 mysqld_safe — MySQL Server Startup Script (5.6)

mysqld_safe reads all options from the [mysqld], [server], and [mysqld_safe] sections in option files.

mysqld_safe Options

4.4 MySQL Installation-Related Programs (5.6)

4.4.3 mysql_install_db — Initialize MySQL Data Directory (5.6)

As of MySQL 5.6.8, on Unix platforms, mysql_install_db creates a default option file named my.cnf in the base installation directory. This file is created from a template included in the distribution package named my-default.cnf. You can find the template in or under the base installation directory. When started using mysqld_safe, the server uses my.cnf file by default. If my.cnf already exists, mysql_install_db assumes it to be in use and writes a new file named my-new.cnf instead.

mysql_install_db Options

4.4.5 mysql_secure_installation — Improve MySQL Installation Security (5.6)

$ mysql_secure_installation

4.5 MySQL Client Programs (5.6)

4.5.1 mysql — The MySQL Command-Line Tool (5.6)

$ mysql --user=user_name --password db_name

$ mysql db_name < script.sql > output.tab

4.5.1.1 mysql Options (5.6)

mysql supports the following options, which can be specified on the command line or in the [mysql] and [client] groups of an option file.

4.5.1.2 mysql Commands (5.6)

mysql> help

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear command.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
exit      (\q) Exit mysql. Same as quit.
help      (\h) Display this help.
quit      (\q) Quit mysql.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
use       (\u) Use another database. Takes database name as argument.

For server side help, type 'help contents'

4.5.1.3 mysql Logging (5.6)

On Unix, the mysql client logs statements executed interactively to a history file. By default, this file is named .mysql_history in your home directory. To specify a different file, set the value of the MYSQL_HISTFILE environment variable.

4.5.1.4 mysql Server-Side Help (5.6)

mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the
following categories:
   Account Management
   Administration
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Language Structure
   Plugins
   Storage Engines
   Stored Routines
   Table Maintenance
   Transactions
   Triggers

4.5.1.5 Executing SQL Statements from a Text File (5.6)

$ mysql db_name < text_file

mysql> source file_name
mysql> \. file_name

4.5.2 mysqladmin — Client for Administering a MySQL Server (5.6)

$ mysqladmin [options] command [command-arg] [command [command-arg]] ...

4.5.4 mysqldump — A Database Backup Program (5.6)

To make a backup of an entire database:

$ mysqldump db_name > backup-file.sql

To load the dump file back into the server:

$ mysql db_name < backup-file.sql

Another way to reload the dump file:

$ mysql -e "source /path-to-backup/backup-file.sql" db_name

mysqldump is also very useful for populating databases by copying data from one MySQL server to another:

$ mysqldump --opt db_name | mysql --host=remote_host -C db_name

You can dump several databases with one command:

$ mysqldump --databases db_name1 [db_name2 ...] > my_databases.sql

To dump all databases, use the --all-databases option:

$ mysqldump --all-databases > all_databases.sql

4.5.6 mysqlshow — Display Database, Table, and Column Information (5.6)

The mysqlshow client can be used to quickly see which databases exist, their tables, or a table's columns or indexes.

$ mysqlshow [options] [db_name [tbl_name [col_name]]]

4.6 MySQL Administrative and Utility Programs (5.6)

4.6.8 mysqlbinlog — Utility for Processing Binary Log Files (5.6)

The server's binary log consists of files containing “events” that describe modifications to database contents. The server writes these files in binary format. To display their contents in text format, use the mysqlbinlog utility. You can also use mysqlbinlog to display the contents of relay log files written by a slave server in a replication setup because relay logs have the same format as binary logs.

$ mysqlbinlog [options] log_file ...

4.6.9 mysqldumpslow — Summarize Slow Query Log Files (5.6)

The MySQL slow query log contains information about queries that take a long time to execute. mysqldumpslow parses MySQL slow query log files and prints a summary of their contents.

$ mysqldumpslow [options] [log_file ...]

4.9 MySQL Program Environment Variables (5.6)

Chapter 5 MySQL Server Administration (5.6, 5.7)

5.1 The MySQL Server (5.6, 5.7)

5.1.1 Configuring the Server (5.6, 5.7)

To determine the command option and system variable values used by the server, execute this command:

$ mysqld --verbose --help

To see the current system variable values used by the server as it runs, connect to it and execute this statement:

mysql> SHOW VARIABLES;

To see some statistical and status indicators for a running server, execute this statement:

mysql> SHOW STATUS;

System variable and status information also is available using the mysqladmin command:

$ mysqladmin variables
$ mysqladmin extended-status

5.1.2 Server Configuration Defaults (5.6, 5.7)

5.1.3 Server Option and Variable Reference (5.6, 5.7)

5.1.4 Server Command Options (5.6, 5.7)

$ mysqld --verbose --help

5.1.5 Server System Variables (5.6, 5.7)

5.1.6 Using System Variables (5.6, 5.7)

SET Syntax for Variable Assignment (5.6, 5.7)

SHOW VARIABLES Syntax (5.6, 5.7)

To display system variable names and values, use the SHOW VARIABLES statement:

mysql> SHOW VARIABLES;

With a LIKE clause, the statement displays only those variables that match the pattern. To obtain a specific variable name, use a LIKE clause as shown:

mysql> SHOW VARIABLES LIKE 'max_join_size';
mysql> SHOW SESSION VARIABLES LIKE 'max_join_size';

To get a list of variables whose name match a pattern, use the % wildcard character in a LIKE clause:

mysql> SHOW VARIABLES LIKE '%size%';
mysql> SHOW GLOBAL VARIABLES LIKE '%size%';

5.1.6.2 Dynamic System Variables (5.6, 5.7)

5.1.7 Server Status Variables (5.6, 5.7)

mysql> SHOW GLOBAL STATUS;

Many status variables are reset to 0 by the FLUSH STATUS statement.

5.2 The MySQL Data Directory (5.6, 5.7)

5.3 The mysql System Database (5.6, 5.7)

5.4 MySQL Server Logs (5.6, 5.7)

5.4.1 Selecting General Query and Slow Query Log Output Destinations (5.6, 5.7)

5.4.2 The Error Log (5.6, 5.7)

5.4.3 The General Query Log (5.6, 5.7)

5.4.4 The Binary Log (5.6, 5.7)

log-bin max_binlog_size binlog-do-db binlog-ignore-db replicate-do-db replicate-ignore-db sync_binlog

mysqlbinlog(5.6, 5.7) — Utility for Processing Binary Log Files

5.4.4.1 Binary Logging Formats (5.6, 5.7)

--binlog-format={ROW|STATEMENT|MIXED}

5.4.4.2 Setting The Binary Log Format (5.6, 5.7)

The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

An individual client can control the logging format for its own statements by setting the session value of binlog_format:

mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';

5.4.5 The Slow Query Log (5.6, 5.7)

long_query_time slow_query_log slow_query_log_file log-output

mysqldumpslow(5.6, 5.7) — Summarize Slow Query Log Files

5.4.7 Server Log Maintenance (5.6, 5.7)

Chapter 6 Security

6.2 The MySQL Access Privilege System (5.6, 5.7)

6.2.1 Privileges Provided by MySQL (5.6, 5.7)

6.2.3 Specifying Account Names (5.6, 5.7)

6.2.6 When Privilege Changes Take Effect (5.6, 5.7)

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

6.3 MySQL User Account Management (5.6, 5.7)

6.3.1 User Names and Passwords (5.6, 5.7)

MySQL stores accounts in the user table of the mysql system database. An account is defined in terms of a user name and the client host or hosts from which the user can connect to the server.

To connect to a MySQL server with a command-line client, specify user name and password options as necessary for the account that you want to use:

$ mysql --user=finley --password db_name

If you prefer short options, the command looks like this:

$ mysql -u finley -p db_name

If you omit the password value following the --password or -p option on the command line (as just shown), the client prompts for one. Alternatively, the password can be specified on the command line:

$ mysql --user=finley --password=password db_name
$ mysql -u finley -ppassword db_name

If you use the -p option, there must be no space between -p and the following password value.

6.3.2 Adding User Accounts (5.6, 5.7)

The following example uses CREATE USER and GRANT statements to set up four accounts:

mysql> CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'finley'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
    ->     WITH GRANT OPTION;
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';
mysql> GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql> CREATE USER 'dummy'@'localhost';

To see the privileges for an account, use SHOW GRANTS:

mysql> SHOW GRANTS FOR 'admin'@'localhost';

To see nonprivilege properties for an account, use SHOW CREATE USER:

## MySQL 5.7 ##
mysql> SHOW CREATE USER 'admin'@'localhost'\G

The next examples create three accounts and grant them access to specific databases. Each of them has a user name of custom and password of obscure:

mysql> CREATE USER 'custom'@'localhost' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON bankaccount.*
    ->     TO 'custom'@'localhost';
mysql> CREATE USER 'custom'@'host47.example.com' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON expenses.*
    ->     TO 'custom'@'host47.example.com';
mysql> CREATE USER 'custom'@'%.example.com' IDENTIFIED BY 'obscure';
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON customer.*
    ->     TO 'custom'@'%.example.com';

6.3.3 Removing User Accounts (5.6, 5.7)

To remove an account, use the DROP USER statement. For example:

mysql> DROP USER 'jeffrey'@'localhost';

6.3.4 Setting Account Resource Limits (5.6, 5.7)

To establish resource limits for an account, use the GRANT statement. Provide a WITH clause that names each resource to be limited. The default value for each limit is zero (no limit). For example, to create a new account that can access the customer database, but only in a limited fashion, issue these statements:

mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank';
mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
    ->     WITH MAX_QUERIES_PER_HOUR 20
    ->          MAX_UPDATES_PER_HOUR 10
    ->          MAX_CONNECTIONS_PER_HOUR 5
    ->          MAX_USER_CONNECTIONS 2;

To modify limits for an existing account, use a GRANT USAGE statement at the global level (ON *.*). The following statement changes the query limit for francis to 100:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
    ->     WITH MAX_QUERIES_PER_HOUR 100;

The statement modifies only the limit value specified and leaves the account otherwise unchanged.

To remove a limit, set its value to zero. For example, to remove the limit on how many times per hour francis can connect, use this statement:

mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
    ->     WITH MAX_CONNECTIONS_PER_HOUR 0;

6.3.5 Assigning Account Passwords (5.6, 5.7)

To assign a password when you create a new account, use CREATE USER and include an IDENTIFIED BY clause:

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

To assign or change a password for an existing account, use one of the following methods:

Use SET PASSWORD with the PASSWORD() function:

## 5.6 ##
mysql> SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');

# If you are not connected as an anonymous user, you can change your own password by omitting the FOR clause:
mysql> SET PASSWORD = PASSWORD('mypass');

Use the ALTER USER statement with an IDENTIFIED BY clause:

## 5.7 ##
mysql> ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

# If you are not connected as an anonymous user, you can change your own password without naming your own account literally:
mysql> ALTER USER USER() IDENTIFIED BY 'mypass';

Use a GRANT USAGE statement at the global level (ON *.*) to change an account password without affecting the account's current privileges:

## 5.6 ##
mysql> GRANT USAGE ON *.* TO 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';

To change an account password from the command line, use the mysqladmin command:

$ mysqladmin -u user_name -h host_name password "new_password"

6.3.6 Password Expiration and Sandbox Mode (5.6, 5.7)

To expire an account password, use the ALTER USER statement. For example:

mysql> ALTER USER 'myuser'@'localhost' PASSWORD EXPIRE;

Chapter 11 Data Types (5.6, 5.7)

Chapter 17 Replication (5.6)

Quick Start

• Master

# my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1

# Session 1
CREATE USER 'repl'@'%' IDENTIFIED BY '666666';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

# Session 1
FLUSH TABLES WITH READ LOCK;

# Session 2
SHOW MASTER STATUS;

mysqldump --all-databases --master-data > dbdump.db

# Session 1
UNLOCK TABLES;

• Slave

mysql < dbdump.db

CHANGE MASTER TO \
MASTER_HOST='128.199.177.76', \
MASTER_USER='repl', \
MASTER_PASSWORD='666666', \
MASTER_LOG_FILE='mysql-bin.000001', \
MASTER_LOG_POS=411;

START SLAVE;

SHOW SLAVE STATUS \G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

17.1 Replication Configuration (5.6)

17.1.1 How to Set Up Replication (5.6)

17.1.1.1 Setting the Replication Master Configuration (5.6)

# Master
[mysqld]
log-bin=mysql-bin
server-id=1

After making the changes, restart the server.

17.1.1.2 Setting the Replication Slave Configuration (5.6)

# Slave
[mysqld]
server-id=2

After making the changes, restart the server.

17.1.1.3 Creating a User for Replication (5.6)

# Master
mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

17.1.1.4 Obtaining the Replication Master Binary Log Coordinates (5.6)

To obtain the master binary log coordinates, follow these steps:

Start a session on the master by connecting to it with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

# Master
mysql> FLUSH TABLES WITH READ LOCK;

Warning
Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.

In a different session on the master, use the SHOW MASTER STATUS statement to determine the current binary log file name and position:

# Master
mysql> SHOW MASTER STATUS;

17.1.1.5 Creating a Data Snapshot Using mysqldump (5.6)

# Master
mysqldump --all-databases --master-data > dbdump.db

# Master
mysql> UNLOCK TABLES;

17.1.1.8 Setting Up Replication with Existing Data (5.6)

# Slave
mysql < fulldb.dump

# Slave
mysql> START SLAVE;

17.1.1.10 Setting the Master Configuration on the Slave (5.6)

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;