MySQL复制简要描述及示例

页面导航:首页 > 数据库 > Mysql > MySQL复制简要描述及示例

MySQL复制简要描述及示例

来源: 作者: 时间:2016-01-22 08:34 【

主从复制技术在MySQL中被广泛使用,主要用于同步一台服务器上的数据至多台从服务器,可以用于实现负载均衡,高可用和故障切换,以及提供备份等等。MySQL支持多种不同的复制技术,

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

1、复制的基本原理(步骤)

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 
[d]
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的实例并添加账户
[[email protected] ~]$ mysqld_safe --defaults-file=/data/inst3406/data3406/my3406.cnf &
[[email protected] ~]$ mysql -P3406    #登陆到3406

[email protected][(none)]> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3406  |
+---------------+-------+

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

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

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

4、配置主从同步

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

[[email protected] ~]$ msyql -P3506

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

#为从库添加指向主库的相关配置信息,该命令会生成及修改备库上的master.info及relay-log.info文件
[email protected][(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,查看一下
[email protected][(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)

#此时查看从库的状态信息
[email protected][(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

[email protected][(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的状态
[email protected][(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线程。
[email protected][(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、验证同步情况

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

#主库上Binlog Dump线程用于发送binlog日志文件到从库,如下查询
[email protected][(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
   
#主库创建数据库及表
[email protected][(none)]> create database tempdb;
Query OK, 1 row affected (0.01 sec)

[email protected][(none)]> use tempdb
Database changed
[email protected][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

#下面是在从库上检查的结果
[email protected][(none)]> select count(*) from tempdb.tb_engines;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<