Linux LAP+MySQL主从 79-16

DAY-16j笔记
1.1 MySQL集群实战
MYSQL主从复制集群在中小企业、大型企业中被广泛使用，MYSQL主从复制的目的是实现数据库冗余备份，将Master数据库数据定时同步至Slave库中，一旦Master数据库宕机，可以将WEB应用数据库配置快速切换至Slave数据库，确保WEB应用较高的可用率，如图11-12所示，为MYSQL主从复制结构图：

Mysql主从复制集群至少需要2台数据库服务器，其中一台为Master库，另外一台为Slave库，MYSQL主从数据同步是一个异步复制的过程，要实现复制首先需要在master上开启bin-log日志功能，bin-log日志用于记录在Master库中执行的增、删、修改、更新操作的sql语句，整个过程需要开启3个线程，分别是Master开启IO线程，Slave开启IO线程和SQL线程，具体主从同步原理详解如下：
Slave上执行slave start，Slave IO线程会通过在Master创建的授权用户连接上至Master，并请求master从指定的文件和位置之后发送bin-log日志内容；
Master接收到来自slave IO线程的请求后，master IO线程根据slave发送的指定bin-log日志position点之后的内容，然后返回给slave的IO线程。
返回的信息中除了bin-log日志内容外，还有master最新的binlog文件名以及在binlog中的下一个指定更新position点；
Slave IO线程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和position点记录到master.info文件中，以便在下一次读取的时候能告知master从响应的bin-log文件名及最后一个position点开始发起请求；
Slave Sql线程检测到relay-log中内容有更新，会立刻解析relay-log的内容成在Master真实执行时候的那些可执行的SQL语句，将解析的SQL语句并在Slave里执行，执行成功后，Master库与Slave库保持数据一致。
常用到的字符的数据类型：
数字：int（整数）、decimal（小数）
字符串char（不可改变），varchar（可变）、text（可变）
日期：date
浮点数：float（单精度）0-24、double（双精度）25-30
1.2 MySQL数据库索引案例
MySQL索引可以用来快速地寻找某些具有特定值的记录，所有MySQL索引都以B-树的形式保存。例如MYSQL没有索引，执行select时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。如果表中数据有上亿条数据，查询一条数据花费的时间会非常长，索引的目的就类似电子书的目录及页码的对应关系。
如果在需搜索条件的列上创建了索引，MySQL无需扫描全表记录即可快速得到相应的记录行。如果该表有1000000条记录，通过索引查找记录至少要比全表顺序扫描快至少100倍，这就是索引在企业环境中带来的执行速度的提升。
MYSQL数据库常见索引类型包括：普通索引（normal）、唯一索引（unique）、全文索引（full text）、主键索引（primary key）、组合索引等，如下为每个索引的应用场景及区别：

如图11-8所示，为t1表的id字段创建主键索引，查看索引是否被创建，然后插入相同的id，提示报错：

图11-8 MYSQL主键索引案例演示
MYSQL数据库表删除各个索引命令，以t1表为案例，操作如下：

MYSQL数据库索引的缺点：
MYSQL数据库索引虽然能够提高数据库查询速度，但同时会降低更新、删除、插入表的速度，例如如对表进行INSERT、UPDATE、DELETE时，update表MySQL不仅要保存数据，还需保存更新索引；
建立索引会占用磁盘空间，大表上创建了多种组合索引，索引文件的会占用大量的空间。

Mysqldulpslow 命令含义
-s 排序参数
l: 查询锁的总时间；
r: 返回记录数；
t: 查询总时间排序；
al: 平均锁定时间；
ar: 平均返回记录数；
at: 平均查询时间；
-t ：返回前面的多少条的数据

[root@localhost html]# mysqldumpslow -s r -t 10 /data/mysql/slow.log
Reading mysql slow query log from /data/mysql/slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=5.0 (5), root[root]@localhost
select * from jf1
Died at /usr/bin/mysqldumpslow line 162, <> chunk 1.
Count=1 出现1次，time（执行时间），lock（等待锁时间），rows（扫描的总行数），用户和主机 以及查询语句。

1.3 MySQL数据库优化
Mysql数据库优化是一项非常重要的工作，而且是一项长期的工作，MYSQL优化三分靠配置文件及硬件资源的优化，七分靠sql语句的优化。
Mysql数据库具体优化包括：配置文件的优化、sql语句的优化、表结构的优化、索引的优化，而配置的优化包括：系统内核优化、硬件资源、内存、CPU、mysql本身配置文件的优化。
硬件上的优化：增加内存和提高磁盘读写速度，都可以提高MySQL数据库的查询，更新的速度。另一种提高MySQL性能的方式是使用多块磁盘来存储数据。因为可以从多块磁盘上并行读取数据，这样可以提高读取数据的速度。
MySQL参数的优化：内存中会为MySQL保留部分的缓冲区，这些缓冲区可以提高MySQL的速度，缓冲区的大小可以在MySQL的配置文件中进行设置。
附企业级MYSQL百万量级真实环境配置文件my.cnf内容，可以根据实际情况修改：

[client]
#[client]和[mysql]都是客户端
port = 3306
#port参数表示的是MySQL数据库的端口，默认的端口是3306
socket = /tmp/mysql.sock
#客户端连接本地mysql时使用sock文件，通信文件
[mysqld]
#主配置
user = mysql
#运行时用户
server_id = 10
#主从区分ID

port = 3306
#守护进程监听端口
socket = /tmp/mysql.sock
#本地mysql.sock文件
datadir = /data/mysql/
mysql数据目录
old_passwords = 1
#当服务器生成长密码哈希值时，允许你维持同4.1之前的客户端的向后兼容性。
lower_case_table_names = 1
#lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
#lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
#lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的
character-set-server = utf8
#服务器字符集，默认情况下所采用的。
default-storage-engine = INNODB
#默认mysql引擎
default_tmp_storage_engine
#表示临时表的默认存储引擎。
log-bin = bin.log
#开启binlog日志
log-error = error.log

#开启错误日志
pid-file = mysql.pid
#记录的是当前 mysqld 进程的 pid，pid 亦即 Process ID。
long_query_time = 2
#慢查询超时时间，默认为10s，MYSQL5.5以上可以设置微秒；
slow_query_log
#关闭慢查询日志
slow_query_log_file = slow.log
#慢查询日志文件
binlog_cache_size = 4M
#服务器配置了 log-bin，为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存。
binlog_format = mixed
#二进制日志 (binlog) 有 3 种不同的格式可选：Mixed,Statement,Row
max_binlog_cache_size = 16M
#表示的是binlog 能够使用的最大cache 内存大小
max_binlog_size = 1G
#如果二进制日志写入的内容超出给定值，日志就会发生滚动。你不能将该变量设置为大于1GB或小于4096字节。 默认值是1GB。
expire_logs_days = 30
#主要用来控制binlog日志文件保留时间，超过保留时间的binlog日志会被自动删除。单位是天
ft_min_word_len = 1
#设置mysql最小索引长度是4
back_log = 512
#在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
max_allowed_packet = 64M

值设置过小将导致单个记录超过限制后写入数据库失败，且后续记录写入也将失败，为了数据完整性，需要考虑到事务因素。

max_connections = 4096
#mysql的最大连接数
max_connect_errors = 100
#一个MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。max_connect_errors的值与性能并无太大关系。
join_buffer_size = 2M

联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享

read_buffer_size = 2M

MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。

read_rnd_buffer_size = 2M

MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，

sort_buffer_size = 2M

MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。

query_cache_size = 64M
#（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。
table_open_cache = 10000

open_tables 表示打开表的数量，opened_tables表示打开过的表数量，如果opened_tables数量过大，说明配置中 table_cache(5.1.3之后这个值叫做table_open_cache)值可能太小

thread_cache_size = 256
#当客户端断开之后，服务器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁（前提是缓存数未达上限）。Threads_created表示创建过的线程数：
max_heap_table_size = 64M
#为了防止意外创建一个超大的内存表导致永尽所有的内存资源.
tmp_table_size = 64M

#临时HEAP数据表的最大长度

thread_stack = 192K
#每个连接线程被创建时，MySQL给它分配的内存大小。
thread_concurrency = 24
local-infile = 0
skip-show-database
skip-name-resolve
skip-external-locking
connect_timeout = 600
interactive_timeout = 600
wait_timeout = 600
#*** MyISAM
key_buffer_size = 512M
#Key Buffer大小，用于缓存MyISAM表的索引块。决定数据库索引处理的速度（尤其是索引读）（1G 内存——>256M）
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G

mysql重建索引时允许使用的临时文件最大大小

myisam_repair_threads = 1
concurrent_insert = 2
myisam_recover
#*** INNODB
innodb_buffer_pool_size = 64G
#动态分配资源
innodb_additional_mem_pool_size = 32M
innodb_data_file_path = ibdata1:1G；ibdata2:1G:autoextend
innodb_read_io_threads = 8
#控制后台线程处理数据页上的写请求， 默认是4，不支持动态修改，建议根据服务器的核数以及读写请求 的比例加以调整。
innodb_write_io_threads = 8
#该参数值之和=2cpu个数cpu核数;如果你的系统读>写,可以设置innodb_read_io_threads值相对大点;反之，也可以.
innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 2
#如果是游戏服务器，建议此值设置为2；如果是对数据安全要求极高的应用，建议设置为1；设置为0性能最高，但如果发生故障，数据可能会有丢失的危险！ 默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存 （Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系 统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失 事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。
innodb_lock_wait_timeout = 120
#是innodb等待行锁直到放弃的秒数
innodb_log_buffer_size = 8M
#这个参数就是用来设置 Innodb 的 Log Buffer 大小的，系统默认值为 1MB。Log Buffer 的主要作用就是缓冲 Log 数据，提高写 Log 的 IO 性能。一般来说，如果你的系统不是写负载非常高且以 大事务居多的话，8MB 以内的大小就完全足够了。
innodb_log_file_size = 256M
#在一个日志组中每一个日志文件大小。日志文件合并后的大小（(innodb_log_file_size *innodb_log_files_in_group）可以到达512G。默认是48M。值明智范围从1MB到缓冲池的大小的1/n，N是日志文件组中文件数。值越大，在缓冲池执行checkpoint 刷新活动次数越小，节省磁盘I/O。
innodb_log_files_in_group = 3

#在日志组的日志文件数。

innodb_max_dirty_pages_pct = 90
#关闭数据库之前，可以手工调整@@global.innodb_max_dirty_pages_pct为比较小的数值，然后等待dity pages变少，然后restart，可以减少启动要恢复的时间。
innodb_thread_concurrency = 16
#线程已满时，Slave 端复制线程的延迟时间（ms), 默认为0， 不延迟，可动态修改。
innodb_open_files = 10000

#innodb_force_recovery = 4
#*** Replication Slave
read-only
#1是只读，0是读写, mysql设置为只读后，无法增删改。
#skip-slave-start
#slave复制进程不随mysql启动而启动
relay-log = relay.log
中继日志，就像二进制日志一样，由一组编号的文件组成，其中包含描述数据库更改的事件，以及包含所有使用的中继日志文件名称的索引文件。
log-slave-updates
#该参数就是为了让从库从主库复制数据时可以写入到binlog日志
#是一个全局非动态选项，其值为布尔型，即TRUE和FALSE。缺省为FALSE，修改该参数需要重启实例。
#从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。