MySQL Deployment configuration Management version 5.6

MySQL server-side program hierarchy

1.1 Connection Layer

Connection Agreement: TCPIP socket

Verifying connection legitimacy

Assigning connection threads to client Services

1.2 SQL Layer

Analytical

Optimization

Authorization Form

Inquire

Provide caching

1.3 Storage Engine Layer

Plug-in type of

Storing 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 to store)

Object storage:

Library----> Catalogue

Table:

Myiasm:

User.frm

User. MYD

User. MYI

InnoDB:

Shared table space: Ibdata1:ibdata2

Stand-alone tablespace: T1.frm t1.ibd

Example: Create a database and a table to see the difference between a separate table space store

Create Database Oldboy;

Use Oldboy

CREATE TABLE t1 (id int);

INSERT into T1 values (1);

Select id from t1;

Desc T1;

Second, MySQL pre-installation Preparation

1) Standalone Installation disk

Mkfs.ext4/dev/sdb

Mkdir/application

Mount/dev/sdb/application

Blkid-----> Query/dev/sdb UUID

Vi/etc/fstab

Xxx-xxx-xxxx-xxxx/application EXT4 Defaults 0 0

2) Dependent package installation

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

Yum Groupinstall "Development tools"-Y

3) Install pre-compiled CMake

Yum Install Cmake-y

Rpm-qa CMake

4) Create a MySQL user

Useradd-s/sbin/nologin-m MySQL

ID MySQL

Perror Viewing error codes

5) Download the MySQL package file and unzip 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: The MySQL installation Parameters section explains the collection

-dcmake_install_prefix=/usr/local/mysql #安装目录

-dmysql_datadir=/data #数据库存放目录

-dsysconfdir=/etc #MySQL配辑文件

-dwith_myisam_storage_engine=1 #安装myisam存储引擎

-dwith_innobase_storage_engine=1 #安装innodb存储引擎

-dwith_archive_storage_engine=1 #安装archive存储引擎

-dwith_blackhole_storage_engine=1 #安装blackhole存储引擎

-dwith_local_infile=1 #允许从本地导入数据

-dwith_readline=1 #快捷键功能

-dmysql_unix_addr=/tmp/mysql.sock #Unix Socket file path

-ddefault_charset=utf8 #默认字符

-ddefault_collation=utf8_general_ci #校验字符

-dextra_charsets=all #安装所有扩展字符集

-dmysql_tcp_port=3306

-dmysql_user=mysql,

-dwith_ssl=yes

-dwith_memory_storage_engine=1 #安装memory存储引擎

-dwith_federated_storage_engine=1 #安装frderated存储引擎

6) initializing the MySQL database and configuring the database

CP SUPPORT-FILES/MY*.CNF/ETC/MY.CNF

Note: Compiled MySQL can temporarily not need to set the configuration file.

If the previous operating system has been installed in the RPM format of MySQL, the system may have left the/etc/my.cnf file, we need to remove 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 boot 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, unable to find Xx/tmp/mysql.sock, because 5.6.36 version does not automatically create the TMP directory, we need to manually mkdir/application/mysql/tmp

8) MySQL Troubleshooting

1, check the log # # # #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. Running for 1 years, a problem (illegal (power off) shutdown or illegal shut down the database, such as kill-9).

9) MySQL cleanup for 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 to MySQL

Via socket: (MySQL local connection by default socket mode)

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

Via TCP/IP mode

Mysql-uroot-p123456-h Remote IP

MySQL User management

#查询用户名, passwords, and permissions

Mysql> select User,password,host from Mysql.user;

+------+-------------------------------------------+-----------+

| user | password | Host |

+------+-------------------------------------------+-----------+

| Root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 | localhost |

+------+-------------------------------------------+-----------+

#查看数据库

show databases;

#创建数据库

Create Database app;

#查看指定数据库表

Use MySQL;

Show tables;

#创建用户并对指定数据库授权

Mysql> Grant all on app.* to [e-mail protected] ' 10.0.0.% ' identified by ' 123456 ';

#创建单个用户

Create user ' username ' @ ' host ' identified by ' password ';

Create user ' Oldboy ' @ ' locahost ' identified by ' oldboy123 ';

#给用户授权

Mysql> Grant all on mysql.* to [email protected] ' 10.0.10.% ';

#删除用户

Drop user ' user ' @ ' host domain '

Special method of deletion:

Mysql> Delete from Mysql.user where user= ' app ' and host= ' localhost ';

mysql> flush Privileges;

#创建用户同时授权

Grant all on * * to [e-mail protected] ' 172.16.1.% ' identified by ' oldgirl123 ';

Flush privileges;

#查看某个用户数据库权限

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 [email protected] ' 10.0.10.% ' \g;

1. Row ***************************

Grants for [e-mail protected]%: GRANT USAGE on *. * to ' Lisi ' @ ' 10.0.10.% ' identified by PASSWORD ' *23ae809ddacaf96af0fd78ed0 4b6a265e05aa257 '

2. Row ***************************

Grants for [e-mail protected]%: GRANT SELECT on ' app '. * to ' Lisi ' @ ' 10.0.10.% '

2 rows in Set (0.00 sec)

Note: When there is a conflict with individual user rights, the setting with the maximum permissions will prevail

#单独收回数据库某个权限

mysql> revoke drop on wordpress.* from [email protected] ' 10.0.0.% ';

Query OK, 0 rows Affected (0.00 sec)

#可以授权的用户权限:

Insert,select, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SU PER, 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 INSERT on wordpress.* from [email protected] ' localhost ';

#收回某个数据库的所有权限

Revoke all on wordpress.* from [email protected] ' 10.0.0.% ';

Note: The creation of users in the enterprise is generally authorized an intranet network segment login, the most common network segment writing two kinds.

Method 1:172.16.1.% (% is a wildcard character, matches everything).

Method 2:172.16.1.0/255.255.255.0, but can not use 172.16.1.0/24, is a small regret.

Example: Blog authorization:

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

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

MySQL password forgot to change method

For the V5.6 version

#用以下命令启动mysql

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

#登陆mysql

Mysql

#修改密码:

Mysql> Select User,password,host from Mysql.user

mysql> Update Mysql.user Set Password=password (' 123456 ') where user= ' WordPress ' and host= ' 192.168.56.% ';

#正常启动mysql

Mysql-uroot-p123456-h 192.168.56.11

MySQL configuration file read order

/ETC/MY.CNF--/ETC/MYSQL/MY.CNF-$MYSQL _home/my.cnf-~/.my.cnf

----defaults-extra-file--and--defaults-file last read the other configuration on the command line

MySQL error code

Perror

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

MY.CNF parameter description only personal collection

[Client]

Port = 3309

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

[Mysqld]

!INCLUDE/HOME/MYSQL/MYSQL/ETC/MYSQLD.CNF #包含的配置文件, store the user name and password file 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/

#当slave when executing load data infile

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

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

# Skip Options

Skip-name-resolve #grant, you must use IP to not use host name

Skip-symbolic-links #不能使用连接文件

Skip-external-locking #不使用系统锁定, to use MYISAMCHK, you must shut down the server

Skip-slave-start #启动mysql, do not start replication

#sysdate-is-now

# res settings

Back_log = #接受队列, for a request queue that does not have a TCP connection placed in the cache, the queue size is Back_log, and the OS parameters are limited

Max_connections = #最大并发连接数, increasing this value requires a corresponding increase in the number of file descriptors allowed to open

Max_connect_errors = 10000 #如果某个用户发起的连接error超过该数值, the user's next connection will be blocked until the administrator executes the flush hosts; An order to prevent a hacker

#open_files_limit = 10240

Connect-timeout = Ten #连接超时之前的最大秒数, on Linux platforms, this timeout is also used as the time to wait for the server to respond first

Wait-timeout = 28800 #等待关闭连接的时间

Interactive-timeout = 28800 #关闭连接之前, allowing Interactive_timeout (instead of wait_timeout) seconds of inactivity. The client's session Wait_timeout variable is set to the value of the session interactive_timeout variable.

Slave-net-timeout = #从服务器也能够处理网络连接中断. However, a network outage is notified only if the server has not received data from the primary server for more than slave_net_timeout seconds

Net_read_timeout = #从服务器读取信息的超时

Net_write_timeout = #从服务器写入信息的超时

Net_retry_count = Ten #如果某个通信端口的读操作中断了, retry multiple times before giving up

Net_buffer_length = 16384 #包消息缓冲区初始化为net_buffer_length字节, but can grow to max_allowed_packet bytes when needed

Max_allowed_packet = 64M #

#table_cache = #所有线程打开的表的数目. Increasing this value can increase the number of file descriptors required by the MYSQLD

Thread_stack = 192K #每个线程的堆栈大小

Thread_cache_size = #线程缓存

Thread_concurrency = 8 #同时运行的线程的数据 It is better to have twice times the number of CPUs here. This machine is configured as the number of CPUs

# Qcache Settings

Query_cache_size = 256M #查询缓存大小

Query_cache_limit = 2M #不缓存查询大于该值的结果

Query_cache_min_res_unit = 2K #查询缓存分配的最小块大小

# Default Settings

# time Zone

Default-time-zone = System #服务器时区

Character-set-server = UTF8 #server级别字符集

Default-storage-engine = InnoDB #默认存储

# tmp & Heap

Tmp_table_size = 512M #临时表大小, if this value is exceeded, the result is placed on disk

Max_heap_table_size = maximum amount of space that a 512M #该变量设置MEMORY (heap) table can grow to

Log-bin = Mysql-bin #这些路径相对于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

Log_output = File #参数log_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 #慢查询时间 more than 1 seconds for slow query

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 them. This operation has the SQL thread complete

# Max Binlog keeps days

Expire_logs_days = #超过30天的binlog删除

Binlog_cache_size = 1M #session级别

# replication

replicate-wild-ignore-table = mysql.% #复制时忽略数据库及表

replicate-wild-ignore-table = test.% #复制时忽略数据库及表

# Slave_skip_errors=all

Key_buffer_size = 256M #myisam索引buffer, only key does not have data

Sort_buffer_size = 2M #排序buffer大小; thread level

Read_buffer_size = 2M #以全表扫描 (sequential scan) mode to scan the buffer size of the data; thread level

join_buffer_size = 8M # join buffer size; thread level

Read_rnd_buffer_size = 8M #MyISAM以索引扫描 (Random Scan) The buffer size of the scanned data; thread level

Bulk_insert_buffer_size = 64M #MyISAM the size of the tree buffer used in block insert optimization. Note: This is a per thread limitation

Myisam_sort_buffer_size = 64M #MyISAM Sets the size of the buffer used when the table is restored, and when the index is created in repair table or in the Create indexes or ALTER TABLE procedure MyISAM Buffer allocated by index

Myisam_max_sort_file_size = 10G #MyISAM If the temporary file becomes more than the index, do not use the Quick Sort index method to create an index. Note: This parameter is given as a byte. When rebuilding the MyISAM index (during repair table, ALTER table, or Load DATA infile), the maximum space size for the temporary files that MySQL uses is allowed. If the size of the file exceeds this value, it is much slower to create the index using the key-value cache. The unit of the value is in bytes

Myisam_repair_threads = 1 #如果该值大于1, MyISAM table index is created in parallel during repair by sorting (each index is in its own line range)

Myisam_recover = maximum length of the result of the 64k# allowed Group_concat () function

Transaction_isolation = Repeatable-read

Innodb_file_per_table

#innodb_status_file = 1

#innodb_open_files = 2048

Innodb_additional_mem_pool_size = 100M #帧缓存的控制对象需要从此处申请缓存, so this value corresponds to Innodb_buffer_pool

Innodb_buffer_pool_size = 2G #包括数据页, index page, insert cache, lock information, adaptive hash So, 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 #表空间

Innodb_file_io_threads = 4 #io线程数

Innodb_thread_concurrency = #InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制

Innodb_flush_log_at_trx_commit = 1 #每次commit The data in the log cache is brushed to disk

Innodb_log_buffer_size = 8M #事物日志缓存

Innodb_log_file_size = 500M #事物日志大小

#innodb_log_file_size =100m

Innodb_log_files_in_group = 2 #两组事物日志

Innodb_log_group_home_dir =/usr/local/mysql/mysql/var/#日志组

innodb_max_dirty_pages_pct = #innodb主线程刷新缓存池中的数据, which makes the proportion of dirty data less than 90%

Innodb_lock_wait_timeout = #InnoDB事务在被回滚之前可以等待一个锁定的超时秒数. InnoDB automatically detects the transaction deadlock in its own locking table and rolls back the transaction. InnoDB Use the Lock tables statement to notice the locking settings. The default value is 50 seconds

#innodb_flush_method = O_dsync

[Mysqldump]

Quick

Max_allowed_packet = 64M

[MySQL]

Disable-auto-rehash #允许通过TAB键提示

Default-character-set = UTF8

Connect-timeout = 3

MySQL Deployment configuration Management version 5.6