MySQL主从复制的类型
基于语句的复制(默认)
在主服务器上执行的语句,从服务器执行同样的语句
基于行的复制
把改变的内容复制到从服务器
混合类型的复制
一旦发现基于语句无法精确复制时,就会采用基于行的复制
主从复制的工作过程
MySQL读写分离原理
读写分离就是只在主服务器上写,只在从服务器上读
主数据库处理事务性查询,从而数据库处理select查询
数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库
实验环境
amoeba服务器(192.168.142.160)
master服务器(192.168.142.151)
slave1服务器 (192.168.142.163)
slave2服务器 (192.168.142.145)
client客户端 (192.168.142.143)
1,在master服务器上安装ntp时间服务器
[root@ameoba ~]
[root@master1 ~]
server 127.127.13.0
fudge 127.127.13.0 stratum 8
[root@master1 ~]
[root@master1 ~]
[root@master1 ~]
2,在slave1上安装ntp,ntpdate服务
[root@slave1 ~]
[root@slave1 ~]
[root@slave1 ~]
[root@slave1 ~]
[root@slave1 ~]
29 Nov 16:58:43 ntpdate[4932]: the NTP socket is in use, exiting
3,在slave2上安装ntp,ntpdate服务
[root@slave2 ~]
[root@slave2 ~]
[root@slave2 ~]
[root@slave2 ~]
[root@slave2 ~]
29 Nov 17:02:08 ntpdate[4850]: the NTP socket is in use, exiting
4,在master,slave1,slave2上分别安装MySQL
[root@master1 ~]
[root@master1 ~]
Password for root@//192.168.100.1/LNMP-C7:
[root@master1 ~]
[root@master1 abc]
mysql-5.5.24
[root@master1 abc]
[root@master1 opt]
> gcc gcc-c++ \
> ncurses \
> ncurese-devel \
> bison \
> make
> cmake
> libaio-devel
[root@slave1 opt]
[root@slave1 opt]
[root@slave1 mysql-5.5.24]
[root@slave1 mysql-5.5.24]
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
> -DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock \
> -DDEFAULT_CHARSET=utf8 \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DWITH_EXTRA_CHARSETS=all \
> -DWITH_MYISAM_STORAGE_ENGINE=1 \
> -DWITH_INNOBASE_STORAGE_ENGINE=1 \
> -DWITH_MEMORY_STORAGE_ENGINE=1 \
> -DWITH_READLINE=1 \
> -DENABLED_LOCAL_INFILE=1 \
> -DMYSQL_DATADIR=/home/mysql \
> -DMYSQL_USER=mysql \
> -DMYSQL_TCP_PORT=3306
[root@slave1 mysql-5.5.24]
[root@master1 mysql-5.5.24]
[root@master1 mysql-5.5.24]
export PATH=$PATH:/usr/local/mysql/bin/
[root@master1 mysql-5.5.24]
[root@master1 mysql-5.5.24]
cp:是否覆盖"/etc/my.cnf"? yes
[root@master1 mysql-5.5.24]
[root@master1 mysql-5.5.24]
[root@master1 mysql-5.5.24]
[root@master1 mysql-5.5.24]
[root@master1 mysql-5.5.24]
> --user=mysql \
> --ldata=/var/lib/mysql \
> --basedir=/usr/local/mysql \
> --datadir=/home/mysql
[root@master1 mysql-5.5.24]
basedir=/usr/local/mysql
datadir=/home/mysql
[root@master1 mysql-5.5.24]
Starting MySQL.. SUCCESS!
[root@master1 mysql-5.5.24]
5,配置master主服务器
[root@master1 mysql-5.5.24]
server-id= 11
log-bin=master-bin
log-slave-updates=true
[root@master1 mysql-5.5.24]
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@master1 ~]
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.142.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 | 338 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6,配置slave1从服务器
[root@slave1 mysql-5.5.24]
server-id = 22
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@slave1 mysql-5.5.24]
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@slave1 mysql-5.5.24]
mysql> change master to master_host='192.168.142.151',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=338;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 338
Relay_Log_File: relay-log-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
7,配置slave2从服务器
[root@slave2 mysql-5.5.24]
server-id = 23
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@slave2 mysql-5.5.24]
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@slave2 mysql-5.5.24]
mysql> change master to master_host='192.168.142.151',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=338;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G;
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 338
Relay_Log_File: relay-log-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
8,测试主从同步情况
主服务器操作:
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
从服务器1操作:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
6 rows in set (0.01 sec)
从服务器2操作:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
6 rows in set (0.01 sec)
9,配置amoeba服务器
[root@amoeba ~]
[root@amoeba ~]
[root@amoeba ~]
Password for root@//192.168.100.1/LNMP-C7:
[root@amoeba ~]
[root@amoeba mnt]
[root@amoeba mnt]
[root@amoeba mnt]
[root@amoeba local]
Do you agree to the above license terms? [yes or no]
yes
Press Enter to continue.....
[root@amoeba local]
[root@amoeba local]
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin
[root@amoeba local]
[root@amoeba local]
[root@amoeba local]
[root@amoeba mnt]
[root@amoeba mnt]
[root@amoeba mnt]
amoeba start|stop
10,在master,slave1,slave2上提权amoeba访问权限
grant all on *.* to test@'192.168.142.%' identified by '123.com';
11,修改amoeba服务器配置文件
[root@amoeba conf]
<property name="user">amoeba</property>
<property name="password">123456</property>
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>
[root@amoeba conf]
<property name="user">test</property>
<property name="password">123.com</property>
<dbServer name="master" parent="abstractServer">
<property name="ipAddress">192.168.142.151</property>
第52行添加从服务器slave1地址
<dbServer name="slave1" parent="abstractServer">
<property name="ipAddress">192.168.142.163</property>
<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.142.145</property>
<dbServer name="slaves" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<property name="poolNames">slave1,slave2</property>
</poolConfig>
[root@amoeba conf]
[root@amoeba ~]
tcp6 0 0 127.0.0.1:26268 :::* LISTEN 40925/java
tcp6 0 0 :::8066 :::* LISTEN 40925/java
tcp6 0 0 192.168.142.160:34090 192.168.142.151:3306 ESTABLISHED 40925/java
tcp6 0 0 192.168.142.160:33866 192.168.142.145:3306 ESTABLISHED 40925/java
tcp6 0 0 192.168.142.160:55984 192.168.142.163:3306 ESTABLISHED 40925/java
12,在客户端测试读写分离
[root@client ~]
测试机创建表
[root@client ~]
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
|
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
6 rows in set (0.00 sec)
MySQL [(none)]> use school;
Database changed
MySQL [school]> create table info (
-> id int(4) not null primary key,
-> name varchar(10) not null,
-> score decimal(4,1) not null);
Query OK, 0 rows affected (0.02 sec)
查看主从服务器上是否有创建的表
mysql> use school;
Database changed
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info |
+------------------+
1 row in set (0.00 sec)
在两台从服务器上关闭同步slave
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
MySQL [school]> insert into info (id,name,score) values (1,'zhangsan',88);
Query OK, 1 row affected (0.03 sec)
在主服务器上查看
mysql> select * from info;
+----+----------+-------+
| id | name | score |
+----+----------+-------+
| 1 | zhangsan | 88.0 |
+----+----------+-------+
1 row in set (0.00 sec)
在从服务器上查看
mysql> select * from info;
Empty set (0.00 sec)
在测试机查看
MySQL [school]> select * from info;
Empty set (0.01 sec)
在slave1写入内容
mysql> insert into info (id,name,score) values (2,'lisi',70);
Query OK, 1 row affected (0.00 sec)
在slave2写入内容
mysql> insert into info (id,name,score) values (3,'wuwang',60);
Query OK, 1 row affected (0.00 sec)
在测试机上查看info表数据(轮询的方式)
MySQL [school]> select * from info;
+----+--------+-------+
| id | name | score |
+----+--------+-------+
| 3 | wuwang | 60.0 |
+----+--------+-------+
1 row in set (0.00 sec)
MySQL [school]> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 2 | lisi | 70.0 |
+----+------+-------+
1 row in set (0.00 sec)
在主服务器上写如数据,在从服务器上读取数据,采用的是轮询机制,从而实现读写分离!