mysql主从复制是拉模式吗,CentOS 7系统配置MySQL的主从复制模式 (Master-Slave Replication)...

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 status\G

*************************** 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: YesSlave_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);

从服务器

在从服务器上，你应该可以看到相同的变化。

拓展知识

GRANT REPLICATION SLAVE

The REPLICATION SLAVE privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.

RULES FOR READ LOCK

The session that holds the lock can read the table (but not write it).

Multiple sessions can acquire a READ lock for the table at the same t  ime.

Other sessions can read the table without explicitly acquiring a READ lock.

RULES FOR WRITE LOCK

The session that holds the lock can read and write the table.

Only the session that holds the lock can access the table. No other session can access it until the lock is released.

Lock requests for the table by other sessions block while the WRITE lock is held.