Mysql 主从复制

环境

• 系统：kalilinux
• Mysql:mysql-5.6.40-linux-glibc2.12-x86_64.tar.gz
• Mysql安装位置：

root@kali:/data# ls /usr/local/mysql/
bin  COPYING  data  docs  include  lib  man  my.cnf  my-new.cnf  mysql-test  README  scripts  share  sql-bench  support-files

• Mysql两个实例：
  安装步骤：Mysql多实例安装笔记

root@kali:/data# ls /data/mysql_3306/
data  log  my.cnf
root@kali:/data# ls /data/mysql_3307
data  log  my.cnf

• master 配置文件

root@kali:/data# cat  mysql_3306/my.cnf 
[client]
user=root
port=3306  
socket=/tmp/mysql.sock  
 
[mysqld] 
user=mysql  
basedir = /usr/local/mysql  
datadir=/data/mysql_3306/data
pid-file=/data/mysql_3306/data/mysql_3306.pid
port=3306  
server_id=3306  
socket=/tmp/mysql_3306.sock  
character_set_server = utf8
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
max_allowed_packet = 16777216  
log-error = /data/mysql_3306/log/error.log  
binlog_format = mixed  
expire_logs_days = 30
log-bin = /data/mysql_3306/log/mysql-bin.log  
log-bin-index = /data/mysql_3306/log/mysql-bin.index

server_id启用二进制日志文件
log-bin服务器唯一ID

• slave 配置文件

root@kali:/data# cat  mysql_3307/my.cnf 
[client]
root=root
port=3307
socket=/tmp/mysql.sock  
 
[mysqld] 
user=mysql  
basedir = /usr/local/mysql  
datadir=/data/mysql_3307/data
pid-file=/data/mysql_3307/data/mysql_3307.pid
port=3307  
server_id=3307  
socket=/tmp/mysql_3307.sock  
character_set_server = utf8
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
max_allowed_packet = 16777216
log-error = /data/mysql_3307/log/error.log  
binlog_format = mixed  
expire_logs_days = 30
log-bin = /data/mysql_3307/log/mysql-bin.log  
log-bin-index = /data/mysql_3307/log/mysql-bin.index
read_only=1

server_id启用二进制日志文件
log-bin服务器唯一ID
read_only

连接master

mysql -h 127.0.0.1 -P3306

1、创建一个用户’repl’,并且允许其他服务器可以通过该用户远程访问master，通过该用户去读取二进制数据，实现数据同步

Create user repl identified by 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl';

2、查看master的状态信息

show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql-bin.000004 后面会用到。

连接slaver

mysql -h 127.0.0.1 -P3307

1、通过如下命令建立同步连接

change master to master_host='127.0.0.1' , master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000004',master_log_pos=0;

master_log_file是master中查看的

2、启动slaver

start selver;

3、查看slave服务器状态

show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 478
               Relay_Log_File: mysql_3307-relay-bin.000002
                Relay_Log_Pos: 641
        Relay_Master_Log_File: mysql-bin.000004
             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: 478
              Relay_Log_Space: 819
              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: 3306
                  Master_UUID: 17e272e2-486c-11e8-b5c6-288023c03806
             Master_Info_File: /data/mysql_3307/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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

Slave_IO_Running: Yes ， Slave_SQL_Running: Yes ，表示成功

测试

在master执行：

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.02 sec)

MySQL [(none)]> create database master;
Query OK, 1 row affected (0.02 sec)

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

在slaver执行：

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master             |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)