搭建mysql的主从复制和读写分离
发表于：2025-02-01 作者：千家信息网编辑
千家信息网最后更新 2025年02月01日，搭建mysql的主从复制和读写分离 +--------+ (write) +--------+ |
千家信息网最后更新 2025年02月01日搭建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安全错误
数据库的锁怎样保障安全
开电子油票提示访问服务器异常
赫点网络技术
什么工具访问服务器
可以用服务器做nas吗
软件开发实施项目合同
焦作市网络安全事故
南京大学数据库技术
医疗保障网络安全管理制度
数据库技术发展的现状和趋势
电大计算机网络安全技术考试
我国网络安全现状简介
数据库常用的数据结构模型有几种
数据库安全权限是什么
unix连接数据库
支付宝软件开发工资待遇
维护网络安全建言献策
计算机网络技术可以报考二建
jdbc数据库
美国政府各部门网络安全财政预算
git java文件服务器
网络安全必修课程
南京节能软件开发销售厂
安徽卫星时钟服务器虚拟主机
intel 边缘服务器
彩票游戏软件开发公司
临汾网络安全教育公益讲座
硅石网络技术有限公司
ftp服务器管理器
医学数据库数据挖掘sci
网络安全的规定是什么
