基于Gtid的mysql主从复制 和 mysql-proxy 读写分离
mysql5.6基于gtid主从复制和mysql-proxy读写分离MySQL5.6引入的GTID(GlobalTransactionIDs)使得其复制功能的配置、监控及管理变得更加易于实现，且更加健..
主从复制的IP地址分配和主从关系：
配置主从复制注意事项：
1,两台服务器时间需要同步
2,相互都能通信ping的通
3,关闭SElinux
mysql版本：mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz
系统版本：redhat 6.4 32位
配置步骤：
提醒：本文的配置过程每一步前面都有（0-9） 数字表名，请按照此顺序配置。
mysql 安装配置
mysql 安装配置 1,# mkdir /mydata/data -pv 2,# useradd -r mysql # chown -R mysql.mysql /mydata/data/ 3,# tar xvf mysql-5.6.10-linux-glibc2.5-i686.tar.gz -C /usr/local 4,# cd /usr/local/ 5,# ln -sv mysql-5.6.10-linux-glibc2.5-i686 mysql 6,# cd mysql 7,# chown -R root.mysql ./* 8,# scripts/mysql_install_db --user=mysql --datadir=/mydata/data 9,# cp support-files/mysql.server /etc/init.d/mysqld 10,# chkconfig --add mysqld下面是配置文件部分 ，值得注意的是在下面配置文件中server-id 此项的值 不能重复。主从不能一样。
11,# vim /etc/local/mysql/my.cnf 添加以下几项 datadir = /mydata/data innodb_file_per_table = ON server-id = 1 从服务器不能跟此id重复 socket=/tmp/mysql.sock log-bin = master-bin 12,# service mysqld start 13,# vim /etc/profile.d/mysqld 添加 export PATH=$PATH:/usr/local/mysql/bin 14,# . /etc/profile.d/mysqld 重读配置文件下一步开始配置从mysql。配置从服务器，步骤如上，尤其注意的事 /etc/local/mysql/my.cnf 中server-id 值 不能重复！！！
配置主从mysql
编辑配置文件 添加主从复制中 主 的一些配置参数。
16,启动mysql服务
16,# service mysqld restart17,进入mysql 数据库 查看gtid 等相关信息
# mysql # mysql> show global variables like '%gtid%';
18,创建复制用户并赋予权限
mysql> GRANT REPLICATION SLAVE ON *.* TO laogen@172.16.%.% IDENTIFIED BY 'laogen'; mysql> flush privileges; mysql> SELECT @@autocommit
下面是编mysql 从的 服务配置信息！！！
slave 19,编辑从mysql 配置文件 # vim /usr/local/mysql/my.cnf 添加以下内容 binlog-format=ROW log-slave-updates=true gtid-mode=on enforce-gtid-consistency=true master-info-repository=TABLE relay-log-info-repository=TABLE sync-master-info=1 slave-parallel-workers=2 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 report-port=3306 port=3306 report-host=172.16.66.220,启动mysql服务
# service mysqld restart21,使用主mysql上创建的账号密码登陆复制。
# msyql>change master to master_host='172.16.66.1', master_user='laogen',master_password='laogen',master_auto_position=1; # mysql>start slave; # mysql>SHOW SLAVE STATUS\G 查看复制状态信息
接下来进入调试和验证步骤。
在主mysql 上创建数据库 监测 从是否能复制
22,登陆进主mysql数据库里 并 创建数据库从是否能同步成功。
# mysql> CREATE DATABASE LAOGEN;23,登陆从mysql 验证数据库
# mysql> SHOW DATABASE;
检查二进制文件同步位置是否一致,两者对比一下！！！！
24,在主mysql数据库里面执行
mysql>show master status\G
25,在从mysql数据库里面执行，同样也是这条命令。
mysql>show master status\G
到此为止 基于Gtid的mysql主从复制 配置成功！！！
#######################################################################
#######################################################################
mysql-proxy主从分离
下面我们就在上面mysql主从复制基础上配置mysql-proxy 读写分离，需要在主从之外另外加一台服务器，注意此mysql-proxy 尽量和 主从一个网段，保证能ping通，时间同步。
IP配置信息如下：
先简单的介绍一下mysql-proxy：
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to achieve separation of read and write in database in React Query?
Sep 26, 2023 am 09:22 AM
How to achieve separation of read and write in database in ReactQuery? In modern front-end development, the separation of reading and writing in the database is an important architectural design consideration. ReactQuery is a powerful state management library that can optimize the data acquisition and management process of front-end applications. This article will introduce how to use ReactQuery to achieve separation of read and write in the database, and provide specific code examples. The core concepts of ReactQuery are Query, Mutatio
Data backup and failure recovery: Discussion on the importance of MySQL master-slave replication in cluster mode
Sep 08, 2023 am 09:03 AM
Data backup and failure recovery: Discussion on the importance of MySQL master-slave replication in cluster mode Introduction: In recent years, with the continuous growth of data scale and complexity, database backup and failure recovery have become particularly important. In distributed systems, MySQL master-slave replication is widely used in cluster mode to provide high availability and fault tolerance. This article will explore the importance of MySQL master-slave replication in cluster mode and give some code examples. 1. Basic principles and advantages of MySQL master-slave replication MySQL master-slave replication is a general
How to configure read-write separation for MySQL connection?
Jun 30, 2023 am 11:39 AM
How to configure read-write separation for MySQL connections? MySQL database is one of the most commonly used relational databases, and its high availability and performance are crucial for large websites and applications. In order to improve the load balancing and performance of the database, we can configure the read-write separation of the MySQL connection. Read-write separation is a database architecture design pattern that improves the concurrent processing capabilities of the database by allocating read operations and write operations to different database servers. This way we can take full advantage of multiple servers
A brief analysis of how php+mysql realizes reading and writing separation
Mar 21, 2023 pm 03:49 PM
MySQL is one of the most popular databases and is widely used in applications. In the case of high concurrency and large data volume, due to insufficient read and write concurrency performance of MySQL, in order to improve the concurrency performance of MySQL, read and write separation has become a necessary solution. As a popular dynamic language, PHP is very closely integrated with MySQL. So, this article will introduce how to use PHP to achieve MySQL read and write separation.
Optimizing database performance: The best way to use MySQL master-slave replication in cluster technology
Sep 10, 2023 am 08:24 AM
Optimizing database performance: The best way to use MySQL master-slave replication in cluster technology Abstract: With the rapid development of the Internet, database performance issues have become the focus of various enterprises and organizations. MySQL master-slave replication technology plays an important role in solving database performance bottlenecks. This article will introduce the concepts and principles of MySQL master-slave replication, as well as the best use methods in cluster technology, to help readers optimize database performance. 1. Introduction As the amount of data continues to increase, database performance problems have become increasingly prominent. How to optimize numbers
Decrypting MySQL master-slave replication: revealing its key implementation mechanism in cluster mode
Sep 10, 2023 am 09:28 AM
Decrypting MySQL master-slave replication: Revealing its key implementation mechanism in cluster mode Introduction: In modern database systems, high availability and flexibility of data are very important. As an open source relational database management system, MySQL has a wide range of applications in meeting user needs. MySQL's master-slave replication is a very critical part of the MySQL database architecture and is used to achieve data backup and high availability. This article will focus on revealing the key implementation mechanism of MySQL master-slave replication, especially in cluster mode.
Cope with high concurrency with ease: Analysis of the performance advantages of MySQL master-slave replication as a cluster technology
Sep 10, 2023 pm 03:48 PM
Cope with high concurrency with ease: Analysis of the performance advantages of MySQL master-slave replication as a cluster technology. With the rapid development of the Internet, user visits to websites and applications have shown an explosive growth trend. In this high-concurrency situation, how to ensure system stability and performance has become an important task for every developer and system administrator. In databases, MySQL master-slave replication technology is widely used and has become one of the effective solutions to deal with high concurrency. This article will explore the performance advantages of MySQL master-slave replication as a cluster technology. first
Is MySQL master-slave replication a cluster technology or a load balancing technology? Analysis and difference
Sep 10, 2023 am 08:40 AM
Is MySQL master-slave replication a cluster technology or a load balancing technology? Summary of analysis and differences: MySQL master-slave replication is a database replication technology used to synchronize database data on multiple servers. This article will analyze and distinguish the differences between MySQL master-slave replication, cluster technology and load balancing technology in terms of technical principles, application scenarios and functional characteristics. Introduction: In modern Internet applications, high availability and scalability of databases are crucial. MySQL master-slave replication is one of the common solutions, however,
