mysql的主从仿制与半同步仿制(一)51CTO博客 - 威尼斯人

mysql的主从仿制与半同步仿制(一)51CTO博客

2019-01-03 15:25:24 | 作者: 采蓝 | 标签: 仿制,主从,同步 | 浏览: 343

一、mysql的主从仿制

仿制进程需求至少两个mysql效劳节点,mysql的主从仿制结构一般都为一主多从,从多机房汇总到数据中心的形式,而仿制进程只能从主效劳器到从效劳器完结。

仿制进程中,主效劳器担任读/写操作,而从效劳器只担任读操作

二、主从仿制的功用

1、完结数据冗余、异地灾备康复、备份

2、完结数据的读/写别离

3、完结数据库效劳的负载均衡

4、完结高可用与毛病切换

5、完结MySQL的晋级测验

三、主从仿制进程中留意的事项

1、不要混合shi用不同的存储引擎

2、主从效劳器的server-id 要坚持不同

3、极力防止修正从效劳器的数据库

4、尽可能的运用根据行或根据混合形式的仿制,防止运用根据句子的仿制

5、主效劳器要发动二进制日志,而从节点则是敞开中继日志,并且要保证从效劳器的仿制线程时刻敞开着

6、留意由于磁盘及内存的巨细缺乏而导致的仿制溃散

四、主从仿制的详细完结进程

mysql主从仿制拓扑图

1、预备两个装置过mysql效劳的节点xz:172.16.200.5,node2:172.16.200.7,修正主机名并同步时刻;

[root@xz ~]# hostname master
[root@master ~]# crontab -e
*/1 * * * * /usr/sbin/ntpdate 172.16.0.1 &> /dev/null
[root@node2 ~]# hostname slave
[root@slave ~]# crontab -e
*/1 * * * * /usr/sbin/ntpdate 172.16.0.1 &> /dev/null

2、在主节点上创立有仿制权限的用户,于从节点上运用授权用户衔接测验;


[root@master ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.33-log MySQL Community Server (GPL)
。。。。。
mysql> grant replication slave,replication client on *.* to zly@172.16.200.7 identified by mypass;
Query OK, 0 rows affected (0.35 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.09 sec)
mysql> show grants for zly@172.16.200.7;
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for zly@172.16.200.7                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO zly@172.16.200.7 IDENTIFIED BY PASSWORD *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
...........
[root@slave ~]# mysql -uzly -pmypass -h 172.16.200.5
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type help; or \h for help. Type \c to clear the current input statement.
mysql>

3、修正主从两个节点的配置文件,并重新发动mysql效劳;

[root@master ~]# vim /etc/my.cnf
log-bin=mysql-bin
log_bin_index = mysql_bin.index
binlog_format=mixed
server-id       = 5
[root@master ~]# killall mysqld
[root@master ~]# ps aux | grep mysqld
root      7700  0.0  0.1 103244   832 pts/3    S+   09:27   0:00 grep mysqld
[root@master ~]# service mysqld restart
MySQL server PID file could not be found!                  [FAILED]
Starting MySQL...                                          [  OK  ]
[root@master ~]# service mysqld restart
Shutting down MySQL.                                       [  OK  ]
Starting MySQL..                                           [  OK  ]
...............
[root@slave ~]# vim /etc/my.cnf
#log-bin=mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
skip_slave_start = 1
read_only = 1
relay_log = relay_log
relay_log_index = relay_log.index
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 7
[root@slave ~]# killall mysqld
[root@slave ~]# ps aux | grep mysqld
root      8796  0.0  0.1 103244   832 pts/4    S+   23:45   0:00 grep mysqld
[root@slave ~]# service mysqld restart
MySQL server PID file could not be found!                  [FAILED]
Starting MySQL..                                           [  OK  ]
[root@slave ~]# service mysqld restart
Shutting down MySQL.                                       [  OK  ]
Starting MySQL..                                           [  OK  ]

4、检查主节点的二进制日志及其事情方位;

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5、与从节点上仿制主节点上的数据;

[root@slave ~]# mysql
mysql> help change master to
Name: CHANGE MASTER TO
Description:
Syntax:
CHANGE MASTER TO option [, option] ...
option:
    MASTER_BIND = interface_name
  | MASTER_HOST = host_name
  | MASTER_USER = user_name
  | MASTER_PASSWORD = password
  | MASTER_PORT = port_num
  | MASTER_CONNECT_RETRY = interval
  | MASTER_HEARTBEAT_PERIOD = interval
  | MASTER_LOG_FILE = master_log_name
  | MASTER_LOG_POS = master_log_pos
  | RELAY_LOG_FILE = relay_log_name
  | RELAY_LOG_POS = relay_log_pos
  | MASTER_SSL = {0|1}
  | MASTER_SSL_CA = ca_file_name
  | MASTER_SSL_CAPATH = ca_directory_name
  | MASTER_SSL_CERT = cert_file_name
  | MASTER_SSL_KEY = key_file_name
  | MASTER_SSL_CIPHER = cipher_list
  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
  | IGNORE_SERVER_IDS = (server_id_list)
server_id_list:
    [server_id [, server_id] ... ]
........................................
mysql> change master to
    -> master_host=172.16.200.5,master_user=zly,master_password=mypass
,master_port=3306,master_log_file=mysql-bin.000011,master_log_pos=107;
Query OK, 0 rows affected (0.07 sec)

6、发动从效劳器仿制线程、检查状况,并检查发动的线程;

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.200.5
                  Master_User: zly
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay_log.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000011
             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: 107
              Relay_Log_Space: 403
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
.................................
mysql> show processlist\G
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 276
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 3
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 275
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
3 rows in set (0.00 sec)

7、在主节点上创立数据库检查从节点是否现已仿制;

[root@master ~]# mysql -e "create database classdb;"
[root@master ~]# mysql -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| classdb            |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
[root@slave ~]# mysql -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| classdb            |
| mysql              |
| performance_schema |
+--------------------+

8、与主从节点检查现已更新的状况及二进制日志方位

[root@master ~]# mysql -e "show master status;"
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 |      453 |              |                  |
+------------------+----------+--------------+------------------+
[root@slave ~]# mysql -e "show slave status\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.200.5
                  Master_User: zly
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 453
               Relay_Log_File: relay_log.000002
                Relay_Log_Pos: 599
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

至此mysql的主从仿制现已完结

五、mysql的半同步仿制的详细完结

半同步仿制:主效劳器只需收到从效劳器中的一台的回来信息,就会提交,否则需等候直至到达超时时刻然后切换成异步再提交。能够使主从效劳器的数据库数据的推迟较小,能够在丢失很小的功用的前提下进步数据的安全性。

要进行半同步仿制,只需求在主从效劳器上装置上半同步仿制的插件,并敞开半同步仿制功用,便能够进行主从仿制了。

1、检查主从节点上的半同步插件,并装置插件;

[root@master ~]# cd /usr/local/mysql/lib/plugin/
[root@master plugin]# ls
adt_null.so          debug                 qa_auth_server.so
auth.so              libdaemon_example.so  semisync_master.so
auth_socket.so       mypluglib.so          semisync_slave.so
auth_test_plugin.so  qa_auth_client.so
daemon_example.ini   qa_auth_interface.so
....................
[root@slave ~]# cd /usr/local/mysql/lib/plugin/
[root@slave plugin]# ls
adt_null.so          debug                 qa_auth_server.so
auth.so              libdaemon_example.so  semisync_master.so
auth_socket.so       mypluglib.so          semisync_slave.so
auth_test_plugin.so  qa_auth_client.so
daemon_example.ini   qa_auth_interface.so
......................
[root@master plugin]# mysql
mysql> install plugin rpl_semi_sync_master soname semisync_master.so;
Query OK, 0 rows affected (0.03 sec)
mysql> set global rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> set global rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)          
..................................
[root@slave plugin]# mysql
mysql> install plugin rpl_semi_sync_slave soname semisync_slave.so;
Query OK, 0 rows affected (0.04 sec)
mysql> set global rpl_semi_sync_slave_enabled = 1 ;
Query OK, 0 rows affected (0.01 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.01 sec)

2、检查半同步敞开的状况,并检查从节点的线程发动状况;

mysql> show global status like rpl_semi%;
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
mysql> show global variables like %rpl%;
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_recovery_rank                  | 0     |
| rpl_semi_sync_master_enabled       | ON    |
| rpl_semi_sync_master_timeout       | 1000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
5 rows in set (0.00 sec)
...............
mysql> show global status like rpl_semi%;
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> show global variables like %rpl%;
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_recovery_rank               | 0     |
| rpl_semi_sync_slave_enabled     | ON    |
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+
3 rows in set (0.00 sec)
................
[root@slave ~]# mysql -e show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.200.5
                  Master_User: zly
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 453
               Relay_Log_File: relay_log.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

3、主从两边节点都存在新建的class数据库,于主效劳器上删去该数据库,然后检查从效劳器是否也同步删去了;

[root@master ~]# mysql -e show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| classdb            |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
[root@slave ~]# mysql -e show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| classdb            |
| mysql              |
| performance_schema |
+--------------------+
。。。。。。。。。。。。。
[root@master ~]# mysql -e drop database classdb;
[root@master ~]# mysql -e show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
[root@slave ~]# mysql -e show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

至此,mysql的半同步仿制现已完结。

版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表威尼斯人立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章