mysql的主从复制
概念:mysql的主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库,主数据库一般是实时的业务数据操作,从数据库常用的读取为主。
为什么使用主从复制、读写分离:
主从复制、读写分离一般是一起使用的。目的很简单,就是为了提高数据库的并发性能。你想,假设是单机,读写都在一台MySQL上面完成,性能肯定不高。如果有三台MySQL,一台mater只负责写操作,两台salve只负责读操作,性能不就能大大提高了吗?
优点:1,可以作为备用数据库进行操作,当主数据库出现故障之后,从数据库可以替代主数据库继续工作,不影响业务流程
2,读写分离,将读和写应用在不同的数据库与服务器上。一般读写的数据库环境配置为,一个写入的数据库,一个或多个读的数据库,各个数据库分别位于不同的服务器上,充分利用服务器性能和数据库性能;当然,其中会涉及到如何保证读写数据库的数据一致,这个就可以利用主从复制技术来完成。
3,吞吐量较大,业务的查询较多,并发与负载较大。
———————————————————————————————————————————————————————————————————————
基础案例
实验环境:一台主mysql(192.168.1.1)
一台从mysql(192.168.1.2)
实验目的:完成从服务器从主服务器里调用数据
1、配置ip地址
vim /etc/sysconfig/network-scripts/ifcfg-ens33
systemctl restart network
2、安装mysql(yum安装)
yum -y install mariadb mariadb-server mariadb-devel
3、配置主mariadb数据库
vim /etc/my.cnf.d/server.cnf
server-id=11
log-bin=master-bin
log-slave-updates=true
systemctl restart mariadb #重启服务
mysqladmin -u root password 123.com #设置mysql登录密码
mysql -u root -p123.com #进入mysql
grant replication slave on *.* to 'slave'@'192.168.1.%' identified by '123.com'; #为其他客户端授权
flush privileges; #刷新日志文件
show master status; #查看主服务状态
4、配置从服务器
vim /etc/my.cnf.d/server.cnf
server-id=21
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
systemctl restart mariadb
mysqladmin -u root password 123.com
mysql -u root -p123.com
change master to master_host='192.168.1.1',master_user='slave',master_password='123.com',master_log_file='master-bin.000004',master_log_pos=612; #连接柱MySQL
start slave; #开启MySQL的从服务
show slave status\G #查看从mysql的服务状态
注意二进制文件和节点ID号、要和主MySQL的二进制文件和节点ID要想同
这两条都确保为yes状态
5、验证效果
在主mysql上面创建新的库和表
mysql> show databases;
mysql> create database abc;
mysql> show databases;
mysql> use abc;
mysql> create table biao1 (id int,name char(20));
mysql> show tables;
在从服务器上查看是否复制了主服务器上的库和表
DB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use abc
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [abc]> show tables;
+---------------+
| Tables_in_abc |
+---------------+
| biao1 |
+---------------+
1 row in set (0.00 sec)
实验完成
mysql的读写分离
实验环境:一台客户端(192.168.1.5)
一台主mysql服务器(192.168.1.1)
一台从mysql服务器(192.168.1.2)
一台从mysql服务器(192.168.1.3)
一台amoeba调度器(192.168.1.4)
1、配置ip地址
步骤(略……)
2、实现主从复制
步骤(略……)
3、安装amoeba
安装Java环境
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
输入yes
mv jdk1.6.0_14/ /usr/local/java
rm -rf /usr/bin/java #删除原有的java
rm -rf /usr/bin/javac #删除原有的javac
mkdir /usr/bin/java
ln -s /usr/local/java/bin/* /usr/bin/java
vim /etc/profile
export JAVA_HOME=/usr/local/java
export CLASSPATH=$CLASSPATH:$JAVA_HOME:/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/bin:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA/bin
source /etc/profile
java -version
安装amoeba(变形虫)
mkdir /usr/local/amoeba
tar -zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod 755 /usr/local/amoeba/ -R
/usr/local/amoeba/bin/amoeba #验证amoeba是否安装成功
/usr/local/amoeba/bin/amoeba start & #启动amoeba
netstat -anpt | grep java
4、配置Amoeba(实现读写分离,两个Slave读负载均衡)
- 主MySQL服务器、从MySQL服务器(2台)开放权限
mysql -u root -p123.com
grant all on *.* to 'test'@'192.168.1.%' identified by '123.com';
- 修改amoeba配置文件
vim /usr/local/amoeba/conf/amoeba.xml
<property name="user">amoeba</property> #更改登录amoeba用户
<property name="password">123456</property> #更改登录amoeba密码
<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<property name="LRUMapSize">1500</property>
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>
<property name="needParse">true</property>
vim /usr/local/amoeba/conf/dbServers.xml
<property name="user">test</property> #修改数据库的用户
<property name="password">123.com</property>
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.1</property>
</factoryConfig> #主数据库的IP地址
</dbServer>
<dbServer name="slave1" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.2</property>
</factoryConfig> #从数据库的IP地址
</dbServer>
<dbServer name="slave2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.3</property>
</factoryConfig> #从数据库的IP地址
</dbServer>
<dbServer name="slaves" virtual="true"> #定义IP地址池
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>
<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave1,slave2</property>
5、客户机验证(也需要安装mysql(mariadb))
mysql -u amoeba -p123456 -h 192.168.1.100 -P8066
create database q123; #主从都会复制q123数据库
use q123;
create table biao1 (id int(5),name char(10),address char(10)); #创建表
- 在主MySQL服务器上:
use t223;
insert into biao1 values(1,'1','1'); #从服务器会复制主服务器的数据
#查看master:
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
+------+------+---------+
1 row in set (0.00 sec)
#查看slave1:
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
+------+------+---------+
1 row in set (0.00 sec)
#查看slave2:
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
+------+------+---------+
1 row in set (0.00 sec)
- 在2台从MySQL服务器上:
slave1:
stop slave;
insert into biao1 values(2,'2','2'); #仅能在slave1上有数据222
#查看master:
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
+------+------+---------+
1 row in set (0.00 sec)
#查看slave1
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+------+------+---------+
2 rows in set (0.00 sec)
#查看slave2
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
+------+------+---------+
1 row in set (0.00 sec)
slave2:
stop slave;
insert into biao1 values(3,'3','3'); #仅能在slave2上有数据333
#查看master
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
+------+------+---------+
1 row in set (0.00 sec)
#查看slave1
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+------+------+---------+
2 rows in set (0.00 sec)
#查看slave2
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 3 | 3 | 3 |
+------+------+---------+
2 rows in set (0.00 sec)
- 在客户端上验证
insert into biao1 values(4,'4','4'); #仅能在主mysql上有数据444
#查看客户端
MySQL [q123]> select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 3 | 3 | 3 |
+------+------+---------+
2 rows in set (0.00 sec)
MySQL [q123]> select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+------+------+---------+
2 rows in set (0.00 sec)
###################两个会来回查看,因为slave服务器负责杜#################
#查看master
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 4 | 4 | 4 |
+------+------+---------+
2 rows in set (0.00 sec)
#查看slave1
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+------+------+---------+
2 rows in set (0.00 sec)
#查看slave2
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 3 | 3 | 3 |
+------+------+---------+
2 rows in set (0.00 sec)
~~~shell
id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
+------+------+---------+
2 rows in set (0.00 sec)
#查看slave2
select * from biao1;
+------+------+---------+
| id | name | address |
+------+------+---------+
| 1 | 1 | 1 |
| 3 | 3 | 3 |
+------+------+---------+
2 rows in set (0.00 sec)
实验完成