主从介绍

  • MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后,在A上写数据,另外一台B也会跟着写数据,两者数据实时同步。
  • MySQL主从是基于binlog的,主上须开启binlog才能进行主从。
  • 主从过程三个步骤
    • 主将更改操作记录到binlog中
    • 从将主的binlog事件(SQL语句)同步到本机并记录在relaylog中
    • 从根据relaylog里面的SQL语句按顺序执行
  • 该过程有三个线程,主上有一个logdump线程,用来和从的i/o线程传递binlog;从上有两个线程,其中i/o线程用来同步主的binlog并生成relaylog,另外一个SQL线程用来把relaylog里面的SQL语句落地。
    mysql主从介绍,准备工作,主配置,从配置,主从测试
  • 应用环境:备份数据和分担主库数据读取压力

    准备工作

  • 准备两台机器,分别安装好mysql并启动服务,akuilinux01为主,02为从。

    配置主服务器

  • 编辑配置文件,增加两行,并重启服务
    [root@akuilinux01 src]# vim /etc/my.cnf
    server-id=130
    #自定义
    log_bin=linux1
    #指定binlog前缀
    [root@akuilinux01 src]# /etc/init.d/mysqld restart
    Shutting down MySQL... SUCCESS! 
    Starting MySQL... SUCCESS! 
  • 把mysql库备份并恢复成aming库,作为测试数据
    查看库,带akuilinux01的是新生成的。
    [root@akuilinux01 src]# ll /data/mysql/
    总用量 110688
    -rw-rw---- 1 mysql mysql      120 6月  29 09:32 akuilinux01.000001
    -rw-rw---- 1 mysql mysql    71795 6月  29 09:32 akuilinux01.err
    -rw-rw---- 1 mysql mysql       21 6月  29 09:32 akuilinux01.index
    -rw-rw---- 1 mysql mysql        5 6月  29 09:32 akuilinux01.pid
    -rw-rw---- 1 mysql mysql       56 5月  23 15:38 auto.cnf
    -rw-rw---- 1 mysql mysql 12582912 6月  29 09:32 ibdata1
    -rw-rw---- 1 mysql mysql 50331648 6月  29 09:32 ib_logfile0
    -rw-rw---- 1 mysql mysql 50331648 5月  23 15:32 ib_logfile1
    drwx------ 2 mysql mysql     4096 5月  23 15:32 mysql
    drwx------ 2 mysql mysql     4096 5月  23 15:32 performance_schema
    drwx------ 2 mysql mysql        6 5月  23 15:27 test
    drwx------ 2 mysql mysql      324 6月  27 22:52 zrlog
    备份库
    [root@akuilinux01 src]# cd /data/mysql/
    [root@akuilinux01 mysql]# mysqldump -uroot -ps5381561 mysql > /tmp/mysql.sql
    Warning: Using a password on the command line interface can be insecure.
    新建库
    [root@akuilinux01 mysql]# mysql -uroot -ps5381561 -e "create database akui"
    备份恢复到新建库
    [root@akuilinux01 mysql]# mysql -uroot -ps5381561 akui < /tmp/mysql.sql
  • 创建一个用户用作同步数据
    mysql> grant replication slave on *.* to 'repl'@192.168.21.129 identified by 'password';
    Query OK, 0 rows affected (0.00 sec)
    #ip为从机器的ip
    mysql> flush tables with read lock;
    Query OK, 0 rows affected (0.09 sec)
    #锁定数据表(目的是暂时使其不能继续写,保持现有状态用于同步)
    mysql> show master status;
    +--------------------+----------+--------------+------------------+-------------------+
    | File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +--------------------+----------+--------------+------------------+-------------------+
    | akuilinux01.000001 |   660260 |              |                  |                   |
    +--------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    #记住file和position(设置主从同步时会使用)
    mysql> quit
    Bye
  • 备份主中的所有数据库,mysql中包含用户和密码的信息可以不用备份
    root@akuilinux01 ~]# cd /data/mysql/
    [root@akuilinux01 mysql]# ls
    akui                akuilinux01.index  ibdata1      mysql               zrlog
    akuilinux01.000001  akuilinux01.pid    ib_logfile0  performance_schema
    akuilinux01.err     auto.cnf           ib_logfile1  test
    [root@akuilinux01 mysql]# mysqldump -uroot -ps5381561 zrlog > /tmp/zrlog.sql
    Warning: Using a password on the command line interface can be insecure.
    [root@akuilinux01 mysql]# mysqldump -uroot -ps5381561 akui > /tmp/akui.sql
    Warning: Using a password on the command line interface can be insecure.

    配置从服务器

  • 安装mysql并启动
  • 查看my.cnf,配置server-id=131,要求和主不一样,并重启服务
    [root@akuilinux02 mysql]# vim /etc/my.cnf
    server-id=131
    [root@akuilinux02 mysql]# /etc/init.d/mysqld restart
    Shutting down MySQL.. SUCCESS! 
    Starting MySQL. SUCCESS! 
  • 同步主的备份数据到从上
    [root@akuilinux02 mysql]# scp 192.168.21.128:/tmp/*.sql /tmp/
    akui.sql                                                                            100%  644KB  15.6MB/s   00:00    
    mysql.sql                                                                           100%  644KB  26.1MB/s   00:00    
    zrlog.sql                                                                           100% 9869     1.7MB/s   00:00    
  • 创建从上的库
    [root@akuilinux02 mysql]# alias 'mysql=/usr/local/mysql/bin/mysql'
    [root@akuilinux02 mysql]# alias 'mysqldump=/usr/local/mysql/bin/mysqldump'
    [root@akuilinux02 mysql]# mysql -uroot
    mysql> create database akui
    -> ;
    Query OK, 1 row affected (0.00 sec)
    mysql> create database zrlog;
    Query OK, 1 row affected (0.00 sec)
    mysql> quit
    Bye
  • 恢复库
    [root@akuilinux02 mysql]# mysql -uroot akui < /tmp/akui.sql 
    [root@akuilinux02 mysql]# mysql -uroot zrlog < /tmp/zrlog.sql 
    保证和主上的一致
  • 主从同步
    [root@akuilinux02 mysql]# mysql -uroot 
    mysql> stop slave;
    Query OK, 0 rows affected (0.01 sec)
    mysql> change master to master_host='192.168.21.128',master_user='repl',master_password='password',master_log_file='akuilinux01.000001',master_log_pos=660260;
    #IP为主的IP;file、pos分别为主的filename和position。
    Query OK, 0 rows affected, 2 warnings (0.02 sec)
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
  • 从上检测是否同步成功
    mysql> show slave status\G
    Relay_Master_Log_File: akuilinux01.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0  //为主从延迟的时间
            Last_IO_Errno: 0
            Last_IO_Error:
            Last_SQL_Errno: 0
            Last_SQL_Error:
  • 还要解锁主库的表
    [root@akuilinux01 mysql]# mysql -urelp -ppassword
    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)

    几个重要的参数

  • 主服务器上/etc/my.cnf
    • binlog-do-db= //仅同步指定的库
    • binlog-ignore-db= //忽略指定库
  • 从服务器上/etc/my.cnf
    • replicate_do_db=
    • replicate_ignore_db= #这两个尽量不要用
    • replicate_do_table=
    • replicate_ignore_table= #这两个尽量不要用
    • replicate_wild_do_table= //如aming.%, 支持通配符%,代表aming这个库
    • replicate_wild_ignore_table= #尽量使用这两个,使匹配更精确

      主从测试

  • 主上
    mysql> use akui;
    Database changed
    mysql> show tables;
    +---------------------------+
    | Tables_in_akui            |
    +---------------------------+
    | columns_priv              |
    | db                        |
    | event                     |
    | func                      |
    | general_log               |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | innodb_index_stats        |
    | innodb_table_stats        |
    | ndb_binlog_index          |
    | plugin                    |
    | proc                      |
    | procs_priv                |
    | proxies_priv              |
    | servers                   |
    | slave_master_info         |
    | slave_relay_log_info      |
    | slave_worker_info         |
    | slow_log                  |
    | tables_priv               |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    | user                      |
    +---------------------------+
    28 rows in set (0.00 sec)
    删除akui库
    mysql> drop database akui;
    Query OK, 28 rows affected (0.04 sec)
  • 从上
    删除之前
    mysql> use akui;
    Database changed
    mysql> show tables;
    +---------------------------+
    | Tables_in_akui            |
    +---------------------------+
    | columns_priv              |
    | db                        |
    | event                     |
    | func                      |
    | general_log               |
    | help_category             |
    | help_keyword              |
    | help_relation             |
    | help_topic                |
    | innodb_index_stats        |
    | innodb_table_stats        |
    | ndb_binlog_index          |
    | plugin                    |
    | proc                      |
    | procs_priv                |
    | proxies_priv              |
    | servers                   |
    | slave_master_info         |
    | slave_relay_log_info      |
    | slave_worker_info         |
    | slow_log                  |
    | tables_priv               |
    | time_zone                 |
    | time_zone_leap_second     |
    | time_zone_name            |
    | time_zone_transition      |
    | time_zone_transition_type |
    | user                      |
    +---------------------------+
    28 rows in set (0.00 sec)
    删除之后
    mysql> show tables;
    ERROR 1049 (42000): Unknown database 'akui'

    问题

  • 遇到主从不能正常同步,提示uuid相同的错误。这是因为克隆机器导致