搭建mysql的主从复制和读写分离
发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，搭建mysql的主从复制和读写分离 +--------+ (write) +--------+ |
千家信息网最后更新 2025年11月07日搭建mysql的主从复制和读写分离
搭建mysql的主从复制和读写分离 +--------+ (write) +--------+ | client | +---------------------+| master | +--------+| | +--------+| | | | | | | | +--------+ (read) |(read) +--------+| amoeba |+---------------------|-----------+ (write) | +--------+| | | | | | | | (read)| | | +--------+| | +--------+ | +--------+| client | +-| slave1 |+---------+----------+| slave2 |+--------+ +--------+ (replication) +--------+master mysql:172.17.0.4slave1 mysql:172.17.0.5slave2 mysql:172.17.0.6mysql-proxy(amoeba):172.17.0.8Test host:172.17.0.7主从复制：master mysql：安装mysqlserver和mysqlyum install mysql-server mysql -y修改配置文件vim /etc/my.cnf.d/server.cnf[mysqld]server-id = 1log-bin = master-bin启动mysql/etc/init.d/mysql start修改root密码mysqladmin -uroot -p password mysql登陆mysql测试并查看master状态mysql -uroot -pmysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 22449Server version: 5.5.47-MariaDB-log MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MariaDB [(none)]> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 3648 | | |+------------------+----------+--------------+------------------+1 row in set (0.03 sec)创建主从同步账号MariaDB [(none)]> grant replication slave on *.* to 'mysqlmaster'@'172.17.0.%' identified by '123456';slave1 mysql：安装mysqlserver和mysqlyum install mysql-server mysql -y修改配置文件vim /etc/my.cnf.d/server.cnf[mysqld]server-id = 2 #id必须唯一log-bin = slave-bin启动mysql/etc/init.d/mysql start修改root密码mysqladmin -uroot -p password mysql创建同步文件MariaDB [(none)]> change master to master_host='172.17.0.4',master_user='mysqlmaster',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=3648;MariaDB [(none)]> start slave;查看是否成功，确保下面两项为YesMariaDB [(none)]> show slave status\G;Slave_IO_Running: YesSlave_SQL_Running: Yesmaster mysql：在master mysql上创建数据查看slave1 mysql是否同步MariaDB [(none)]> create database new1;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> create database new2;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || new1 || new2 || performance_schema || test |+--------------------+6 rows in set (0.00 sec)slave1 mysql:MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || new1 || new2 || performance_schema || test |+--------------------+6 rows in set (0.00 sec)我们生产环境中会碰到这种情况：备份主机数据，或者添加一台主机。进行双主机的结构。另外就是在线上中途添加更多的从机。而我们知道，从机上设置 slave 时要指定 master_log_file 和 master_log_pos, 即指定binlog文件和偏移值。这也就是说，从机是可以从任意位置的 binlog 文件中进行数据的同步。比如：我们将 binlog 文件备份到其它某处放置，某天，数据库出问题了，需要对某些数据进行数据恢复，这时候从该文件中进行恢复。添加一个新的从机，可以有两种方式：从 master 机器复制; 另一种是直接从 slave 复制.mysql-主从结构添加新的slave两种解决办法1.copy mastermaster mysql：锁定数据库MariaDB [(none)]> flush tables with read lock;Query OK, 0 rows affected (0.04 sec)查看主机状态，几下file position参数MariaDB [(none)]> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 3974 | | |+------------------+----------+--------------+------------------+备份所有数据库mysqldump --all-databases -uroot -pmysql > backup.sql拷贝到准备新加的slave主机scp backup.sql root@172.17.0.6:/rootslave2 mysql:安装mysqlserver和mysqlyum install mysql-server mysql -y修改配置文件vim /etc/my.cnf.d/server.cnf[mysqld]server-id = 3 #id必须唯一log-bin = slave2-bin启动mysql/etc/init.d/mysql start修改root密码mysqladmin -uroot -p password mysql导入主服务器scp过来的数据库mysql -uroot -pmysql < backup.sql创建同步文件MariaDB [(none)]> change master to master_host='172.17.0.4',master_user='mysqlmaster',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=3974;MariaDB [(none)]> start slave;查看是否成功，确保下面两项为YesMariaDB [(none)]> show slave status\G;Slave_IO_Running: YesSlave_SQL_Running: Yesmaster mysql：创建数据验证是否添加成功MariaDB [(none)]> create database new3;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || new1 || new2 | | new3 | | performance_schema || test |+--------------------+6 rows in set (0.00 sec) slave2 mysql:MariaDB [(none)]> create database new3;Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || new1 || new2 | | new3 | | performance_schema || test |+--------------------+6 rows in set (0.00 sec)2 copy slave复制从库要步骤：====================可以看到，从主库复制会有段时间锁表，这段时间会影响主库的使用。如果我们能直接从从库进行复制，就不会对主库产生影响了。但是，从从库复制要保证的是复制过程中从库上的数据不会发生变化，所以要先停掉从库。1.停止从库: mysql> stop slave;2.看当前从库的状态。和前面的看主库状态一样。但现在是从从库复制，所以查看从库状态：mysql> show slave status;记下 Relay_Master_Log_file 和 Exec_Master_Log_Pos, 用处和前面一样.3.备份从库数据.用 mysqldump4.在新的从库上还原数据5.设置新从库的 slave 参数.change master to master_host = '192.168.3.119',master_port = 3306,master_user = 'repl_user',master_password='root',master_log_file='master-bin.000005',master_log_pos=194244;可以看到，虽然新从库是从从库复制的数据，但实际上 binlog 的 master 还是指向的主库。另外，这里将 master_log_file 和 master_log_pos 设置成第 2 步中的 Relay_Master_Log_file 和 Exec_Master_Log_Posstart slave; mysql的主从复制+读写分离 +--------+ (write) +--------+ | client | +---------------------+| master | +--------+| | +--------+| | | | | | | | +--------+ (read) |(read) +--------+| amoeba |+---------------------|-----------+ (write) | +--------+| | | | | | | | (read)| | | +--------+| | +--------+ | +--------+| client | +-| slave1 |+---------+----------+| slave2 |+--------+ +--------+ (replication) +--------+读写分离Amoeba(变形虫)项目，专注 分布式数据库 proxy 开发。座落与Client、DB Server(s)之间。对客户端透明。具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库、可并发请求多台数据库合并结果。再看下上面的架构图。因为amoeba是java编写的，所以需要先安装java框架Amoeba：java安装下载javawget http://download.oracle.com/otn-pub/java/jdk/7u79-b15/jdk-7u79-linux-x64.tar.gz创建java目录，并解压mkdir -p /usr/jdktar -xzvf jdk-7u79-linux-x64.tar.gz -C /usr/jdk配置环境变量，在/etc/profile文件最后添加如下配置vim /etc/profileexport JAVA_HOME=/usr/jdk/export CLASSPATH=${JAVA_HOME}/libexport PATH=${JAVA_HOME}/bin:$PATH使配置文件生效source /etc/profile测试java -versionjava version "1.7.0_79"Java(TM) SE Runtime Environment (build 1.7.0_79-b15)Java HotSpot(TM) 64-Bit Server VM (build 24.79-b02, mixed mode)证明已经安装完成amoeba安装配置下载解压wget http://ufpr.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zipunzip amoeba-mysql-3.0.5-RC-distribution.zipmv amoeba-mysql-3.0.5-RC /usr/local/配置vim amoeba-mysql-3.0.5-RC/conf/amoeba.xml （前段连接文件） 8066 ///////amoeba监听端口///////// 128 64 root ///Amoeba代理用户名///// mysql ///Amoeba代理用户密码///// ${amoeba.home}/conf/access_list.conf .............................................. ${amoeba.home}/conf/rule.xml ${amoeba.home}/conf/ruleFunctionMap.xml ${amoeba.home}/conf/functionMap.xml 1500 master ///////默认地址池//////// master //////写地址池/////////// vipdb //////读地址池//////// true vim amoeba-mysql-3.0.5-RC/conf/dbServers.xml （后端数据库参数文件） ${defaultManager} 64 128 3306 /////数据库连接端口/////// test ////默认数据库//// amoeba ///主从数据库默认连接用户//// mysql ////主从数据库默认连接用户密码//// ........................................................ +---+ | | 172.17.0.4 | | | | | | |+-------//////定义后端数据库，dbServer可以随意命名，但自己必须清楚哪个是主，那个是从，而且主服务器命名要和amoeba.xml中writePool相对应，从服务器对应下面 172.17.0.5 |virturl dbServer中的poolNames ip对应各个db/////// | | | | | 172.17.0.6 | +---| +---+ | | 1 | |+-------///第一行的dbserver name命名一定和amoeba.xml的readPool相对应，property name配置从服务器的集合，可以是一个，可以是多个！ | slave1,slave2 | | +---+vim amoeba-mysql-3.0.5-RC/jvm.properties （java虚拟机配置）把原来的这一句JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"改成JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16m -XX:MaxPermSize=96m"在master，slave1，slave2分别授权dbServer.xml中定义的用户名和密码MariaDB [(none)]> grant all on *.* to 'amoeba'@'%' identified by "mysql";MariaDB [(none)]> grant all on *.* to 'amoeba'@'localhost' identified by "mysql";启动amoeba/usr/local/amoeba-mysql-3.0.5-RC/bin/launcher&查看进程(如果启动成功，会看到如下进程)ps -efroot 467 337 0 12:51 pts/0 00:00:00 /bin/bash amoeba-mysql-3.0.5-RC/bin/launcherroot 472 467 0 12:51 pts/0 00:00:05 /usr/local/jdk1.7.0_67/bin/java -server -Xms1024m -Xmx1024m -Xss256k -XX:PermSize=16mroot 473 467 0 12:51 pts/0 00:00:00 tail -f /usr/local/amoeba-mysql-3.0.5-RC/logs/console.log查看监听端口netstat -anpltcp 0 0 :::8066 :::* LISTEN 472/java tcp 0 0 ::ffff:172.17.0.8:8066 ::ffff:172.17.0.7:39978 ESTABLISHED 472/java tcp 0 0 ::ffff:172.17.0.8:46624 ::ffff:172.17.0.5:3306 ESTABLISHED 472/java tcp 0 0 ::ffff:172.17.0.8:41286 ::ffff:172.17.0.4:3306 ESTABLISHED 472/java Test host测试测试是拿amoeba.xml中定义的用户名密码去测试，千万别被这地方绕进去[root@7898596a875b ~]# mysql -uroot -p -h272.17.0.8 -P8066Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g.Your MySQL connection id is 1265630343Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA MariaDB ServerCopyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || new1 || new2 || performance_schema || test |+--------------------+6 rows in set (0.00 sec)测试读写分离master mysql:在master上建表MariaDB [(none)]> use new1;MariaDB [new1]> create table student( id int(4) not null AUTO_INCREMENT, name char(20) not null, age tinyint(2) NOT NULL default '0', dept varchar(16) default NULL, primary key(id), KEY index_name (name) );slave1,slave2:slave1停掉slaveMariaDB [(none)]> stop slave;Query OK, 0 rows affected (0.02 sec)MariaDB [new1]> insert into new1.student(id,name) values(2,'slave');Query OK, 1 row affected (0.03 sec)MariaDB [new1]> select * from new1.student;+----+-------+-----+------+| id | name | age | dept |+----+-------+-----+------+| 2 | slave | 0 | NULL |+----+-------+-----+------+1 row in set (0.00 sec)slave2MariaDB [(none)]> stop slave;Query OK, 0 rows affected (0.02 sec)MariaDB [(none)]> insert into new1.student(id,name) values(3,'slave');Query OK, 1 row affected (0.04 sec)MariaDB [(none)]> select * from new1.student;+----+-------+-----+------+| id | name | age | dept |+----+-------+-----+------+| 3 | slave | 0 | NULL |+----+-------+-----+------+1 row in set (0.00 sec)master上也插入一条数据MariaDB [new1]> insert into new1.student(id,name) values(1,'slave');Query OK, 1 row affected (0.02 sec)查询插入MariaDB [new1]> select * from new1.student;+----+-------+-----+------+| id | name | age | dept |+----+-------+-----+------+| 1 | slave | 0 | NULL |+----+-------+-----+------+1 row in set (0.00 sec)Test host测试MySQL [new1]> select * from new1.student;+----+-------+-----+------+| id | name | age | dept |+----+-------+-----+------+| 3 | slave | 0 | NULL |+----+-------+-----+------+1 row in set (0.00 sec)第一次查询发现只查询到slave的数据，以为另一个slave读写分离没有做成功，再次查询发现amoeba是轮询着从从数据库池里读取数据MySQL [new1]> select * from new1.student;+----+-------+-----+------+| id | name | age | dept |+----+-------+-----+------+| 2 | slave | 0 | NULL |+----+-------+-----+------+1 row in set (0.00 sec)再插入一条数据，发现查询不到插入的，还是只能查询到slave上数据MySQL [new1]> insert into student(id,name) values(4,'yufyang');Query OK, 1 row affected (0.02 sec)MySQL [new1]> select * from new1.student;+----+-------+-----+------+| id | name | age | dept |+----+-------+-----+------+| 3 | slave | 0 | NULL |+----+-------+-----+------+1 row in set (0.00 sec)MySQL [new1]> select * from new1.student;+----+-------+-----+------+| id | name | age | dept |+----+-------+-----+------+| 2 | slave | 0 | NULL |+----+-------+-----+------+1 row in set (0.01 sec)master mysql：MariaDB [new1]> select * from new1.student;+----+---------+-----+------+| id | name | age | dept |+----+---------+-----+------+| 1 | slave | 0 | NULL || 4 | yufyang | 0 | NULL |+----+---------+-----+------+2 rows in set (0.00 sec)发现刚才在测试机上通过amoeba插入的数据已经出现在主数据库的表中开启slave再次查询slave上的数据MariaDB [new1]> select * from new1.student;+----+---------+-----+------+| id | name | age | dept |+----+---------+-----+------+| 1 | slave | 0 | NULL || 2 | slave | 0 | NULL || 4 | yufyang | 0 | NULL |+----+---------+-----+------+发现已经同步master，amoeba测试机上的数据了
数据
数据库
文件
配置
测试
主从
密码
查询
用户
主机
状态
同步
成功
地址
备份
服务器
服务
参数
用户名
端口
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
服务器流量包没使用
软件开发企业分包的成本分录
天津特色软件开发供应商
大学生网络安全培训总结
铁路职工网络安全反思
乾颐堂网络安全吗
图片服务器解决方案
戴尔服务器屏幕拉出来推不回去了
祥林网络技术有限公司
数据库系统教程王能斌
数据库主从 集群
济源学习软件开发
马鞍山系统软件开发定制
数据库子窗体不可编辑
网吧服务器原理
莱西市勤荔铎软件开发部
朗致软件开发
为什么要选择网络技术专业
数据库1048错误
网络安全工程师项目经验案例
刑警网络技术视频
代驾软件开发公司
网络安全意识动画片
佛山gis软件开发
齐鲁软件开发大赛
c 小型软件开发
甘肃政法网络安全复试
服务器管理怎样可以快速
我心中的网络安全关大讨论
地图数据库的特点是什么
