MySQL主从配置

一、MySQL主从介绍

MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的

MySQL主从是基于binlog的，主上须开启binlog才能进行主从。

主从过程大致有3个步骤

1）主将更改操作记录到binlog里

2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里

3）从根据relaylog里面的sql语句按顺序执行

主上有一个log dump线程，用来和从的I/O线程传递binlog

从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地

 

二、MySQL主从原理图

 

 

 

 

三、准备工作

主：192.168.0.236

从：192.168.0.182

2台机器都要安装mysql服务，安装过程参照LANP中mysql搭建过程

 

主操作：

ps aux |grep mysql               //查看服务是否启动

 

vim /etc/my.cnf          //修改配置文件，增加server-id=1和log_bin=master

 

 

/etc/init.d/mysqld restart                   //重启服务

 

cd /data/mysql/          //进入mysql数据目录

 

 

 

mysqldump -uroot -pabc123 zrlog > /tmp/zrlog.sql            //备份zrlog

 

mysql -uroot -pabc123 -e "create database test2"              //创建test2库

 

mysql -uroot -pabc123 test2 < /tmp/zrlog.sql                  //恢复数据库

 

mysql -uroot -pabc123                         //进入数据库

mysql> grant replication slave on *.* to 'repl'@192.168.0.182 identified by 'abc-123';          //创建用户

 

mysql> flush tables with read lock;                   //锁表，目的是为了让数据不再继续写入表里

 

mysql> show master status;                            

 

 

 

 

备份所有的数据库

cd /data/mysql/          //进入mysql数据目录

 

 

mysqldump -uroot -pabc123 mysql2 > /tmp/mysql2.sql                //备份mysql2库

 

mysqldump -uroot -pabc123 test2 > /tmp/test2.sql                     //备份test2库

 

 

ls /tmp/*sql               //查看备份的数据库

 

 

 

 

从操作：

ps aux |grep mysql               //查看服务是否启动

 

 

查看my.cnf，配置server-id=2，要求和主不一样

vim /etc/my.cnf           //编辑配置文件

 

 

/etc/init.d/mysqld restart              //重启服务

 

scp 192.168.0.236:/tmp/*.sql /tmp/              //将主备份的数据库复制到从上面

 

 

首次运行mysql命令是不存在的

 

alias 'mysql=/usr/local/mysql/bin/mysql'             //设置mysql别名

alias 'mysqldump=/usr/local/mysql/bin/mysqldump'            //设置mysqldump别名

 

mysqladmin -uroot password 'abc123'      //给数据库设置密码

 

mysql -uroot -p             //登录mysql

 

 

mysql> create database zrlog;                 //创建zrlog库

mysql> create database mysql2;                           //创建mysql2库

mysql> create database test2;                    //创建test2库

 

mysql -uroot -pabc123 zrlog < /tmp/zrlog.sql               //还原zrlog数据库

mysql -uroot -pabc123 mysql2 < /tmp/mysql2.sql           //还原mysql2数据库

 

mysql -uroot -pabc123 test2 < /tmp/test2.sql                 //还原test2数据库   

 

 

mysql -uroot -pabc123          //进入数据库

 

mysql> stop slave;                   //停止从

 

mysql> change master to master_host='192.168.0.236', master_user='repl', master_password='abc-123', master_log_file='master.000001', master_log_pos=10698;

 

mysql> start slave;                      //启动从

 

mysql> show slave status\G                        //检查主从是否配置成功

 

 

此时还要到主上执行 unlock tables;

mysql -uroot -pabc123              //进入数据库

mysql> unlock tables;                //恢复写操作

 

 

 

四、测试主从

 

几个配置参数

主服务器上

binlog-do-db=      //仅同步指定的库，多个数据库分行写

binlog-ignore-db= //忽略指定库

从服务器上

replicate_do_db=

replicate_ignore_db=

replicate_do_table=                 //不建议使用

replicate_ignore_table=           //不建议使用

replicate_wild_do_table=   //如test2.%, 支持通配符%

replicate_wild_ignore_table=

 

 

主服务器上操作

mysql -uroot -pabc123             //进入数据库

 

mysql> use test2;                     //进入test2库

mysql> show tables;                      //查看test2的所有表

 

mysql> select count(*) website;              //查看表行数

 

 

 

 

 

 

 

从服务器上操作

 

mysql -uroot -pabc123             //进入数据库

 

mysql> use test2;                     //进入test2库

mysql> show tables;                      //查看test2的所有表

 

mysql> select count(*) website;               //查看表行数，和主服务器一致

 

 

 

 

测试一

主服务器

mysql> truncate table website;              //删除表数据

mysql> select * from website;             //查询表数据

 

 

 

从服务器

mysql> select count(*) from  website;              //查询表数据，显示一致

mysql> select * from  website;              //无数据

 

 

测试二

主服务器

mysql> drop table website;                //删除表

 

 

从服务器

select * from  website;       //查询表，显示不存在

 

 

总结：删除或更改数据库和表，不允许在从服务器上操作，会导致数据不同步，主从失效，如果不慎操作

此时应该重新做主从：

主服务器操作

mysql> show master status;          //记录Position的ID号

 

 

 

从服务器操作

mysql> stop slave;                   //停止从

 

mysql> change master to master_host='192.168.0.236', master_user='repl', master_password='abc-123', master_log_filelog_file='master.000001', master_log_pos=10918;               //数据还是一致的情况下，重新执行主从命令，否则只能重新备份数据库

 

 

mysql> start slave;                      //启动从

 

 

 

mysql> show slave status\G                        //检查主从是否配置成功

 

 

 

转载于:https://my.oschina.net/u/3991860/blog/2994623