MySQL 复制简要描述及示例

    主从复制技术在MySQL中被广泛使用，主要用于同步一台服务器上的数据至多台从服务器，可以用于实现负载均衡，高可用和故障切换，以及提供备份等等。MySQL支持多种不同的复制技术，诸如单向，半同步异步复制等以及不同级别的复制，诸如数据库级别，表级，跨库同步等等。本文简要描述了一个基本的主从复制并给出示例。

 

1、复制的基本原理(步骤)
    a、在主库上把数据更改记录的二进制日志(binary log)
    b、从库上的I/O线程连接到主库并请求发送其二进制日志文件(主库上的binlog dump线程将二进制日志内容发送到从库)
    c、从库上的I/O线程读取主服务发送的二进制内容并将其拷贝到中继日志
    d、从库上的SQL线程读取中继日志并执行日志中包含的更新

 

2、为配置文件添加复制项

# 本文的演示基于同一服务器上的多实例环境，其中3406端口用作主库，而3506用作从库。
# 关于多实例的部署可参考：
# MySQL多实例配置(一) http://blog.csdn.net/leshami/article/details/40339167
# MySQL多实例配置(二) http://blog.csdn.net/leshami/article/details/40339295
# 3406与3506为都为新装且含缺省库等，所以本文演示中未涉及先迁移主库数据到备库步骤
a、主库上的配置文件
# more my3406.cnf
[mysqld]
socket = /tmp/mysql3406.sock
port = 3406
pid-file = /data/inst3406/data3406/my3406.pid
user = mysql
log-error=/data/inst3406/data3406/inst3406.err
datadir=/data/inst3406/data3406
basedir=/app/soft/mysql5

#### for master items ####
server-id=3406
log_bin=/data/inst3406/log/bin/inst3406bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

b、从库上的配置文件
# more my3506.cnf
[mysqld]
socket = /tmp/mysql3506.sock      # Author : Leshami
port = 3506                       # Blog   : http://blog.csdn.net/leshami
pid-file = /data/inst3506/data3506/my3506.pid
user = mysql
log-error=/data/inst3506/data3506/inst3506.err
datadir=/data/inst3506/data3506
basedir=/app/soft/mysql5

#### for slave items ####
server-id=3506
relay_log=/data/inst3506/log/relay/relay-bin
read_only=1

3、创建复制账号

#启动端口为3406的实例并添加账户
[mysql@app ~]$ mysqld_safe --defaults-file=/data/inst3406/data3406/my3406.cnf &
[mysql@app ~]$ mysql -P3406    #登陆到3406

master@localhost[(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3406  |
+---------------+-------+

#创建用于复制的账户
master@localhost[(none)]> grant replication slave,replication client on *.*
    -> to repl@'192.168.1.177' identified by 'repl';

#初始化主库日志文件，生成环境慎用reset
master@localhost[(none)]> reset master;
Query OK, 0 rows affected (0.01 sec)

#查看主库的状态，日志初始化至000001，
master@localhost[(none)]> show master status,Position为120
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| inst3406bin.000001 |      120 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+

4、配置主从同步

#启动端口为3506的实例
[mysql@app ~]$ mysqld_safe --defaults-file=/data/inst3506/data3506/my3506.cnf &

[mysql@app ~]$ msyql -P3506

slave@localhost[(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3506  |
+---------------+-------+
1 row in set (0.00 sec)

#为从库添加指向主库的相关配置信息，该命令会生成及修改备库上的master.info及relay-log.info文件
slave@localhost[(none)]> CHANGE MASTER TO MASTER_HOST='192.168.1.177',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_PORT=3406,
    -> MASTER_LOG_FILE='inst3406bin.000001',
    -> MASTER_LOG_POS=0;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

#出现了2个warnings，查看一下
slave@localhost[(none)]> show warnings \G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended.
Please see the MySQL Manual for more about this issue and possible alternatives.
2 rows in set (0.00 sec)

#此时查看从库的状态信息
slave@localhost[(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.1.177
                  Master_User: repl
                  Master_Port: 3406
                Connect_Retry: 60
              Master_Log_File: inst3406bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: inst3406bin.000001
             Slave_IO_Running: No      #IO线程没有运行
            Slave_SQL_Running: No      #SQL线程没有运行
                    ......................
             Master_Info_File: /data/inst3506/data3506/master.info

slave@localhost[(none)]> start slave;  #启动slave
Query OK, 0 rows affected (0.01 sec)

#含义如下
START SLAVE with no thread_type options starts both of the slave threads. The I/O thread reads
events from the master server and stores them in the relay log. The SQL thread reads events from the
relay log and executes them.

#再次查看slave的状态
robin@localhost[(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.177
                  Master_User: repl
                  Master_Port: 3406
                Connect_Retry: 60
              Master_Log_File: inst3406bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 285
        Relay_Master_Log_File: inst3406bin.000001
             Slave_IO_Running: Yes        #IO线程处于运行状态
            Slave_SQL_Running: Yes        #SQL线程处于运行状态
                      ..............
          Exec_Master_Log_Pos: 120
              Relay_Log_Space: 452
                      ............
             Master_Server_Id: 3406
                  Master_UUID: 32f53a0a-63ef-11e4-93d9-8c89a5d108ae
             Master_Info_File: /data/inst3506/data3506/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it #重要的提示信息

#可以看到从库上的2个线程，一个是用于I/O线程，用于连接到主库请求主库发送binlog，一个是用于执行SQL的SQL线程。
slave@localhost[(none)]> show processlist\G
*************************** 1. row ***************************
     Id: 4
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 510993
  State: Waiting for master to send event
   Info: NULL
*************************** 2. row ***************************
     Id: 5
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 333943
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL

5、验证同步情况

#下面在主库上执行一些操作以检查从库的同步情况
master@localhost[(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3406  |
+---------------+-------+
1 row in set (0.00 sec)

#主库上Binlog Dump线程用于发送binlog日志文件到从库，如下查询
master@localhost[(none)]> show processlist\G
*************************** 1. row ***************************
     Id: 12
   User: repl
   Host: 192.168.1.177:57440
     db: NULL
Command: Binlog Dump
   Time: 511342
  State: Master has sent all binlog to slave; waiting for binlog to be updated
   Info: NULL

#主库创建数据库及表
master@localhost[(none)]> create database tempdb;
Query OK, 1 row affected (0.01 sec)

master@localhost[(none)]> use tempdb
Database changed
master@localhost[tempdb]> create table tb_engines as select * from information_schema.engines;
Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0

#下面是在从库上检查的结果
slave@localhost[(none)]> select count(*) from tempdb.tb_engines;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
时间： 2024-09-30 18:18:04

MySQL 复制简要描述及示例的相关文章

mysql释放结果内存代码示例

  mysql释放结果内存代码示例 定义和用法 mysql_free_result() 函数释放结果内存. 如果成功,则返回 true,如果失败,则返回 false. 语法 mysql_free_result(data)参数 描述 data 必需.要释放的结果标识符.该结果标识符是从 mysql_query() 返回的结果. 提示和注释 注释:mysql_free_result() 仅需要在考虑到返回很大的结果集时会占用多少内存时调用.在脚本结束后所有关联的内存都会被自动释放. 例子 $con

数据库-求助一个关于mysql复制的问题

问题描述 求助一个关于mysql复制的问题 各位大神好: 小弟是个新人,接手一个现有系统的维护,因想弄套测试环境想把正在使用的mysql数据库在另一台机器上复制一个,倒腾半天无法成功,求大神答惑. 背景:原服务器是windows2003系统,新服务器的系统相同. 1.首先在新服务上安装了一个mysql数据库,由于不了解原来的数据库配置所以瞎配置了一通, 也就是在运行MySQLInstanceConfig.exe的时候选择的内容可能跟原来的不同. 2.将目标服务器上的mysql通过net stop

理解MySQL——复制(Replication)

原文地址: http://www.cnblogs.com/hustcat/archive/2009/12/19/1627525.html 1.复制概述 1.1.复制解决的问题数据复制技术有以下一些特点:(1)    数据分布(2)    负载平衡(load balancing)(3)    备份(4)    高可用性(high availability)和容错 1.2.复制如何工作从高层来看,复制分成三步:(1)    master将改变记录到二进制日志(binary log)中(这些记录叫做二

MySQL复制表数据操作sql语句

MySQL复制表数据操作相信大家都不会太陌生,下面就为您详细介绍MySQL复制表数据到新表的步骤,希望对您会有所启迪.  1.MySQL复制表结构及数据到新表   代码如下 复制代码    CREATE TABLE 新表  SELECT * FROM 旧表      2.只复制表结构到新表   代码如下 复制代码    CREATE TABLE 新表  SELECT * FROM 旧表 WHERE 1=2  即:让WHERE条件不成立.  方法二:(低版本的mysql不支持,mysql4.0.2

mysql 复制表数据，表结构的3种方法

 什么时候我们会用到复制表?例如:我现在对一张表进行操作,但是怕误删数据,所以在同一个数据库中建一个表结构一样,表数据也一样的表,以作备份.如果用mysqldump比较麻烦,备份.MYD,.MYI这样的文件呢,操作起来也还是麻烦. 一,复制表结构 方法1: mysql> create table a like users; //复制表结构 Query OK, 0 rows affected (0.50 sec)   mysql> show tables; +------+ | Tables_i

【转载】MySQL复制的概述、安装、故障、技巧、工具

概述 首先主服务器把数据变化记录到主日志,然后从服务器通过I/O线程读取主服务器上的主日志,并且把它写入到从服务器的中继日志中,接着SQL线程读取中继日志,并且在从服务器上重放,从而实现MySQL复制.具体如下图所示: MySQL复制 整个过程反映到从服务器上,对应三套日志信息,可在从服务器上用如下命令查看: mysql> SHOW SLAVE STATUS;   Master_Log_File & Read_Master_Log_Pos:下一个传输的主日志信息. Relay_Master_

详细讲解MySQL复制

同MongoDB,Redis这样的NoSQL数据库的复制相比,MySQL复制显得相当复杂! 概述 首先主服务器把数据变化记录到主日志,然后从服务器通过I/O线程读取主服务器上的主日志,并且把它写入到从服务器的中继日志中,接着SQL线程读取中继日志,并且在从服务器上重放,从而实现MySQL复制.具体如下图所示: MySQL复制 整个过程反映到从服务器上,对应三套日志信息,可在从服务器上用如下命令查看: mysql> SHOW SLAVE STATUS; Master_Log_File & Re

MySQL复制（3） 创建主备复制（从另一个服务器开始复制）

前一篇讲到的创建主备复制是假设主备库都为刚刚安装好的数据库,也就是说两台服务器上的数据相同,这不是典型 的案例,大多数情况下有一个已经运行了一段时间的主库,然后用一台新安装的备库与之同步,本文讲述在这种情况下 该如何配置. 1.在备库的主机上安装MySQL,注意备库的版本不能低于主库. 2.备份主库,复制备份文件到备库,并在备库上恢复. 可以有很多种方法实现上述过程,我这里介绍的是利用innobackupex在线备份主库,这样就不需要停主库(注意: innobackupex只适用于MyISAM和

MySQL复制（2） 主备库都为空的情况下创建主备复制

本文适用于新安装的主库和备库,假定主备库为空,如果你是从已存在的主库复制,请转到<[MySQL] 复制(3)- 创建主备复制(从另一个服务器开始复制)> 主库的配置 主库需要打开二进制日志,并制定一个唯一的server id,my.cnf文件中增加或修改如下内容: server_id=60 log-bin = /data/mysql/log/mysql-bin 备库的配置 备库my.cnf的配置如下: server_id=61 read_only=1 log_bin = /data/mysql