mysql主从复制

1、MYSQL 主从复制(单活)
2、双主(单活),failover比单主简单
3、双主配san存储(单活)
4、DRBD双主配DRBD(单活)
5、NDB CLUSTER
6、第三方的Tungsten
7、网易的INNOSQL


实验mysql 主从复制

1、从服务器发起对主服务器连接同步请求,主服务器验证连接。
2、主服务器为从服务器单独开启一个线程。
3、从服务器将自身(Read_Master_Log_Pos)记录位置点告诉主服务器,(show slave status;查看)
4、主服务器将从服务器传递过来的位置记录点与自身二进制日志记录位置进行比较,是否少于
5、如果小于,则通知从服务器来拉取数据。
6、从服务器持续到主服务器读取数据,直至取完,从服务线程与主服务器同步的线程进入睡眠。
7、当主服务器有更新时,主服务器线程被激活,并将二进制日志推送给从服务器,并通知从服务器线程开始工作
8、从服务器的sql线程执行中继日志。

实验环境:

centos 6 + mariadb 10
master: hostname node1 ip 192.168.1.154
slave1: hostname node2 ip 192.168.3.6
slave2: hostname node3 ip 192.168.10.27
同步过程:
master(二进制日志) <--- slave1(二进制日志+中继日志) <--- slave2(中继日志)

系统准备:

 ~]# ntpdate pool.ntp.org
 ~]# getenforce
 Disabled
 ~]# iptables -F
 准备ssh互信(三台主机一样)
 ~]# ssh-keygen -t rsa
 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.1.154
 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.3.6
 ~]# ssh-copy-id -i .ssh/id_rsa.pub root@192.168.10.27
 准备hosts文件
 ~]# cat /etc/hosts
 192.168.1.154  node1
 192.168.3.6    node2
 192.168.10.27  node3
 复制到其他节点
 scp /etc/hosts root@node2:/etc/hosts
 scp /etc/hosts root@node3:/etc/hosts

安装Mariadb(二进制安装包方式)

 data]# pwd
 /data
 data]# scp mariadb-10.0.10-linux-x86_64.tar.gz root@node2:/data/
 data]# tar xf mariadb-10.0.10-linux-x86_64.tar.gz -C /usr/local/
 data]# useradd mysql -s /sbin/nologin -M
 data]# mkdir -p /data/mysql/{data,binlogs}
 data]# chown -R mysql.mysql /data/mysql/*
 local]# ln -sv mariadb-10.0.10-linux-x86_64 /usr/local/mysql
 local]# chown -R root.mysql mysql/*
 mysql]# cp support-files/mysql.server /etc/rc.d/init.d/mysqld
 mysql]# mv /etc/my.cnf{,.bk}
 mysql]# cp support-files/my-large.cnf /etc/my.cnf
 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data     /mysql/data --basedir=/usr/local/mysql     //初始化数据库
 mysql]# chkconfig --add mysqld
 mysql]# chkconfig mysqld on

master编辑配置文件:

 vim /etc/my.cnf
 [mysqld]
 ...
 server-id       = 1
 log-bin=/data/mysql/binlogs/mysql-bin
 thread_concurrency = 2
 datadir=/data/mysql/data
 basedir=/usr/local/mysql
 innodb-file-per-table=on
 skip-name-resolve=on
 replicate_wild_ignore_table=test1.%
 ...

创建两个数据库testdb,testdb1 模拟目前是采用一台mysql运行。

 MariaDB [(none)]> create database testdb;
 MariaDB [(none)]> create database testdb1;
 MariaDB [(none)]> create table testdb.info(id int primary key ,name varchar(20),age int );
 MariaDB [testdb]> insert into info values(1,"neo",27);
 MariaDB [testdb]> insert into info values(2,"andy",30);
 MariaDB [testdb]> insert into info values(3,"super",100);

以上可以模拟正常生成环境的单台数据库,此时业务数据比较重要,考虑添加从数据库,以保护数据。
准备从数据库slave1(node2),
同样的进行数据库安装,准备配置文件,数据存储目录,日志文件存储目录,以及初始化,开启二进制日志和中继日志

 server-id       = 2
 thread_concurrency = 2
 datadir=/data/mysql/data
 basedir=/usr/local/mysql
 innodb-file-per-table=on
 skip-name-resolve=on
 replicate_wild_ignore_table=testdb1.%     //跨数据库更新操作也能同步
 log-bin=/data/mysql/binlogs/mysql-bin
 log-bin-index=/data/mysql/binlogs/mysql-bin.index
 relay_log_purge=0
 relay_log=/data/mysql/binlogs/mysql-relay-log
 relay_log_index=/data/mysql/binlogs/mysql-relay-log.index
 log_slave_updates=on    //开启从服务器更新数据时写入自己的二进制日志文件

启动mysql服务

 service mysqld start

回到master主数据库,创建复制数据的mysql账户

 MariaDB [(none)]> grant replication slave on *.* to 'test'@'%' identified by '123456';
 MariaDB [(none)]> flush privileges;

想象一下生成环境,数据库运行N年,数据量很多,此时来做mysql主从,我们肯定不能从最开始位置进行复制,很早之前二进制日志可能不存在,我们需要先进行数据库备份,然后将备份在从数据库恢复,接着从数据库在从备份记录点开始复制。
那么问题又来了,数据库目前在线上生产,如何备份同时记录二进制日志的记录位置?
1、可以停下业务,先备份,记录位置。时间长。
2、采用Xtrabackup备份工具,比较靠谱。
数据库备份:

 mysqldump --database testdb > testdb.sql
 记录二进制日志记录点
 MariaDB [(none)]> show master status;
   mysql-bin.000003       1438 
 在从库slave1进行恢复
 data]# scp testdb.sql root@node2:/data/
 slave1进行恢复
 data]# mysql < testdb.sql
 slave1 配置进行数据库同步
 MariaDB [(none)]> change master to master_host='192.168.1.154',master_user='test',master_password='123456',master_log_file="mysql-bin.000003",master_log_pos=1438;
 MariaDB [(none)]> show slave status\G
 *************************** 1. row ***************************
           Slave_IO_State: 
              Master_Host: 192.168.1.154
              Master_User: test
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000003
      Read_Master_Log_Pos: 1438
           Relay_Log_File: mysql-relay-log.000001
            Relay_Log_Pos: 4
    Relay_Master_Log_File: mysql-bin.000003
         Slave_IO_Running: No
        Slave_SQL_Running: No
 MariaDB [(none)]> start slave;
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
   Replicate_Wild_Ignore_Table: testdb1.%

此时在master上在进行插入数据

 MariaDB [testdb]> insert into info values(4,"piter",22);
 slava1上确认数据同步
 MariaDB [testdb]> select * from info;
 +----+-------+------+
 | id | name  | age  |
 +----+-------+------+
 |  1 | neo   |   27 |
 |  2 | andy  |   30 |
 |  3 | super |  100 |
 |  4 | piter |   22 |
 +----+-------+------+

可以看到slave1已经正常同步master 数据库了,关闭slave1的mysql服务,在master上新增数据,启动slave1之后,数据自动同步过去。
注意:此时为了避免认为误操作将在从数据库进行写操作,需要将从数据库设置为只读,在my.cnf添加如下配置:

 read_only=on

=====slave2(node3)从slave1从库进行同步=====
同样的进行数据库安装,准备配置文件,数据存储目录,日志文件存储目录,以及初始化,开启中继日志(无需开启二进制日志)

 vi /etc/my.cnf
 datadir=/data/mysql/data
 basedir=/usr/local/mysql
 innodb-file-per-table=on
 skip-name-resolve=on
 # replicate_wild_ignore_table=testdb1.%
 # log-bin=mysql-bin
 server-id       = 3
 relay_log_purge=0
 relay_log=/data/mysql/binlogs/mysql-relay-log
 relay_log_index=/data/mysql/binlogs/mysql-relay-log.index

启动mysql数据库

 service mysqld start
 slave1创建复制同步的帐号信息
 MariaDB [(none)]> grant replication slave on *.* to 'test'@'%' identified by '123456';
 slave1进行数据库备份并切记录二进制日志记录点
 data]# mysqldump --database testdb > testdb_slave2.sql
 data]# scp testdb_slave2.sql root@node3:/data
 MariaDB [(none)]> show master status;
   mysql-bin.000002         732 
 slave2进行数据库恢复
 data]# mysql < testdb_slave2.sql
 slave2设置复制信息
 MariaDB [(none)]> change master to master_host='192.168.3.6',master_user='test',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=732;
 MariaDB [(none)]> start slave;
 MariaDB [(none)]> show slave status\G
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes

同步复制正常工作。

      slave2 也应该开启read_only=on

在master上写入数据测试,会同时同步到slave1,slave2上。

假如master 挂了呢?假如说master--slave数据完全同步
1、手动配置,取消read_only,
2、前段将数据库IP切换到从服务器IP,

推荐阅读更多精彩内容