集团网站建设成都,网站设计文字超链接,微信清粉网站开发,WordPress打开文章页面404目录 一、原理
二、准备环境
三、部署mysql主从复制
1.五台服务器下载mariadb
2.修改master配置文件#xff0c;重启数据库
3.登录mysql创建replication
4.从服务器登录验证
5.获得master服务器 DB的相关信息
6.备份master原有数据
7.修改slave1、slave2配置 8. 进入…目录 一、原理
二、准备环境
三、部署mysql主从复制
1.五台服务器下载mariadb
2.修改master配置文件重启数据库
3.登录mysql创建replication
4.从服务器登录验证
5.获得master服务器 DB的相关信息
6.备份master原有数据
7.修改slave1、slave2配置 8. 进入msql执行下列命令slave1、slave2开启主从复制
四、部署mysql读写分离
1.在amoeba服务器192.168.1.138安装java 1.6版本过高的版本amoeba无法兼容
2.下载amoeba tar包
3.在master、slave1、slave2服务器中配置amoeba的访问权限
4.修改amoeba配置文件
5.修改数据服务配置文件
6.放入后台启动
7.测试amoeba
8.测试读写分离
9.总结 一、原理 一台主服务器多台从数据服务器主服务器开启日志功能且建立一个允许从数据服务器访问的账号从数据服务器开启从服务主服务器所以产生的数据记录在日志文件里保存到本地磁盘当日志文件发生变化时从数据服务器通过IO线程同步变化的数据把变化的二进制文件传到从数据服务器并告知主数据服务数据没有问题然后保存到本地磁盘从数据服务器和主数据服务有一样的数据所以当读取数据时通过SQL线程调用从数据服务器而主服务器只负责写减轻主数据服务器压力。
二、准备环境
五台服务器
master服务器192.168.1.12、slave1服务器192.168.1.133、slave2服务器192.168.1.134
amoeba服务器192.168.1.138、localhost服务器192.168.1.141
五台服务器都关闭防火墙同步时间
[rootlocalhost ~] systemctl stop firewalld
[rootlocalhost ~] setenforce 0
[rootlocalhost ~] iptables -F
[rootlocalhost ~] yum -y install ntp
[rootmaster ~] systemctl start ntpd
[rootmaster ~] ntpdate 192.168.1.12
28 Jun 14:26:12 ntpdate[8815]: the NTP socket is in use, exiting生产环境中最好将ntpdate 192.168.1.12 写入周期计划任务每天执行一下时间不同步会造成数据缺失
三、部署mysql主从复制
1.五台服务器下载mariadb
[rootlocalhost ~] yum -y install mariadb mariadb-server
2.修改master配置文件重启数据库
[rootmaster ~] vim /etc/my.cnf
server-id1 #编号
log-binmysql-binlog #启动日志
log-slave-updatestrue #开启主从复制3.登录mysql创建replication主从复制功能将slave作为从服务器
[rootmaster ~] mysql
MariaDB [(none)] grant replication slave on*.* to myslave192.168.1.%identified by 123456;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)] flush privileges; #刷新
Query OK, 0 rows affected (0.00 sec)
4.从服务器登录验证
[rootslave1 ~] mysql -umyslave -p123456 -h192.168.1.12
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-MariaDB MariaDB ServerCopyright (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)] 5.获得master服务器 DB的相关信息
MariaDB [(none)] show master status;
---------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
---------------------------------------------------------------
| mysql-binlog.000003 | 1029164 | | |
---------------------------------------------------------------
6.备份master原有数据
[rootmaster ~] mysqldump -uroot --all-databases /root/alldbbackup.sql #因为主从同步从开启同步的节点开始同步之前的数据没有
[rootmaster ~] scp /root/alldbbackup.sql root192.168.1.133: /root/
alldbbackup.sql 100% 503KB 68.1MB/s 00:00 #通过scp传输给slaveslave服务器导入到 本地数据库
[rootmaster ~] mysql -uroot -p /root/alldbbackup.sql #将数据库还原
7.修改slave1、slave2配置
[rootslave1 ~] vim /etc/my.cnf
[mysqld]
server-id2 #编号
relay-logrelay-log-bin #中继日志
relay-log-indexslave-relay-bin.index #定义日志索引[rootslave2 ~] vim /etc/my.cnf
[mysqld]
server-id3 #编号
relay-logrelay-log-bin #中继日志
relay-log-indexslave-relay-bin.index #定义日志索引8. 进入msql执行下列命令slave1、slave2开启主从复制
#slave1、slave2开启主从复制
MariaDB [(none)] stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)MariaDB [(none)] change master to master_host192.168.1.12, master_usermyslave, master_password123456, master_log_filemysql-binlog.000003, master_log_pos1029164;
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)] start slave;
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)] show slave status\G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.12Master_User: myslaveMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-binlog.000003Read_Master_Log_Pos: 1029164Relay_Log_File: relay-log-bin.000002Relay_Log_Pos: 532Relay_Master_Log_File: mysql-binlog.000003Slave_IO_Running: YesSlave_SQL_Running: Yes
#主从复制成功开启
四、部署mysql读写分离
1.在amoeba服务器192.168.1.138安装java 1.6版本过高的版本amoeba无法兼容
[rootamoeba ~] java -version #确保服务器未安装java
bash: java: 未找到命令Creating jdk1.6.0_31/jre/lib/ext/localedata.jar
Creating jdk1.6.0_31/jre/lib/plugin.jar
Creating jdk1.6.0_31/jre/lib/javaws.jar
Creating jdk1.6.0_31/jre/lib/deploy.jarJava(TM) SE Development Kit 6 successfully installed. #java1.6安装完成
[rootamoeba ~] mkdir /usr/local/jkd1.6
[rootamoeba ~] mv jdk1.6.0_31/ /usr/local/jkd1.6/ #将文件全部移到jdk1.6目录中[rootamoeba ~] vim /etc/profile #添加变量让jdk1.6生效
#在文件最下面添加
export CLASSPATH$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH$PATH:$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$HOME/bin
export PATH$PATH:$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$HOME/bin[rootamoeba ~] source /etc/profile
[rootamoeba ~] java -version
java version 1.6.0_31 #成功下载
Java(TM) SE Runtime Environment (build 1.6.0_31-b04)
Java HotSpot(TM) 64-Bit Server VM (build 20.6-b01, mixed mode)
2.下载amoeba tar包
[rootamoeba ~] ll
总用量 86672
-rw-r--r--. 1 root root 3161433 2月 29 2016 amoeba-mysql-binary-2.2.0.tar.gz
[rootamoeba ~] mkdir /usr/local/amoeba #给amoeba创建一个目录
[rootamoeba ~] tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ #解压到目录里
[rootamoeba ~] chmod -R 755 /usr/local/amoeba/ #递归的更改目录所有文件权限
[rootamoeba ~] vim /etc/profile #在文件最下面添加变量
export AMOEBA_HOME/usr/local/amoeba
export PATH$PATH:$AMOEBA_HOME/bin
[rootamoeba ~] source /etc/profile
3.在master、slave1、slave2服务器中配置amoeba的访问权限
#进入mysql执行下列命令
MariaDB [(none)] grant all on *.* to test192.168.1.138identified by 123.com;
Query OK, 0 rows affected (0.00 sec)
4.修改amoeba配置文件
[rootamoeba ~] vim /usr/local/amoeba/conf/amoeba.xml30 property nameuseramoeba/property31 32 property namepassword123456/property
115 property namedefaultPoolmaster/property
116
117
118 property namewritePoolmaster/property
119 property namereadPoolslaves/property
120 --5.修改数据服务配置文件
[rootamoeba ~] vim /usr/local/amoeba/conf/dbServers.xml 20 property nameport3306/property #修改端口 23 property nameschematest/property #mysql授权用户 26 property nameusertest/property #登录用户和授权用户一致29 property namepassword123.com/property #mysql授权的密码45 dbServer namemaster parentabstractServer48 property nameipAddress192.168.1.12/property #定义masterIP地址51 #定义slave1 和 slave252 dbServer nameslave1 parentabstractServer53 factoryConfig54 !-- mysql ip --55 property nameipAddress192.168.1.133/property56 /factoryConfig57 /dbServer58 59 dbServer nameslave2 parentabstractServer60 factoryConfig61 !-- mysql ip --62 property nameipAddress192.168.1.134/property63 /factoryConfig64 /dbServer#定义读的池slave65 dbServer nameslaves virtualtrue68 property nameloadbalance1/property #轮询为169 70 !-- Separated by commas,such as: server1,server2,server1 --71 property namepoolNamesslave1,slave2/property #slave1和slave2间轮询
6.放入后台启动
[rootamoeba ~] cd /usr/local/amoeba/bin/
[rootamoeba bin] ./amoeba start
[2] 13599
[rootamoeba bin] remote application .Amoeba:39059 response OK
amoeba server is running with port39059
[rootamoeba bin] netstat -anptl |grep 8066
tcp6 0 0 :::8066 :::* LISTEN 13566/java
7.测试amoeba
[rootslave2 ~] mysql -uamoeba -p123456 -h 192.168.1.138 -P 8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 926681939
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type help; or \h for help. Type \c to clear the current input statement.MySQL [(none)]
#可以用amoeba用户登录8.测试读写分离
master创建一个数据库 slave1和slave2 自动同步
MariaDB [(none)] create database ceshi;
Query OK, 1 row affected (0.01 sec)MariaDB [(none)] show databases;
--------------------
| Database |
--------------------
| information_schema |
| ceshi |
| mysql |
| performance_schema |
| test |
--------------------
5 rows in set (0.00 sec)
关闭 slave1和slave2的主从复制功能再向master和slave1、slave2 的ceshi数据库插入表
MySQL [(none)] stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)MariaDB [(none)] create database shiyan;
MariaDB [(none)] use shiyan;
Database changed在表中分别插入不同数据
MariaDB [shiyan] CREATE TABLE customer (- id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,- name VARCHAR(30) NOT NULL,- email VARCHAR(50),- created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP- );
Query OK, 0 rows affected (0.01 sec)MariaDB [shiyan] insert into customer (name,email) values (master master,master.masterexample.com);
Query OK, 1 row affected (0.00 sec)MariaDB [shiyan] select * from customer;
-------------------------------------------------------------------
| id | name | email | created_date |
-------------------------------------------------------------------
| 1 | master master | master.masterexample.com | 2023-06-28 20:17:16 |
-------------------------------------------------------------------
1 row in set (0.00 sec)MariaDB [shiyan] insert into customer (name,email) values (slave1 slave1,slave1,slave1example.com);
Query OK, 1 row affected (0.00 sec)MariaDB [shiyan] select * from customer;
-------------------------------------------------------------------
| id | name | email | created_date |
-------------------------------------------------------------------
| 1 | slave1 slave1 | slave1,slave1example.com | 2023-06-28 20:18:51 |
-------------------------------------------------------------------
1 row in set (0.00 sec)MariaDB [shiyan] insert into customer (name,email) values (slave2 slave2,slave2,slave2example.com);
Query OK, 1 row affected (0.00 sec)MariaDB [shiyan] select * from customer;
-------------------------------------------------------------------
| id | name | email | created_date |
-------------------------------------------------------------------
| 1 | slave2 slave2 | slave2,slave2example.com | 2023-06-28 20:19:25 |
-------------------------------------------------------------------
1 row in set (0.00 sec)
用amoeba用户登录mysql查看表发现只能看见slave1和slave2的轮询
[rootslave2 ~]# mysql -uamoeba -p123456 -h 192.168.1.138 -P 8066
MySQL [shiyan] select * from customer;
-------------------------------------------------------------------
| id | name | email | created_date |
-------------------------------------------------------------------
| 1 | slave1 slave1 | slave1,slave1example.com | 2023-06-28 20:18:51 |
-------------------------------------------------------------------
1 row in set (0.00 sec)MySQL [shiyan] select * from customer;
-------------------------------------------------------------------
| id | name | email | created_date |
-------------------------------------------------------------------
| 1 | slave2 slave2 | slave2,slave2example.com | 2023-06-28 20:19:25 |
-------------------------------------------------------------------
1 row in set (0.01 sec)MySQL [shiyan] select * from customer;
-------------------------------------------------------------------
| id | name | email | created_date |
-------------------------------------------------------------------
| 1 | slave1 slave1 | slave1,slave1example.com | 2023-06-28 20:18:51 |
-------------------------------------------------------------------
1 row in set (0.00 sec)在aomeba服务器里进入数据库插入表来测试
[rootamoeba bin] mysql -uamoeba -p123456 -h 192.168.1.138 -P 8066MySQL [shiyan] insert into customer (name,email) values (slave1 slave1,slave1,slave1example.com);
Query OK, 1 row affected (0.00 sec#主master查看 写入了数据库中
MariaDB [shiyan] select * from customer;
-------------------------------------------------------------------
| id | name | email | created_date |
-------------------------------------------------------------------
| 1 | master master | master.masterexample.com | 2023-06-28 20:17:16 |
| 2 | slave1 slave1 | slave1,slave1example.com | 2023-06-28 20:31:56 |
-------------------------------------------------------------------#slave1和slave2 查看 无变化 没有写入权限
MariaDB [shiyan] select * from customer;
-------------------------------------------------------------------
| id | name | email | created_date |
-------------------------------------------------------------------
| 1 | slave1 slave1 | slave1,slave1example.com | 2023-06-28 20:18:51 |
-------------------------------------------------------------------9.总结
master只有写入能力slave1和slave2进行轮询。
