MySQL主从复制概念
-
Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的。复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。
- 注:在进行mysql复制时,所有对复制中的表的更新必须在主服务器上进行。否则必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。
MySQL主从复制原理
(1)MySQL支持复制类型
- 基于语句的复制: 在主服务器上执行的SQL语句,在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。一旦发现没法精确复制时,会自动选着基于行的复制。
- 基于行的复制:把改变的内容复制过去,而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
- 混合类型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。
(2)MySQL主从复制过程
- MySQL主从复制基本流程图:
-
复制过程详解:
- 1、在每个事物更新数据完成之前,master主服务器在二进制日志记录这些改变,写入二进制日志完成后,master通知存储引擎提交事物。
- 2、slave将master主服务器的二进制日志Binary log 复制到其中继日志Relay log,首先slave(从服务器)开始一个工作线程——I/O线程,I/O线程在master上打开一个普通的连接,然后开始Binlog dump process,该进程从master的二进制日志中读取事件,如果已经跟上master,它会睡眠并等待master产生新的事件,而I/O线程将这些事件写入中继日志。
- 3、SQLslave thread(SQL从线程)处理该过程的最后一步,SQL线程从中继日志中读取事件,并重放其中的事件而更新salve数据,使其与master中数据保持一致,只要该线程与I/O线程保持一致,中继日志通常会位于OS缓存中(也就是内存当中),所以中继开销很小。
(3)主从复制注意点
- 1、master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)。
- 2、slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和master数据保持一致了。
- 3、Mysql复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。
- 4、Mysql复制最好确保master和slave服务器上的Mysql版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本)。
- 5、master和slave两节点间时间需同步。
MySQL主从服务部署
实验环境
服务器 | 服务器系统 | IP地址 | 所需服务 |
---|---|---|---|
MySQL主服务器 | centos7.3 | 192.168.144.128 | ntp,mysql5.7 |
MySQL从服务器 | centos7.3 | 192.168.144.129 | ntp,mysql5.7 |
MySQL主服务器
- 设置主服务器为本地时钟源
安装时间同步软件:
yum install ntp -y
vim /etc/ntp.conf
末行添加:
server 127.127.144.0 //设置本地是时钟源,注意自己所在网段是144段
fudge 127.127.144.0 stratum 8 //设置时间层级为8(限制在15内)
service ntpd start
service iptables stop
setenforce 0
- 安装MySQL软件
这里在centos7上安装MySQL5.7版本,详细过程请参见MySQL5.7安装
- 安装好MySQL后:
vim /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 10 //设置主从服务器身份唯一标识
log-bin=master-bin //开启二进制日志
log-slave-updates=true //开启主从功能
systemctl restart mysqld.service
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.144.%' IDENTIFIED BY '123456'; //将从服务器权限赋予账号myslave,可以在192.168.144.0网段登录,密码为123456
FLUSH PRIVILEGES; //刷新状态
show master status; //查看主服务器状态
- 切记主服务器状态!
MySQL从服务器
- 安装ntp时间同步软件,由于主服务器已经设置了时间源,从服务器只需打开时间同步服务,并跟主服务器同步即可。
yum install ntp -y
systemctl restart mysqld.service
systemctl stop firewalld/service
setenforce 0
/usr/sbin/ntpdate 192.168.144.128 //进行时间同步//
- 安装MySQL软件
vim /etc/my.cnf
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 20 //设置唯一身份标识
relay-log=relay-log-bin //打开中继日志功能
relay-log-index=slave-relay-bin.index //定义relay-log的位置和名称
- 进入数据库
mysql -u root -p //
change master to master_host='192.168.144.128',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604; //切记根据主服务器状态更改相应二进制日志文件和偏移量;
start slave; //开启从服务器状态
show slave status\G; //查看状态
验证主从结果
- 当选择在主MySQL服务器中创建数据,从服务器中也会同步更新数据文件。
会遇到的问题:
数据库已搭建完成,各种配置均已完成,但是在start slave ;的时候,报错:
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
这里说,我没有配置为从机,但我明明配置过的啊。
MySQL版本:5.6.19
主:master IP : 172.17.210.199
从:slave IP :172.17.206.138
先看看从机172.17.206.138的my.cnf
[root@liuyazhuang ~]# vi /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
skip-grant-tables
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
tmpdir=/tmp
[mysqld_safe]
log-error=/usr/local/data/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
###############以下是添加主从的配置
server_id = 2
log-bin = /usr/local/mysql/log/solve-bin.log
master-host = 172.17.210.199
master-user = test
master-pass = 123456
master-port = 3306
master-connect-retry = 60
这是MySQL主机172.17.210.199的my.cnf
[root@liuyazhuang ~]$ cat /etc/my.cnf
[mysqld]
log-bin = /u01/mysql/log/masters-bin.log
read-only = 0
basedir = /u01/mysql
datadir = /u01/mysql/data
port = 3306
server_id = 1
socket = /tmp/mysql.sock
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
尝试过多次 重启主从还是报错;
接着手动chang to 后还是一样报错
mysql> CHANGE MASTER TO
MASTER_HOST='172.17.210.199',
MASTER_USER='test',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='masters-bin.000003',
MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
在从库服务器执行START slave;命令启动slave
mysql> START slave;
报错:
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
于是查看日志
1.查看SLAVE172.17.206.138上的Mysql报错日志,有这么一句:
141009 6:06:29 [ERROR] Server id not set, will not start slave
意思是,slave的server-id没有设置。
那就奇怪了,我明明在配置文件里面指定了server-id的了,并且有重启mysql服务,难道不起效?
分别在主从上执行命令“show variables like 'server_id';”。
-------从机上面查看端口
mysql> mysql> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 0 |
+---------------+-------+
1 row in set (0.00 sec)
命名设置的是2,怎么会变成0
-------主机上面查看
mysql> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.00 sec)
跟设置的一样。
既然参数文件不生效,就试试在数据库命令里面设置:在从机 172.17.206.138上执行命令
mysql > SET GLOBAL server_id=2;
再次在从机 172.17.206.138上执行slave start和show slave status,成功了。
注意!!!由于“SET GLOBAL server_id=;”命令会在mysql服务重启后丢失,所以一定要写到配置文件里面。
但为什么我之前修改了my.cnf文件不起效?
仔细排查,发现配置里面有[mysqld]和[mysqld_safe],之前将修改的配置内容基本都放在了[mysqld_safe]下面,新增的配置文件放的位置不一样也有关系?于是我尝试把配置文件里修改的部分放在[mysqld]下面,于是改成这样:
[root@liuyazhuang]# cat /etc/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
skip-grant-tables
user=mysql
symbolic-links=0
tmpdir=/tmp
server_id = 2
log-bin = /usr/local/mysql/log/solve-bin.log
master-host = 172.17.210.199
master-user = test
master-pass = 123456
master-port = 3306
master-connect-retry = 60
[mysqld_safe]
log-error=/usr/local/data/mysqld.log
pid-file=/usr/local/mysql/data/mysqld.pid
如图,就是将图中标注的内容从[mysqld_safe]下修改到[mysqld]下
修改之后,再次同步,成功了!
mysql>
mysql> STOP slave;
Query OK, 0 rows affected (0.05 sec)
mysql> START slave;
Query OK, 0 rows affected (0.00 sec)
如何连接到
1、先 ping 一下数据库服务器的ip 地址确认网络畅通。
2、关闭数据库服务的防火墙
service iptables stop
3、 确认Mysql中已经有可以通过远程登录的账户
select * from mysql.user where user='li4' and host='%';
如果没有用户,先执行如下命令:
grant all privileges on *.* to li4@'%' identified by '123123';
4、测试连接:
5.如果使用的是Navicat远程工具,如果连接出错,在my.conf配置文件的mysqld下添加以下配置
skip-name-resolve