Introduction

This article described the installation and service control of MySQL.

Table Of Contents

Before Install

Choose Operating System

  • stable
  • reliable
  • easy to manage
  • plenty of resources available online
  • allow runing MySQL without too much hassle

Recommendations

  • SUSE Linux Enterprise Server
    • Live Patching
  • Red Hat Enterprise Linux

Choose Package

OS specific packages

  • packages have been rigorously tested with other componenets of the given OS

    Ubuntu has MySQL packaging team

  • simplicity of maintenance

    • auto resolv dependencies.
    • often not up to date
    • 3rd party specialized repositories provide updated versions
    • but these repos may not included by default

Pre-bulit Binaries

  • careful with incompatible core dependencies like glibc and libaio

    which may silent corrupt your data

  • flexibility of install and update

    manually installed pre-built binaries is less likely to be replaced/overwritten during an update by simply keeping package folders on unique directories

Custom Built Binaries

  • need to alter the default behavior of MySQL

    • disabling and totally disallowing use of query cache
    • increasing maximum total number of indexes per table from 64
    • take adavntages of new hardware, kernel or core libs

  • require engineering effort for continuous integration (may be a lot)

    • Google, Facebook, Twitter

Install

MySQL Server

Make data directory

mkdir /var/mysql
chown -R mysql:mysql /var/mysql

Create Configure File

vim /etc/my.cnf

package manager

pre-built binary

tar zxf mysql-5.7.12.tar.gz
vim my.cnf
# run as current user
./bin/mysqld --defaults-file=my.cnf --initialize
# alternative: run as mysql
sudo -u mysql ./bin/mysqld --defaults-file=my.cnf --initialize

source code

Plugins

$ mysql_plugin -P
mysql_plugin would have been started with the following arguments:
--datadir=/var/lib/mysql

MySQL Client

Configure

back_log < (system back_log)

my-{huge,large,medium,small}.cnf

Default Configure

[will@rhel7.2.vmg]$ my_print_defaults mysqld
--datadir=/var/lib/mysql
--socket=/var/lib/mysql/mysql.sock
--symbolic-links=0

[will@ubuntu-14.04.4.vmg]$ my_print_defaults mysqld
--user=mysql
--pid-file=/var/run/mysqld/mysqld.pid
--socket=/var/run/mysqld/mysqld.sock
--port=3306
--basedir=/usr
--datadir=/var/lib/mysql
--tmpdir=/tmp
--lc-messages-dir=/usr/share/mysql
--skip-external-locking
--lower_case_table_names=1
--bind-address=127.0.0.1
--key_buffer=16M
--max_allowed_packet=16M
--thread_stack=192K
--thread_cache_size=8
--myisam-recover=BACKUP
--query_cache_limit=1M
--query_cache_size=16M
--log_error=/var/log/mysql/error.log
--expire_logs_days=10
--max_binlog_size=100M

Example Configure

!include /path/to/other.conf

[client]
port=3306
socket=/tmp/mysql.sock
# store password is not recommended, at lease make this file not readable by other users
password='passphrase'

[mysql]
# used for invoke of command: 'mysql'

[mysql-5.7]
# specific version
sql_mode=TRADITIONAL

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=8M

character-set-server=utf8                                                       
collation-server=utf8_general_ci

performance_schema
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000

[mysqldump]
quick

[mysqladmin]
force

Production Configure

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[client]
port = 3306
user = root
# DO NOT USE IN PRODUCTION ENV
password = root

[mysqld]

# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
port = 3306
server-id = 1677
character-set-server=utf8
collation-server=utf8_general_ci
# socket = .....
bind-address = 127.0.0.1
user = _mysql

# Layout
basedir = /usr/local/mysql
datadir = /var/mysql

# Options
skip-symbolic-links
skip-external-locking

# Security
skip-name-resolve

# Resource Limit
open_files_limit = 1024

# Threading
thread_cache_size = 256
innodb_read_io_threads = 4
innodb_write_io_threads = 4
#innodb_purge_batch_size = 300
#innodb_purge_threads = 4
# Determines the number of threads that can enter InnoDB concurrently
#innodb_concurrency_tickets = 5000
#innodb_thread_concurrency = 8
# Higher values cause more frequent flushing, avoiding the backlog of work that can cause dips in throughput
# keep this option value as low as practical
# http://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html
innodb_io_capacity = 512

# Memory
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
bulk_insert_buffer_size = 32M
key_buffer_size = 256M
innodb_change_buffering = ALL
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 32M
# table open cache = max_connections * tables
table_open_cache = 1024
tmp_table_size = 32M
max_heap_table_size = 512M
innodb_max_dirty_pages_pct = 80
# Prefetch
innodb_random_read_ahead = ON
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Networking
back_log = 128
net_buffer_length = 16K
max_allowed_packet = 32M
max_connections = 1024
wait_timeout = 28800

# Performance Schema
performance_schema = ON

# Misc
explicit_defaults_for_timestamp = ON
skip_external_locking = ON

# Logging
general_log = ON
general_log_file = /var/mysql/log/general.log
slow_query_log = ON
long_query_time = 1.00
# Binlog_cache_disk_use | Binlog_cache_use
binlog_cache_size = 2M
expire_logs_days = 7
# A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 2
# Small redo log files cause many unnecessary disk writes
innodb_log_file_size = 4G
# log-bin = mysql-bin
log-bin
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Query Cache
query_cache_limit = 1M
query_cache_min_res_unit = 4096
query_cache_size = 256M

# Table
innodb_file_per_table = ON

# SQL
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

Environment Variables

MYSQL_TCP_PORT=3306
EXPORT MYSQL_TCP_PORT

Post Install

mysql_install_db

First Run

sudo -u mysql /usr/local/mysql/bin/mysqld_safe &

Update Password

ALTER USER 'root'@'localhost' IDENTIFIED BY 'passphrase';

Invoke mysql_secure_installation to:

  1. set root password
  2. remove test databases
  3. restrict access

Setup Users

-- show current user
SELECT USER();
-- update password
UPDATE mysql.user SET password = PASSWORD('passphrase') WHERE user = 'root';
FLUSH PRIVILEGES;

# set root password
mysqladmin -u root -password passphrase`

Grant Accesses

Grant Minimal Access Only

help grant

Syntax grant on <access> testdb.* to developer@'192.168.0.%';

access:

  • create
  • alter
  • drop
  • references
  • execute
  • create temporary tables
  • index
  • create view
  • show view
  • create routine (can show procedure status)
  • alter routine (can drop a procedure)
with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count

Running

  • mysqld — The MySQL Server
  • mysqld_safe — MySQL Server Startup Script
  • mysql.server — MySQL Server Startup Script
  • mysqld_multi — Manage Multiple MySQL Servers

Startup

sudo mysqld_safe --user=mysql

Shutdown

/usr/local/mysql/bin/mysqladmin shutdown -u root -p

Partitioning

Partitioning Types

  • RANGE
  • LIST
  • COLUMNS
  • HASH
  • KEY
  • subpartitioning

Restrictions & Limitations

  • Query cache not supported
  • Foreign keys not supported for partitioned InnoDB tables
  • Partitioned tables do not support FULLTEXT indexes or searches
  • Temporary tables cannot be partitioned
  • A partitioning key must be either an integer or an expression that resolves to an integer

Performance Considerations

  • Filesystem operations
  • MyISAM and partition file descriptor usage
    • MySQL use 2 file descriptor for each partition
  • Table locks
  • Storage engine
    • generally tend to be faster with MyISAM tables than with InnoDB or NDB tables
  • Indexes; partition pruning
  • Performance with LOAD DATA

Partitioning

Backup and Recovery

InnoDB Backup and Recovery

Backup

Type Tool
hot backup MySQL Enterprise Backup
cold backup copying files when sql server is down
physical backup fast operation (esp. restore)
logical backup mysqldump

Notes

mysqldump utility is usually used under two circumstances:

  • smaller data volumes
  • record schema obj structure
Cold Backup
  1. slow shutdown
  2. copy ibdata files and .ibd files
  3. copy .frm files
  4. copy ib_logfile files
  5. copy my.cnf configuration files

InnoDB Recovery Process

  1. apply redo log
  2. rolling back incomplete transactions
  3. chagne buffer merge
  4. perge

mysqlimport

mysqlimport is a command-line interface to the LOAD DATA INFILE statement, for which you need the ‘FILE’ privilege (server level).

Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege.

You can avoid the need for the extra privileges by using the –local parameter to mysqlimport:

--local, -L
           Read input files locally from the client host.

mysqlimport --default-character-set=utf8 \
	--local --replace \
	--fields-terminated-by=' ' --lines-terminated-by='\n' \
	users user.txt

High Availability

See [High Availability] for more details.

MySQL Replication

Performance

  • Multi-Threaded Slaves
  • Binary Log Group Commit
  • Optimized Row-Based Replication

Failover & Recovery

  • Global Transaction Identifers
  • Replication Failover & Admin Utilities
  • Crash Safe Slaves & Binlogs

Data Integrity

  • Replication Event Checksums

Dev/Ops Agility

  • Replication Utilities
  • Time-Delayed Replication
    • To protect against user mistakes on the master (DBA rollback to the time just before the disaster)
    • To test how the system behaves when there is a lag
    • To inspect what the database looked like long ago, without having to reload a backup. ( 1 week ago)
  • Remote Binlog Backup
  • Informational Log Events
  • Server UUIDs

Migration

Inspect Table Size

SELECT table_name,
  data_length/1024/1024 AS 'data_length(MB)',
  index_length/1024/1024 AS 'index_length(MB)',
  (data_length + index_length)/1024/1024 AS 'total(MB)'
FROM information_schema.tables
WHERE table_schema='test' AND table_name = 't1';

Tools

  • logical migrate
    • mysqldump
    • mysqlpump (5.7)
    • mysqldumper
  • MySQL Utilities
    • mysqldbexport
    • mysqldbimport
  • SELECT * INTO OUTFILE * FROM table
  • xtrabackup

It is recommond to use logical migration when the data size is small, otherwise phsical migration would performs better.

mysqlpump

  • Parallel processing of databases, and of objects within databases, to speed up the dump process
  • Dumping of user accounts as account-management statements (CREATE USER, GRANT) rather than as inserts into the mysql system database
  • Capability of creating compressed output
  • Progress indicator (the values are estimates)
  • For dump file reloading, faster secondary index creation for InnoDB tables by adding indexes after rows are inserted

Copy *.ibd Files

  • Large amount of data
  • Temporary interrupt of service is acceptable

Prerequisite

innodb_file_per_table = 1 && engine = InnoDB

Create new table the same as old one:

CREATE DATABASE db2;
USE db2;
CREATE TABLE tbl LIKE db1.tbl;

Discard tablespace:

ALTER TABLE tbl DISCARD TABLESPACe;

Lock for export:

FLUSH TABLES tbl FOR EXPORT;

Copy *.ibd and *.cfg files

Release lock:

UNLOCK TABLES;

Import data:

ALTER TABLE tbl2 IMPORT TABLESPACE;

MySQL Replication

Master/Slave Replication

Either

MySQL Enterprise Backup (without taking down server)

or

Cold Backup.

MySQL Replication is based on binlog.

Transactions that fails on the master do not affect replication at all.

Note

Replication and CASCADE foreign key cautious

Master/Master Replication

my.cnf

[mysqld]
server-id = 1 # 2 for backup
log-bin = mysql-bin
auto-increment-increment = 2
auto-increment-offset = 1
slave-skip-errors = all

show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| rmbp-bin.000001 |      154 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

setup master

GRANT  REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.%' IDENTIFIED  BY 'replication';
flush  privileges;
change  master to
    master_host='192.168.0.17{2,4}',
    master_user='replication',
    master_password='replication',
    master_log_file='rmbp-bin.000001',
    master_log_pos=106;
start  slave;
Verify
check if Slave_IO_Running/Slave_SQL_Running is both YES
show slave status;
Keepalived
setup hot standby backup

Upgrade

In-place Upgrade

  1. shut down the old MySQL version
  2. replace the old MySQL binaries with the new ones
  3. restart MySQL on the existing data directory
  4. run mysql_upgrade

Logical Upgrade

  1. export existing data from the old MySQL version using mysqldump

    mysqldump -u root -p database > database.sql
  2. install the new MySQL version

  3. loading the dump file into the new MySQL version

    mysql -u root -p database < database.sql

  4. run mysql_upgrade

    mysql_upgrade

Downgrade

References

[High Availability]: