galaracluster的实现：
- percona公司提供galaracluster集群解决方案
- mariadb提供galaracluster解决方案
mariadb提供galaracluster的实现
yum源配置
http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-5.5.64/yum/centos74-amd64/
https://mirrors.aliyun.com/mariadb/mariadb-10.0.38/yum/centos7-amd64/
http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-5.5.63/yum/centos/7.0/x86_64/
repodata指向
https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-10.0.38/yum/centos7-amd64/
国内yum源用此
-------------------将其复制到所需安装主机----------------
[root@master-mariadb ~]# cat >/etc/yum.repos.d/mysql.repo <<EOF
> [mysql]
> name=mysq
> baseurl=http://ftp.hosteurope.de/mirror/archive.mariadb.org//mariadb-5.5.64/yum/centos74-amd64/
> gpgcheck=0
> EOF
------------------------------------------------------
[root@galaraclster ~]# cat > /etc/yum.repos.d/mysql.repo <<EOF
>[mysql]
>name=mysql
>baseurl=https://mirrors.aliyun.com/mariadb/mariadb-5.5.68/yum/centos7-amd64/
>gpgcheck=0
> EOF
yum repolist
yum install MariaDB-Galera-server
#安装报错可以
yum clean all
yum makecache
配置/etc/my.cnf.d/server.cnf
#192.168.37.7
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.37.7,192.168.37.17,192.168.37.37"
wsrep_cluster_name="testcluser"
binlog_format=row
#配置文件拷贝至所有节点
[root@master-mariadb ~]# scp /etc/my.cnf.d/server.cnf 192.168.37.17:/etc/my.cnf.d/
The authenticity of host '192.168.37.17 (192.168.37.17)' can't be established.
ECDSA key fingerprint is 82:01:ab:34:04:e9:98:28:c2:10:0b:79:9a:60:19:06.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.37.17' (ECDSA) to the list of known hosts.
root@192.168.37.17's password:
server.cnf 100% 1159 1.1KB/s 00:00
[root@master-mariadb ~]# scp /etc/my.cnf.d/server.cnf 192.168.37.37:/etc/my.cnf.d/
The authenticity of host '192.168.37.37 (192.168.37.37)' can't be established.
ECDSA key fingerprint is 82:01:ab:34:04:e9:98:28:c2:10:0b:79:9a:60:19:06.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.37.37' (ECDSA) to the list of known hosts.
root@192.168.37.37's password:
server.cnf 100% 1159 1.1KB/s 00:00
启动cluster
#192.168.37.7
首次启动时，需要初始化集群，在其中一个节点上执行命令
[root@master-mariadb ~]# /etc/init.d/mysql start --wsrep-new-cluster
Starting MariaDB.200708 08:53:25 mysqld_safe Logging to '/var/lib/mysql/master-mariadb.err'.
200708 08:53:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
... SUCCESS!
[root@master-mariadb ~]# ss -tnl|grep 3306
LISTEN 0 50 *:3306 *:*
#其它节点只要启动就好
#192.168.37.17
[root@slave-mariadb1 ~]# /etc/init.d/mysql start
Starting MariaDB.200708 08:54:31 mysqld_safe Logging to '/var/lib/mysql/slave-mariadb1.err'.
200708 08:54:31 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
...SST in progress, setting sleep higher. SUCCESS!
#192.168.37.37
[root@slave-mariadb2 ~]# /etc/init.d/mysql start
Starting MariaDB.200708 08:54:52 mysqld_safe Logging to '/var/lib/mysql/slave-mariadb2.err'.
200708 08:54:52 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
..SST in progress, setting sleep higher. SUCCESS!
测试数据
#37.7上导入测试数据 其它节点均已复制
[root@master-mariadb test]# mysql < hellodb_innodb.sql
[root@master-mariadb test]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.63-MariaDB-wsrep MariaDB Server, wsrep_25.25.r9949137
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#hellodb已经导入成功
MariaDB [(none)]> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
#查看表
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
#37.17
[root@slave-mariadb1 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.63-MariaDB-wsrep MariaDB Server, wsrep_25.25.r9949137
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
#192.168.37.37
MariaDB [(none)]> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
#查看集群中相关变量
MariaDB [hellodb]> show variables like 'wsrep_%'\G;
*************************** 1. row ***************************
Variable_name: wsrep_OSU_method
Value: TOI
*************************** 2. row ***************************
Variable_name: wsrep_auto_increment_control
Value: ON
*************************** 3. row ***************************
Variable_name: wsrep_causal_reads
Value: OFF
*************************** 4. row ***************************
Variable_name: wsrep_certification_rules
Value: strict
*************************** 5. row ***************************
Variable_name: wsrep_certify_nonPK
Value: ON
*************************** 6. row ***************************
Variable_name: wsrep_cluster_address
Value: gcomm://192.168.37.7,192.168.37.17,192.168.37.37
*************************** 7. row ***************************
Variable_name: wsrep_cluster_name
Value: testcluser
*************************** 8. row ***************************
Variable_name: wsrep_convert_LOCK_to_trx
Value: OFF
*************************** 9. row ***************************
Variable_name: wsrep_data_home_dir
Value: /var/lib/mysql/
*************************** 10. row ***************************
Variable_name: wsrep_dbug_option
Value:
*************************** 11. row ***************************
Variable_name: wsrep_debug
Value: OFF
*************************** 12. row ***************************
Variable_name: wsrep_desync
Value: OFF
*************************** 13. row ***************************
Variable_name: wsrep_dirty_reads
Value: OFF
*************************** 14. row ***************************
Variable_name: wsrep_drupal_282555_workaround
Value: OFF
*************************** 15. row ***************************
Variable_name: wsrep_forced_binlog_format
Value: NONE
*************************** 16. row ***************************
Variable_name: wsrep_load_data_splitting
Value: ON
*************************** 17. row ***************************
Variable_name: wsrep_log_conflicts
Value: OFF
*************************** 18. row ***************************
Variable_name: wsrep_max_ws_rows
Value: 131072
*************************** 19. row ***************************
Variable_name: wsrep_max_ws_size
Value: 1073741824
*************************** 20. row ***************************
Variable_name: wsrep_mysql_replication_bundle
Value: 0
*************************** 21. row ***************************
Variable_name: wsrep_node_address
Value:
*************************** 22. row ***************************
Variable_name: wsrep_node_incoming_address
Value: AUTO
*************************** 23. row ***************************
Variable_name: wsrep_node_name
Value: master-mariadb
*************************** 24. row ***************************
Variable_name: wsrep_notify_cmd
Value:
*************************** 25. row ***************************
Variable_name: wsrep_on
Value: ON
*************************** 26. row ***************************
Variable_name: wsrep_provider
Value: /usr/lib64/galera/libgalera_smm.so
*************************** 27. row ***************************
Variable_name: wsrep_provider_options
Value: base_dir = /var/lib/mysql/; base_host = 192.168.37.7; base_port = 4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 2; evs.version = 0; evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no; gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.listen_addr = tcp://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.segment = 0; gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = 192.168.37.7; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = PT30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 9; socket.checksum = 2; socket.recv_buf_size = 212992;
*************************** 28. row ***************************
Variable_name: wsrep_recover
Value: OFF
*************************** 29. row ***************************
Variable_name: wsrep_replicate_myisam
Value: OFF
*************************** 30. row ***************************
Variable_name: wsrep_restart_slave
Value: OFF
*************************** 31. row ***************************
Variable_name: wsrep_retry_autocommit
Value: 1
*************************** 32. row ***************************
Variable_name: wsrep_slave_FK_checks
Value: ON
*************************** 33. row ***************************
Variable_name: wsrep_slave_UK_checks
Value: OFF
*************************** 34. row ***************************
Variable_name: wsrep_slave_threads
Value: 1
*************************** 35. row ***************************
Variable_name: wsrep_sst_auth
Value:
*************************** 36. row ***************************
Variable_name: wsrep_sst_donor
Value:
*************************** 37. row ***************************
Variable_name: wsrep_sst_donor_rejects_queries
Value: OFF
*************************** 38. row ***************************
Variable_name: wsrep_sst_method
Value: rsync
*************************** 39. row ***************************
Variable_name: wsrep_sst_receive_address
Value: AUTO
*************************** 40. row ***************************
Variable_name: wsrep_start_position
Value: 00000000-0000-0000-0000-000000000000:-1
*************************** 41. row ***************************
Variable_name: wsrep_sync_wait
Value: 0
41 rows in set (0.00 sec)
MariaDB [hellodb]> SHOW STATUS LIKE 'wsrep_%'\G;
*************************** 1. row ***************************
Variable_name: wsrep_local_state_uuid
Value: 6f6526d5-c0b5-11ea-9151-72b06f14bb18
*************************** 2. row ***************************
Variable_name: wsrep_protocol_version
Value: 9
*************************** 3. row ***************************
Variable_name: wsrep_last_committed
Value: 35
*************************** 4. row ***************************
Variable_name: wsrep_replicated
Value: 35
*************************** 5. row ***************************
Variable_name: wsrep_replicated_bytes
Value: 18400
*************************** 6. row ***************************
Variable_name: wsrep_repl_keys
Value: 142
*************************** 7. row ***************************
Variable_name: wsrep_repl_keys_bytes
Value: 1976
*************************** 8. row ***************************
Variable_name: wsrep_repl_data_bytes
Value: 14034
*************************** 9. row ***************************
Variable_name: wsrep_repl_other_bytes
Value: 0
*************************** 10. row ***************************
Variable_name: wsrep_received
Value: 10
*************************** 11. row ***************************
Variable_name: wsrep_received_bytes
Value: 801
*************************** 12. row ***************************
Variable_name: wsrep_local_commits
Value: 6
*************************** 13. row ***************************
Variable_name: wsrep_local_cert_failures
Value: 0
*************************** 14. row ***************************
Variable_name: wsrep_local_replays
Value: 0
*************************** 15. row ***************************
Variable_name: wsrep_local_send_queue
Value: 0
*************************** 16. row ***************************
Variable_name: wsrep_local_send_queue_max
Value: 1
*************************** 17. row ***************************
Variable_name: wsrep_local_send_queue_min
Value: 0
*************************** 18. row ***************************
Variable_name: wsrep_local_send_queue_avg
Value: 0.000000
*************************** 19. row ***************************
Variable_name: wsrep_local_recv_queue
Value: 0
*************************** 20. row ***************************
Variable_name: wsrep_local_recv_queue_max
Value: 2
*************************** 21. row ***************************
Variable_name: wsrep_local_recv_queue_min
Value: 0
*************************** 22. row ***************************
Variable_name: wsrep_local_recv_queue_avg
Value: 0.100000
*************************** 23. row ***************************
Variable_name: wsrep_local_cached_downto
Value: 1
*************************** 24. row ***************************
Variable_name: wsrep_flow_control_paused_ns
Value: 0
*************************** 25. row ***************************
Variable_name: wsrep_flow_control_paused
Value: 0.000000
*************************** 26. row ***************************
Variable_name: wsrep_flow_control_sent
Value: 0
*************************** 27. row ***************************
Variable_name: wsrep_flow_control_recv
Value: 0
*************************** 28. row ***************************
Variable_name: wsrep_cert_deps_distance
Value: 1.000000
*************************** 29. row ***************************
Variable_name: wsrep_apply_oooe
Value: 0.000000
*************************** 30. row ***************************
Variable_name: wsrep_apply_oool
Value: 0.000000
*************************** 31. row ***************************
Variable_name: wsrep_apply_window
Value: 1.000000
*************************** 32. row ***************************
Variable_name: wsrep_commit_oooe
Value: 0.000000
*************************** 33. row ***************************
Variable_name: wsrep_commit_oool
Value: 0.000000
*************************** 34. row ***************************
Variable_name: wsrep_commit_window
Value: 1.000000
*************************** 35. row ***************************
Variable_name: wsrep_local_state
Value: 4
*************************** 36. row ***************************
Variable_name: wsrep_local_state_comment
Value: Synced
*************************** 37. row ***************************
Variable_name: wsrep_cert_index_size
Value: 81
*************************** 38. row ***************************
Variable_name: wsrep_causal_reads
Value: 0
*************************** 39. row ***************************
Variable_name: wsrep_cert_interval
Value: 0.000000
*************************** 40. row ***************************
Variable_name: wsrep_open_transactions
Value: 0
*************************** 41. row ***************************
Variable_name: wsrep_open_connections
Value: 0
*************************** 42. row ***************************
Variable_name: wsrep_incoming_addresses
Value: 192.168.37.7:3306,192.168.37.17:3306,192.168.37.37:3306
*************************** 43. row ***************************
Variable_name: wsrep_cluster_weight
Value: 3
*************************** 44. row ***************************
Variable_name: wsrep_desync_count
Value: 0
*************************** 45. row ***************************
Variable_name: wsrep_evs_delayed
Value:
*************************** 46. row ***************************
Variable_name: wsrep_evs_evict_list
Value:
*************************** 47. row ***************************
Variable_name: wsrep_evs_repl_latency
Value: 0/0/0/0/0
*************************** 48. row ***************************
Variable_name: wsrep_evs_state
Value: OPERATIONAL
*************************** 49. row ***************************
Variable_name: wsrep_gcomm_uuid
Value: 6f643412-c0b5-11ea-ba43-97ac8526a5d3
*************************** 50. row ***************************
Variable_name: wsrep_cluster_conf_id
Value: 3
*************************** 51. row ***************************
Variable_name: wsrep_cluster_size
Value: 3
*************************** 52. row ***************************
Variable_name: wsrep_cluster_state_uuid
Value: 6f6526d5-c0b5-11ea-9151-72b06f14bb18
*************************** 53. row ***************************
Variable_name: wsrep_cluster_status
Value: Primary
*************************** 54. row ***************************
Variable_name: wsrep_connected
Value: ON
*************************** 55. row ***************************
Variable_name: wsrep_local_bf_aborts
Value: 0
*************************** 56. row ***************************
Variable_name: wsrep_local_index
Value: 0
*************************** 57. row ***************************
Variable_name: wsrep_provider_name
Value: Galera
*************************** 58. row ***************************
Variable_name: wsrep_provider_vendor
Value: Codership Oy <info@codership.com>
*************************** 59. row ***************************
Variable_name: wsrep_provider_version
Value: 25.3.26(r3857)
*************************** 60. row ***************************
Variable_name: wsrep_ready
Value: ON
*************************** 61. row ***************************
Variable_name: wsrep_thread_count
Value: 2
61 rows in set (0.00 sec)
#同时创建表 会自动解决冲突
MariaDB [hellodb]> create table t1(id int);
Query OK, 0 rows affected (0.05 sec)
MariaDB [hellodb]> create table t1(id int);
ERROR 1050 (42S01): Table 't1' already exists
#测试效率，比主从慢，因为要检查数据一致性3min中 事务机制提升效率
#begin; commit
29：00
复制的问题和解决方案
复制的问题和解决方案：
(1) 数据损坏或丢失
Master： MHA + semi repl （半同步复制）
Slave： 重新复制
(2) 混合使用存储引擎
MyISAM：不支持事务
InnoDB： 支持事务
(3) 不惟一的server id
重新复制
(4) 复制延迟
需要额外的监控工具的辅助
一从多主：mariadb10版后支持
多线程复制：对多个数据库复制
33.17
TiDb概述
TiDB 是 PingCAP 公司受 Google Spanner / F1 论文启发而设计的开源分布式 HTAP (Hybrid Transactional and Analytical Processing) 数据库，结合了传统的 RDBMS 和NoSQL 的最佳特性。TiDB 兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。 tidb和mysql几乎完全兼容 TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标
准 SQL、MySQL 语法和 MySQL 协议，具有数据强一致的高可用特性，是一个不仅适合 OLTP 场景还适合 OLAP 场景的混合数据库。
TiDB 的目标是为 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场景提供一站式的解决方案。
TiDB 具备如下核心特点
1 高度兼容 MySQL 大多数情况下，无需修改代码即可从 MySQL 轻松迁移至 TiDB，分库分表后的 MySQL 集群亦可通过 TiDB 工具进行实时迁移
2水平弹性扩展 通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景。
3分布式事务 TiDB 100% 支持标准的 ACID 事务
4 真正金融级高可用 相比于传统主从 (M-S) 复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可以实现故障的自动恢复 (auto-failover)，无需人工介入。
5 一站式 HTAP 解决方案 TiDB 作为典型的 OLTP 行存数据库，同时兼具强大的 OLAP 性能，配合 TiSpark，可提供一站式 HTAP解决方案，一份存储同时处理OLTP & OLAP(OLAP、OLTP的介绍和比较 )无需传统繁琐的 ETL 过程。
6云原生 SQL 数据库 TiDB 是为云而设计的数据库，同 Kubernetes （十分钟带你理解Kubernetes核心概念 ）深度耦合，支持公有云、私有云和混合云，使部署、配置和维护变得十
分简单。 TiDB 的设计目标是 100% 的 OLTP 场景和 80% 的 OLAP 场景，更复杂的 OLAP 分析可以通过 TiSpark 项目来完成。 TiDB 对业务没有任何侵入性，能优雅的替换传统的数据
库中间件、数据库分库分表等 Sharding 方案。同时它也让开发运维人员不用关注数据库 Scale 的细节问题，专注于业务开发，极大的提升研发的生产力.
39:29
性能衡量指标
数据库服务衡量指标：
Qps：query per second
Tps：transaction per second
压力测试工具：
mysqlslap 自带的
Sysbench：功能强大
https://github.com/akopytov/sysbench
tpcc-mysql
MySQL Benchmark Suite
MySQL super-smack
MyBench
mysqlslap测试
[root@slave-mariadb1 ~]# mysqlslap -a -c 100
#测试前关闭二进制日志
#-a 自动生成表和数据 -c 并发数
Benchmark
Average number of seconds to run all queries: 1.762 seconds
Minimum number of seconds to run all queries: 1.762 seconds
Maximum number of seconds to run all queries: 1.762 seconds
Number of clients running queries: 100
Average number of queries per client: 0
51
生产环境my.cnf配置示例
硬件：内存32G
innodb_file_per_table = 1
打开独立表空间
max_connections = 8000
mariadb如何修改?
#1.修改配置文件/etc/my.cnf
max-connections=2000
ulimit -a
#2.修改文件描述符
ulimit -n 65505
#3. 修改配置 vim /usr/lib/systemd/system/mariadb.service
[Service]
LimitNOFILE=66666
#MySQL 服务所允许的同时会话数的上限，经常出现Too Many Connections的错误提
示，则需要增大此值
#mysqlslap -a -c 1000
----------------------------------------------
back_log = 300
#back_log 是操作系统在监听队列中所能保持的连接数max_connections+back_log后援队列
max_connect_errors = 1000
#每个客户端连接最大的错误允许数量，当超过该次数，MYSQL服务器将禁止此主机的连
接请求，直到MYSQL服务器重启或通过flush hosts命令清空此主机的相关信息
open_files_limit = 10240
#所有线程所打开表的数量，编译安装时相当于systemctl limit
max_allowed_packet = 32M
#每个连接传输数据大小.最大1G，须是1024的倍数，一般设为最大的BLOB的值
wait_timeout = 10
#指定一个请求的最大连接时间
sort_buffer_size = 16M
# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序
join_buffer_size = 16M
#不带索引的全表扫描.使用的buffer的最小值
query_cache_size = 128M
#查询缓冲大小
query_cache_limit = 4M
#指定单个查询能够使用的缓冲区大小，缺省为1M
transaction_isolation = REPEATABLE-READ
# 设定默认的事务隔离级别
thread_stack = 512K
# 线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和SQL语句复杂性，
此容量的内存在每次连接时被预留.
log-bin
# 二进制日志功能
binlog_format=row
#二进制日志格式
innodb_buffer_pool_size = 24G
#InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到物理内存大小的80%
innodb_file_io_threads = 4
#用来同步IO操作的IO线程的数量
innodb_thread_concurrency = 16
#在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍
innodb_log_buffer_size = 16M
# 用来缓冲日志数据的缓冲区的大小
innodb_log_file_size = 512M
在日志组中每个日志文件的大小
innodb_log_files_in_group = 3
# 在日志组中的文件总数
innodb_lock_wait_timeout = 120
# SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间
long_query_time = 2
#慢查询时长
log-queries-not-using-indexes
#将没有使用索引的查询也记录下来
MYSQL配置最佳实践
参考：
阿里巴巴Java开发手册
58到家数据库30条军规解读
http://zhuanlan.51cto.com/art/201702/531364.htm
转自：https://note.youdao.com/ynoteshare1/index.html?id=1b3377e3bacd327d28efacb81fe03e83&type=note
