MYSQL数据库的主从复制

MySQL主从复制

实现过程分析：

1、master将写操作（DML语句：data manipulation language数据操作语言-->insert、update、delete；DDL语句-->create、drop、alter）写入到binary log（二进制日志）中

2、salve（从库）的IO线程读取master上的binary log并将其写入到relay log(中继日志)

3、slave上的SQL线程将读取relay log中的数据应用（写）到slave上，实现主从数据一致

 

实验一：两台干净的服务器

搭建核心思路：

1、明确自己为主，从库（server_id，唯一性。优先级越小越优先）

2、开启二进制日志文件log_bin=binlog（指定二进制日志的文件名）

3、在master上提前准备一个给I/O线程复制用的用户并具有复制应有的权限（replication slave）

 

实验过程截图：主服务器master

1、安装mysql、mysql-server，并启动/etc/init.d/mysqld  start 

2、明确主从，进入mysql配置文件  /etc/my.cnf修改

 master:

yum -y install mysql mysql-server

/etc/init.d/mysqld start

vim /etc/my.cnf

server_id = 1          //设置优先级，确认主从

log_bin=binlog    //开启二进制日志并指定二进制日志文件名

log_bin_index=binlog_index  //创建普通索引，使速度加快

 

mysql -e "grant replication slave on *.* to repl@10.1.1.2 identified by '123'"   //创建用户赋予权限

 

/etc/init.d/mysqld restart 

登陆mysql无需密码，查看二进制日志是否开启

 

从服务器slave

1、安装mysql、mysql-server，并启动/etc/init.d/mysqld  start 

2、明确主从，进入mysql配置文件  /etc/my.cnf修改

slave:

yum -y install mysql mysql-server

/etc/init.d/mysqld start

vim /etc/my.cnf

server_id=2

relay_log=/var/lib/mysql/relay_log

relay_log_index=/var/lib/mysql/relay_log_index

master_user=repl

master_password=123

master_port+3306

master_host=10.1.1.1

 

/etc/init.d/mysqld restart

 

show variables  like  'log_bin'    

show  slave  statues \G    //查看状态

注意：如果线程没有开启，可以mysql>stop slave; 关闭防火墙，关闭selinux;

然后再开启start slave

 

Chkconfig  iptables off  开机关闭防火墙

一般是事务回滚造成的：

解决办法一：

mysql> stop slave ;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;    #客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。

mysql> start slave ;

 

管理：

slave：

slave start； 开启salve上的两个线程

slave stop； 关闭slave上的两个线程

show slave status\G 查看slave状态的信息

 

每次重启mysql都会产生一个新的binlog，show显示的是当前正在写入的binlog

查看二进制日志的命令：

[root@service1 /]# mysqlbinlog binlog.000001

 

 

实验二：单一MySQL已经运行一段时间，因业务量增加，需要加入一台slave实现MySQL AB复制架构

1、先给master数据库做一个备份，

Mysql> flush  tables with read lock

备份数据到/opt下 

    mysqldump  -uroot   --lock-all-tables   --all-databases  >/opt/all.sql

2、查看备份前的master二进制日志文件和二进制日志文件的位置

       show master status

 

3、现在主库已经停止更新操作，可以趁机备份数据，把添加二进制日志之前的数据/opt/all.sql进行全部恢复到slave上，然后master要释放锁，unlock tables,此时开始有新的日志插入，在进行备份时候就可以直接通过二进制日志进行备份了.

4、在master创建连接的用户repl

mysql> grant replication slave on *.* to root@192.168.19.253 identified by '123'；

5、在slave服务器上，关掉mysql服务，进入mysql配置文件  /etc/my.cnf修改

    slave:

yum -y install mysql mysql-server

/etc/init.d/mysqld start

vim /etc/my.cnf

server_id=2

relay_log=/var/lib/mysql/relay_log

relay_log_index=/var/lib/mysql/relay_log_index

master_user=repl

master_password=123

master_port+3306

master_host=10.1.1.1

6、开启slave的mysql因为想要从指定的二进制文件位置进行同步，所以在开启的时候要先跳过slave，不然它会立刻就同步：

[root@client  /]#cd /usr/bin 

[root@client bin]# mysqld_safe --skip-slave-start  &  放到后台跳过slave启动Mysql

7、在slave里进入数据库

mysql> change master to master_host='192.168.19.253', master_port=3306, master_password=123,master_user='repl',master_log_file='binlog.000001',master_log_pos=511;

指定二进制日志文件开始复制位置

8、在slave服务器上启动mysql > slave start;

9、开始测试，主服务器，增加数据，查看从服务器是否同步

 

Master服务器：

Iptables -F //关闭防火墙

Chkconfig iptables off //开机自关闭防火墙

Yum -y install mysql mysql-server //安装mysql数据库

/etc/init.d/mysqld start //启动mysql服务

Mysql //进入mysql

Mysql> create databse DB; //建库

Mysql> create table DB.tab_db1; //建表

mysql> grant replication slave on *.* to "repl"@192.168.19.253 identified by "123"; //创建用户以供二进制日志功能进行连接

Mysqldump  -uroot -p --lock-all-tables --all-database >/opt/all.sql //加锁数据库进行热备份

Vim /etc/my.cf //修改mysql服务配置文件，开启二进制日志备份功能

Server_id=1

Log_bin=binlog

Log_bin_index=binlog_index

/etc/init.d/mysqld restart1

Unlock tables //解锁数据库

Mysql > show master status //查看二进制master状态

 

slave服务器

Iptables -F

Chkconfig iptables off

/etc/init.d/mysqld start

Scp  192.168.19.254:/opt/all.sql  /opt/ //下载master开启二进制日志之前的备份

Mysql < /opt/all.sql //还原到master开启二进制日志之前

/etc/init.d/mysqld stop //停止服务，开启二进制日志备份功能

Vim /etc/my.cf

Server_id=2

Relay_log=/var/lib/mysql/relay_log

Relay_log_index=/var/lib/mysql/relay_log_index

master_host=192.168.19.252

master_port=3306

master_user=repl

master_password=123

Cd /usr/bin

./mysqld_safe  --skip-slave-start & //开启mysql服务时跳过slave二进制日志功能

Mysql> 

change master to master_host='192.168.19.252',master_user='repl',master_password='123', master_port=3306, master_log_file='binlog.000001', master_log_pos=256;

Mysql> slave start;

Mysql> show slave status\G;

Mysql> show processlist;

开始测试，master增加数据，然后查看slave是否同步

 

三、Mysql增量备份和恢复（在有全库备份的基础上，对新的操作进行备份和恢复）

1、先做全库备份，flush tables with read lock

Mysqldump  -uroot -p --lock-all-tables --all-database >/opt/all.sql //加锁数据库进行热备份

Unlock tables;    //此后为一天所产生的数据量

Flush logs; //刷新日志，产生一天的二进制日志

Cp /var/lib/mysql/binlog.000001 /opt   //周一一天产生的数据

恢复数据

先注释掉二进制日志开启功能

Mysqlbinlog  /opt/binlog.000001 |mysql -uroot -p123  //对于一天的二进制日志进行恢复

[root@client2 mysql]# mysqlbinlog --start-position=106 --stop-position=195 binlog.000002 |mysql -uroot  //跳过错误操作进行恢复

 

在周二产生误操作：解决如下

先关闭服务-->恢复全库备份-->将周一的增量备份恢复-->因为误操作发生在周二，需要注意不能直接将周二的二进制日志全部恢复（需要阅读二进制日志文件，跳过有误操作的部分来恢复）

如果是在复制的环境中，需要关闭二进制日志的记录功能

 

转载于:https://www.cnblogs.com/zs-wei/p/9001313.html