MySQL集群高可用
程序员文章站
2023-02-05 22:28:38
MySQL高可用 Galera Cluster [toc] Galera Cluster Galera Cluster:集成了Galera插件的MySQL集群,是一种新型的,数据不共享的,高度冗余的高可用方案,目前Galera Cluster有两个版本,分别是Percona Xtradb Clust ......
目录
mysql高可用 galera cluster
galera cluster
galera cluster:集成了galera插件的mysql集群,是一种新型的,数据不共享的,高度冗余的高可用方案,目前galera cluster有两个版本,分别是percona xtradb cluster及mariadb cluster,galera本身是具有多主特性的,即采用multi-master的集群架构,是一个既稳健,又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案
galera cluster特点
• 多主架构:真正的多点读写的集群,在任何时候读写数据,都是最新的 • 同步复制:集群不同节点之间数据同步,没有延迟,在数据库挂掉之后,数据不会丢失 • 并发复制:从节点apply数据时,支持并行执行,更好的性能 • 故障切换:在出现数据库故障时,因支持多点写入,切换容易 • 热插拔:在服务期间,如果数据库挂了,只要监控程序发现的够快,不可服务时间就会非常少。在节点故障期间,节点本身对集群的影响非常小 • 自动节点克隆:在新增节点,或者停机维护时,增量数据或者基础数据不需要人工手动备份提供,galera cluster会自动拉取在线节点数据,最终集群会变为一致 • 对应用透明:集群的维护,对应用程序是透明的
只支持innodb存储引擎的表
galera cluster 缺点
• 由于ddl 需全局验证通过,则集群性能由集群中最差性能节点决定(一般集群节点配置都是一样的) • 新节点加入或延后较大的节点重新加入需全量拷贝数据(sst,state snapshot transfer),作为donor( 贡献者,如: 同步数据时的提供者)的节点在同步过程中无法提供读写 • 只支持innodb存储引擎的表
galera cluster工作过程
galera cluster官方文档
http://galeracluster.com/documentation-webpages/galera-documentation.pdf
http://galeracluster.com/documentation-webpages/index.html
https://www.percona.com/doc/percona-xtradb-cluster/latest/index.html
https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/
galera cluster包括两个组件
• galera replication library (galera-3) • wsrep:mysql extended with the write set replication
wsrep复制实现
- pxc:percona xtradb cluster,是percona对galera的实现
参考仓库:
https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/rpms/$basearch
- mariadb galera cluster:
参考仓库:
https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.x/yum/centos7-amd64/
注意:两者都需要至少三个节点,不能安装mysql server 或 mariadb-server
pxc 原理
pxc最常使用如下4个端口号:
• 3306:数据库对外服务的端口号 • 4444:请求sst的端口号 • 4567:组成员之间进行沟通的端口号 • 4568:用于传输ist的端口号
pxc中涉及到的重要概念和核心参数:
- 集群中节点的数量:整个集群中节点数量应该控制在最少3个、最多8个的范围内。最少3个节点是为了防止出现脑裂现象,因为只有在2个节点下才会出现此现象。脑裂现象的标志就是输入任何命令,返回的结果都是unknown command。节点在集群中,会因新节点的加入或故障、同步失效等原因发生状态的切换。
- 节点状态的变化阶段:
• open:节点启动成功,尝试连接到集群时的状态 • primary:节点已处于集群中,在新节点加入并选取donor进行数据同步时的状态 • joiner:节点处于等待接收同步文件时的状态 • joined:节点完成数据同步工作,尝试保持和集群进度一致时的状态 • synced:节点正常提供服务时的状态,表示已经同步完成并和集群进度保持一致 • donor:节点处于为新加入的节点提供全量数据时的状态
备注:donor节点就是数据的贡献者,如果一个新节点加入集群,此时又需要大量数据的sst数据传输,就有可能因此而拖垮整个集群的性能,所以在生产环境中,如果数据量较小,还可以使用sst全量数据传输,但如果数据量很大就不建议使用这种方式,可以考虑先建立主从关系,然后再加入集群。
- 节点的数据传输方式:
• sst:state snapshot transfer,全量数据传输 • ist:incremental state transfer,增量数据传输
sst数据传输有xtrabackup、mysqldump和rsync三种方式,而增量数据传输就只有一种方式xtrabackup,但生产环境中一般数据量较小时,可以使用sst全量数据传输,但也只使用xtrabackup方法。
- gcache模块:
在pxc中一个特别重要的模块,它的核心功能就是为每个节点缓存当前最新的写集。如果有新节点加入进来,就可以把新数据的增量传递给新节点,而不需要再使用sst传输方式,这样可以让节点更快地加入集群中
- 涉及如下参数:
• gcache.size:缓存写集增量信息的大小,它的默认大小是128mb,通过wsrep_provider_options参数设置,建议调整为2gb~4gb范围,足够的空间便于缓存更多的增量信息。 • gcache.mem_size:gcache中内存缓存的大小,适度调大可以提高整个集群的性能 • gcache.page_size:如果内存不够用(gcache不足),就直接将写集写入磁盘文件中
实战案例:percona xtradb cluster(pxc 5.7)
-
环境准备
四台主机:
pxc1:192.168.39.7 pxc1:192.168.39.27 pxc1:192.168.39.37 pxc4:192.168.39.47
os版本
[root@pxc1 ~]#cat /etc/redhat-release centos linux release 7.6.1810 (core)
关闭防火墙和selinux,保证时间同步
注意:如果已经安装mysql,必须卸载 - 安装 percona xtradb cluster 5.7
- 配置yum源
[root@pxc1 ~]#vim /etc/yum.repos.d/percona_pxc.repo [percona] name=percona_repo baseurl=https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/rpms/$basearch enabled=1 gpgcheck=0
- 其他三台虚拟机也配置yum源
scp /etc/yum.repos.d/percona_pxc.repo 192.168.39.27:/etc/yum.repos.d/ scp /etc/yum.repos.d/percona_pxc.repo 192.168.39.37:/etc/yum.repos.d/ scp /etc/yum.repos.d/percona_pxc.repo 192.168.39.47:/etc/yum.repos.d/
- 在三个节点都安装好pxc 5.7
[root@pxc1 ~]#yum install percona-xtradb-cluster-57 -y [root@pxc2 ~]#yum install percona-xtradb-cluster-57 -y [root@pxc3 ~]#yum install percona-xtradb-cluster-57 -y [root@pxc4 ~]#yum install percona-xtradb-cluster-57 -y
- 在各个节点上分别配置mysql及集群配置文件
/etc/my.cnf为主配置文件,当前版本中,其余的配置文件都放在/etc/percona-xtradb-cluster.conf.d目录里,包括mysqld.cnf,mysqld_safe.cnf,wsrep.cnf 三个文件
#主配置文件不需要修改 [root@pxc1 ~]#cat /etc/my.cnf # # the percona xtradb cluster 5.7 configuration file. # # # * important: additional settings that can override those from this file! # the files must end with '.cnf', otherwise they'll be ignored. # please make any edits and changes to the appropriate sectional files # included below. # !includedir /etc/my.cnf.d/ !includedir /etc/percona-xtradb-cluster.conf.d/ [root@pxc1 ~]#ls /etc/percona-xtradb-cluster.conf.d/ # 查看一下三个文件 mysqld.cnf mysqld_safe.cnf wsrep.cnf #下面配置文件不需要修改 [root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld.cnf # template my.cnf for pxc # edit to your requirements. [client] socket=/var/lib/mysql/mysql.sock [mysqld] server-id=1 # 建议各个节点不同 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid log-bin # 建议启用,非必须项 log_slave_updates expire_logs_days=7 # disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 #下面配置文件不需要修改 [root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf # # the percona server 5.7 configuration file. # # one can use all long options that the program supports. # run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # for explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html [mysqld_safe] pid-file = /var/run/mysqld/mysqld.pid socket = /var/lib/mysql/mysql.sock nice = 0 #pxc的配置文件必须修改 [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27 #三个节点的ip binlog_format=row default_storage_engine=innodb wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=10.0.0.7 #各个节点,指定自已的ip wsrep_cluster_name=pxc-cluster # 集群名称 wsrep_node_name=pxc-cluster-node-1 #各个节点,指定自已节点名称 pxc_strict_mode=enforcing wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:centos7" #取消本行注释,可以改一个自己的密码 # 根据以上修改信息所有主机都修改 [root@pxc2 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@pxc3 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [root@pxc1 ~]#grep -ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf # 通过这个命令查看配置文件 [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.39.7,192.168.39.27,192.168.39.37 binlog_format=row default_storage_engine=innodb wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=192.168.39.7 wsrep_cluster_name=pxc-cluster wsrep_node_name=pxc-cluster-node-1 pxc_strict_mode=enforcing wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:centos7"
注意:尽管galera cluster不再需要通过binlog的形式进行同步,但还是建议在配置文件中开启二进制日志功能,原因是后期如果有新节点需要加入,老节点通过sst全量传输的方式向新节点传输数据,很可能会拖垮集群性能,所以让新节点先通过binlog方式完成同步后再加入集群会是一种更好的选择
- 配置文件各项配置意义
[root@pxc1 ~]#ss -ntul netid state recv-q send-q local address:port peer address:port udp unconn 0 0 192.168.122.1:53 *:* udp unconn 0 0 *%virbr0:67 *:* udp unconn 0 0 *:111 *:* udp unconn 0 0 *:5353 *:* udp unconn 0 0 *:58153 *:* udp unconn 0 0 *:812 *:* udp unconn 0 0 :::111 :::* udp unconn 0 0 :::812 :::* tcp listen 0 128 *:111 *:* tcp listen 0 128 *:6000 *:* tcp listen 0 5 192.168.122.1:53 *:* tcp listen 0 128 *:22 *:* tcp listen 0 128 127.0.0.1:631 *:* tcp listen 0 128 127.0.0.1:6010 *:* tcp listen 0 128 :::111 :::* tcp listen 0 128 :::6000 :::* tcp listen 0 128 :::22 :::* tcp listen 0 128 ::1:631 :::* tcp listen 0 128 ::1:6010 :::* #启动第一人节点(第一个节点启动和别的节点启动都不一样)属于创建集群的服务器现在阶段可以理解为主服务器,但是集群做完之后就没有主从之分了都是主 [root@pxc1 ~]#systemctl start mysql@bootstrap.service [root@pxc1 ~]#ss -ntul # 以下两个端口启动起来就行了 (3306、4567) 。。。。。。。。。。。。。。。。。。。。。。。 netid state recv-q send-q local address:port peer address:port udp unconn 0 0 192.168.122.1:53 *:* udp unconn 0 0 *%virbr0:67 *:* udp unconn 0 0 *:111 *:* udp unconn 0 0 *:5353 *:* udp unconn 0 0 *:58153 *:* udp unconn 0 0 *:812 *:* udp unconn 0 0 :::111 :::* udp unconn 0 0 :::812 :::* tcp listen 0 128 *:111 *:* tcp listen 0 128 *:6000 *:* tcp listen 0 5 192.168.122.1:53 *:* tcp listen 0 128 *:22 *:* tcp listen 0 128 *:4567 *:* tcp listen 0 128 127.0.0.1:631 *:* tcp listen 0 128 127.0.0.1:6010 *:* tcp listen 0 80 :::3306 :::* tcp listen 0 128 :::111 :::* tcp listen 0 128 :::6000 :::* tcp listen 0 128 :::22 :::* tcp listen 0 128 ::1:631 :::* tcp listen 0 128 ::1:6010 :::*
- 找到mysql的临时密码,第一次登录要修改要不执行不了管理操作。
[root@centos7 ~]#grep "password" /var/log/mysqld.log 2019-12-03t06:13:39.187929z 1 [note] a temporary password is generated for root@localhost: a>#;l(sm4ln: [root@centos7 ~]#mysql -uroot -p'a>#;l(sm4ln:' # 需要加引号引起来 mysql: [warning] using a password on the command line interface can be insecure. welcome to the mysql monitor. commands end with ; or \g. your mysql connection id is 11 server version: 5.7.27-30-57-log copyright (c) 2009-2019 percona llc and/or its affiliates copyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql> show databases; # 执行查看库操作提示必须重置密码 error 1820 (hy000): you must reset your password using alter user statement before executing this statement. # 在执行此语句之前,必须使用alter user语句重置密码. mysql> alter user root@'localhost' identified by 'magedu'; # 修改密码(其他节点不用修改加入集群以后会自动同步密码) query ok, 0 rows affected (0.00 sec) mysql> exit bye [root@centos7 ~]#mysql -uroot -pmagedu # 修改以后重新登录 mysql: [warning] using a password on the command line interface can be insecure. welcome to the mysql monitor. commands end with ; or \g. your mysql connection id is 12 server version: 5.7.27-30-57-log percona xtradb cluster (gpl), release rel30, revision 64987d4, wsrep version 31.39, wsrep_31.39 copyright (c) 2009-2019 percona llc and/or its affiliates copyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql>
- 查看变量
# 查看写集复制相关变量 mysql> show variables like 'wsrep%'\g *************************** 1. row *************************** variable_name: wsrep_osu_method value: toi *************************** 2. row *************************** variable_name: wsrep_rsu_commit_timeout value: 5000 *************************** 3. row *************************** variable_name: wsrep_auto_increment_control value: on *************************** 4. row *************************** variable_name: wsrep_causal_reads value: off *************************** 5. row *************************** variable_name: wsrep_certification_rules value: strict *************************** 6. row *************************** variable_name: wsrep_certify_nonpk value: on *************************** 7. row *************************** variable_name: wsrep_cluster_address value: gcomm://192.168.39.7,192.168.39.27,192.168.39.37 *************************** 8. row *************************** variable_name: wsrep_cluster_name value: pxc-cluster *************************** 9. row *************************** variable_name: wsrep_convert_lock_to_trx value: off *************************** 10. row *************************** variable_name: wsrep_data_home_dir value: /var/lib/mysql/ *************************** 11. row *************************** variable_name: wsrep_dbug_option value: *************************** 12. row *************************** variable_name: wsrep_debug value: off *************************** 13. row *************************** variable_name: wsrep_desync value: off *************************** 14. row *************************** variable_name: wsrep_dirty_reads value: off *************************** 15. row *************************** variable_name: wsrep_drupal_282555_workaround value: off *************************** 16. row *************************** variable_name: wsrep_forced_binlog_format value: none *************************** 17. row *************************** variable_name: wsrep_load_data_splitting value: on *************************** 18. row *************************** variable_name: wsrep_log_conflicts value: on *************************** 19. row *************************** variable_name: wsrep_max_ws_rows value: 0 *************************** 20. row *************************** variable_name: wsrep_max_ws_size value: 2147483647 *************************** 21. row *************************** variable_name: wsrep_node_address value: 192.168.39.7 *************************** 22. row *************************** variable_name: wsrep_node_incoming_address value: auto *************************** 23. row *************************** variable_name: wsrep_node_name value: pxc-cluster-node-1 *************************** 24. row *************************** variable_name: wsrep_notify_cmd value: *************************** 25. row *************************** variable_name: wsrep_on value: on *************************** 26. row *************************** variable_name: wsrep_preordered value: off *************************** 27. row *************************** variable_name: wsrep_provider value: /usr/lib64/galera3/libgalera_smm.so *************************** 28. row *************************** variable_name: wsrep_provider_options value: base_dir = /var/lib/mysql/; base_host = 192.168.39.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 = 10; evs.stats_report_period = pt1m; evs.suspect_timeout = pt5s; evs.use_aggregate = true; evs.user_send_window = 4; evs.version = 0; evs.view_forget_timeout = p1d; gcache.dir = /var/lib/mysql/; gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0; 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; gcs.fc_limit = 100; gcs.fc_master_slave = no; *************************** 29. row *************************** variable_name: wsrep_recover value: off *************************** 30. row *************************** variable_name: wsrep_reject_queries value: none *************************** 31. row *************************** variable_name: wsrep_replicate_myisam value: off *************************** 32. row *************************** variable_name: wsrep_restart_slave value: off *************************** 33. row *************************** variable_name: wsrep_retry_autocommit value: 1 *************************** 34. row *************************** variable_name: wsrep_slave_fk_checks value: on *************************** 35. row *************************** variable_name: wsrep_slave_uk_checks value: off *************************** 36. row *************************** variable_name: wsrep_slave_threads value: 8 *************************** 37. row *************************** variable_name: wsrep_sst_auth value: ******** *************************** 38. row *************************** variable_name: wsrep_sst_donor value: *************************** 39. row *************************** variable_name: wsrep_sst_donor_rejects_queries value: off *************************** 40. row *************************** variable_name: wsrep_sst_method value: xtrabackup-v2 *************************** 41. row *************************** variable_name: wsrep_sst_receive_address value: auto *************************** 42. row *************************** variable_name: wsrep_start_position value: 00000000-0000-0000-0000-000000000000:-1 *************************** 43. row *************************** variable_name: wsrep_sync_wait value: 0 43 rows in set (0.01 sec) # 查看相关状态变量 mysql> show status like 'wsrep%'\g *************************** 1. row *************************** variable_name: wsrep_local_state_uuid value: 10c3398e-1594-11ea-9f84-c2cae1c33555 *************************** 2. row *************************** variable_name: wsrep_protocol_version value: 9 *************************** 3. row *************************** variable_name: wsrep_last_applied value: 1 *************************** 4. row *************************** variable_name: wsrep_last_committed value: 1 *************************** 5. row *************************** variable_name: wsrep_replicated value: 1 *************************** 6. row *************************** variable_name: wsrep_replicated_bytes value: 232 *************************** 7. row *************************** variable_name: wsrep_repl_keys value: 1 *************************** 8. row *************************** variable_name: wsrep_repl_keys_bytes value: 32 *************************** 9. row *************************** variable_name: wsrep_repl_data_bytes value: 133 *************************** 10. row *************************** variable_name: wsrep_repl_other_bytes value: 0 *************************** 11. row *************************** variable_name: wsrep_received value: 2 *************************** 12. row *************************** variable_name: wsrep_received_bytes value: 154 *************************** 13. row *************************** variable_name: wsrep_local_commits value: 0 *************************** 14. row *************************** variable_name: wsrep_local_cert_failures value: 0 *************************** 15. row *************************** variable_name: wsrep_local_replays value: 0 *************************** 16. row *************************** variable_name: wsrep_local_send_queue value: 0 *************************** 17. row *************************** variable_name: wsrep_local_send_queue_max value: 1 *************************** 18. row *************************** variable_name: wsrep_local_send_queue_min value: 0 *************************** 19. row *************************** variable_name: wsrep_local_send_queue_avg value: 0.000000 *************************** 20. row *************************** variable_name: wsrep_local_recv_queue value: 0 *************************** 21. row *************************** variable_name: wsrep_local_recv_queue_max value: 2 *************************** 22. row *************************** variable_name: wsrep_local_recv_queue_min value: 0 *************************** 23. row *************************** variable_name: wsrep_local_recv_queue_avg value: 0.500000 *************************** 24. row *************************** variable_name: wsrep_local_cached_downto value: 1 *************************** 25. row *************************** variable_name: wsrep_flow_control_paused_ns value: 0 *************************** 26. row *************************** variable_name: wsrep_flow_control_paused value: 0.000000 *************************** 27. row *************************** variable_name: wsrep_flow_control_sent value: 0 *************************** 28. row *************************** variable_name: wsrep_flow_control_recv value: 0 *************************** 29. row *************************** variable_name: wsrep_flow_control_interval value: [ 100, 100 ] *************************** 30. row *************************** variable_name: wsrep_flow_control_interval_low value: 100 *************************** 31. row *************************** variable_name: wsrep_flow_control_interval_high value: 100 *************************** 32. row *************************** variable_name: wsrep_flow_control_status value: off *************************** 33. row *************************** variable_name: wsrep_cert_deps_distance value: 1.000000 *************************** 34. row *************************** variable_name: wsrep_apply_oooe value: 0.000000 *************************** 35. row *************************** variable_name: wsrep_apply_oool value: 0.000000 *************************** 36. row *************************** variable_name: wsrep_apply_window value: 1.000000 *************************** 37. row *************************** variable_name: wsrep_commit_oooe value: 0.000000 *************************** 38. row *************************** variable_name: wsrep_commit_oool value: 0.000000 *************************** 39. row *************************** variable_name: wsrep_commit_window value: 1.000000 *************************** 40. row *************************** variable_name: wsrep_local_state value: 4 *************************** 41. row *************************** variable_name: wsrep_local_state_comment value: synced *************************** 42. row *************************** variable_name: wsrep_cert_index_size value: 1 *************************** 43. row *************************** variable_name: wsrep_cert_bucket_count value: 22 *************************** 44. row *************************** variable_name: wsrep_gcache_pool_size value: 1592 *************************** 45. row *************************** variable_name: wsrep_causal_reads value: 0 *************************** 46. row *************************** variable_name: wsrep_cert_interval value: 0.000000 *************************** 47. row *************************** variable_name: wsrep_open_transactions value: 0 *************************** 48. row *************************** variable_name: wsrep_open_connections value: 0 *************************** 49. row *************************** variable_name: wsrep_ist_receive_status value: *************************** 50. row *************************** variable_name: wsrep_ist_receive_seqno_start value: 0 *************************** 51. row *************************** variable_name: wsrep_ist_receive_seqno_current value: 0 *************************** 52. row *************************** variable_name: wsrep_ist_receive_seqno_end value: 0 *************************** 53. row *************************** variable_name: wsrep_incoming_addresses value: 192.168.39.7:3306 *************************** 54. row *************************** variable_name: wsrep_cluster_weight value: 1 *************************** 55. row *************************** variable_name: wsrep_desync_count value: 0 *************************** 56. row *************************** variable_name: wsrep_evs_delayed value: *************************** 57. row *************************** variable_name: wsrep_evs_evict_list value: *************************** 58. row *************************** variable_name: wsrep_evs_repl_latency value: 0/0/0/0/0 *************************** 59. row *************************** variable_name: wsrep_evs_state value: operational *************************** 60. row *************************** variable_name: wsrep_gcomm_uuid value: 10c2c674-1594-11ea-83fc-6e0df986585c *************************** 61. row *************************** variable_name: wsrep_cluster_conf_id value: 1 *************************** 62. row *************************** variable_name: wsrep_cluster_size value: 1 *************************** 63. row *************************** variable_name: wsrep_cluster_state_uuid value: 10c3398e-1594-11ea-9f84-c2cae1c33555 *************************** 64. row *************************** variable_name: wsrep_cluster_status value: primary *************************** 65. row *************************** variable_name: wsrep_connected value: on *************************** 66. row *************************** variable_name: wsrep_local_bf_aborts value: 0 *************************** 67. row *************************** variable_name: wsrep_local_index value: 0 *************************** 68. row *************************** variable_name: wsrep_provider_name value: galera *************************** 69. row *************************** variable_name: wsrep_provider_vendor value: codership oy <info@codership.com> *************************** 70. row *************************** variable_name: wsrep_provider_version value: 3.39(rb3295e6) *************************** 71. row *************************** variable_name: wsrep_ready value: on 71 rows in set (0.00 sec) #重点内容 mysql> show status like 'wsrep%'; +----------------------------------+--------------------------------------+ | variable_name | value | +----------------------------------+--------------------------------------+ | wsrep_local_state_uuid | 10c3398e-1594-11ea-9f84-c2cae1c33555 | | wsrep_protocol_version | 9 | | wsrep_last_applied | 1 | | wsrep_last_committed | 1 | | wsrep_replicated | 1 | | wsrep_replicated_bytes | 232 | | wsrep_repl_keys | 1 | | wsrep_repl_keys_bytes | 32 | | wsrep_repl_data_bytes | 133 | | wsrep_repl_other_bytes | 0 | | wsrep_received | 2 | | wsrep_received_bytes | 154 | | wsrep_local_commits | 0 | | wsrep_local_cert_failures | 0 | | wsrep_local_replays | 0 | | wsrep_local_send_queue | 0 | | wsrep_local_send_queue_max | 1 | | wsrep_local_send_queue_min | 0 | | wsrep_local_send_queue_avg | 0.000000 | | wsrep_local_recv_queue | 0 | | wsrep_local_recv_queue_max | 2 | | wsrep_local_recv_queue_min | 0 | | wsrep_local_recv_queue_avg | 0.500000 | | wsrep_local_cached_downto | 1 | | wsrep_flow_control_paused_ns | 0 | | wsrep_flow_control_paused | 0.000000 | | wsrep_flow_control_sent | 0 | | wsrep_flow_control_recv | 0 | | wsrep_flow_control_interval | [ 100, 100 ] | | wsrep_flow_control_interval_low | 100 | | wsrep_flow_control_interval_high | 100 | | wsrep_flow_control_status | off | | wsrep_cert_deps_distance | 1.000000 | | wsrep_apply_oooe | 0.000000 | | wsrep_apply_oool | 0.000000 | | wsrep_apply_window | 1.000000 | | wsrep_commit_oooe | 0.000000 | | wsrep_commit_oool | 0.000000 | | wsrep_commit_window | 1.000000 | | wsrep_local_state | 4 | | wsrep_local_state_comment | synced | | wsrep_cert_index_size | 1 | | wsrep_cert_bucket_count | 22 | | wsrep_gcache_pool_size | 1592 | | wsrep_causal_reads | 0 | | wsrep_cert_interval | 0.000000 | | wsrep_open_transactions | 0 | | wsrep_open_connections | 0 | | wsrep_ist_receive_status | | | wsrep_ist_receive_seqno_start | 0 | | wsrep_ist_receive_seqno_current | 0 | | wsrep_ist_receive_seqno_end | 0 | | wsrep_incoming_addresses | 192.168.39.7:3306 | | wsrep_cluster_weight | 1 | | wsrep_desync_count | 0 | | wsrep_evs_delayed | | | wsrep_evs_evict_list | | | wsrep_evs_repl_latency | 0/0/0/0/0 | | wsrep_evs_state | operational | | wsrep_gcomm_uuid | 10c2c674-1594-11ea-83fc-6e0df986585c | | wsrep_cluster_conf_id | 1 | | wsrep_cluster_size | 1 | | wsrep_cluster_state_uuid | 10c3398e-1594-11ea-9f84-c2cae1c33555 | | wsrep_cluster_status | primary | | wsrep_connected | on | | wsrep_local_bf_aborts | 0 | | wsrep_local_index | 0 | | wsrep_provider_name | galera | | wsrep_provider_vendor | codership oy <info@codership.com> | | wsrep_provider_version | 3.39(rb3295e6) | | wsrep_ready | on | +----------------------------------+--------------------------------------+ 71 rows in set (0.00 sec)
说明: wsrep_cluster_size表示,该galera集群中只有一个节点 wsrep_local_state_comment 状态为synced(4),表示数据已同步完成(因为是第一个引导节点,无数据需要同步)。 如果状态是joiner, 意味着 sst 没有完成. 只有所有节点状态是synced,才可以加新节点 wsrep_cluster_status为primary,且已经完全连接并准备好
启动pxc集群中其它所有节点
- 启动pxc集群中其它所有节点
# 启动前在第一个节点上创建一个授权账户(要不然可能别的节点没有授权数据库会起不来) mysql> grant reload, lock tables, process, replication client on *.* to 'sstuser'@'localhost' identified by 'centos7'; # 这里授权的密码必须和配置文件里设置的密码对应,所有服务器都要一样。 query ok, 0 rows affected, 1 warning (0.00 sec) [root@pxc2 etc]#ss -ntl # 启动前端口情况 state recv-q send-q local address:port peer address:port listen 0 128 *:111 *:* listen 0 128 *:6000 *:* listen 0 5 192.168.122.1:53 *:* listen 0 128 *:22 *:* listen 0 128 127.0.0.1:631 *:* listen 0 128 127.0.0.1:6010 *:* listen 0 128 :::111 :::* listen 0 128 :::6000 :::* listen 0 128 :::22 :::* listen 0 128 ::1:631 :::* listen 0 128 ::1:6010 :::* [root@pxc2 etc]#systemctl start mysql # 启动数据库 [root@pxc2 etc]#ss -ntl # 启动后 3306、4567 端口都出现了 state recv-q send-q local address:port peer address:port listen 0 128 *:111 *:* listen 0 128 *:6000 *:* listen 0 5 192.168.122.1:53 *:* listen 0 128 *:22 *:* listen 0 128 *:4567 *:* listen 0 128 127.0.0.1:631 *:* listen 0 128 127.0.0.1:6010 *:* listen 0 128 127.0.0.1:6011 *:* listen 0 80 :::3306 :::* listen 0 128 :::111 :::* listen 0 128 :::6000 :::* listen 0 128 :::22 :::* listen 0 128 ::1:631 :::* listen 0 128 ::1:6010 :::* listen 0 128 ::1:6011 :::* [root@pxc2 etc]#mysql -uroot -pmagedu # 登录数据库 mysql: [warning] using a password on the command line interface can be insecure. welcome to the mysql monitor. commands end with ; or \g. your mysql connection id is 11 server version: 5.7.27-30-57-log percona xtradb cluster (gpl), release rel30, revision 64987d4, wsrep version 31.39, wsrep_31.39 copyright (c) 2009-2019 percona llc and/or its affiliates copyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql> # 另一个节点一样启动 [root@pxc3 etc]#systemctl start mysql # 启动数据库 [root@pxc3 ~]#mysql -uroot -pmagedu mysql: [warning] using a password on the command line interface can be insecure. welcome to the mysql monitor. commands end with ; or \g. your mysql connection id is 11 server version: 5.7.27-30-57-log percona xtradb cluster (gpl), release rel30, revision 64987d4, wsrep version 31.39, wsrep_31.39 copyright (c) 2009-2019 percona llc and/or its affiliates copyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql>
- 查看集群状态,验证集群是否成功
# 在任意节点,查看集群状态 mysql> show variables like 'wsrep_node_name'; +-----------------+--------------------+ | variable_name | value | +-----------------+--------------------+ | wsrep_node_name | pxc-cluster-node-1 | +-----------------+--------------------+ 1 row in set (0.00 sec) mysql> show variables like 'wsrep_node_address'; +--------------------+--------------+ | variable_name | value | +--------------------+--------------+ | wsrep_node_address | 192.168.39.7 | +--------------------+--------------+ 1 row in set (0.01 sec) mysql> show variables like 'wsrep_on'; +---------------+-------+ | variable_name | value | +---------------+-------+ | wsrep_on | on | +---------------+-------+ 1 row in set (0.00 sec) mysql> show status like 'wsrep_cluster_size'; +--------------------+-------+ | variable_name | value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.00 sec) # 在任意节点创建数据库测试(pxc2) mysql> show variables like 'wsrep_node_address'; +--------------------+---------------+ | variable_name | value | +--------------------+---------------+ | wsrep_node_address | 192.168.39.27 | +--------------------+---------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> create database db1; query ok, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) # 在pxc1上查看 mysql> show variables like 'wsrep_node_address'; +--------------------+--------------+ | variable_name | value | +--------------------+--------------+ | wsrep_node_address | 192.168.39.7 | +--------------------+--------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) # 在pxc2上查看 mysql> show variables like 'wsrep_node_address'; +--------------------+---------------+ | variable_name | value | +--------------------+---------------+ | wsrep_node_address | 192.168.39.37 | +--------------------+---------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | db1 | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) # 两个节点都可以看到同步成功
- 测试是否有冲突问题
#利用xshell软件,同时在三个节点数据库,在其中一个节点成功 mysql> create database db2; # 只有一个节点会成功其他节点报错不创建 query ok, 1 row affected (0.00 sec) mysql> create database db2; error 1007 (hy000): can't create database 'db2'; database exists mysql> create database db2; error 1007 (hy000): can't create database 'db2'; database exists
- pxc缺点(同时写入数据性能会有影响)
# 使用一个命令循环创建10w条记录 mysql> create table test (id int auto_increment primary key,name char(10)); delimiter ; query ok, 0 rows affected (0.01 sec) mysql> mysql> delimiter $$ mysql> mysql> create procedure proc_test() -> begin -> declare i int; -> set i = 1; -> while i < 100000 -> do insert into test(name) values (concat('wang',i)); -> set i = i +1; -> end while; -> end$$ query ok, 0 rows affected (0.01 sec) mysql> mysql> delimiter ; mysql> call proc_test(); query ok, 1 row affected (3 min 39.51 sec) # 用了39s接近40s时间才添加完成 # 使用事务方式添加记录还是很快的 mysql> begin; query ok, 0 rows affected (0.00 sec) mysql> call proc_test(); query ok, 1 row affected (2.44 sec) mysql> commit; query ok, 0 rows affected (0.25 sec)
- 添加记录的话每个节点添加id变化
# 每个节点添加记录可重复。(是自动增长的) mysql> insert t1(name)values('mage'); query ok, 1 row affected (0.01 sec) mysql> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | mage | | 4 | magea | | 7 | mageb | | 10 | mage | +----+-------+ 4 rows in set (0.00 sec) # 2 节点添加 mysql> insert t1(name)values('mage'); query ok, 1 row affected (0.00 sec) mysql> insert t1(name)values('mage'); query ok, 1 row affected (0.00 sec) mysql> insert t1(name)values('mage'); query ok, 1 row affected (0.00 sec) mysql> select * from t1; +----+-------+ | id | name | +----+-------+ | 1 | mage | | 4 | magea | | 7 | mageb | | 10 | mage | | 11 | mage | | 14 | mage | | 17 | mage | +----+-------+ 7 rows in set (0.00 sec)
在pxc集群中加入节点
一个节点加入到galera集群有两种情况:新节点加入集群、暂时离组的成员再次加入集群 1)新节点加入galera集群 新节点加入集群时,需要从当前集群中选择一个donor节点来同步数据,也就是所谓state_snapshot_tranfer(sst)过程。sst同步数据的方式由选项wsrep_sst_method决定,一般选择的是xtrabackup。 必须注意,新节点加入galera时,会删除新节点上所有已有数据,再通过xtrabackup(假设使用的是该方式)从donor处完整备份所有数据进行恢复。所以,如果数据量很大,新节点加入过程会很慢。而且,在一个新节点成为synced状态之前,不要同时加入其它新节点,否则很容易将集群压垮。如果是这种情况,可以考虑使用wsrep_sst_method=rsync来做增量同步,既然是增量同步,最好保证新节点上已经有一部分数据基础,否则和全量同步没什么区别,且这样会对donor节点加上全局readonly锁。 2)旧节点加入galera集群 如果旧节点加入galera集群,说明这个节点在之前已经在galera集群中呆过,有一部分数据基础,缺少的只是它离开集群时的数据。这时加入集群时,会采用ist(incremental snapshot transfer)传输机制,即使用增量传输。
# 同样先配置yum源 # 在安装数据库 [root@pxc4 ~]#yum install percona-xtradb-cluster-57 -y # 修改配置文件 [root@pxc4 ~]#grep -ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf [mysqld] wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_address=gcomm://192.168.39.7,192.168.39.27,192.168.39.37,192.168.39.47 # 这一行添加上新节点的ip(虽然其他节点配置文件不添加也可以,但是最好都加上,比如说服务器依次关机到新添加的节点(新添加的节点留为最后的引导服务器),但是下次开机别的节点会找不到新添加的服务器,数据库就起不来了) binlog_format=row default_storage_engine=innodb wsrep_slave_threads= 8 wsrep_log_conflicts innodb_autoinc_lock_mode=2 wsrep_node_address=192.168.39.47 wsrep_cluster_name=pxc-cluster wsrep_node_name=pxc-cluster-node-4 pxc_strict_mode=enforcing wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth="sstuser:centos7" # 查看连接数以变为4个 mysql> show status like 'wsrep_cluster_size'\g *************************** 1. row *************************** variable_name: wsrep_cluster_size value: 4 1 row in set (0.00 sec)
在pxc集群中修复故障节点
- 在其他节点停止服务
[root@pxc2 ~]#systemctl stop mysql [root@pxc2 ~]#systemctl status mysql ● mysql.service - percona xtradb cluster loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; vendor preset: disabled) active: inactive (dead) since tue 2019-12-03 16:52:51 cst; 3min 21s ago process: 29259 execstoppost=/usr/bin/mysql-systemd stop-post (code=exited, status=0/success) process: 29221 execstop=/usr/bin/mysql-systemd stop (code=exited, status=0/success) process: 26928 execstartpost=/usr/bin/mysql-systemd start-post $mainpid (code=exited, status=0/success) process: 26925 execstart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/success) process: 26853 execstartpre=/usr/bin/mysql-systemd start-pre (code=exited, status=0/success) main pid: 26925 (code=exited, status=0/success) dec 03 15:12:54 pxc2 mysqld_safe[26925]: 2019-12-03t07:12:54.344288z mysqld_safe assigning 00000000-0000-0000-0000-000000000000:-1 to wsrep_start_position dec 03 15:12:56 pxc2 mysql-systemd[26928]: state transfer in progress, setting sleep higher dec 03 15:13:16 pxc2 mysql-systemd[26928]: success! dec 03 15:13:16 pxc2 systemd[1]: started percona xtradb cluster. dec 03 16:52:39 centos7.localdomain systemd[1]: stopping percona xtradb cluster... dec 03 16:52:39 centos7.localdomain mysql-systemd[29221]: success! stopping percona xtradb cluster...... dec 03 16:52:51 centos7.localdomain mysqld_safe[26925]: 2019-12-03t08:52:51.720383z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended dec 03 16:52:51 centos7.localdomain mysql-systemd[29259]: warning: mysql pid file /var/run/mysqld/mysqld.pid empty or not readable dec 03 16:52:51 centos7.localdomain mysql-systemd[29259]: warning: mysql may be already dead dec 03 16:52:51 centos7.localdomain systemd[1]: stopped percona xtradb cluster.
- 在其它任意节点查看wsrep_cluster_size变量
[root@centos7 ~]#mysql -uroot -pmagedu mysql: [warning] using a password on the command line interface can be insecure. welcome to the mysql monitor. commands end with ; or \g. your mysql connection id is 21 server version: 5.7.27-30-57-log percona xtradb cluster (gpl), release rel30, revision 64987d4, wsrep version 31.39, wsrep_31.39 copyright (c) 2009-2019 percona llc and/or its affiliates copyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql> show status like 'wsrep_cluster_size'\g *************************** 1. row *************************** variable_name: wsrep_cluster_size value: 3 # 少了一个节点 1 row in set (0.00 sec)
- 添加一个数据库查看同步情况
mysql> create database db4; query ok, 1 row affected (0.01 sec) #在其它任意节点可看到数据已同步 mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | mysql | | performance_schema | | sys | +--------------------+ 8 rows in set (0.00 sec) # 其他节点正常同步
- 恢复服务,数据同步
[root@pxc2 ~]#systemctl start mysql [root@pxc2 ~]#mysql -uroot -pmagedu mysql: [warning] using a password on the command line interface can be insecure. welcome to the mysql monitor. commands end with ; or \g. your mysql connection id is 11 server version: 5.7.27-30-57-log percona xtradb cluster (gpl), release rel30, revision 64987d4, wsrep version 31.39, wsrep_31.39 copyright (c) 2009-2019 percona llc and/or its affiliates copyright (c) 2000, 2019, oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of oracle corporation and/or its affiliates. other names may be trademarks of their respective owners. type 'help;' or '\h' for help. type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | database | +--------------------+ | information_schema | | db1 | | db2 | | db3 | | db4 | | mysql | | performance_schema | | sys | +--------------------+ 8 rows in set (0.00 sec) # 恢复服务之后数据还是能同步的 mysql> show status like 'wsrep_cluster_size'; +--------------------+-------+ | variable_name | value | +--------------------+-------+ | wsrep_cluster_size | 4 | +--------------------+-------+ 1 row in set (0.00 sec)
实现mariadb galera cluster
#在三个节点上都实现 [root@centos8 ~]#dnf install mariadb-server-galera -y [root@centos8 ~]#vim /etc/my.cnf.d/galera.cnf # 修改配置文件 #wsrep_cluster_address="dummy://" wsrep_cluster_address="gcomm://192.168.39.7,192.168.39.27,192.168.39.37" #启动第一节点 [root@centos8 ~]#galera_new_cluster #再启动其它节点 [root@centos8 ~]#systemctl start mariadb [root@centos8 ~]#ss -ntul netid state recv-q send-q local address:port peer address:port tcp listen 0 128 0.0.0.0:22 0.0.0.0:* tcp listen 0 128 0.0.0.0:4567 0.0.0.0:* tcp listen 0 80 0.0.0.0:3306 0.0.0.0:* tcp listen 0 128 [::]:22 [::]:* [root@centos8 ~]#mysql welcome to the mariadb monitor. commands end with ; or \g. your mariadb connection id is 11 server version: 10.3.11-mariadb mariadb server 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)]> mariadb [(none)]> show status like "wsrep_ready"; +---------------+-------+ | variable_name | value | +---------------+-------+ | wsrep_ready | on | +---------------+-------+ 1 row in set (0.001 sec) mariadb [(none)]> show status like 'wsrep_cluster_size'; +--------------------+-------+ | variable_name | value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.001 sec) mariadb [(none)]> show variables like 'wsrep_%'\g mariadb [(none)]> show status like 'wsrep_%';
复制的问题和解决方案
复制的问题和解决方案: (1) 数据损坏或丢失 master: mha + semi repl slave: 重新复制 (2) 混合使用存储引擎 myisam:不支持事务 innodb: 支持事务 (3) 不惟一的server id 重新复制 (4) 复制延迟 需要额外的监控工具的辅助 一从多主:mariadb10版后支持 多线程复制:对多个数据库复制
性能衡量指标
数据库服务衡量指标:
qps:query per second tps:transaction per second
压力测试工具
常见mysql压力测试工具
mysqlslap sysbench:功能强大 https://github.com/akopytov/sysbench tpcc-mysql mysql benchmark suite mysql super-smack mybench mysql压力测试
mysqlslap
mysqlslap:来自于mariadb包,测试的过程默认生成一个mysqlslap的schema,生成测试表t1,查询和插入测试数据,mysqlslap库自动生成,如果已经存在则先删除。用--only-print来打印实际的测试过程,整个测试完成后不会在数据库中留下痕迹。
- 使用格式:
mysqlslap [options]
常用参数 [options] 说明:
--auto-generate-sql, -a #自动生成测试表和数据,表示用mysqlslap工具自己生成的sql脚本来测试 并发压力 --auto-generate-sql-load-type=type #测试语句的类型。代表要测试的环境是读操作还是写操作还 是两者混合的。取值包括:read,key,write,update和mixed(默认) --auto-generate-sql-add-auto-increment #代表对生成的表自动添加auto_increment列,从 5.1.18版本开始支持 --number-char-cols=n, -x n #自动生成的测试表中包含多少个字符类型的列,默认1 --number-int-cols=n, -y n #自动生成的测试表中包含多少个数字类型的列,默认1 --number-of-queries=n #总的测试查询次数(并发客户数×每客户查询次数) --query=name,-q #使用自定义脚本执行测试,例如可以调用自定义的存储过程或者sql语句来执行测试 --create-schema #代表自定义的测试库名称,测试的schema --commint=n #多少条dml后提交一次 --compress, -c #如服务器和客户端都支持压缩,则压缩信息 --concurrency=n, -c n #表示并发量,即模拟多少个客户端同时执行select。可指定多个值,以逗号 或者--delimiter参数指定值做为分隔符,如:--concurrency=100,200,500 --engine=engine_name, -e engine_name #代表要测试的引擎,可以有多个,用分隔符隔开。例 如:--engines=myisam,innodb --iterations=n, -i n #测试执行的迭代次数,代表要在不同并发环境下,各自运行测试多少次 --only-print #只打印测试语句而不实际执行。 --detach=n #执行n条语句后断开重连 --debug-info, -t #打印内存和cp
mysqlslap示例
#单线程测试 mysqlslap -a -uroot -pmagedu #多线程测试。使用--concurrency来模拟并发连接 mysqlslap -a -c 100 -uroot -pmagedu #迭代测试。用于需要多次执行测试得到平均值 mysqlslap -a -i 10 -uroot -pmagedu mysqlslap ---auto-generate-sql-add-autoincrement -a mysqlslap -a --auto-generate-sql-load-type=read mysqlslap -a --auto-generate-secondary-indexes=3 mysqlslap -a --auto-generate-sql-write-number=1000 mysqlslap --create-schema world -q "select count(*) from city" mysqlslap -a -e innodb -uroot -pmagedu mysqlslap -a --number-of-queries=10 -uroot -pmagedu #测试同时不同的存储引擎的性能进行对比 mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 -- engine=myisam,innodb --debug-info -uroot -pmagedu #执行一次测试,分别50和100个并发,执行1000次总查询 mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot - pmagedu #50和100个并发分别得到一次测试结果(benchmark),并发数越多,执行完所有查询的时间越长。为了准确 起见,可以多迭代测试几次 mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 -- debug-info -uroot -pmagedu
生产环境 my.cnf 配置案例
- 硬件:内存32g
#打开独立表空间 innodb_file_per_table = 1 #mysql 服务所允许的同时会话数的上限,经常出现too many connections的错误提示,则需要增大此值 max_connections = 8000 #所有线程所打开表的数量 open_files_limit = 10240 #back_log 是操作系统在监听队列中所能保持的连接数 back_log = 300 #每个客户端连接最大的错误允许数量,当超过该次数,mysql服务器将禁止此主机的连接请求,直到mysql 服务器重启或通过flush hosts命令清空此主机的相关信息 max_connect_errors = 1000 #每个连接传输数据大小.最大1g,须是1024的倍数,一般设为最大的blob的值 max_allowed_packet = 32m #指定一个请求的最大连接时间 wait_timeout = 10 # 排序缓冲被用来处理类似order by以及group by队列所引起的排序 sort_buffer_size = 16m #不带索引的全表扫描.使用的buffer的最小值 join_buffer_size = 16m #查询缓冲大小 query_cache_size = 128m #指定单个查询能够使用的缓冲区大小,缺省为1m query_cache_limit = 4m # 设定默认的事务隔离级别 transaction_isolation = repeatable-read # 线程使用的堆大小. 此值限制内存中能处理的存储过程的递归深度和sql语句复杂性,此容量的内存在每次 连接时被预留. thread_stack = 512k # 二进制日志功能 log-bin #二进制日志格式 binlog_format=row #innodb使用一个缓冲池来保存索引和原始数据, 可设置这个变量到物理内存大小的80% innodb_buffer_pool_size = 24g #用来同步io操作的io线程的数量 innodb_file_io_threads = 4 #在innodb核心内的允许线程数量,建议的设置是cpu数量加上磁盘数量的两倍 innodb_thread_concurrency = 16 # 用来缓冲日志数据的缓冲区的大小 innodb_log_buffer_size = 16m 在日志组中每个日志文件的大小 innodb_log_file_size = 512m # 在日志组中的文件总数 innodb_log_files_in_group = 3 # sql语句在被回滚前,innodb事务等待innodb行锁的时间 innodb_lock_wait_timeout = 120 #慢查询时长 long_query_time = 2 #将没有使用索引的查询也记录下来 log-queries-not-using-indexes
mysql配置最佳实践
高并发大数据的互联网业务,架构设计思路是“解放数据库cpu,将计算转移到服务层”,并发量大的情况下,这些功能很可能将数据库拖死,业务逻辑放到服务层具备更好的扩展性,能够轻易实现“增机器就加性能”
- 参考:
阿里巴巴java开发手册
58到家数据库30条军规解读
http://zhuanlan.51cto.com/art/201702/531364.htm
上一篇: 16GB内存一根卖3000 苹果确定不是在卖金条?
下一篇: 小米手机“隐藏”的四个功能:铁粉才知道