MySQL主从复制的类型
基于语句的复制(默认)
- 在主服务器上执行的语句，从服务器执行同样的语句
基于行的复制
- 把改变的内容复制到从服务器
混合类型的复制
- 一旦发现基于语句无法精确复制时，就会采用基于行的复制
主从复制的过
MySQL读写分离原理
- 读写分离就是只在主服务器上写，只在从服务器上读
- 主数据库处理事务性查询，而从数据库处理select查询
- 数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库
读写分离的过程
实践操作
实验环境
amoeba服务器IP地址：192.168.144.160
master服务器IP地址：192.168.144.151
slave1服务器IP地址：192.168.144.163
slave2服务器IP地址：192.168.144.145
client客户端在master服务器上安装ntp时间服务器
[[email protected] ~]# yum install ntp -y //安装时间服务器
[[email protected] ~]# vim /etc/ntp.conf
##在server下添加
server 127.127.144.0 //本地是时间源
fudge 127.127.144.0 stratum 8 //设置时间层级为8
[[email protected] ~]# systemctl start ntpd //启动时间服务
[[email protected] ~]# systemctl stop firewalld //关闭防火墙
[[email protected] ~]# setenforce 0在slave1上安装ntp，ntpdate服务
[[email protected] ~]# yum install ntp ntpdate -y
[[email protected] ~]# systemctl start ntpd
[[email protected] ~]# systemctl stop firewalld
[[email protected] ~]# setenforce 0
[[email protected] ~]# /usr/sbin/ntpdate 192.168.144.151 //同步主服务器时间
29 Nov 16:58:43 ntpdate[4932]: the NTP socket is in use, exiting在slave2上安装ntp，ntpdate服务
[[email protected] ~]# yum install ntp ntpdate -y
[[email protected] ~]# systemctl start ntpd
[[email protected] ~]# systemctl stop firewalld
[[email protected] ~]# setenforce 0
[[email protected] ~]# /usr/sbin/ntpdate 192.168.144.151
29 Nov 17:02:08 ntpdate[4850]: the NTP socket is in use, exiting在master，slave1，slave2上分别安装MySQL-5.5.24
[[email protected] ~]# mkdir /abc //创建挂载点
[[email protected] ~]# mount.cifs //192.168.100.8/LNMP-C7 /abc/ //远程挂载
Password for [email protected]//192.168.100.8/LNMP-C7:
[[email protected] ~]# cd /abc/
[[email protected] abc]# ls
mysql-5.5.24
[[email protected] abc]# tar zxvf mysql-5.5.24.tar.gz -C /opt/ //解压
[[email protected] opt]# yum install -y > gcc gcc-c > ncurses > ncurese-devel //控制终端屏幕显示的库
> bison //语法分析
> make
> cmake //cmake工具
> libaio-devel //系统调用来实现异步IO
[[email protected] opt]# useradd -s /sbin/nologin mysql //添加不可登录的mysql用户
[[email protected] opt]# cd /opt/mysql-5.5.24/
[[email protected] mysql-5.5.24]# mkdir /usr/local/mysql //创建安装目录
[[email protected] mysql-5.5.24]# cmake //配置
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql //安装路径
> -DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock //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 //端口
[[email protected] mysql-5.5.24]# make && make install //编译及安装
[[email protected] mysql-5.5.24]# chown -R mysql.mysql /usr/local/mysql //设置mysql属主属组
[[email protected] mysql-5.5.24]# vim /etc/profile //配置环境变量便于系统识别
export PATH=$PATH:/usr/local/mysql/bin/
[[email protected] mysql-5.5.24]# source /etc/profile //刷新配置文件
[[email protected] mysql-5.5.24]# cp support-files/my-medium.cnf /etc/my.cnf //主配置文件
cp：是否覆盖"/etc/my.cnf"？ yes
[[email protected] mysql-5.5.24]# cp support-files/mysql.server /etc/init.d/mysqld //启动文件
[[email protected] mysql-5.5.24]# chmod 755 /etc/init.d/mysqld //设置权限
[[email protected] mysql-5.5.24]# chkconfig --add /etc/init.d/mysqld //添加到service管理中
[[email protected] mysql-5.5.24]# chkconfig mysqld --level 35 on //开机自启动
[[email protected] mysql-5.5.24]# /usr/local/mysql/scripts/mysql_install_db //初始化数据库
> --user=mysql > --ldata=/var/lib/mysql > --basedir=/usr/local/mysql > --datadir=/home/mysql
[[email protected] mysql-5.5.24]# vim /etc/init.d/mysqld //编辑启动脚本文件
basedir=/usr/local/mysql //找到此处添加路径
datadir=/home/mysql
[[email protected] mysql-5.5.24]# service mysqld start //启动MySQL
Starting MySQL.. SUCCESS!
[[email protected] mysql-5.5.24]# mysqladmin -u root password ‘abc123‘ //设置密码配置master主服务器
[[email protected] mysql-5.5.24]# vim /etc/my.cnf
server-id = 11 //服务ID号
log-bin=master-bin //主服务器日志文件
log-slave-updates=true //从服务器更新二进制日志
[[email protected] mysql-5.5.24]# service mysqld restart //重启MySQL服务
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[[email protected] ~]# mysql -uroot -pabc123 //进入数据库
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.144.%‘ IDENTIFIED BY ‘123456‘; //给从服务器提权复制权限，名为myslave密码123456对于144段网段
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)配置slave1从服务器
[[email protected] mysql-5.5.24]# vim /etc/my.cnf
server-id = 22 //另外一台为23
relay-log=relay-log-bin //从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index //定义relay-log的位置和名称
[[email protected] mysql-5.5.24]# service mysqld restart //重新服务
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[[email protected] mysql-5.5.24]# mysql -uroot -pabc123
mysql> change master to master_host=‘192.168.144.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 statusG; ##查看状态
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配置slave2从服务器
[[email protected] mysql-5.5.24]# vim /etc/my.cnf
server-id = 23
relay-log=relay-log-bin //从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index //定义relay-log的位置和名称
[[email protected] mysql-5.5.24]# service mysqld restart //重新服务
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[[email protected] mysql-5.5.24]# mysql -uroot -pabc123
mysql> change master to master_host=‘192.168.144.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 statusG; //查看状态
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测试同步情况
Master
mysql> create database school; //创建school数据库
Query OK, 1 row affected (0.00 sec)Slave1
mysql> show databases; //查看数据库,实现主从同步
--------------------
| Database |
--------------------
| information_schema |
| #mysql50#.mozilla |
| mysql |
| performance_schema |
| school |
| test |
--------------------
6 rows in set (0.01 sec)Slave2
mysql> show databases; //查看数据库,实现主从同步
--------------------
| Database |
--------------------
| information_schema |
| #mysql50#.mozilla |
| mysql |
| performance_schema |
| school |
| test |
--------------------
6 rows in set (0.01 sec)配置amoeba服务器
[[email protected] ~]# systemctl stop firewalld.service //关闭防火墙
[[email protected] ~]# setenforce 0
[[email protected] ~]# mount.cifs //192.168.100.8/LNMP-C7 /mnt/ //挂载
Password for [email protected]//192.168.100.8/LNMP-C7:
[[email protected] ~]# cd /mnt/
[[email protected] mnt]# ls
[[email protected] mnt]# cp jdk-6u14-linux-x64.bin /usr/local/ //复制jdk二进制文件到/usr/local下
[[email protected] mnt]# cd /usr/local/
[[email protected] local]# ./jdk-6u14-linux-x64.bin //直接执行安装
Do you agree to the above license terms? [yes or no]
yes //选择yes进行安装
Press Enter to continue.....
##回车继续
[[email protected] local]# mv jdk1.6.0_14/ /usr/local/jdk1.6 //简化文件名
[[email protected] local]# vim /etc/profile //设置环境变量
...
export JAVA_HOME=/usr/local/jdk1.6 //家目录
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib //class环境变量
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin //环境变量
export AMOEBA_HOME=/usr/local/amoeba //amoeba家目录
export PATH=$PATH:$AMOEBA_HOME/bin //环境变量
[[email protected] local]# source /etc/profile //刷新配置文件
[[email protected] local]# mkdir /usr/local/amoeba //创建amoeba目录
[[email protected] local]# cd /mnt/
[[email protected] mnt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba //解压amoeba压缩包
[[email protected] mnt]# chmod -R 755 /usr/local/amoeba/ //设置权限
[[email protected] mnt]# /usr/local/amoeba/bin/amoeba //检查是否安装成功
amoeba start|stop在master，slave1，slave2上授权amoeba访问
grant all on *.* to [email protected]‘192.168.144.%‘ identified by ‘123.com‘; //给amoeba访问权限用户test密码123.com回到amoeba服务器修改配置文件
[[email protected] conf]# vim amoeba.xml //修改主配置文件
---30行--
<property name="user">amoeba</property> //从服务器同步主服务器的用户密码
----32行---------
<property name="password">123456</property>
---117-去掉注释-
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>
:wq
[[email protected] conf]# vim conf/dbServers.xml //配置数据库配置文件
--26-29--去掉注释--
<property name="user">test</property>
<property name="password">123.com</property>
-----42-主服务器地址---
<dbServer name="master" parent="abstractServer">
<property name="ipAddress">192.168.144.151</property>
--52-从服务器主机名-
<dbServer name="slave1" parent="abstractServer">
<property name="ipAddress">192.168.144.163</property>
##复制6行添加slave2
<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.144.145</property>
--65行左右--
<dbServer name="slaves" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
--末尾--
<property name="poolNames">slave1,slave2</property>
</poolConfig>
[[email protected] conf]# /usr/local/amoeba/bin/amoeba start& //开启amoeba服务
[[email protected] ~]# netstat -anpt | grep java //开启另一个终端查看开启情况
tcp6 0 0 127.0.0.1:26268 :::* LISTEN 40925/java
tcp6 0 0 :::8066 :::* LISTEN 40925/java
tcp6 0 0 192.168.144.160:34090 192.168.144.151:3306 ESTABLISHED 40925/java
tcp6 0 0 192.168.144.160:33866 192.168.144.145:3306 ESTABLISHED 40925/java
tcp6 0 0 192.168.144.160:55984 192.168.144.163:3306 ESTABLISHED 40925/java 在客户端测试读写分离
[[email protected] ~]# yum install mysql -y //安装测试数据库client创建表
[[email protected] ~]# mysql -u amoeba -p123456 -h 192.168.144.160 -P8066 //使用amoeba账户密码登录amoeba
MySQL [(none)]> show databases; //查看数据库
--------------------
| Database |
--------------------
| information_schema |
| #mysql50#.mozilla |
| 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)在client上写入表数据
MySQL [school]> insert into info (id,name,score) values (1,‘zhangsan‘,88); //插入数据内容
Query OK, 1 row affected (0.03 sec)在Master上查看
mysql> select * from info;
---- ---------- -------
| id | name | score |
---- ---------- -------
| 1 | zhangsan | 88.0 |
---- ---------- -------
1 row in set (0.00 sec)在从服务器上查看
mysql> select * from info; //slave上没有写入
Empty set (0.00 sec)在client查看
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)