MySQL主从复制类型及读写分离原理

本文主要给大家介绍MySQL主从复制类型及读写分离原理，其所涉及的东西，从理论知识来获悉，有很多书籍、文献可供大家参考，从现实意义来讲，累计多年的实践经验可分享给大家。

MySQL主从复制的类型

基于语句的复制(默认)

• 在主云服务器上执行的语句，从服务器执行同样的语句

基于行的复制

• 把改变的内容复制到从服务器

混合类型的复制

• 一旦发现基于语句无法精确复制时，就会采用基于行的复制

主从复制的过

MySQL读写分离原理

• 读写分离就是只在主服务器上写，只在从服务器上读
• 主数据库处理事务性查询，而从数据库处理select查询
• 数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库

读写分离的过程

实践操作

实验环境

amoeba服务器IP地址：192.168.144.160master服务器IP地址：192.168.144.151slave1服务器IP地址：192.168.144.163slave2服务器IP地址：192.168.144.145client客户端

在master服务器上安装ntp时间服务器

[root@ameoba ~]# yum install ntp -y   //安装时间服务器[root@master1 ~]# vim /etc/ntp.conf ##在server下添加server 127.127.144.0      //本地是时间源fudge 127.127.144.0 stratum 8   //设置时间层级为8[root@master1 ~]# systemctl start ntpd    //启动时间服务[root@master1 ~]# systemctl stop firewalld   //关闭防火墙[root@master1 ~]# setenforce 0

在slave1上安装ntp，ntpdate服务

[root@slave1 ~]# yum install ntp ntpdate -y[root@slave1 ~]# systemctl start ntpd[root@slave1 ~]# systemctl stop firewalld[root@slave1 ~]# setenforce 0[root@slave1 ~]# /usr/sbin/ntpdate 192.168.144.151    //同步主服务器时间29 Nov 16:58:43 ntpdate[4932]: the NTP socket is in use, exiting

在slave2上安装ntp，ntpdate服务

[root@slave2 ~]# yum install ntp ntpdate -y[root@slave2 ~]# systemctl start ntpd[root@slave2 ~]# systemctl stop firewalld[root@slave2 ~]# setenforce 0[root@slave2 ~]# /usr/sbin/ntpdate 192.168.144.15129 Nov 17:02:08 ntpdate[4850]: the NTP socket is in use, exiting

在master，slave1，slave2上分别安装MySQL-5.5.24

[root@master1 ~]# mkdir /abc   //创建挂载点[root@master1 ~]# mount.cifs //192.168.100.8/LNMP-C7 /abc/   //远程挂载Password for root@//192.168.100.8/LNMP-C7:  [root@master1 ~]# cd /abc/[root@master1 abc]# lsmysql-5.5.24[root@master1 abc]# tar zxvf mysql-5.5.24.tar.gz -C /opt/   //解压[root@master1 opt]# yum install -y \> gcc gcc-c++ \> ncurses \> ncurese-devel \    //控制终端屏幕显示的库> bison \                 //语法分析> make> cmake                 //cmake工具> libaio-devel         //系统调用来实现异步IO[root@slave1 opt]# useradd -s /sbin/nologin mysql   //添加不可登录的mysql用户[root@slave1 opt]# cd /opt/mysql-5.5.24/[root@slave1 mysql-5.5.24]# mkdir /usr/local/mysql   //创建安装目录[root@slave1 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    //端口[root@slave1 mysql-5.5.24]# make && make install   //编译及安装[root@master1 mysql-5.5.24]# chown -R mysql.mysql /usr/local/mysql   //设置mysql属主属组[root@master1 mysql-5.5.24]# vim /etc/profile   //配置环境变量便于系统识别export PATH=$PATH:/usr/local/mysql/bin/[root@master1 mysql-5.5.24]# source /etc/profile   //刷新配置文件[root@master1 mysql-5.5.24]# cp support-files/my-medium.cnf /etc/my.cnf   //主配置文件cp：是否覆盖"/etc/my.cnf"？ yes[root@master1 mysql-5.5.24]# cp support-files/mysql.server /etc/init.d/mysqld   //启动文件[root@master1 mysql-5.5.24]# chmod 755 /etc/init.d/mysqld    //设置权限[root@master1 mysql-5.5.24]# chkconfig --add /etc/init.d/mysqld    //添加到service管理中[root@master1 mysql-5.5.24]# chkconfig mysqld --level 35 on   //开机自启动[root@master1 mysql-5.5.24]# /usr/local/mysql/scripts/mysql_install_db \   //初始化数据库> --user=mysql \> --ldata=/var/lib/mysql \> --basedir=/usr/local/mysql \> --datadir=/home/mysql[root@master1 mysql-5.5.24]# vim /etc/init.d/mysqld    //编辑启动脚本文件basedir=/usr/local/mysql   //找到此处添加路径datadir=/home/mysql[root@master1 mysql-5.5.24]# service mysqld start    //启动MySQLStarting MySQL.. SUCCESS! [root@master1 mysql-5.5.24]# mysqladmin -u root password 'abc123'  //设置密码

配置master主服务器

[root@master1 mysql-5.5.24]# vim /etc/my.cnfserver-id       = 11     //服务ID号log-bin=master-bin                         //主服务器日志文件log-slave-updates=true                   //从服务器更新二进制日志[root@master1 mysql-5.5.24]# service mysqld restart   //重启MySQL服务Shutting down MySQL. SUCCESS! Starting MySQL.. SUCCESS![root@master1 ~]# 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从服务器

[root@slave1 mysql-5.5.24]# vim /etc/my.cnfserver-id       = 22              //另外一台为23relay-log=relay-log-bin                         //从主服务器上同步日志文件记录到本地relay-log-index=slave-relay-bin.index           //定义relay-log的位置和名称[root@slave1 mysql-5.5.24]# service mysqld restart    //重新服务Shutting down MySQL. SUCCESS!  Starting MySQL.. SUCCESS![root@slave1 mysql-5.5.24]# mysql -uroot -pabc123mysql> 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 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

配置slave2从服务器

[root@slave2 mysql-5.5.24]# vim /etc/my.cnfserver-id       = 23              relay-log=relay-log-bin                         //从主服务器上同步日志文件记录到本地relay-log-index=slave-relay-bin.index           //定义relay-log的位置和名称[root@slave2 mysql-5.5.24]# service mysqld restart    //重新服务Shutting down MySQL. SUCCESS!  Starting MySQL.. SUCCESS![root@slave2 mysql-5.5.24]# mysql -uroot -pabc123mysql> 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 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

测试同步情况

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服务器

[root@amoeba ~]# systemctl stop firewalld.service   //关闭防火墙[root@amoeba ~]# setenforce 0[root@amoeba ~]# mount.cifs //192.168.100.8/LNMP-C7 /mnt/  //挂载Password for root@//192.168.100.8/LNMP-C7:  [root@amoeba ~]# cd /mnt/[root@amoeba mnt]# ls[root@amoeba mnt]# cp jdk-6u14-linux-x64.bin /usr/local/   //复制jdk二进制文件到/usr/local下[root@amoeba mnt]# cd /usr/local/[root@amoeba local]# ./jdk-6u14-linux-x64.bin      //直接执行安装Do you agree to the above license terms? [yes or no]yes                //选择yes进行安装Press Enter to continue.....##回车继续[root@amoeba local]# mv jdk1.6.0_14/ /usr/local/jdk1.6         //简化文件名[root@amoeba 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                //环境变量[root@amoeba local]# source /etc/profile            //刷新配置文件[root@amoeba local]# mkdir /usr/local/amoeba         //创建amoeba目录[root@amoeba local]# cd /mnt/[root@amoeba mnt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba      //解压amoeba压缩包[root@amoeba mnt]# chmod -R 755 /usr/local/amoeba/            //设置权限[root@amoeba mnt]# /usr/local/amoeba/bin/amoeba                //检查是否安装成功amoeba start|stop

在master，slave1，slave2上授权amoeba访问

grant all on *.* to test@'192.168.144.%' identified by '123.com';    //给amoeba访问权限用户test密码123.com

回到amoeba服务器修改配置文件

[root@amoeba conf]# vim amoeba.xml       //修改主配置文件---30行-- amoeba        //从服务器同步主服务器的用户密码----32行--------- 123456---117-去掉注释- master master slaves:wq[root@amoeba conf]# vim conf/dbServers.xml       //配置数据库配置文件--26-29--去掉注释-- test 123.com-----42-主服务器地址--- 192.168.144.151--52-从服务器主机名- 192.168.144.163 ##复制6行添加slave2  192.168.144.145--65行左右--  --末尾--slave1,slave2 [root@amoeba conf]# /usr/local/amoeba/bin/amoeba start&      //开启amoeba服务[root@amoeba ~]# 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 

在客户端测试读写分离

[root@client ~]# yum install mysql -y      //安装测试数据库

client创建表

[root@client ~]# mysql -u amoeba -p123456 -h 192.168.144.160 -P8066     //使用amoeba账户密码登录amoebaMySQL [(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 changedMySQL [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 changedmysql> 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)

看了以上介绍MySQL主从复制类型及读写分离原理，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，大家可以继续关注行业资讯板块，会定期给大家更新行业新闻和知识，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。