MySQL主从配置

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL主从配置

技术小胖子 2017-11-08 18:55:00 浏览564
展开阅读全文

MySQL主从(MySQL Replication),主要用于MySQL的时时备份或者读写分离。在配置之前先做一下准备工作,配置两台 mysql 服务器,如果你的机器不能同时跑两台 Linux 虚拟机,那可以考虑在同一个机器上跑两个 mysql 服务。 


一、原理


1、每个从仅可以设置一个主。 

2、主在执行 sql 之后,记录二进制 log 文件(bin-log)。 

3、从连接主,并从主获取 binlog,存于本地 relay-log,并从上次记住的位置起执行 sql,一旦遇到错误则停止同步。 


二、推论


1、主从间的数据库不是实时同步,就算网络连接正常,也存在瞬间,主从数据不一致。 

2、如果主从的网络断开,从会在网络正常后,批量同步。 

3、如果对从进行修改数据,那么很可能从在执行主的 bin-log 时出现错误而停止同步,这个是很危险的操作。所以一般情况下,非常小心的修改从上的数据。 

4、一个衍生的配置是双主,互为主从配置,只要双方的修改不冲突,可以工作良好。 

5、如果需要多主的话,可以用环形配置,这样任意一个节点的修改都可以同步到所有节点。  

6、可以应用在读写分离的场景中,用以降低单台 MySQL 服务器的 I/O。

7、可以实现 MySQL 服务的 HA 集群。

8、可以是 1 主多从,也可以是相互主从(主主)。 


三、主从配置


说明:主库IP:192.168.0.109   从库IP:192.168.0.104


1、安装MySQL

分别在主库的服务器和从库的服务器上安装mysql,版本都为MySQL-5.1.73;详细步骤见我之前的文章LAMP环境搭建中mysql的安装配置,在这里就不阐述了。


2、主库的配置

首先创建一个数据库db1

[root@tpp ~]# mysql
mysql> create database db1;
mysql> quit

:数据库安装完成后默认会生成两个库:mysql库与test库;为了做方便试验,我们直接把mysql库复制给db1库(下面采用备份恢复方法来实现),你也可以自己在db1库里新建表。

[root@tpp ~]# mysqldump mysql > 123.sql
[root@tpp ~]# mysql db1 < 123.sql
[root@tpp ~]# mysql
mysql> use db1;
mysql> show tables;

如图:

wKioL1YBTW-x9sDFAADhTb_b_7E268.jpg


接着退出,编辑配置文件

[root@tpp ~]# vim /etc/my.cnf          //在[mysqld]段添加以下内容
server-id = 1                    
log-bin=mysql-bin  
max_binlog_size = 500M              
binlog_cache_size = 128K      
binlog-do-db = db1          
log-slave-updates        
expire_logs_day=2          
binlog_format=mixed

如图:       

wKioL1YBWzvh7HiUAAF8o-qNdNA217.jpg

参数说明:


(1)server-id = 1                           //服务器标志号,一组主从中此标识号不能重复。
(2)log-bin=mysql-bin                       //开启bin-log,并指定文件目录和文件名前缀。
(3)max_binlog_size=500M                    //当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
(4)binlog_cache_size = 128K          //日志缓存大小
(5)binlog-do-db = db1                //需要同步的数据库名字,如果是多个,以逗号隔开继续加库名
:如果有很多库要设置主从,则可以设黑名单。
binlog-do-db = db1
改为:
binlog-ignore-db = mysql              //不需要同步的数据库名字。
(6)log-slave-updates                 //当Slave从Master数据库读取日志时更新新写入日志中,如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。
(7)expire_logs_day=2                 //bin-log日志文件保存的天数
  binlog_format=mixed                 //bin-log日志文件格式为:MIXED,可以防止主键重复
:配置expire_logs_day与binlog_format参数,只有mysql版本是5.0以上才有。




然后重启mysql服务

[root@tpp ~]# /etc/init.d/mysqld restart
Shutting down MySQL....           [确定]
Starting MySQL..                  [确定]

在/data/mysql/目录(即MySQL安装目录)下可以看到生成了以mysql-bin开头的一些文件,如下图:

wKioL1YBXA-DEX2_AAEkIroFjWc366.jpg


最后我们添加一个用于主从同步的用户:repl,密码为:123456

[root@tpp ~]# mysql
mysql> grant replication slave on *.* to 'repl'@'192.168.0.109' identified by '123456';
mysql> flush privileges;               //刷新权限
mysql> flush tables with read lock;    //锁死表读
mysql> show master status;             //查看状态,待会儿要用这个参数

如图:

wKioL1YBWQqgyhC5AAEjErXgwZ8162.jpg


3、从库的配置

首先新建一个和主数据库一样的db1库。我同样是复制之前的123.sql数据库到bd1库,这个是通过主服务器中的vsftpd文件服务下载到本地,然后恢复到db1库。

[root@tpp ~]# lftp test2@192.168.0.109
口令:
lftp test2@192.168.0.109:~> ls
-rw-r--r--    1 504      504             0 Sep 14 17:36 1.txt
-rw-r--r--    1 0        0          527174 Sep 22 21:12 123.sql
drwxr-xr-x    2 504      504          4096 Sep 14 17:36 222

下载在本地

lftp test2@192.168.0.109:/> get 123.sql -o /data/
lftp test2@192.168.0.109:/> quit

复制123.sql库到db1库

[root@tpp ~]# cd /data/
[root@tpp data]# mysql -e "create database db1"
[root@tpp data]# mysql db1 < 123.sql
[root@tpp data]# mysql
mysql> use db1;
mysql> show tables;    //可以看到表和主一样


接着编辑配置文件

[root@tpp ~]# vim /etc/my.cnf        //修改以下地方
server-id = 2
master-host=192.168.0.109
master-user=repl
master-password=123456
master-port=3306
master-connect-retry=30             //控制重试间隔,默认为60秒
slave-skip-errors=1062              //忽略错误,1062为主键重复错误
replicate-do-db = db1
slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 //忽略一些其他错误,不影响数据库完整性

保存退出。重启下mysql服务

[root@tpp ~]# /etc/init.d/mysqld restart
Shutting down MySQL....           [确定]
Starting MySQL..                  [确定]


同步数据库

[root@tpp ~]# mysql
mysql> slave stop;
mysql> change master to master_host='192.168.0.109',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000021',master_log_pos=524861;

mysql> slave start;

mysql> show slave status\G;

显示出现了错误:从数据库无法同步

Slave_IO_Running: No
Slave_SQL_Running: Yes
Seconds_Behind_Master: NULL
Last_IO_Error: error connecting to master 'repl@192.168.0.109:3306' - retry-time: 60 retries: 86400

这个问题很有可能就是密码不对,而我的问题是因为没有关闭防火墙,关闭防火墙后我再show下,发现问题解决了。

wKioL1YBbOzCotZvAAKARTELygI033.jpg



4、测试主从是否同步

首先在主数据库上解锁,查看表内容

mysql> unlock tables;
mysql> use db1
mysql> show tables;

wKioL1YBfhXx1gliAAFCSh_4y-Y782.jpg


然后查看下从数据库是否生成了db1数据库,这里可以看到表的内容和主数据库中db1库是一样的。


测试下:

在主数据库上执行下面的命令删除event表

mysql> drop table event;

接着在从数据库上可以发现该表也被同步的删除了

wKiom1YBfvHyjmaYAACxPVY_s2U936.jpg


这样就实现了主从数据同步。





实现轻松部署 MySQL 主从复制:参考 mysqldump 的 --master-data 参数。

mysqldump导出数据时,当这个参数的值为1的时候,mysqldump出来的文件就会包括CHANGE MASTER TO这个语句,CHANGE MASTER TO后面紧接着就是file和position的记录,在slave上导入数据时就会执行这个语句,salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1;当这个值是2的时候,chang master to也是会写到dump文件里面去的,但是这个语句是被注释的状态。

3步即可:

1、主上导出数据库:mysqldump 带--master-data=1参数

2、从上导入数据库:mysql 导入数据命令

3、start slave;







      本文转自 M四月天 51CTO博客,原文链接:http://blog.51cto.com/msiyuetian/1697288,如需转载请自行联系原作者



网友评论

登录后评论
0/500
评论
技术小胖子
+ 关注