mysql 5.7 搭建基本的 master/salve详解数据库

os: centos 7.4
db: mysql 5.7

安装好一台虚拟机，安装好mysql，然后克隆好另外一台虚拟机

centos74_mysql5.7         192.168.56.100   
centos74_mysql5.7_slave   192.168.56.200

master端操作

my.cnf 参考另一篇blog

create user 'replicator'@'192.168.56.%' identified by 'mysqlmysql'; 
grant replication slave on *.* to 'replicator'@'192.168.56.%'; 
flush privileges; 
show master status/G

既有MYISAM又有INNODB的话就在主服务器上使用如下命令导出服务器的一个快照：

mysqldump -uroot -p --lock-tables --events --triggers --routines --flush-logs --master-data=2 --databases peiybdb1 > /tmp/db.sql

只有INNODB的话就是用如下命令：

mysqldump -uroot -p --single-transaction --events --triggers --routines --flush-logs --master-data=2 --databases peiybdb1 > /tmp/db.sql

这里需要注意几个参数的使用：
–single-transaction 这个参数只对innodb适用。
–databases 后面跟除mysql以后的其他所有数据库的库名，我这里只有一个test库。
–master-data 参数会记录导出快照时候的mysql二进制日志位置，一会会用到。

slave端操作

my.cnf 参考另一篇blog

初始化数据库

# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/var/lib/mysql/ 

使用grep命令查找到二进制日志的名称以及位置

# grep -i "change master" /tmp/db.sql

导入sql文件

# mysql -hlocalhost -uroot -p peiybdb1 < /tmp/db.sql

show slave status/G 
stop slave; 
 
change master to 
  master_host='192.168.56.100', 
  master_user='replicator', 
  master_password='mysqlmysql', 
  master_port=3306, 
  master_log_file='mysql-bin.000141', 
  master_log_pos=154, 
  master_connect_retry=10; 
 
start slave/G   
show slave status/G 
*************************** 1. row *************************** 
               Slave_IO_State: Waiting for master to send event 
                  Master_Host: 192.168.56.100 
                  Master_User: replicator 
                  Master_Port: 3306 
                Connect_Retry: 10 
              Master_Log_File: mysql-bin.000141 
          Read_Master_Log_Pos: 322 
               Relay_Log_File: mysql-relay-bin.000006 
                Relay_Log_Pos: 488 
        Relay_Master_Log_File: mysql-bin.000141 
             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: 322 
              Relay_Log_Space: 695 
              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_UUID: 73b494ef-30dd-11e8-a80f-0800273d32a4 
             Master_Info_File: /var/lib/mysql/master.info 
                    SQL_Delay: 0 
          SQL_Remaining_Delay: NULL 
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 
           Master_Retry_Count: 86400 
                  Master_Bind:  
      Last_IO_Error_Timestamp:  
     Last_SQL_Error_Timestamp:  
               Master_SSL_Crl:  
           Master_SSL_Crlpath:  
           Retrieved_Gtid_Set:  
            Executed_Gtid_Set:  
                Auto_Position: 0 
         Replicate_Rewrite_DB:  
                 Channel_Name:  
           Master_TLS_Version:  
1 row in set (0.00 sec)

查看Slave_IO_Running和Slave_SQL_Running的状态，如果都为Yes，那么就成功了。