Mysql搭建双主热备@TOC
windows安装
先每台主机上安装mysql
安装步骤不在阐述
安装完成后,保证两台机器可以相互拼通
大致流程
修改配置文件,主库和从库配置文件有所不同
开始配置。
创建同步用户
在主服务器上为从服务器建立一个连接账户,该账户必须授予replication slave权限。因为服务器A和服务器B互为主从,所以都要分别建立一个同步用户。
200主机 创建给180用的账户
mysql> grant replication slave on *.* to 'kyle'@'192.168.8.180' identified by 'Pa$$w0rd';
// 刷新配置
mysql> flush privileges;
180主机 创建给200用的账户
mysql> grant replication slave on *.* to 'kyle'@'192.168.8.200' identified by 'Pa$$w0rd';
// 刷新配置
mysql> flush privileges;
测试同步用户
创建好同步连接账户后,我们可以在从服务器(slave)上用replicate账户对主服务器(master)数据库进行访问,看下是否能连接成功。
主机200
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h192.168.8.180 -uroot -proot
主机180
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h192.168.8.200 -uroot -proot
如果出现下图,则表示能登录成功,说明这两台机器可以进行双击热备操作
如果连接失败,查看防火墙是否开启,查看是否开启mysql远程访问权限
开启账户的远程访问
GRANT ALL PRIVILEGES ON *.* TO 'kyle'@'%' IDENTIFIED BY 'Pa$$w0rd' WITH GRANT OPTION;
修改配置文件 my.ini
My.ini文件所在路径,不在mysql的安装路径在下列地址
C:\ProgramData\MySQL\MySQL Server 5.7
打开配置文件
180主机
server-id=1 #这是数据库ID,此ID是唯一的,ID值不能重复,否则会同步出错,值区间为1-32.
log-bin = mysql-bin#二进制日志文件,此项为必填项,否则不能同步数据;如果不取名字的话,那么就会以 计算机的名字加编号来命名二进制文件;
binlog-do-db = masters#需要同步的数据库,如果还需要同步另外的数据库,那么继续逐条添加,如果 不写,那么默认同步所有的数据库;这个配置暂时不写
binlog-ignore-db = mysql #不需要同步的数据库;
200主机
server-id=2 #这是数据库ID,此ID是唯一的,ID值不能重复,否则会同步出错,值区间为1-32.
log-bin = mysql-bin#二进制日志文件,此项为必填项,否则不能同步数据;如果不取名字的话,那么就会以 计算机的名字加编号来命名二进制文件;
binlog-do-db = masters#需要同步的数据库,如果还需要同步另外的数据库,那么继续逐条添加,如果 不写,那么默认同步所有的数据库;这个配置暂时不写
binlog-ignore-db = mysql #不需要同步的数据库;
从数据库的话只需要配置server-id,binlog-do-db,binlog-ignore-db即可。
serverid需要和master服务器的区分开,binlog-do-db,binlog-ignore-d的参数需要和主服务器一致。
修改完成之后重启mysql服务
配置主主双机热备
先配置180为主 200为从,再配置200为主.180为从
200配置
以上参数设定好之后在200主机登录mysql , bin目录下运行CMD
mysql -uroot -p
然后输入命令,查看master状态
show master status;
,如果以上配置都成功就会出现一个列表,请记录里面的file和position参数。如下图
完成之后在180服务器建立账号并做slave行为赋权,在slave服务器设定master行为赋权。
1、在180服务器主数据库添加同步账号,这个账号用于salve服务器用这个账号登录master服务器并读取bin-log文件同步数据用,执行如下命令:
命令格式:
grant replication slave on . to ‘用户名称’@‘SLAVE服务器地址’ identified by ‘用户密码’;
例如执行:
grant replication slave on *.* to 'kyle'@'%' identified by 'Pa$$w0rd';
//刷新配置
flush privileges;
2、在200服务器设定同步账号,这个账号用于salve服务器用这个账号登录master服务器并读取bin-log文件同步数据用,执行如下命令:
master_log_file=‘上面我们提到的master服务器看到的file名称’,
master_log_pos=上面我们看到的master服务器看到的position参数;
change master to master_host='192.168.8.180',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=154;
// 各个属性含义
mysql> change master to
-> master_host=180服务器IP’,
-> master_user=180服务器设定的同步用户’,
-> master_password=180服务器设定的同步用户密码’,
-> master_log_file=‘上面我们提到的180服务器看到的file名称’,
-> master_log_pos=上面我们看到的180服务器看到的position参数;
开启slave服务器的slave服务即可。
在slave服务器执行 下列 命令,启动服务。
start slave
验证是否同步:
执行
slave status\G;
slave_io_running和slave_sql_running两个参数均为YES就可以了。
也可以在navicat中查询命令中输入,show slave status 查看
如果不是YES就重启下Mysql再试一下
这时候随便你更改180上的这个任意数据库,都可以看到200服务器的数据库被同步了。
上面是180主200从配置,双机热备配置就是把主库的配置在从库反着来一遍
200 创建同步账号
命令格式:
grant replication slave on . to ‘用户名称’@‘SLAVE服务器地址’ identified by ‘用户密码’;
例如执行:
grant replication slave on *.* to 'root'@'%' identified by 'root';
//刷新配置
flush privileges;
200 设定好之后输入命令
show master status;,
如果以上配置都成功就会出现一个列表,请记录里面的file和position参数。
180 在mysql执行下列命令(把从库作为主库的主库,相互为主)
master_log_file=‘上面我们提到的master服务器看到的file名称’,
master_log_pos=上面我们看到的master服务器看到的position参数;
change master to master_host='192.168.8.200',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=1263;
在180上启动奴隶(slave)
命令:
start slave;
180控制台输入
show slave status\g
或者navicat查询界面输入 show slave 显示如下,
- 此时双机热备配置完成
可以在180数据库加个数据,查看200有没有同步
在200加个数据,在180查看是否同步
都同步的话,就配置完成了
mysql配置文件参数说明:
1.
server-id
ID值唯一的标识了复制群集中的主从服务器,因此它们必须各不相同。master_id必须为1到2^32-1之间的一个正整数值,slave_id值必须为2到2^32-1之间的一个正整数值。
2. log-bin
表示打开binlog,打开该选项才可以通过I/O写到Slave的relay-log,也是可以进行replication的前提。
3. binlog-do-db
表示需要记录二进制日志的数据库。如果有多个数据可以用逗号分隔,或者使用多个binlog-do-dg选项。
4. binlog-ingore-db
表示不需要记录二进制日志的数据库,如果有多个数据库可用逗号分隔,或者使用多binlog-ignore-db选项。
5. replicate-do-db
表示需要同步的数据库,如果有多个数据可用逗号分隔,或者使用多个replicate-do-db选项。
6. replicate-ignore-db
表示不需要同步的数据库,如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项。
7. log-slave-updates
配置从库上的更新操作是否写入二进制文件,如果这台从库,还要做其他从库的主库,那么就需要打这个参数,
以便从库的从库能够进行日志同步。
8. slave-skip-errors
在复制过程,由于各种原因导致binglo中的sql出错,默认情况下,从库会停止复制,要用户介入。可以设置slave-skip-errors
来定义错误号,如果复制过程中遇到的错误是定义的错误号,便可以路过。
如果从库是用来做备份,设置这个参数会存在数据不一致,不要使用。如果是分担主库的查询压力,可以考虑。
9. sync_binlog=1 Or N
Sync_binlog的默认值是0,这种模式下,MySQL不会同步到磁盘中去。这样的话,Mysql依赖操作系统来刷新
二进制日志 binary log,就像操作系统刷新其他文件的机制一样。因此如果操作系统或机器(不仅仅是 Mysql服务器)崩溃,
有可能binlog中最后的语句丢失了。要想防止这种情况,可以使用 sync_binlog全局变量,使binlog在每N次binlog 写入后
与硬盘同步。当sync_binlog 变量设置为1是最安全的,因为在crash崩溃的情况下,你的二进制日志binary log只有可能丢失
最多一个语句或者一个事务。但是,这也是最慢的一种方式(除非磁盘有使用带蓄电池后备电源的缓存cache,使得同步到磁盘的
操作非常快)。
即使sync_binlog设置为1,出现崩溃时,也有可能表内容和binlog内容之间存在不一致性。如果使用 InnoDB表,
Mysql服务器处理COMMIT语句,它将整个事务写入binlog并将事务提交到InnoDB中。 如果在两次操作之间出现崩溃,重启时,
事务被InnoDB回滚,但仍然存在binlog中。可以用-innodb-safe-binlog选项来增加InnoDB表内容和binlog之间的一致性。
(注释:在Mysql 5.1版本 中不需要-innodb-safe-binlog;由于引入了XA事务支持,该选项作废了),该选项可以提供更大
程度的安全,使每个事务的binlog(sync_binlog=1)和(默认情况为真) InnoDB日志与硬盘同步,该选项的效果是
崩溃后重启时,在滚回事务后,Mysql服务器从binlog剪切回滚的InnoDB 事务。这样可以确保binlog反馈InnoDB表
的确切数据等,并使从服务器保持与主服务器保持同步(不接收回滚的语句)。
10. auto_increment_offset和auto_increment_increment
Auto_increment_increment和auto_increment_offset用于主-主服务器(master-to-master)复制,
并可以用来控制AUTO_INCREMENT列的操作。两个变量均可以设置为全局或局部变量,并且假定每个值都可以为1到
65,535之间的整数值。将其中一个变量设置为0会使该变量为1。
这两个变量影响AUTO_INCREMENT列的方式:auto_increment_increment控制列中的值的增量值,
auto_increment_offset确定AUTO_INCREMENT列值的起点。
如果auto_increment_offset的值大于auto_increment_increment的值,则auto_increment_offset的值被忽略。
例如:表内已有一些数据,就会用现在已有的最大自增值做为初始值。
11. max_binlog_size
单个二进制日志文件最大的大小,超过此大小会自动创建新的二进制日志文件
12. expire_logs_days
二进制日志的过期天数,过期的日志将会被自动删除,减少日志占用硬盘的容量
13. binlog_format
二进制日志格式,三种模式:statement语句模式,row行模式,mixed混合模式。
当设置隔离级别为read-commited必须设置二进制日志格式为row,现在mysql官方认为statement这个已经不再适合继续使用;
但mixed类型在默认的事务隔离级别下,可能会导致主从数据不一致;
linux安装
- 先安装好mysql数据库
linux安装后的mysql默认配置文件是在/etc/my.cnf
mysql启动停止命令
systemctl stop mysql
查看防火墙状态
systemctl status firewalld
关闭防火墙
systemctl stop firewalld
关闭防火墙命令:systemctl stop firewalld.service
开启防火墙:systemctl start firewalld.service
关闭开机自启动:systemctl disable firewalld.service
开启开机启动:systemctl enable firewalld.service
3。添加用户,授权replication slave就可以
在A库上创建一个 B库上创建一个
#创建授权用户 创建一个给另外一个mysql用于同步数据的账户 两个库都需要创建
grant replication slave on *.* to 'kyleA'@'%' identified by 'Pa$$w0rd';
刷新权限
flush privileges;
#创建授权用户 创建一个给另外一个mysql用于同步数据的账户 两个库都需要创建
grant replication slave on *.* to 'kyleB'@'%' identified by 'Pa$$w0rd';
刷新权限
flush privileges;
- 在A库使用命令看能不能连接到B库,账号密码为上边 B库创建的账号密码
mysql -u kyleB -h192.168.133.130 -p
如果不能访问,查看防火墙,或者查看该账户是否开启远程访问
#开启远程访问命令 需要登录mysql运行
GRANT ALL PRIVILEGES ON *.* TO 'kyleB'@'%' IDENTIFIED BY 'Pa$$w0rd' WITH GRANT OPTION;
刷新权限
flush privileges;
5 修改配置文件(mysql默认配置文件是在/etc/my.cnf)
配置文件A
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir = /opt/mysql/mysql-5.7
datadir = /opt/mysql/mysql-5.7/data
port = 3306
socket = /tmp/mysql.sock
character-set-server=utf8
log-error = /opt/mysql/mysql-5.7/data/mysqld.log
pid-file = /opt/mysql/mysql-5.7/data/mysqld.pid
server_id=1
log-bin = mysql-bin
binlog-ignore-db = mysql
log_slave_updates=1
slave-skip-errors=all
auto_increment_increment=2
auto_increment_offset=1
配置文件B
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
basedir = /opt/mysql/mysql-5.7
datadir = /opt/mysql/mysql-5.7/data
port = 3306
socket = /tmp/mysql.sock
character-set-server=utf8
log-error = /opt/mysql/mysql-5.7/data/mysqld.log
pid-file = /opt/mysql/mysql-5.7/data/mysqld.pid
server_id=2
log-bin = mysql-bin
binlog-ignore-db = mysql
log_slave_updates=1
slave-skip-errors=all
auto_increment_increment=2
auto_increment_offset=2
:wq 保存并退出
6. 然后重启mysql服务
service mysql restart
- 分别登陆mysql数据库 登陆root账户,查看master状态,需要记录 file
show master status;
如果以上配置都成功就会出现一个列表,请记录里面的file和position参数。如下图
注意 两个状态的值可能不一样 切记不要写反
8. 然后分别交叉运行下列命令: A主机运行命令时,写B主机之前创建的那个同步账号,和B主机IP 和第七步查到的B主机上的logFile 和 pos
B主机运行 A主机上创建的那个同步账号,master_host是A主机的IP ,logfile和pos是A主机上的状态值。
master_log_file=‘上面我们提到的master服务器看到的file名称’,
master_log_pos=上面我们看到的master服务器看到的position参数;
A主机运行
change master to master_host='192.168.71.129',master_user='kyle',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
B主机运行
change master to master_host='192.168.71.128',master_user='kyle',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=154;
// 各个属性含义
mysql> change master to
-> master_host=180服务器IP',
-> master_user=180服务器设定的同步用户',
-> master_password=180服务器设定的同步用户密码',
-> master_log_file='上面我们提到的180服务器看到的file名称',
-> master_log_pos=上面我们看到的180服务器看到的position参数;
- 启动主从(在root账户下运行)
start slave;
- 查看主从状态
show slave status\G;
slave_io_running和slave_sql_running两个参数均为YES就可以了。
- 设置mysql开机启动
systemctl enable mysql
- 双机热备配置就是把主库的配置在从库反着来一遍 .两个主机分别执行上面步骤
支持 双机热备结束 - 验证 在A主机中创建一个数据库 刷新B主机上的数据库就可以看到对应的数据库了 反之也一样的。