Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1976286
  • 博文数量: 188
  • 博客积分: 3409
  • 博客等级: 中校
  • 技术积分: 3860
  • 用 户 组: 普通用户
  • 注册时间: 2010-12-24 10:21
  • 认证徽章:
个人简介

● ITPUB名人堂嘉宾 ● ChinaUnix社区博客专家 ● ChinaUnix社区Oracle板块版主 ● 优酷网认证音乐牛人:EricGuitar ● SDOUG 核心成员 ●E-mail:gaoqiangdba@163.com

文章分类

全部博文(188)

文章存档

2016年(5)

2015年(39)

2014年(25)

2013年(17)

2012年(23)

2011年(61)

2010年(18)

分类: Mysql/postgreSQL

2015-04-15 16:43:07

  

实验目的:
体验在数据库中配置多个slave线程进行同放操作有助于提高复制效率,减少主从数据库的数据差异。



操作演示:

在my.conf文件中加入slave_parallel_threads=N,N为并行数量。
[root@dbserver2 bin]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]


#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
log-bin=logbin
server_id=2
expire_logs_days=7
slave_parallel_threads=2




MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.221.135
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: logbin.000034
          Read_Master_Log_Pos: 323
               Relay_Log_File: dbserver2-relay-bin.000002
                Relay_Log_Pos: 607
        Relay_Master_Log_File: logbin.000034
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 323
              Relay_Log_Space: 908
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: Slave_Pos
                  Gtid_IO_Pos: 0-1-12
1 row in set (0.00 sec)







可以看到已经有2个并行的进程在等待了:
MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             | Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|  3 | system user |           | NULL | Connect |  148 | Waiting for work from SQL thread                                            | NULL             |    0.000 |
|  4 | system user |           | NULL | Connect |  148 | Waiting for work from SQL thread                                            | NULL             |    0.000 |
|  5 | system user |           | NULL | Connect |  148 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
|  6 | system user |           | NULL | Connect |  148 | Waiting for master to send event                                            | NULL             |    0.000 |
|  8 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |    0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
5 rows in set (0.00 sec)










提升到5的并发,看一下是否有5个线程在等待:
[root@dbserver2 bin]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]


#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysqld]
log-bin=logbin
server_id=2
expire_logs_days=7
slave_parallel_threads=5




验证是否分配了5个并发:
MariaDB [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)


MariaDB [(none)]> show processlist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host      | db   | Command | Time | State                                                                       | Info             | Progress |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|  3 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |
|  4 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |
|  5 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |
|  6 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |
|  7 | system user |           | NULL | Connect |   14 | Waiting for work from SQL thread                                            | NULL             |    0.000 |
|  8 | system user |           | NULL | Connect |   14 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
|  9 | system user |           | NULL | Connect |   14 | Waiting for master to send event                                            | NULL             |    0.000 |
| 11 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist |    0.000 |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
8 rows in set (0.00 sec)


MariaDB [(none)]> show processlist\G;
*************************** 1. row ***************************
      Id: 3
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 36
   State: Waiting for work from SQL thread
    Info: NULL
Progress: 0.000
*************************** 2. row ***************************
      Id: 4
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 36
   State: Waiting for work from SQL thread
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 5
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 36
   State: Waiting for work from SQL thread
    Info: NULL
Progress: 0.000
*************************** 4. row ***************************
      Id: 6
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 36
   State: Waiting for work from SQL thread
    Info: NULL
Progress: 0.000
*************************** 5. row ***************************
      Id: 7
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 36
   State: Waiting for work from SQL thread
    Info: NULL
Progress: 0.000
*************************** 6. row ***************************
      Id: 8
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 36
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000
*************************** 7. row ***************************
      Id: 9
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 36
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 8. row ***************************
      Id: 11
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: init
    Info: show processlist
Progress: 0.000
8 rows in set (0.00 sec)


OK,已经有5个线程在等待重放主库的binlog!~~~

阅读(3781) | 评论(2) | 转发(1) |
给主人留下些什么吧!~~

jackson1985742015-04-20 18:42:12

niao5929:很久没见你发帖了。又在沉积吗??哈哈

最近略忙哈。论坛也去,发的少了,一直在发哈。

回复 | 举报

niao59292015-04-20 18:11:11

很久没见你发帖了。又在沉积吗??哈哈

评论热议
请登录后评论。

登录 注册