MySQL Notes

MySQL_Refman-5.7 Guide

1.Install:
1.1 >apt-cache search libaio
>apt-get install liabio1

1.2 install layout:
Directory Contents of Directory
bin mysqld server, client and utility programs
docs - MySQL manual in Info format
man - Unix manual pages
include Include (header) files
lib Libraries
share Error messages, dictionary, and SQL for database
installation
support-files Miscellaneous support files

1.3
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> mkdir mysql-files
shell> chown mysql:mysql mysql-files
shell> chmod 750 mysql-files
shell> bin/mysqld --initialize --user=mysql
shell> bin/mysql_ssl_rsa_setup
shell> bin/mysqld_safe --user=mysql &

Next command is optional

shell> cp support-files/mysql.server /etc/init.d/mysql.server

1.4
shell> groupadd mysql
shell> useradd -r -g mysql -s /bin/false mysql

1.5
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf -
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> export PATH=$PATH:/usr/local/mysql/bin

2. Postinstallation Setup and Testing
   2.1 Initializing the Data Directory

cd /usr/local/mysql
mkdir mysql-files # bydefault it’s secure_file_priv
chown mysql:mysql mysql-files
chmcod 750 mysql-files
bin/mysqld --initialize --user=mysql
bin/mysql_ssl_rsa_setup #If you want to deploy the server with automatic support for secure connections, use the
mysql_ssl_rsa_setup utility to create default SSL and RSA files.

2.2 Data Directory Initialization Procedure

cd /usr/local/mysql
bin/mysqld --initialize --user=mysql
bin/mysqld --initialize-insecure --user=mysql

2.3 Server Actions During Data Directory Initialization

With --initialize but not --initialize-insecure, the server generates a random

password, marks it as expired, and writes a message displaying the password:
[Warning] A temporary

2.4 Post-Initialization root Password Assignment

mysql -u root -p # connect to the server

2.5 Starting the server

bin/mysqld_safe --user=mysql &
systemctl start mysqld

2.6 Testing the server

bin/mysqladmin version
bin/mysqladmin variables
bin/mysqladmin -u root shutdown # shutdown server
bin/mysqld_safe --user=mysql & # start server
bin/mysqlshow # show what databases exist
bin/mysqlshow ** # show tables in the ** DB.
bin/mysql -e “SELECT User, Host, plugin FROM mysql.user” DBname

2.7 Securing the Initial MySQL account

If you do not know the initial random password, look in the server error log.

2.7.1

mysql -u root -p
ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘root-password’;
2.7.2
mysql -u root --skip-password
mysql> ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘root-password’;

3. Upgrade MySQL
   3.1 In-Place Upgrade
   #If you use XA transactions with InnoDB, run XA RECOVER before upgrading to check for uncommitted XA transactions. If results are returned, either commit or rollback the XA transactions
   by issuing an XA COMMIT or XA ROLLBACK statement.

mysql -u root -p --execute=“SET GLOBAL innodb_fast_shutdown=0”
#With a slow shutdown, InnoDB performs a full purge and change buffer merge before shutting
down, which ensures that data files are fully prepared in case of file format differences between
releases.
mysqladmin -u root -p shutdown # shutdown old MySQL Server
#Upgrade the MySQL binary installation or packages
mysqld_safe --user=mysql --datadir=/path/to/existing-datadir & #Start the MySQL 5.7 server, using the existing data directory
mysql_upgrade -u root -p # Run mysql_gpupgrade
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/existing-datadir & #Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect.

3.2 Logical Upgrade
#Review info in Section 2.11.1 ‘Before you begin’

mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > data-for-upgrade.sql #Export your existing data from the previous MySQL installation:
mysqladmin -u root -p shutdown #shutdown old MySQL server
mysqld --initialize --datadir=/path/to/5.7-datadir # Initialize a new data directory # copy the temp root pasword from your screen / error log
mysqld_safe --user=mysql --datadir=/path/to/5.7-datadir & # Start tje servre using the new data directory
mysql -u root -p --force < data-for-upgrade.sql # load the previously created dump fileinto the new MySQL server.
mysql_upgrade -u root -p # run mysql_upgrade
mysqladmin -u root -p shutdown
mysqld_safe --user=mysql --datadir=/path/to/5.7-datadir & #Shut down and restart the MySQL server to ensure that any changes made to the system tables take effect.

4. Copy MySQL databases to another machine

4.1

mysqladmin -h ‘other_hostname’ create db_name
mysqldump db_name | mysql -h ‘other_hostname’ db_name #run the above two commands on the machine on which the database is located
4.2
If you want to copy a database from a remote machine over a slow network, you can use these commands:
mysqladmin create db_name
mysqldump -h ‘other_hostname’ --compress db_name | mysql db_name
4.3
You can also store the dump in a file, transfer the file to the target machine, and then load the file into the database there. For example, you can dump a database to a compressed file on the source machine like this:
mysqldump --quick db_name | gzip > db_name.gz
Transfer the file containing the database contents to the target machine and run these commands there:
mysqladmin create db_name
gunzip < db_name.gz | mysql db_name
4.4
You can also use mysqldump and mysqlimport to transfer the database. For large tables, this is much faster than simply using mysqldump. In the following commands, DUMPDIR represents the full path name of the directory you use to store the output from mysqldump.
First, create the directory for the output files and dump the database:
mkdir DUMPDIR
mysqldump --tab=DUMPDIR db_name
Then transfer the files in the DUMPDIR directory to some corresponding directory on the target machine and load the files into MySQL there:
mysqladmin create db_name # create database
cat DUMPDIR/.sql | mysql db_name # create tables in database
mysqlimport db_name DUMPDIR/.txt # load data into tables
Note: Do not forget to copy the mysql database because that is where the grant tables are stored. You might have to run commands as the MySQL root user on the new machine until you have the mysql database in place.
After you import the mysql database on the new machine, execute mysqladmin flushprivileges so that the server reloads the grant table information.

5. Entering Queries
   mysql> SELECT VERSION(), CURRENT_DATA;
   mysql> SELECT USER() \c # \C
   mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
   species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
   mysql> mysql> LOAD DATA LOCAL INFILE ‘/path/pet.txt’ INTO TABLE pet; # load / insert data into table
   mysql> SELECT what_to_select
   FROM which_table
   WHERE conditions_to_satisfy; # Retrieving info from a table
   mysql> SELECT * FROM pet; # the simplest from to retrieve everything from a table
   mysql> mysql> UPDATE pet SET birth = ‘1989-08-31’ WHERE name = ‘Bowser’;

   update one cell info

mysql> SELECT * FROM pet WHERE species = ‘dog’ AND sex = ‘f’;
# Selecting Particular Rows
mysql> SELECT name, birth FROM pet;
# selecting particulat columns
mysql> SELECT name, birth FROM pet ORDER BY birth;
# Sort rows
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age FROM pet;
mysql> SELECT name, birth, death,
TIMESTAMPDIFF(YEAR,birth,death) AS age
FROM pet WHERE death IS NOT NULL ORDER BY age;
# Date Calculations
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
# The NULL value can be surprising until you get used to it. Conceptually, NULL means “a missing
unknown value” and it is treated somewhat differently from other values. To test for NULL, use the IS NULL and IS NOT NULL operators
mysql> SELECT * FROM pet WHERE name LIKE ‘b%’;
mysql> SELECT * FROM pet WHERE name REGEXP ‘fy${}^{\prime };mysql>SELECT\ast FROMpetWHEREnameREGEX{P}^{\prime .}....$’;
# Pattern Matching
mysql> SELECT COUNT() FROM pet;
mysql> SELECT owner, COUNT() FROM pet GROUP BY owner;
SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
# Counting Rows
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1 INNER JOIN pet AS p2
ON p1.species = p2.species
AND p1.sex = ‘f’ AND p1.death IS NULL
AND p2.sex = ‘m’ AND p2.death IS NULL;
# Using more than one table

shell> mysql < batch-file
# using Batch mode
mysql> source filename;
mysql> . filename
# You can also use scripts from the mysql prompt by using the source command or . command.
mysql> SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
# find the #, dealer, and price of the most expensive ones
mysql> SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
ORDER BY article;
# Find the highest price per article.

SELECT article, dealer, priceFROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article)
ORDER BY article;
# (Correlated Subqueries)The Rows Holding the Group-wise Maximum of a Certain Column

Uncorrelated subquery is as following two subquery below:

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price
ORDER BY article;

LEFT JOIN:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL
ORDER BY s1.article;

mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;
# You can employ MySQL user variables to remember results without having to store them in temporary variables in the client. (See Section 9.4, “User-Defined Variables”.)

6.1 Using Foreign Keys

CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM(‘t-shirt’, ‘polo’, ‘dress’) NOT NULL,
color ENUM(‘red’, ‘blue’, ‘orange’, ‘white’, ‘black’) NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, ‘Antonio Paz’);
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, ‘polo’, ‘blue’, @last),
(NULL, ‘dress’, ‘white’, @last),
(NULL, ‘t-shirt’, ‘blue’, @last);
INSERT INTO person VALUES (NULL, ‘Lilliana Angelovska’);
SELECT @last := LAST_INSERT_ID();
INSERT INTO shirt VALUES
(NULL, ‘dress’, ‘orange’, @last),
(NULL, ‘polo’, ‘red’, @last),
(NULL, ‘dress’, ‘blue’, @last),
(NULL, ‘t-shirt’, ‘white’, @last);
SELECT s.* FROM person p INNER JOIN shirt s
ON s.owner = p.id
WHERE p.name LIKE ‘Lilliana%’ AND s.color <> ‘white’;

6.2 Calculating visits per day

CREATE TABLE t1 (year YEAR, month INT UNSIGNED,
day INT UNSIGNED);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);
SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;

6.3 Using Auto-increment

CREATE TABLE animals (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
(‘dog’),(‘cat’),(‘penguin’),
(‘lax’),(‘whale’),(‘ostrich’);
SELECT * FROM animals;

7. MySQL Programes
   7.1
   • mysqld
   The SQL daemon (that is, the MySQL server). To use client programs, mysqld must be running,
   because clients gain access to
   • mysqld_safe
   A server startup script. mysqld_safe attempts to start mysqld. See Section 4.3.2, “mysqld_safe
   — MySQL Server Startup Script”.
   • mysql.server
   A server startup script. This script is used on systems that use System V-style run directories containing scripts that start system services for particular run levels. It invokes mysqld_safe to start
   the MySQL server. See Section 4.3.3, “mysql.server — MySQL Server Startup Script”.
   • mysqld_multi
   A server startup script that can start or stop multiple servers installed on the system. See
   Section 4.3.4, “mysqld_multi — Manage Multiple MySQL Servers”.
   • comp_err
   This program is used during the MySQL build/installation process. It compiles error message files from the error source files. See Section 4.4.1, “comp_err — Compile MySQL Error Message File”.
   • mysql_install_db
   This program initializes the MySQL data directory, creates the mysql database and initializes its grant tables with default privileges, and sets up the InnoDB system tablespace. It is usually executed only once, when first installing MySQL on a system. See Section 4.4.2, “mysql_install_db — Initialize MySQL Data Directory”, and Section 2.10, “Postinstallation Setup and Testing”.
   • mysql_plugin
   This program configures MySQL server plugins. See Section 4.4.3, “mysql_plugin — Configure MySQL Server Plugins”.
   • mysql_secure_installation
   This program enables you to improve the security of your MySQL installation. See Section 4.4.4, “mysql_secure_installation — Improve MySQL Installation Security”.
   • mysql_ssl_rsa_setup
   This program creates the SSL certificate and key files and RSA key-pair files required to support
   secure connections, if those files are missing. Files created by mysql_ssl_rsa_setup can be used for secure connections using SSL or RSA. See Section 4.4.5, “mysql_ssl_rsa_setup — Create SSL/RSA Files”.
   • mysql_tzinfo_to_sql
   This program loads the time zone tables in the mysql database using the contents of the host system zoneinfo database (the set of files describing time zones). See Section 4.4.6, “mysql_tzinfo_to_sql — Load the Time Zone Tables”.
   • mysql_upgrade
   This program is used after a MySQL upgrade operation. It updates the grant tables with any changes that have been made in newer versions of MySQL, and checks tables for incompatibilities and repairs them if necessary. See Section 4.4.7, “mysql_upgrade — Check and Upgrade MySQL Tables”.

7.2
MySQL client programs that connect to the MySQL server:
• mysql
The command-line tool for interactively entering SQL statements or executing them from a file in
batch mode. See Section 4.5.1, “mysql — The MySQL Command-Line Client”.
• mysqladmin
A client that performs administrative operations, such as creating or dropping databases, reloading
the grant tables, flushing tables to disk, and reopening log files. mysqladmin can also be used to
retrieve version, process, and status information from the server. See Section 4.5.2, “mysqladmin
— A MySQL Server Administration Program”.
• mysqlcheck
A table-maintenance client that checks, repairs, analyzes, and optimizes tables. See Section 4.5.3,
“mysqlcheck — A Table Maintenance Program”.
• mysqldump
A client that dumps a MySQL database into a file as SQL, text, or XML. See Section 4.5.4,
“mysqldump — A Database Backup Program”.
• mysqlimport
A client that imports text files into their respective tables using LOAD DATA. See Section 4.5.5,
“mysqlimport — A Data Import Program”.
• mysqlpump
A client that dumps a MySQL database into a file as SQL. See Section 4.5.6, “mysqlpump — A
Database Backup Program”.
• mysqlsh
MySQL Shell is an advanced client and code editor for MySQL Server. See MySQL Shell 8.0 (part of
MySQL 8.0). In addition to the provided SQL functionality, similar to mysql, MySQL Shell provides
scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. X DevAPI
enables you to work with both relational and document data, see Chapter 19, Using MySQL as a
Document Store. AdminAPI enables you to work with InnoDB Cluster, see Using MySQL AdminAPI.
• mysqlshow
A client that displays information about databases, tables, columns, and indexes. See Section 4.5.7,
“mysqlshow — Display Database, Table, and Column Information”.
• mysqlslap
A client that is designed to emulate client load for a MySQL server and report the timing of each
stage. It works as if multiple clients are accessing the server. See Section 4.5.8, “mysqlslap — A
Load Emulation Client”.
MySQL administrative and utility programs:
• innochecksum
An offline InnoDB offline file checksum utility. See Section 4.6.1, “innochecksum — Offline InnoDB
File Checksum Utility”.
• myisam_ftdump
A utility that displays information about full-text indexes in MyISAM tables. See Section 4.6.2,
“myisam_ftdump — Display Full-Text Index information”.
• myisamchk
A utility to describe, check, optimize, and repair MyISAM tables. See Section 4.6.3, “myisamchk —
MyISAM Table-Maintenance Utility”.
• myisamlog
A utility that processes the contents of a MyISAM log file. See Section 4.6.4, “myisamlog — Display
MyISAM Log File Contents”.
• myisampack
A utility that compresses MyISAM tables to produce smaller read-only tables. See Section 4.6.5,
“myisampack — Generate Compressed, Read-Only MyISAM Tables”.
• mysql_config_editor
A utility that enables you to store authentication credentials in a secure, encrypted login path file
named .mylogin.cnf. See Section 4.6.6, “mysql_config_editor — MySQL Configuration
Utility”.
• mysqlbinlog
A utility for reading statements from a binary log. The log of executed statements contained in the
binary log files can be used to help recover from a crash. See Section 4.6.7, “mysqlbinlog —
Utility for Processing Binary Log Files”.
• mysqldumpslow
A utility to read and summarize the contents of a slow query log. See Section 4.6.8,
“mysqldumpslow — Summarize Slow Query Log Files”.

7.3

7.4
MySQL program-development utilities:
• mysql_config
A shell script that produces the option values needed when compiling MySQL programs. See
Section 4.7.1, “mysql_config — Display Options for Compiling Clients”.
• my_print_defaults
A utility that shows which options are present in option groups of option files. See Section 4.7.2,
“my_print_defaults — Display Options from Option Files”.
• resolve_stack_dump
A utility program that resolves a numeric stack trace dump to symbols. See Section 4.7.3,
“resolve_stack_dump — Resolve Numeric Stack Trace Dump to Symbols”.
Miscellaneous utilities:
• lz4_decompress
A utility that decompresses mysqlpump output that was created using LZ4 compression. See
Section 4.8.1, “lz4_decompress — Decompress mysqlpump LZ4-Compressed Output”.
• perror
A utility that displays the meaning of system or MySQL error codes. See Section 4.8.2, “perror —
Display MySQL Error Message Information”.
• replace
A utility program that performs string replacement in the input text. See Section 4.8.3, “replace — A
String-Replacement Utility”.
• resolveip
A utility program that resolves a host name to an IP address or vice versa. See Section 4.8.4,
“resolveip — Resolve Host name to IP Address or Vice Versa”.
• zlib_decompress
A utility that decompresses mysqlpump output that was created using ZLIB compression. See
Section 4.8.5, “zlib_decompress — Decompress mysqlpump ZLIB-Compressed Output”.

7.5 Using MySQL Programs
4.2.3 Command Options for Connecting to the Server
4.2.4 Connecting to the MySQL Server Using Command Options
4.2.5 Connection Transport Protocols
4.2.6 Connection Compression Control
4.2.7 Setting Environment Variables

mysqld --verbose --help

You can add options for mysql.server in a global /etc/my.cnf file. A typical my.cnf file might
look like this:
[mysqld]
datadir=/usr/local/mysql/var
socket=/var/tmp/mysql.sock
port=3306
user=mysql
[mysql.server]
basedir=/usr/local/mysql
The mysql.server script supports the options shown in the following table. If specified, they must be
placed in an option file, not on the command line. mysql.server supports only start and stop as
command-line arguments

mysqld_multi [options] {start|stop|reload|report} [GNR[,GNR] …]

MySQL Server Administration
8.1
Configuring the Server
shell> mysqld --verbose --help
# The command produces a list of all mysqld options and configurable system variables. Its output includes the default option and variable values and looks something like this:
abort-slave-event-count 0
allow-suspicious-udfs FALSE
archive ON
auto-increment-increment 1
auto-increment-offset 1
autocommit TRUE
automatic-sp-privileges TRUE
avoid-temporal-upgrade FALSE
back-log 80

basedir /home/jon/bin/mysql-5.7/
…
tmpdir /tmp
transaction-alloc-block-size 8192
transaction-isolation REPEATABLE-READ
transaction-prealloc-size 4096
transaction-read-only FALSE
transaction-write-set-extraction OFF
updatable-views-with-limit YES
validate-user-plugins TRUE
verbose TRUE
wait-timeout 28800

mysql> SHOW VARIABLES; #see the current system variable values actually used by the server as it runs, connect to it and
execute this statement
mysql> SHOW STATUS; #To see some statistical and status indicators for a running server, execute this statement
shell> mysqladmin variables
shell> mysqladmin extended-status
#System variable and status information also is available using the mysqladmin command

8.2
The mysql System Database
The mysql database is the system database. It contains tables that store information required by the
MySQL server as it runs.
Tables in the mysql database fall into these categories:
• Grant System Tables
• Object Information System Tables
• Log System Tables
• Server-Side Help System Tables
• Time Zone System Tables
• Replication System Tables
• Optimizer System Tables
• Miscellaneous System Tables
8.3
MySQL Server has several logs that can help you find out what activity is taking place.
Log Type Information Written to Log
Error log Problems encountered starting, running, or stopping mysqld
General query log Established client connections and statements received from clients
Binary log Statements that change data (also used for replication)
Relay log Data changes received from a replication source server
Slow query log Queries that took more than long_query_time seconds to execute
DDL log (metadata log) Metadata operations performed by DDL statements
P916