在CentOS 7上配置MySQL的主从复制模式 (Master-Slave Replication)

  1. 云栖社区>
  2. 博客>
  3. 正文

在CentOS 7上配置MySQL的主从复制模式 (Master-Slave Replication)

香思人 2017-04-27 11:10:39 浏览2548
展开阅读全文

MySQL的主从复制广泛用于数据库备份、故障转移、数据分析等场合。

MySQL主从复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此,要进行复制,必须在主服务器上启用二进制日志。从服务器从主服务器接收已经记录到其二进制日志的更新,当一个从服务器连接主服务器时,主服务器从日志中读取最后一次成功更新的位置,从服务器接收从那时起发生的更新,并在本机上执行相同的更新,然后等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器,在备份过程中主服务器可以继续处理更新。

测试环境

Master: 192.168.10.201
Slave: 192.168.10.202
端口: 3306
数据库:test2
安装MySQL

yum install mariadb mariadb-server
systemctl enable mariadb
service mariadb start

Reset root password

mysqladmin -u root password abc@DEF
主服务器配置

主服务器配置文件/etc/my.cnf

[mysqld]
server-id=1
binlog-do-db=test2
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
log-error=/var/lib/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
log-bin=/var/lib/mysql/mysql-bin
重启MySQL

service mariadb restart
赋予REPLICATION SLAVE权限

mysql -uroot -p
GRANT REPLICATION SLAVE ON . TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

File Position Binlog_Do_DB Binlog_Ignore_DB
mysql-bin.000002 469 test2

1 row in set (0.00 sec)
注意:记下红色部分,稍后还会用到。

备份数据库

为了备份数据库,需要为数据库中所有表叫上“只读锁” (Read Lock),再进行dump备份:

mysqldump -u root -p --all-databases --master-data > /root/dbdump.db
备份完成后,可以用以下命令解锁:

mysql -uroot -p
UNLOCK TABLES;
从服务器配置

还原数据库

mysql -u root -p < /root/dbdump.db
从服务器配置文件/etc/my.cnf

[mysqld]
server-id=2
replicate-do-db=test2
relay-log=/var/lib/mysql/mysql-relay-bin
relay-log-index=/var/lib/mysql/mysql-relay-bin.index
log-error=/var/lib/mysql/mysql.err
master-info-file=/var/lib/mysql/mysql-master.info
relay-log-info-file=/var/lib/mysql/mysql-relay-log.info
log-bin=/var/lib/mysql/mysql-bin
重启MySQL

service mariadb restart
连接主服务器

mysql -uroot -p
stop slave;
CHANGE MASTER TO MASTER_HOST='192.168.10.201', MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=469;
start slave;
show slave statusG
1. row **

           Slave_IO_State: Waiting for master to send event
              Master_Host: 192.168.10.201
              Master_User: slave_user
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000002
      Read_Master_Log_Pos: 469
           Relay_Log_File: mysql-relay-bin.000004
            Relay_Log_Pos: 529
    Relay_Master_Log_File: mysql-bin.000002
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB: test2
      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: 469
          Relay_Log_Space: 1107
          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

验证

主服务器

mysql -uroot -p
drop test2;
create database test2;
use test2;
create table emp (c int);
insert into emp (c) values (10);

网友评论

登录后评论
0/500
评论
香思人
+ 关注