天天看点
返回

Mysql5.5配置主从复制

Mysql提供了主从复制的功能，作用类似oracle的dataguard，但是配置和管理远比dataguard简单，没有所谓的物理备库和逻辑备库之分，也没有提供相应的数据保护模式，只有master和slave数据库角色，这种架构广泛应用于各大门户，游戏网站中，提供数据库的读写分离功能；相比之下oracle的读写功能到了11g版本才能借助active dataguard完美实现，否则就只能用logical standby，但又有许多的数据类型和操作不被逻辑备库支持！先前使用过mysql5.1.36版本的master-slave架构做CMS数据库的读写分离，有着非常痛苦的使用经历，经常由于各种各样的原因的导致主从数据不同步，然后又没有提供额外的同步机制(确切的说是没学会)，于是经常在下班时间重构主从架构；下一步将在mysql5.5平台测试mha架构，故本文记录下如何在mysql5.5平台下构建主从数据库复制环境；另外mysql的主从也可看为是mysql的实时备份，有一定的数据灾备功能，mysql本身没有提供类似oracle rman的热备份工具，因而多数场景下会使用主从对数据库做一个实时备份，以备不时只需！PS：一直比较不解的mysql如何做基于时间或者SCN的不完全恢复？难道真要一个个binlog分析过去？

一：在主库上创建用于复制的账号并在从库上连接测试

     
  1.  [[email protected] ~]# mysql         
    2.       
  2.  Welcome to the MySQL monitor.  Commands end with ; or \g.         
    3.       
  3.  Your MySQL connection id is 1         
    4.       
  4.  Server version: 5.5.25-log Source distribution         
    5.           
  5.  Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.         
    6.           
  6.  Oracle is a registered trademark of Oracle Corporation and/or its         
    7.       
  7.  affiliates. Other names may be trademarks of their respective         
    8.       
  8.  owners.         
    9.           
  9.  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.         
    10.           
  10.  mysql> grant replication slave on *.* to 'r_test'@'192.168.123.14' identified by '123456';         
    11.       
  11.  Query OK, 0 rows affected (0.00 sec)         
    12.           
  12.  [[email protected] ~]# mysql -u r_test -h 192.168.123.13 -p         
    13.       
  13.  Enter password:          
    14.       
  14.  Welcome to the MySQL monitor.  Commands end with ; or \g.         
    15.       
  15.  Your MySQL connection id is 2         
    16.       
  16.  Server version: 5.5.25-log Source distribution         
    17.           
  17.  Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.         
    18.           
  18.  Oracle is a registered trademark of Oracle Corporation and/or its         
    19.       
  19.  affiliates. Other names may be trademarks of their respective         
    20.       
  20.  owners.         
    21.           
  21.  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.         
    22.           
  22.  mysql> show databases;         
    23.       
  23.  +--------------------+         
    24.       
  24.  | Database           |         
    25.       
  25.  +--------------------+         
    26.       
  26.  | information_schema |         
    27.       
  27.  | test               |         
    28.       
  28.  +--------------------+         
    29.       
  29.  2 rows in set (0.01 sec)        
    30.

二：修改主库my.cnf文件如下，注意红色字体部分，重启数据库实例

     
  1.  [[email protected] ~]# grep -v '^#' /etc/my.cnf |grep -v '^$'         
    2.       
  2.  [client]         
    3.       
  3.  port            = 3306        
    4.       
  4.  socket          = /tmp/mysql.sock         
    5.       
  5.  [mysqld]         
    6.       
  6.  port            = 3306        
    7.       
  7.  socket          = /tmp/mysql.sock         
    8.       
  8.  skip-external-locking         
    9.       
  9.  key_buffer_size = 256M        
    10.       
  10.  max_allowed_packet = 1M        
    11.       
  11.  table_open_cache = 256        
    12.       
  12.  sort_buffer_size = 1M        
    13.       
  13.  read_buffer_size = 1M        
    14.       
  14.  read_rnd_buffer_size = 4M        
    15.       
  15.  myisam_sort_buffer_size = 64M        
    16.       
  16.  thread_cache_size = 8        
    17.       
  17.  query_cache_size= 16M        
    18.       
  18.  thread_concurrency = 8        
    19.       
  19.  innodb_data_home_dir =         
    20.       
  20.  innodb_data_file_path = /dev/raw/raw6:1Graw         
    21.       
  21.  log-bin=mysql-bin         
    22.       
  22.  binlog_format=mixed        
    23.       
  23.  server-id=1        
    24.       
  24.  log-bin=mysql-bin         
    25.       
  25.  binlog-do-db=bbs        
    26.       
  26.  binlog-do-db=test        
    27.       
  27.  binlog-ignore-db=mysql        
    28.       
  28.  [mysqldump]         
    29.       
  29.  quick         
    30.       
  30.  max_allowed_packet = 16M        
    31.       
  31.  [mysql]         
    32.       
  32.  no-auto-rehash         
    33.       
  33.  [myisamchk]         
    34.       
  34.  key_buffer_size = 128M        
    35.       
  35.  sort_buffer_size = 128M        
    36.       
  36.  read_buffer = 2M        
    37.       
  37.  write_buffer = 2M        
    38.       
  38.  [mysqlhotcopy]         
    39.       
  39.  interactive-timeout         
    40.           
  40.  [[email protected] ~]# service mysqld restart         
    41.       
  41.  Shutting down MySQL.[  OK  ]         
    42.       
  42.  Starting MySQL..[  OK  ]         
    43.           
  43.  [[email protected] ~]# mysql         
    44.       
  44.  Welcome to the MySQL monitor.  Commands end with ; or \g.         
    45.       
  45.  Your MySQL connection id is 1         
    46.       
  46.  Server version: 5.5.25-log Source distribution         
    47.           
  47.  Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.         
    48.           
  48.  Oracle is a registered trademark of Oracle Corporation and/or its         
    49.       
  49.  affiliates. Other names may be trademarks of their respective         
    50.       
  50.  owners.         
    51.           
  51.  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.         
    52.           
  52.  mysql> show master status;         
    53.       
  53.  +------------------+----------+--------------+------------------+         
    54.       
  54.  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |         
    55.       
  55.  +------------------+----------+--------------+------------------+         
    56.       
  56.  | mysql-bin.000011 |      107 | bbs,test     | mysql            |         
    57.       
  57.  +------------------+----------+--------------+------------------+         
    58.       
  58.  1 row in set (0.00 sec)        
    59.

三：修改从库my.cnf文件如下，注意红色字体部分，重启数据库实例

     
  1.  [[email protected] ~]# grep -v '^#' /etc/my.cnf |grep -v '^$'         
    2.       
  2.  [client]         
    3.       
  3.  port            = 3306        
    4.       
  4.  socket          = /tmp/mysql.sock         
    5.       
  5.  [mysqld]         
    6.       
  6.  port            = 3306        
    7.       
  7.  socket          = /tmp/mysql.sock         
    8.       
  8.  skip-external-locking         
    9.       
  9.  key_buffer_size = 256M        
    10.       
  10.  max_allowed_packet = 1M        
    11.       
  11.  table_open_cache = 256        
    12.       
  12.  sort_buffer_size = 1M        
    13.       
  13.  read_buffer_size = 1M        
    14.       
  14.  read_rnd_buffer_size = 4M        
    15.       
  15.  myisam_sort_buffer_size = 64M        
    16.       
  16.  thread_cache_size = 8        
    17.       
  17.  query_cache_size= 16M        
    18.       
  18.  thread_concurrency = 8        
    19.       
  19.  innodb_data_home_dir =         
    20.       
  20.  innodb_data_file_path = /dev/raw/raw6:1Graw         
    21.       
  21.  log-bin=mysql-bin         
    22.       
  22.  binlog_format=mixed        
    23.       
  23.  server-id       = 2        
    24.       
  24.  log-bin=mysql-bin         
    25.       
  25.  replicate-do-db=test          
    26.       
  26.  replicate-do-db=bbs        
    27.       
  27.  [mysqldump]         
    28.       
  28.  quick         
    29.       
  29.  max_allowed_packet = 16M        
    30.       
  30.  [mysql]         
    31.       
  31.  no-auto-rehash         
    32.       
  32.  [myisamchk]         
    33.       
  33.  key_buffer_size = 128M        
    34.       
  34.  sort_buffer_size = 128M        
    35.       
  35.  read_buffer = 2M        
    36.       
  36.  write_buffer = 2M        
    37.       
  37.  [mysqlhotcopy]         
    38.       
  38.  interactive-timeout         
    39.           
  39.  [[email protected] ~]# service mysqld restart         
    40.       
  40.  Shutting down MySQL..[  OK  ]         
    41.       
  41.  Starting MySQL..[  OK  ]        
    42.

四:在从库上指定主库的连接信息，并开启同步进程

     
  1.  [[email protected] ~]# mysql         
    2.       
  2.  Welcome to the MySQL monitor.  Commands end with ; or \g.         
    3.       
  3.  Your MySQL connection id is 1         
    4.       
  4.  Server version: 5.5.25-log Source distribution         
    5.           
  5.  Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.         
    6.           
  6.  Oracle is a registered trademark of Oracle Corporation and/or its         
    7.       
  7.  affiliates. Other names may be trademarks of their respective         
    8.       
  8.  owners.         
    9.           
  9.  Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.         
    10.           
  10.  mysql> change master to master_host='192.168.123.13',master_user='r_test',master_password='123456';         
    11.       
  11.  Query OK, 0 rows affected (0.07 sec)         
    12.           
  12.  mysql> slave start;         
    13.       
  13.  Query OK, 0 rows affected, 1 warning (0.00 sec)         
    14.           
  14.  mysql> show slave status \G;         
    15.       
  15.  *************************** 1. row ***************************         
    16.       
  16.                 Slave_IO_State: Waiting for master to send event         
    17.       
  17.                    Master_Host: 192.168.123.13         
    18.       
  18.                    Master_User: r_test         
    19.       
  19.                    Master_Port: 3306         
    20.       
  20.                  Connect_Retry: 60         
    21.       
  21.                Master_Log_File: mysql-bin.000011         
    22.       
  22.            Read_Master_Log_Pos: 107         
    23.       
  23.                 Relay_Log_File: dg54-relay-bin.000013         
    24.       
  24.                  Relay_Log_Pos: 253         
    25.       
  25.          Relay_Master_Log_File: mysql-bin.000011         
    26.       
  26.               Slave_IO_Running: Yes         
    27.       
  27.              Slave_SQL_Running: Yes         
    28.       
  28.                Replicate_Do_DB: test,bbs         
    29.       
  29.            Replicate_Ignore_DB:          
    30.       
  30.             Replicate_Do_Table:          
    31.       
  31.         Replicate_Ignore_Table:          
    32.       
  32.        Replicate_Wild_Do_Table:          
    33.       
  33.    Replicate_Wild_Ignore_Table:          
    34.       
  34.                     Last_Errno: 0         
    35.       
  35.                     Last_Error:          
    36.       
  36.                   Skip_Counter: 0         
    37.       
  37.            Exec_Master_Log_Pos: 107         
    38.       
  38.                Relay_Log_Space: 554         
    39.       
  39.                Until_Condition: None         
    40.       
  40.                 Until_Log_File:          
    41.       
  41.                  Until_Log_Pos: 0         
    42.       
  42.             Master_SSL_Allowed: No         
    43.       
  43.             Master_SSL_CA_File:          
    44.       
  44.             Master_SSL_CA_Path:          
    45.       
  45.                Master_SSL_Cert:          
    46.       
  46.              Master_SSL_Cipher:          
    47.       
  47.                 Master_SSL_Key:          
    48.       
  48.          Seconds_Behind_Master: 0         
    49.       
  49.  Master_SSL_Verify_Server_Cert: No         
    50.       
  50.                  Last_IO_Errno: 0         
    51.       
  51.                  Last_IO_Error:          
    52.       
  52.                 Last_SQL_Errno: 0         
    53.       
  53.                 Last_SQL_Error:          
    54.       
  54.    Replicate_Ignore_Server_Ids:          
    55.       
  55.               Master_Server_Id: 1         
    56.       
  56.  1 row in set (0.00 sec)         
    57.           
  57.  ERROR:          
    58.       
  58.  No query specified        
    59.

五：验证数据同步结果，在5.1.36版本下，这里还需要在从库建相应的数据库，导入表数据

     
  1.  mysql> show databases;         
    2.       
  2.  +--------------------+         
    3.       
  3.  | Database           |         
    4.       
  4.  +--------------------+         
    5.       
  5.  | information_schema |         
    6.       
  6.  | bbs                |         
    7.       
  7.  | mysql              |         
    8.       
  8.  | performance_schema |         
    9.       
  9.  | test               |         
    10.       
  10.  +--------------------+         
    11.       
  11.  5 rows in set (0.02 sec)         
    12.           
  12.  mysql> use bbs;         
    13.       
  13.  Database changed         
    14.       
  14.  mysql> show tables;         
    15.       
  15.  +---------------+         
    16.       
  16.  | Tables_in_bbs |         
    17.       
  17.  +---------------+         
    18.       
  18.  | user          |         
    19.       
  19.  +---------------+         
    20.       
  20.  1 row in set (0.00 sec)         
    21.           
  21.  mysql> select count(*) from user;         
    22.       
  22.  +----------+         
    23.       
  23.  | count(*) |         
    24.       
  24.  +----------+         
    25.       
  25.  |      768 |         
    26.       
  26.  +----------+         
    27.       
  27.  1 row in set (0.01 sec)        
    28.
数据库 mysql Mysql5.5配置主从复制
上一篇: 基于docker容器的Mysql主从同步实战一、测试环境二、安装docker、拉取mysql镜像、使用镜像创建mysql容器三、实现mysql主从复制
下一篇: redis学习四、redis三种特殊数据类型

继续阅读