Linux 下Mysql主从复制

时间:2022-09-21 12:42:01

Linux 下Mysql的主从复制

master ip:192.168.20.60

slave ip:192.168.20.61

 

##对master的操作

1.创建专门用于备份的用户:

    mysql> grant replication slave on *.* to 'repl'@'192.168.20.61' identified by 'repl';

2.开启binarylog

`vi /etc/my.cnf`

    log-bin=mysql-bin
    server-id=60
    #innodb_flush_log_at_trx_commit = 1
    #sync_binlog=1

    #binlog-do-db=test

    binlog-ignore-db=mysql,information_schema,performance_schema # 忽略的数据库
    #replicate-do-db=test

   修改mysql配置文件后需重启mysql服务 : `service mysqld restart`

 

* ※导出现有数据库。如果现有库要同步到 slave ,需要先导出,然后传到 slave 进行导入

# 先锁定
    mysql> FLUSH TABLES WITH READ LOCK;
   
    # 导出
    mysqldump --master-data --databases -uroot DB1 DB2 ... > data.sql
   
    # 这里可以接着执行下面的步骤3
    mysql> show master status\G
   
    # 解锁
    mysql> UNLOCK TABLES;
   
    # 传送
    scp data.sql root@192.168.20.61:~
   
    # slave 端导入
    mysql -uroot < ~/data.sql

3.查看并记下master状态

    mysql> show master status\G

    *************************** 1. row ***************************
                File: mysql-bin.000005
                Position: 1406
                Binlog_Do_DB:
                Binlog_Ignore_DB: mysql,information_schema,performance_schema
    1 row in set (0.00 sec)

    # 记住两个状态:
    # 日志文件 mysql-bin.000005
    # 记录的位置 1406

## slave 操作

1.开启binarylog (同 master 操作, 修改 `server-id=61` 即可)
2.配置 slave
    mysql> CHANGE MASTER to
            MASTER_HOST='192.168.20.60',
            MASTER_PORT=3306,
            MASTER_USER='repl',
            MASTER_PASSWORD='repl',
            MASTER_LOG_FILE='mysql-bin.000005',
            MASTER_LOG_POS=1406;
   
    # 根据 master 状态指定相应的值   
3.启动 slave
    mysql> start slave;
4.查看 slave 状态

    mysql> show slave status\G

    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.20.60
                      Master_User: repl
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000005
              Read_Master_Log_Pos: 2281
                   Relay_Log_File: test-61-relay-bin.000002
                    Relay_Log_Pos: 1141
            Relay_Master_Log_File: mysql-bin.000005
                 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: 2281
                  Relay_Log_Space: 1312
                  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: 60
                      Master_UUID:
                 Master_Info_File: /usr/local/mysql/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
    1 row in set (0.00 sec)
   
    # 有这两行说明开启成功
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

    # 不成功可以查看错误日志,检查状态是否准确,MySQL 端口是否放行,MySQL 配置是否生效等等。
   
----------------------------------------------------------------
到这里,简单的主从复制配置完成,实现的效果是 **除了指定的不同步的数据库外,master 上的数据库变化都将同步到 slave**。
要说明的是同步的效果是单向的,slave 上的修改不会同步到 master 上。 

----------------------------------------------------------------
参考 
http://www.cnblogs.com/shuidao/p/3551238.html
http://369369.blog.51cto.com/319630/790921
http://laoxu.blog.51cto.com/4120547/1198728