MySQL Scale out-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

MySQL Scale out

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:15 PM

mechanismRole

简介

MySQL复制中较常见的复制架构有“一主一从”、“一主多从”、“双主”、“多级复制”和“多主环形机构”等，见下图；

最常用，也最灵活的就要数“一主多从”复制架构了，其能满足多种需求，如：

为不同的角色使用不同的备库(例如添加不同的索引或使用不同的存储引擎)；
把一台备库当做待用的主库，除了复制没有其它数据传输；
将一台备库放在远程数据中心，用作灾难恢复；
延迟一个或多个备库，以备灾难恢复；
使用其中一个备库，作为备份、培训、开发或者测试使用服务器；

而“双主”复制架构则用于特殊的场景下，如两个处于不同地理位置的办公室，且都需要一份可写的数据拷贝；

这种架构最大的问题是如何解决数据冲突和不一致，尤其当两台服务器同时修改同一行记录，或同时在两台服务器上向一个包含auto_increment列的表里插入数据时；

而通过将一台服务器设置为只读的被动服务器，则可以很好的避免数据写入冲突的问题，这种主动-被动模式下的主-主复制架构使得反复切换主动和被动服务器非常方便，可以实现在不关闭服务器的情况下执行维护、优化表、升级操作系统或其他任务；

配置主动-被动模式的主-主复制架构的一般流程:

确保两台服务器上有相同的数据；
启用二进制日志，选择唯一的服务器ID，并创建复制账号；
启用备库更新的日志记录，这是故障转移和故障恢复的关键；
把被动服务器配置成只读，防止可能与主动服务器上的更新产生冲突；
启动每个服务器的MySQL实例；
将每个主库设置为对方的备库，使用新创建的二进制日志开始工作；

同时为了消除不同地理位置的站点单点故障问题，可以为每个主库增加冗余，即为每一个主库增加一个从库;

而MMM(=Master-Master Replication Manager for MySQL)则是一套脚本集合，用以监控、管理双主复制架构，通过设置一个可写的VIP和多个只读的VIP，完成故障自动转移、读负载分摊等功能；

架构设计

服务器规划

虚IP规划

配置部署

双主复制架构部署

MySQL或MariaDB的安装初始化可详见博客“MySQL架构”

利用mysqld_multi在一台主机上启动多个mysqld实例

数据库初始化

# 在主机Host1和Host2上cd /usr/local/mysqlscripts/mysql_install_db --user=mysql --datadir=/data/mariadb_data_3406/scripts/mysql_install_db --user=mysql --datadir=/data/mariadb_data_3506/

数据库配置

# 在主机Host1上vi /etc/my.cnf[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladmin[mysqld1]port = 3406socket = /tmp/mysql3406.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 2datadir = /data/mariadb_data_3406innodb_file_per_table = 1default_storage_engine = InnoDBlog-bin=mysql-binrelay-log=/data/relaylogs_3406/relay-bin # 指定中继日志路径log_slave_updates=1 # 开启从库更新操作写入二进制日志功能auto_increment_increment=2 # 双主复制中自增长字段的步长auto_increment_offset=1 # 双主复制中自增长字段的起始值，此为1sync_binlog = 1 # 可保证事务日志及时写入磁盘文件binlog_format=rowserver-id = 11# 注意server-id的唯一性[mysqld2]port = 3506socket = /tmp/mysql3506.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 2datadir = /data/mariadb_data_3506innodb_file_per_table = 1default_storage_engine = InnoDBlog-bin=mysql-binrelay-log=/data/relaylogs_3506/relay-binlog_slave_updates=1sync_binlog = 1binlog_format=rowserver-id = 12# 在主机Host2上vi /etc/my.cnf[mysqld_multi]mysqld = /usr/local/mysql/bin/mysqld_safemysqladmin = /usr/local/mysql/bin/mysqladmin[mysqld1]port = 3406socket = /tmp/mysql3406.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 2datadir = /data/mariadb_data_3406innodb_file_per_table = 1default_storage_engine = InnoDBlog-bin=mysql-binrelay-log=/data/relaylogs_3406/relay-binlog_slave_updates=1auto_increment_increment=2 # # 双主复制中自增长字段的步长auto_increment_offset=2 # 双主复制中自增长字段的起始值，此为2sync_binlog = 1binlog_format=rowserver-id = 21[mysqld2]port = 3506socket = /tmp/mysql3506.sockskip-external-lockingkey_buffer_size = 256Mmax_allowed_packet = 1Mtable_open_cache = 256sort_buffer_size = 1Mread_buffer_size = 1Mread_rnd_buffer_size = 4Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size= 16Mthread_concurrency = 2datadir = /data/mariadb_data_3506innodb_file_per_table = 1default_storage_engine = InnoDBlog-bin=mysql-binrelay-log=/data/relaylogs_3506/relay-binlog_slave_updates=1sync_binlog = 1binlog_format=rowserver-id = 22

启动数据库实例

# 在主机Host1和Host2上/etc/init.d/mysqld_multi start 1 # 停止服务操作是/etc/init.d/mysqld_multi stop 1/etc/init.d/mysqld_multi start 2 # 停止服务操作是/etc/init.d/mysqld_multi stop 2

登录数据库

# 在主机Host1和Host2上mysql -S /tmp/mysql3406.sock # 登录master1或master2mysql -S /tmp/mysql3506.sock # 登录slave1或slave2

创建所需账户(在Master1实例上)

grant replication client on *.* to '3m_moni'@'192.168.0.%' identified by '3m_12345'; # 创建MMM的监控账户grant super,replication client,process on *.* to '3m_agen'@'192.168.0.%' identified by '3m_12345'; # 创建MMM的代理账户grant replication slave on *.* to '3m_repl'@'192.168.0.%' identified by '3m_12345'; # 创建复制账户

配置数据同步

# 每次从库连接主库前，需先查询对应主库的二进制日志文件及其事件位置，即在主库上执行show master status即可，据此决定从库连接时的master_log_file和master_log_pos参数；# slave1实例上change master to master_host='192.168.0.45',master_port=3406,master_user='3m_repl',master_password='3m_12345',master_log_file='mysql-bin.000001',master_log_pos=2448;# master2实例上change master to master_host='192.168.0.45',master_port=3406,master_user='3m_repl',master_password='3m_12345',master_log_file='mysql-bin.000002',master_log_pos=365;# slave2实例上change master to master_host='192.168.0.46',master_port=3406,master_user='3m_repl',master_password='3m_12345',master_log_file='mysql-bin.000004',master_log_pos=342;# master1实例上change master to master_host='192.168.0.46',master_port=3406,master_user='3m_repl',master_password='3m_12345',master_log_file='mysql-bin.000004',master_log_pos=342;

查看同步状态

# 重点检查Slave_IO_Running、Slave_SQL_Running和Master_Server_Id等参数MariaDB [(none)]> show slave status/G*************************** 1. row ***************************					Slave_IO_State: Waiting for master to send event						Master_Host: 192.168.0.45						Master_User: 3m_repl						Master_Port: 3406					 Connect_Retry: 60				Master_Log_File: mysql-bin.000005			 Read_Master_Log_Pos: 326					Relay_Log_File: relay-bin.000010					 Relay_Log_Pos: 613		Relay_Master_Log_File: mysql-bin.000005				 Slave_IO_Running: Yes				Slave_SQL_Running: Yes				 Master_Server_Id: 11

MMM安装部署

Host1主机上：部署agent和monitor

yum -y install mysql-mmm-*# 配置公共设置vi /etc/mysql-mmm/mmm_common.confactive_master_role	writer<host default>	cluster_interface	 eth0	pid_path				/var/run/mysql-mmm/mmm_agentd.pid	bin_path				/usr/libexec/mysql-mmm/	replication_user		3m_repl# 复制账户	replication_password	3m_12345# 复制账户密码	agent_user			3m_agen# agent账户	agent_password		3m_12345# agent账户密码</host><host db1>	ip	192.168.0.45	mysql_port 3406# 可指定需连接的mysqld的端口	mode	master	peer	db2# peer表示db1、db2是同等级别的</host><host db2>	ip	192.168.0.46	mysql_port 3406	mode	master	peer	db1</host><host db3>	ip	192.168.0.45	mysql_port 3506	mode	slave</host><host db4>	ip	192.168.0.46	mysql_port 3506	mode	slave</host><role writer>	hosts db1, db2	ips	 192.168.0.11# 可写VIP只配置一个	mode	exclusive# 表示排它</role><role reader>	hosts db1, db2,db3,db4	ips	 192.168.0.12,192.168.0.13,192.168.0.14,192.168.0.15 # 只读VIP可配置多个	mode	balanced# 表示可以共用</role>==========scp mmm_common.conf 192.168.0.46:/etc/mysql-mmm/ # 将公共配置文件拷贝至其它主机==========# 配置监控设置vi /etc/mysql-mmm/mmm_mon.confinclude mmm_common.conf<monitor>	ip				127.0.0.1	pid_path			/var/run/mysql-mmm/mmm_mond.pid	bin_path			/usr/libexec/mysql-mmm	status_path		 /var/lib/mysql-mmm/mmm_mond.status	ping_ips			192.168.0.45,192.168.0.46# 健康监测时需ping的主机IP，不是VIP哦	auto_set_online	 60</monitor><host default>	monitor_user		3m_moni# 监控账户	monitor_password	3m_12345 # 监控账户密码</host>debug 0# 配置agent设置vi /etc/mysql-mmm/mmm_agent.confinclude mmm_common.confthis db1# 因为在一台主机上启用了2个mysqld实例，故可配置2个this参数哦this db3

Host2主机上：只需部署agent

yum -y install mysql-mmm-agent# 配置agent设置vi /etc/mysql-mmm/mmm_agent.confinclude mmm_common.confthis db2this db4

服务启动

# 在主机Host1上[root@mysql mysql-mmm]# mmm_control showdb1(192.168.0.45) master/ONLINE. Roles: reader(192.168.0.14), writer(192.168.0.11)db2(192.168.0.46) master/ONLINE. Roles: reader(192.168.0.13)db3(192.168.0.45) slave/ONLINE. Roles: reader(192.168.0.15)db4(192.168.0.46) slave/ONLINE. Roles: reader(192.168.0.12)

测试验证

查看双主复制架构中基于MMM实现的状态信息：

# 在主机Host1上[root@mysql mysql-mmm]# mmm_control showdb1(192.168.0.45) master/ONLINE. Roles: reader(192.168.0.14), writer(192.168.0.11)db2(192.168.0.46) master/ONLINE. Roles: reader(192.168.0.13)db3(192.168.0.45) slave/ONLINE. Roles: reader(192.168.0.15)db4(192.168.0.46) slave/ONLINE. Roles: reader(192.168.0.12)

手动进行各节点的健康监测

# 在主机Host1上[root@mysql mysql-mmm]# mmm_control checksdb4ping [last change: 2014/05/06 22:38:27]OKdb4mysql[last change: 2014/05/06 22:38:27]OKdb4rep_threads[last change: 2014/05/06 22:38:27]OKdb4rep_backlog[last change: 2014/05/06 22:38:27]OK: Backlog is nulldb2ping [last change: 2014/05/06 22:38:27]OKdb2mysql[last change: 2014/05/06 22:38:27]OKdb2rep_threads[last change: 2014/05/06 22:38:27]OKdb2rep_backlog[last change: 2014/05/06 22:38:27]OK: Backlog is nulldb3ping [last change: 2014/05/06 22:38:27]OKdb3mysql[last change: 2014/05/06 22:38:27]OKdb3rep_threads[last change: 2014/05/06 22:38:27]OKdb3rep_backlog[last change: 2014/05/06 22:38:27]OK: Backlog is nulldb1ping [last change: 2014/05/06 22:38:27]OKdb1mysql[last change: 2014/05/06 22:38:27]OKdb1rep_threads[last change: 2014/05/06 22:38:27]OKdb1rep_backlog[last change: 2014/05/06 22:38:27]OK: Backlog is null

补充说明

在本篇的演示案例中，前端程序若要与MySQL通信，则写库需连接192.168.0.11:3406，读库可连接192.168.0.12-15中的一个或多个，端口可能是3406或3506；
在只读VIP漂移时，会导致前端程序连接的mysqld端口发生变化，所以生产环境下还是统一使用3306端口为宜；
利用MMM实现了双主复制架构中的故障自动转移后，mysql并非直接与前端程序通信，还需配合使用读写分离器(如Ameoba)，以统一对外的连接地址，由读写分离器负责读写的向下分配；

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Explain the role of InnoDB redo logs and undo logs.Apr 15, 2025 am 12:16 AM

InnoDB uses redologs and undologs to ensure data consistency and reliability. 1.redologs record data page modification to ensure crash recovery and transaction persistence. 2.undologs records the original data value and supports transaction rollback and MVCC.

What are the key metrics to look for in an EXPLAIN output (type, key, rows, Extra)?Apr 15, 2025 am 12:15 AM

Key metrics for EXPLAIN commands include type, key, rows, and Extra. 1) The type reflects the access type of the query. The higher the value, the higher the efficiency, such as const is better than ALL. 2) The key displays the index used, and NULL indicates no index. 3) rows estimates the number of scanned rows, affecting query performance. 4) Extra provides additional information, such as Usingfilesort prompts that it needs to be optimized.

What is the Using temporary status in EXPLAIN and how to avoid it?Apr 15, 2025 am 12:14 AM

Usingtemporary indicates that the need to create temporary tables in MySQL queries, which are commonly found in ORDERBY using DISTINCT, GROUPBY, or non-indexed columns. You can avoid the occurrence of indexes and rewrite queries and improve query performance. Specifically, when Usingtemporary appears in EXPLAIN output, it means that MySQL needs to create temporary tables to handle queries. This usually occurs when: 1) deduplication or grouping when using DISTINCT or GROUPBY; 2) sort when ORDERBY contains non-index columns; 3) use complex subquery or join operations. Optimization methods include: 1) ORDERBY and GROUPB

Describe the different SQL transaction isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) and their implications in MySQL/InnoDB.Apr 15, 2025 am 12:11 AM

MySQL/InnoDB supports four transaction isolation levels: ReadUncommitted, ReadCommitted, RepeatableRead and Serializable. 1.ReadUncommitted allows reading of uncommitted data, which may cause dirty reading. 2. ReadCommitted avoids dirty reading, but non-repeatable reading may occur. 3.RepeatableRead is the default level, avoiding dirty reading and non-repeatable reading, but phantom reading may occur. 4. Serializable avoids all concurrency problems but reduces concurrency. Choosing the appropriate isolation level requires balancing data consistency and performance requirements.

MySQL vs. Other Databases: Comparing the OptionsApr 15, 2025 am 12:08 AM

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

How does MySQL index cardinality affect query performance?Apr 14, 2025 am 12:18 AM

MySQL index cardinality has a significant impact on query performance: 1. High cardinality index can more effectively narrow the data range and improve query efficiency; 2. Low cardinality index may lead to full table scanning and reduce query performance; 3. In joint index, high cardinality sequences should be placed in front to optimize query.

MySQL: Resources and Tutorials for New UsersApr 14, 2025 am 12:16 AM

The MySQL learning path includes basic knowledge, core concepts, usage examples, and optimization techniques. 1) Understand basic concepts such as tables, rows, columns, and SQL queries. 2) Learn the definition, working principles and advantages of MySQL. 3) Master basic CRUD operations and advanced usage, such as indexes and stored procedures. 4) Familiar with common error debugging and performance optimization suggestions, such as rational use of indexes and optimization queries. Through these steps, you will have a full grasp of the use and optimization of MySQL.

Real-World MySQL: Examples and Use CasesApr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

See all articles