准备

数据库1：master

IP:192,168.10.10这是博主本地的虚拟机。

数据库：mariadb10；

数据库2：slaver

127.0.0.1本地IP。

数据库MYSQL 5.6

这里只是测试,应用中需要同版本数据库,防止错误.

开始

配置主服务器master

[mysqld]

log-bin=mysql-bin   //开启日志
server-id=1             //master ID
read-only=0
binlog-do-db= ***    //需要复制的数据库
binlog_do_db=**    //多个数据库列出
binlog-format=MIXED  //日志格式

查看master情况

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |     2570 | ***|         |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

配置从数据库 slave

vi my.ini

Id配置从库
log_bin           = mysql-bin
server_id         = 2  //不同于master的id
relay_log          = mysql-relay-bin
log_slave_updates  = 1
read_only         = 1

进入从库数据库,配置

mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.10.10',
-> MASTER_USER=‘user’,  //创建的master用于复制的账户 
-> MASTER_PASSWORD='1234',
-> MASTER_LOG_FILE='mysql-bin.000036', //master获取的信息
-> MASTER_PORT=3306, //master获取的端口
-> MASTER_LOG_POS=3252; //master获取的位置 or 0

从服务器开启slave进程

slave start

无报错表示成功

查看从服务器状态

show slave status \G；

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.10
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2570
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 2716
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes                    //重点 日志
            Slave_SQL_Running: Yes                    //重点 sql  
              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: 2570
              Relay_Log_Space: 2872
              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
1 row in set (0.00 sec)

排错

show variables like 'log-bin'; //查看

注意