galaracluster的實現(xiàn)：
- percona公司提供galaracluster集群解決方案
- mariadb提供galaracluster解決方案
mariadb提供galaracluster的實現(xiàn)
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/
國內(nèi)yum源用此
-------------------將其復(fù)制到所需安裝主機----------------
[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
#配置文件拷貝至所有節(jié)點
[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
首次啟動時，需要初始化集群，在其中一個節(jié)點上執(zhí)行命令
[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 *:*
#其它節(jié)點只要啟動就好
#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!
測試數(shù)據(jù)
#37.7上導(dǎo)入測試數(shù)據(jù) 其它節(jié)點均已復(fù)制
[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已經(jīng)導(dǎo)入成功
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)
#查看集群中相關(guān)變量
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)
#同時創(chuàng)建表 會自動解決沖突
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
#測試效率侥涵，比主從慢奢方，因為要檢查數(shù)據(jù)一致性3min中 事務(wù)機制提升效率
#begin; commit
29：00
復(fù)制的問題和解決方案
復(fù)制的問題和解決方案：
(1) 數(shù)據(jù)損壞或丟失
Master： MHA + semi repl （半同步復(fù)制）
Slave： 重新復(fù)制
(2) 混合使用存儲引擎
MyISAM：不支持事務(wù)
InnoDB： 支持事務(wù)
(3) 不惟一的server id
重新復(fù)制
(4) 復(fù)制延遲
需要額外的監(jiān)控工具的輔助
一從多主：mariadb10版后支持
多線程復(fù)制：對多個數(shù)據(jù)庫復(fù)制
33.17
TiDb概述
TiDB 是 PingCAP 公司受 Google Spanner / F1 論文啟發(fā)而設(shè)計的開源分布式 HTAP (Hybrid Transactional and Analytical Processing) 數(shù)據(jù)庫，結(jié)合了傳統(tǒng)的 RDBMS 和NoSQL 的最佳特性彼哼。TiDB 兼容 MySQL，支持無限的水平擴展，具備強一致性和高可用性沟优。 tidb和mysql幾乎完全兼容 TiDB 是一個分布式 NewSQL 數(shù)據(jù)庫。它支持水平彈性擴展睬辐、ACID 事務(wù)挠阁、標(biāo)
準(zhǔn) SQL、MySQL 語法和 MySQL 協(xié)議溯饵，具有數(shù)據(jù)強一致的高可用特性侵俗，是一個不僅適合 OLTP 場景還適合 OLAP 場景的混合數(shù)據(jù)庫。
TiDB 的目標(biāo)是為 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 場景提供一站式的解決方案丰刊。
TiDB 具備如下核心特點
1 高度兼容 MySQL 大多數(shù)情況下隘谣，無需修改代碼即可從 MySQL 輕松遷移至 TiDB，分庫分表后的 MySQL 集群亦可通過 TiDB 工具進(jìn)行實時遷移
2水平彈性擴展 通過簡單地增加新節(jié)點即可實現(xiàn) TiDB 的水平擴展啄巧，按需擴展吞吐或存儲寻歧，輕松應(yīng)對高并發(fā)、海量數(shù)據(jù)場景秩仆。
3分布式事務(wù) TiDB 100% 支持標(biāo)準(zhǔn)的 ACID 事務(wù)
4 真正金融級高可用 相比于傳統(tǒng)主從 (M-S) 復(fù)制方案码泛，基于 Raft 的多數(shù)派選舉協(xié)議可以提供金融級的 100% 數(shù)據(jù)強一致性保證，且在不丟失大多數(shù)副本的前提下逗概，可以實現(xiàn)故障的自動恢復(fù) (auto-failover)弟晚，無需人工介入。
5 一站式 HTAP 解決方案 TiDB 作為典型的 OLTP 行存數(shù)據(jù)庫逾苫，同時兼具強大的 OLAP 性能卿城，配合 TiSpark，可提供一站式 HTAP解決方案铅搓，一份存儲同時處理OLTP & OLAP(OLAP瑟押、OLTP的介紹和比較 )無需傳統(tǒng)繁瑣的 ETL 過程。
6云原生 SQL 數(shù)據(jù)庫 TiDB 是為云而設(shè)計的數(shù)據(jù)庫星掰，同 Kubernetes （十分鐘帶你理解Kubernetes核心概念 ）深度耦合多望，支持公有云嫩舟、私有云和混合云，使部署怀偷、配置和維護(hù)變得十
分簡單家厌。 TiDB 的設(shè)計目標(biāo)是 100% 的 OLTP 場景和 80% 的 OLAP 場景，更復(fù)雜的 OLAP 分析可以通過 TiSpark 項目來完成椎工。 TiDB 對業(yè)務(wù)沒有任何侵入性饭于，能優(yōu)雅的替換傳統(tǒng)的數(shù)據(jù)
庫中間件、數(shù)據(jù)庫分庫分表等 Sharding 方案维蒙。同時它也讓開發(fā)運維人員不用關(guān)注數(shù)據(jù)庫 Scale 的細(xì)節(jié)問題掰吕，專注于業(yè)務(wù)開發(fā)，極大的提升研發(fā)的生產(chǎn)力.
39:29
性能衡量指標(biāo)
數(shù)據(jù)庫服務(wù)衡量指標(biāo)：
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
#測試前關(guān)閉二進(jìn)制日志
#-a 自動生成表和數(shù)據(jù) -c 并發(fā)數(shù)
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
生產(chǎn)環(huán)境my.cnf配置示例
硬件：內(nèi)存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 服務(wù)所允許的同時會話數(shù)的上限颅痊，經(jīng)常出現(xiàn)Too Many Connections的錯誤提
示殖熟，則需要增大此值
#mysqlslap -a -c 1000
----------------------------------------------
back_log = 300
#back_log 是操作系統(tǒng)在監(jiān)聽隊列中所能保持的連接數(shù)max_connections+back_log后援隊列
max_connect_errors = 1000
#每個客戶端連接最大的錯誤允許數(shù)量，當(dāng)超過該次數(shù)斑响，MYSQL服務(wù)器將禁止此主機的連
接請求菱属，直到MYSQL服務(wù)器重啟或通過flush hosts命令清空此主機的相關(guān)信息
open_files_limit = 10240
#所有線程所打開表的數(shù)量，編譯安裝時相當(dāng)于systemctl limit
max_allowed_packet = 32M
#每個連接傳輸數(shù)據(jù)大小.最大1G舰罚，須是1024的倍數(shù)照皆，一般設(shè)為最大的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
#指定單個查詢能夠使用的緩沖區(qū)大小，缺省為1M
transaction_isolation = REPEATABLE-READ
# 設(shè)定默認(rèn)的事務(wù)隔離級別
thread_stack = 512K
# 線程使用的堆大小. 此值限制內(nèi)存中能處理的存儲過程的遞歸深度和SQL語句復(fù)雜性沸停，
此容量的內(nèi)存在每次連接時被預(yù)留.
log-bin
# 二進(jìn)制日志功能
binlog_format=row
#二進(jìn)制日志格式
innodb_buffer_pool_size = 24G
#InnoDB使用一個緩沖池來保存索引和原始數(shù)據(jù), 可設(shè)置這個變量到物理內(nèi)存大小的80%
innodb_file_io_threads = 4
#用來同步IO操作的IO線程的數(shù)量
innodb_thread_concurrency = 16
#在InnoDb核心內(nèi)的允許線程數(shù)量膜毁，建議的設(shè)置是CPU數(shù)量加上磁盤數(shù)量的兩倍
innodb_log_buffer_size = 16M
# 用來緩沖日志數(shù)據(jù)的緩沖區(qū)的大小
innodb_log_file_size = 512M
在日志組中每個日志文件的大小
innodb_log_files_in_group = 3
# 在日志組中的文件總數(shù)
innodb_lock_wait_timeout = 120
# SQL語句在被回滾前,InnoDB事務(wù)等待InnoDB行鎖的時間
long_query_time = 2
#慢查詢時長
log-queries-not-using-indexes
#將沒有使用索引的查詢也記錄下來
MYSQL配置最佳實踐
參考：
阿里巴巴Java開發(fā)手冊
58到家數(shù)據(jù)庫30條軍規(guī)解讀
http://zhuanlan.51cto.com/art/201702/531364.htm
轉(zhuǎn)自：https://note.youdao.com/ynoteshare1/index.html?id=1b3377e3bacd327d28efacb81fe03e83&type=note
