Shulou(Shulou.com)06/01 Report--

1. MYSQL server-side program hierarchy

1.1 connection layer

Connection protocol: tcpip socket

Verify the validity of the connection

Assign a connection thread to serve the client

1.2 SQL layer

Analysis

Optimize

Authorization form

Query

Provide caching

1.3 Storage engine layer

Plug-in

Store data to disk

Data extraction

2. The logical structure of the database

Library

Show databases

Use mysql

Table

Show tables

Records (rows, columns)

Select user,host,password from user

Desc user

3. The physical structure of the database (how is it stored)

Object Storage:

Library-> directory

Table:

MyIASM:

User.frm

User.MYD

User.MYI

InnoDB:

Shared tablespaces: ibdata1:ibdata2

Independent tablespaces: t1.frm t1.ibd

Example: create a database and table and look at the differences in separate tablespace storage

Create database oldboy

Use oldboy

Create table T1 (id int)

Insert into T1 values (1)

Select id from t1

Desc t1

2. Prepare MYSQL before installation

1) stand-alone installation disk

Mkfs.ext4 / dev/sdb

Mkdir / application

Mount / dev/sdb / application

Blkid-> query the uuid of / dev/sdb

Vi / etc/fstab

Xxx-xxx-xxxx-xxxx / application ext4 defaults 0 0

2) dependency package installation

Yum install-y ncurses-devel libaio-devel C++ gcc

Yum groupinstall "Development tools"-y

3) install precompiled cmake

Yum install cmake-y

Rpm-qa cmake

4) create a mysql user

Useradd-s / sbin/nologin-M mysql

Id mysql

Perror View error Cod

5) download the mysql package file on the official website and extract the installation

Wget-Q http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.36.tar.gz

Ls-l mysql-5.6.36.tar.gz

Tar xf mysql-5.6.36.tar.gz

Cd mysql-5.6.36

Cmake. -DCMAKE_INSTALL_PREFIX=/application/mysql-5.6.36\

-DMYSQL_DATADIR=/application/mysql-5.6.36/data\

-DMYSQL_UNIX_ADDR=/application/mysql-5.6.36/tmp/mysql.sock\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci\

-DWITH_EXTRA_CHARSETS=all\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_FEDERATED_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1\

-DWITH_ZLIB=bundled\

-DWITH_SSL=bundled\

-DENABLED_LOCAL_INFILE=1\

-DWITH_EMBEDDED_SERVER=1\

-DENABLE_DOWNLOADS=1\

-DWITH_DEBUG=0

Note: mysql installation parameters section explains the collection

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql # installation directory

-DMYSQL_DATADIR=/data # database storage directory

-DSYSCONFDIR=/etc # MySQL catalogs

-DWITH_MYISAM_STORAGE_ENGINE=1 # install the myisam storage engine

-DWITH_INNOBASE_STORAGE_ENGINE=1 # install the innodb storage engine

-DWITH_ARCHIVE_STORAGE_ENGINE=1 # install the archive storage engine

-DWITH_BLACKHOLE_STORAGE_ENGINE=1 # install the blackhole storage engine

-DWITH_LOCAL_INFILE=1 # allows data to be imported locally

-DWITH_READLINE=1 # shortcut key function

-DMYSQL_UNIX_ADDR=/tmp/mysql.sock # Unix socket file path

-DDEFAULT_CHARSET=utf8 # default character

-DDEFAULT_COLLATION=utf8_general_ci # check character

-DEXTRA_CHARSETS=all # install all extended character sets

-DMYSQL_TCP_PORT=3306

-DMYSQL_USER=mysql,

-DWITH_SSL=yes

-DWITH_MEMORY_STORAGE_ENGINE=1 # install the memory storage engine

-DWITH_FEDERATED_STORAGE_ENGINE=1 # install the frderated storage engine

6) initialize mysql database and configuration database

Cp support-files/my*.cnf / etc/my.cnf

Note: the compiled MySQL does not need to set the configuration file for the time being.

If mysql in rpm format has been installed in the previous operating system, the / etc/my.cnf file may be left in the system and we need to delete it

/ application/mysql/scripts/mysql_install_db-basedir=/application/mysql/-datadir=/application/mysql/data-user=mysql

Make & & make install

Ln-s / application/mysql-5.6.36/ / application/mysql

7) mysql starts up

Chown-R mysql.mysql / application/mysql/

Cp support-files/mysql.server / etc/init.d/mysqld

Chmod 700 / etc/init.d/mysqld

Chkconfig mysqld on

Chkconfig-list mysqld

/ etc/init.d/mysqld start

Netstat-lntup | grep 330

Echo 'PATH=/application/mysql/bin/:$PATH' > > / etc/profile

Tail-1 / etc/profile

Source / etc/profile

Echo $PATH

Mysql

Note:

At this point, the database startup will prompt that xx/tmp/mysql.sock cannot be found because the 5.6.36 version does not automatically create a tmp directory, so we need to manually mkdir / application/mysql/tmp

8) mysql troubleshooting

1. Log search # tail-100 / application/mysql/data/db02.err

2. Check the screen output

Fault: ERROR! The server quit without updating PID file

1. Permissions .Chown-R mysql.mysql

2.killall mysqld

3. Reinitialize the data.

4. It has been running for 1 year, and there is a problem (illegal power off) or illegal database shutdown, such as kill-9).

9) mysql cleans up useless users

Select user,host from mysql.user

Drop user'@ 'db02'

Drop user'@ 'localhost'

Drop user 'root'@'db02'

Drop user 'root'@'::1'

Select user,host from mysql.user

Drop database test

Show databases

10) client connection mysql

Through socket: (mysql local connection uses socket by default)

Mysql-uroot-p123456-S / usr/local/mysql/data/mysql.sock

Through the TCP/IP way

Mysql-uroot-p123456-h remote IP

11) mysql user Management

# query user name, password and permissions

Mysql > select user,password,host from mysql.user

+-+

| | user | password | host | |

+-+

| | root | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | localhost | |

+-+

# View the database

Show databases

# create a database

Create database app

# View the specified database table

Use mysql

Show tables

# create a user and authorize the specified database

Mysql > grant all on app.* to app@'10.0.0.%' identified by '123456'

# create a single user

Create user 'user' @ 'host' IDENTIFIED BY 'password'

Create user 'oldboy'@'locahost' identified by' oldboy123'

# authorize users

Mysql > grant all on mysql.* to zhangsan1@'10.0.10.%'

# deleting a user

Drop user' user'@' host domain'

Special deletion method:

Mysql > delete from mysql.user where user='app' and host='localhost'

Mysql > flush privileges

# create users and authorize them at the same time

Grant all on *. * to oldgirl@'172.16.1.%' identified by 'oldgirl123'

Flush privileges

# View a user's database permissions

Mysql > select user,host,password from mysql.user

+-- +

| | user | host | password | |

+-- +

| | root | localhost | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| | zhangsan | 10.0.10.% | * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| | lisi | 10.0.10.% | * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

| | super | localhost | * 531E182E2F72080AB0740FE2F2D689DBE0146E04 |

| | wanger | 10.0.10.% | * E8D868B7DA46FC9F996DC761C1AE01754A4447D5 |

+-- +

Mysql > show grants for lisi@'10.0.10.%'\ G

* * 1. Row *

Grants for lisi@10.0.10.%: GRANT USAGE ON *. * TO 'lisi'@'10.0.10.%' IDENTIFIED BY PASSWORD' * 23AE809DDACAF96AF0FD78ED04B6A265E05AA257'

* 2. Row * *

Grants for lisi@10.0.10.%: GRANT SELECT ON `app`.* TO 'lisi'@'10.0.10.%'

2 rows in set (0.00 sec)

Note: when there is a conflict of individual user permissions, the setting with the maximum permissions shall prevail.

# revoke a permission in the database separately

Mysql > revoke drop on wordpress.* from wordpress@'10.0.0.%'

Query OK, 0 rows affected (0.00 sec)

# user permissions that can be authorized:

INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

# revoke permissions:

REVOKE INSERT ON wordpress.* from oldboy@'localhost'

# take back all permissions of a database

Revoke all on wordpress.* from wordpress@'10.0.0.%'

Note: the creation of users in an enterprise generally authorizes a private network segment to log in, and there are two most common ways to write the network segment.

Method 1RV 172.16.1% (% is a wildcard and matches everything).

The method 2RV 172.16.1.0 hand 255.255.255.0, but it is a small pity that 172.16.1.0 hand 24 cannot be used.

Example: blog authorization:

Grant select,insert,update,delete,create,drop on blog.* to 'blog'@'172.16.1.%' identified by' blog123'

Revoke create,drop on blog.* from 'blog'@'172.16.1.%'

11) forgot to change the mysql password

For V5.6 version

# start mysql with the following command

/ usr/local/mysql/bin/mysqld_safe-skip-grant-table-skip-networking &

# Log in to mysql

Mysql

# modify password:

Mysql > select user,password,host from mysql.user

Mysql > update mysql.user set password=PASSWORD ('123456') where user='wordpress' and host='192.168.56.%'

# start mysql normally

Mysql-uroot-p123456-h 192.168.56.11

12) mysql configuration file read order

/ etc/my.cnf-- > / etc/mysql/my.cnf-- > $MYSQL_HOME/my.cnf-- > ~ / .my.cnf

-- >-- defaults-extra-file-- >-- defaults-file finally reads other configurations on the command line

13) mysql error cod

Perror

Http://oldboy.blog.51cto.com/2561410/1728380

The my.cnf parameter indicates that only personal collections

[client]

Port = 3309

Socket = / home/mysql/mysql/tmp/mysql.sock

[mysqld]

! include / home/mysql/mysql/etc/mysqld.cnf # includes configuration files that store user name and password files separately

Port = 3309

Socket = / home/mysql/mysql/tmp/mysql.sock

Pid-file = / usr/local/mysql/var/mysql.pid

Basedir = / home/mysql/mysql/

Datadir = / usr/local/mysql/mysql/mysql/var/

# tmp dir settings

Tmpdir = / home/mysql/mysql/tmp/

Slave-load-tmpdir = / home/mysql/mysql/tmp/

# used when slave executes load data infile

# language = / home/mysql/mysql/share/mysql/english/

Character-sets-dir = / home/mysql/mysql/share/mysql/charsets/

# skip options

When skip-name-resolve # grant, you must use ip, not the hostname

Skip-symbolic-links # cannot use connection files

Skip-external-locking # does not use system locking, to use myisamchk, you must shut down the server

Skip-slave-start # starts mysql and does not start replication

# sysdate-is-now

# res settings

Back_log = 50 # accept the queue, and put it in the cache for the request queue that does not establish a tcp connection. The queue size is back_log, which is limited to the OS parameter.

Max_connections = 1000 # maximum number of concurrent connections. Increasing this value requires a corresponding increase in the number of file descriptors allowed to be opened

Max_connect_errors = 10000 # if the connection error initiated by a user exceeds this value, the user's next connection will be blocked until the administrator executes the flush hosts; command; prevent *

# open_files_limit = 10240

Connect-timeout = the maximum number of seconds before the connection timeout, which on Linux platforms is also used as the time to wait for the first response from the server

Wait-timeout = 28800 # time to wait for connection to be closed

Interactive-timeout = 28800 # allow interactive_timeout (instead of wait_timeout) seconds of inactivity before closing the connection. The client's session wait_timeout variable is set to the value of the session interactive_timeout variable.

Slave-net-timeout = 600 # slave server can also handle network connection interruptions. However, a network outage is notified only if the slave server does not receive data from the master server for more than slave_net_timeout seconds.

Net_read_timeout = 30 # timeout for reading information from the server

Net_write_timeout = 60 # timeout for writing information from the server

Net_retry_count = 10 # if the read operation of a communication port is interrupted, retry several times before giving up

Net_buffer_length = 16384 # the packet message buffer is initialized to net_buffer_length bytes, but can grow to max_allowed_packet bytes if needed

Max_allowed_packet = 64m #

# table_cache = 512 # number of tables opened by all threads. Increasing this value increases the number of file descriptors required by mysqld

Thread_stack = 192 K # stack size per thread

Thread_cache_size = 20 # thread cache

Thread_concurrency = 8 # the data for simultaneous threads is preferably twice the number of CPU here. The number of CPU configured locally

# qcache settings

Query_cache_size = 256m # query cache size

Query_cache_limit = 2m # does not cache the results of queries greater than this value

Query_cache_min_res_unit = 2K # minimum block size allocated by query cache

# default settings

# time zone

Default-time-zone = system # server time zone

Character-set-server = utf8 # server level character set

Default-storage-engine = InnoDB # default storage

# tmp & heap

Tmp_table_size = 512m # temporary table size, if this value is exceeded, the result is put on disk

Max_heap_table_size = 512m # this variable sets the maximum size to which the MEMORY (HEAP) table can grow

Log-bin = mysql-bin # these paths are relative to datadir

Log-bin-index = mysql-bin.index

Relayrelay-log = relay-log

Relayrelay_log_index = relay-log.index

# warning & error log

Log-warnings = 1

Log-error = / home/mysql/mysql/log/mysql.err

The log_output = FILE # parameter log_output specifies the format of the slow query output. The default is FILE. You can set it to TABLE, and then you can query the slow_log table under the mysql schema.

# slow query log

Slow_query_log = 1

Long-query-time = 1 # slow query if the time is longer than 1 second

Slow_query_log_file = / home/mysql/mysql/log/slow.log

# log-queries-not-using-indexes

# log-slow-slave-statements

General_log = 1

General_log_file = / home/mysql/mysql/log/mysql.log

Max_binlog_size = 1G

Max_relay_log_size = 1G

# if use auto-ex, set to 0

Relay-log-purge = 1 # delete logs when they are not relayed. This operation is completed by the SQL thread

# max binlog keeps days

Expire_logs_days = 30 # binlog deletion for more than 30 days

Binlog_cache_size = 1m # session level

# replication

Replicate-wild-ignore-table = mysql.% # ignore databases and tables when replicating

Replicate-wild-ignore-table = test.% # ignore databases and tables when replicating

# slave_skip_errors=all

Key_buffer_size = 256m # myisam index buffer, only key without data

Sort_buffer_size = 2m # sort buffer size; thread level

Read_buffer_size = 2m # buffer size of scanned data in full table scan (Sequential Scan); thread level

Join_buffer_size = 8m # join buffer size; thread level

Read_rnd_buffer_size = 8m # MyISAM buffer size of scanned data in index scan (Random Scan); thread level

Bulk_insert_buffer_size = 64m # the size of the tree buffer used in block insertion optimization. Note: this is a limitation of per thread

Myisam_sort_buffer_size = 64m # MyISAM sets the size of the buffer used when recovering the table, when sorting the buffer allocated by the MyISAM index during REPAIR TABLE or CREATE INDEX index creation or ALTER TABLE process

Myisam_max_sort_file_size = 10G # MyISAM if the temporary file becomes larger than the index, do not use the quick sort index method to create an index. Note: this parameter is given in bytes. The maximum space size of temporary files that MySQL is allowed to use when rebuilding MyISAM indexes (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the size of the file exceeds this value, the index is created using the key-value cache, which is much slower. The value is measured in bytes

Myisam_repair_threads = 1 # if the value is greater than 1, create the MyISAM table index in parallel during the Repair by sorting process (each index in its own thread)

Myisam_recover = the maximum length of the GROUP_CONCAT () function allowed by 64K#

Transaction_isolation = REPEATABLE-READ

Innodb_file_per_table

# innodb_status_file = 1

# innodb_open_files = 2048

Innodb_additional_mem_pool_size = 100m # the control object of the frame cache needs to apply for cache from here, so this value corresponds to innodb_buffer_pool

Innodb_buffer_pool_size = 2G # includes data page, index page, insert cache, lock information, adaptive hash, data dictionary information

Innodb_data_home_dir = / usr/local/mysql/var/

# innodb_data_file_path = ibdata1:1G:autoextend

Innodb_data_file_path = ibdata1:500M;ibdata2:2210M:autoextend # tablespace

Innodb_file_io_threads = 4 # IO threads

Innodb_thread_concurrency = 16 # InnoDB try to keep the number of operating system threads within InnoDB less than or equal to the limit given by this parameter

Innodb_flush_log_at_trx_commit = 1 # every time the data in the commit log cache is brushed to disk

Innodb_log_buffer_size = 8m # transaction log cache

Innodb_log_file_size = 500m # transaction log size

# innodb_log_file_size = 100m

Innodb_log_files_in_group = 2 # two sets of things log

Innodb_log_group_home_dir = / usr/local/mysql/mysql/var/# log group

Innodb_max_dirty_pages_pct = 90 # innodb main thread refreshes the data in the cache pool so that the proportion of dirty data is less than 90%

Innodb_lock_wait_timeout = 50 # the number of timeout seconds that an InnoDB transaction can wait for a lock before being rolled back. InnoDB automatically detects transaction deadlocks and rolls back transactions in its own locking table. InnoDB notices the lock setting with the LOCK TABLES statement. The default value is 50 seconds

# innodb_flush_method = O_DSYNC

[mysqldump]

Quick

Max_allowed_packet = 64m

[mysql]

Disable-auto-rehash # allows prompt via TAB key

Default-character-set = utf8

Connect-timeout = 3

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.