Mysql主从双机备份

Aug 10, 2017 | Experience | 阅读

文章目录

准备
一主一从
互主互从&一主多从
参考

准备

Ubuntu 16.04.3。在digitalocean上设了两个VPS，Master_VPS & Slave_VPS。
安装MySql
1
2
3
sudo apt-get install mysql-server
apt install mysql-client 
apt install libmysqlclient-dev
如果过程中有出现找不到资源的情况，跟新apt-get.
1
apt-get update
检查是否安装成功
1
2
root@Master:~# sudo netstat -tap | grep mysql
tcp        0      0 localhost:mysql         *:*                     LISTEN      3056/mysqld
开启远程访问
注释掉bind-address=127.0.0.1这一行。
1
vi /etc/mysql/mysql.conf.d/mysqld.cnf
进入mysql，修改权限。
1
2
3
4
mysql -u root -p123456 
grant all privileges on *.* to '用户名'@'%' identified by '123456'
flush privileges;
exit;
用户名我设为master和slave。(其实这一步和主菜没什么关系……)
重启mysql
1
/etc/init.d/mysql restart
检查远程访问Mysql是否成功。
1
mysql -h IP -u master -p

到此为止准备工作完成，开始正餐。

一主一从

主从数据库技术应用非常广泛，也非常通用简单。可以合理分流，将大规模的Select操作分到Slave里，只留插入、修改的操作到Master。读写分离，负载均衡。
理论说明

Master端
数据库设置
在Master_VPS中添加账号’MySlave1’，并且只允许Slave_VPS的IP登录
1
2
3
grant replication slave on *.* to 'MySlave1'@'SlaveIP' identified by '123456';
flush privileges;
exit;
重启mysql
1
/etc/init.d/mysql restart
要修改/etc/my.cnf文件。但是Mysql5.5以后就改变这个文件设计，有的人说，去吧my-medium.cnf复制到/etc/目录下，很不幸的告诉你，在MySql5.7里面，这个my-medium.cnf也被取消了。
不信你可以用
1
find / -name my-medium.cnf
找找看啊。
仔细阅读安装mysql时显示的detail，你会发现my.cnf在etc/mysql/my.cnf。在这个文件末尾添加以下代码，打开binarylog功能
1
2
3
4
5
6
7
8
9
10
11
[mysqld]
server-id = 1
log-bin = mysql-log
binlog-do-db=Test
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
auto-increment-increment = 10
auto-increment-offset = 1
注意，这时我需要双机备份的数据库是Test，里面有一张user表，其他数据库变化我给忽略了。

重启mysql，进入后
1
show variables like '%log%';
查看log_bin是否为on。
1
show master status\G
查看日志记录情况。

同步数据库

锁定数据库
1
FLUSH TABLES WITH READ LOCK;
导出数据库
1
mysqldump --master-data -uroot -p Test > Test.sql
记住这个，然后解锁
1
UNLOCK TABLES

Slave端

数据库设置

1	/etc/mysql/my.cnf

末尾加入

[mysqld]
server-id = 2
log-bin = mysql-log
binlog-do-db=Test
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
auto-increment-increment = 10
auto-increment-offset = 1
relay_log=mysql-relay-bin
log-slave-updates =ON

用scp命令讲Master的Test.sql，传到Slave上。此时最好现在Slave的Mysql里建一个Test数据库再导入数据。

1	mysql -u root -p Test < Test.sql

进入mysql，输入以下代码

CHANGE MASTER TO
MASTER_HOST='MasterIP',
MASTER_USER='MySlave1',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-log.000002',
MASTER_LOG_POS=154;

查看slave进程有无启动

1	show slave status\G

奴隶进程

如果你的Slave_IO_Running,Slave_SQL_Running显示是no，多半是Master没打开防火墙。
在master输入以下代码
1
2
3
4
sudo apt-get install ufw 
sudo ufw enable 
sudo ufw default deny 
sudo ufw allow from Slave'sIP
然后重启Slave的MySql，即可。

检查

在Master_VPS中插入一些数据，查看确认。在Slave_VPS中查看，能看到新数据。

互主互从&一主多从

上面讲的是一主一从关系，意思就是说，Slave会copy Master的动作，但是Master不会Copy Slave的动作。单向热备份，但是你的BOSS要你双向备份呢？其实就是交换Slave和Master的身份，重复上面的步骤就可以了。
同理，一主多从也是很简单的。
操作都是一样的我也不多说了。
遇到困难看日志。在mysql中输入
1
show variables like '%log_error%';
查看错误日志的地址，如查找出错的原因就可以了。

参考

binyao02123202的专栏

SQL