MySQL中的主从复制
概念
MySQL主从复制是一个异步的复制过程，底层是基于Mysql数据库自带的 二进制日志 功能。就是一台或多台MySQL数据库（slave，即从库）从另一台MySQL数据库（master，即主库）进行日志的复制，然后再解析日志并应用到自身，最终实现 从库 的数据和 主库 的数据保持一致。MySQL主从复制是MySQL数据库自带功能，无需借助第三方工具。
MySQL的主从复制原理如下：
实现
环境准备
提前准备好两台Linux CentOS7服务器，并安装MySQL（版本需一致），服务器信息如下：
| 数据库 | ip | 数据库版本 |
|---|---|---|
| Master | 192.168.220.110 | 5.7.40 |
| slave | 192.168.220.100 | 5.7.40 |
关闭两台服务器的firewalld，以及开启MySQL服务
# 临时关闭
[root@zjx111 ~]# systemctl stop firewalld
# 开机关闭
[root@zjx111 ~]# systemctl disable firewalld
# 开启
[root@zjx111 ~]# mysql -uroot -p'zjx3203766770.'
修改两台服务器的配置
master 需要开启二进制日志，即log_bin，以及server_id=1（设置为master）
my.cnf配置如下：
[root@zjx111 etc]# cat /etc/my.cnf
[mysqld_safe]
[client]
socket=/data/mysql/mysql.sock
[mysqld]
socket=/data/mysql/mysql.sock
port=3306
open_files_limit=8192
innodb_buffer_pool_size=512M
character-set-server=utf8
# binlog
log_bin=1
server_id=1
[mysql]
auto-rehash
prompt=\u@\d \R:\m mysql>
slave 可开启二进制日志也可不开，需开启server_id=2（设置为slave）
# 配置如上所叙，只需将server_id等于2
实验步骤
1、同步主从起始数据
先由master服务器导出其MySQL备份文件，slave服务器复制master服务器的MySQL备份文件，将起始数据与master服务器同步。
# master导出备份文件
[root@master mysql]# mysqldump --all-databases -uroot -p'zjx3203766770.' >all_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
# 正常警告，来自于密码暴露不安全。
# master将备份文件复制到slave中的/data/mysql文件夹下去
[root@master mysql]# scp /data/mysql/all_db.sql root@192.168.220.100:/data/mysql
root@192.168.220.100’s password:
all_db.sql 100% 865KB 89.7MB/s 00:00
# slave上传备份文件，使数据与master一致
[root@slave mysql]# mysql -uroot -p'zjx3203766770.' <all_db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
# 测试主从数据库数据都一致
root@(none) 19:59 mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| zjx |
| zwp |
+--------------------+
6 rows in set (0.00 sec)
2、查看master的二进制日志文件与起始位置号
root@(none) 01:15 mysql>show master status;
+----------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+-------------------+
| 1.000003 | 154 | | | |
+----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 二进制日志文件为1.000003，起始位置号为154。
3、创建有复制权限的用户（提倡先用create创建，后用grant授权）
root@(none) 01:23 mysql>grant replication slave on *.* to 'fan'@'192.168.220.%' identified by 'zjx3203766770.'
Query OK, 0 rows affected, 1 warning (0.02 sec)
# relication slave 授予slave复制的权限
4、在slave上添加授权用户的信息并查看
root@(none) 17:40 mysql>CHANGE MASTER TO MASTER_HOST='192.168.220.110',
-> MASTER_USER='fan',
-> MASTER_PASSWORD='zjx3203766770.',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='1.000003',
-> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
root@(none) 17:40 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.220.110
Master_User: fan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: 1.000003
Read_Master_Log_Pos: 154
Relay_Log_File: nfs-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: 1.000003
Slave_IO_Running: No
Slave_SQL_Running: No
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: 154
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)
ERROR:
No query specified
5、开启slave服务（即Slave_IO与Slave_SQL服务）
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
root@(none) 17:40 mysql>start slave;
Query OK, 0 rows affected (0.02 sec)
root@(none) 17:41 mysql>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.220.110
Master_User: fan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: 1.000003
Read_Master_Log_Pos: 448
Relay_Log_File: nfs-relay-bin.000002
Relay_Log_Pos: 606
Relay_Master_Log_File: 1.000003
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: 448
Relay_Log_Space: 811
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
Master_UUID: 841dad98-ce79-11ed-a03b-000c29aeef9f
Master_Info_File: /data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
实验测试
查看master数据，并插入新数据
root@(none) 20:41 mysql>select * from zjx.zwp520;
+------+------+
| id | name |
+------+------+
| 1 | zjx |
| 2 | zwp |
| 3 | xyx |
| 4 | jx |
| 5 | zrw |
| 6 | lml |
+------+------+
6 rows in set (0.00 sec)
root@(none) 20:42 mysql>insert into zjx.zwp520(id,name) values(7,'lxj');
Query OK, 1 row affected (0.00 sec)
root@(none) 20:43 mysql>select * from zjx.zwp520;
+------+------+
| id | name |
+------+------+
| 1 | zjx |
| 2 | zwp |
| 3 | xyx |
| 4 | jx |
| 5 | zrw |
| 6 | lml |
| 7 | lxj |
+------+------+
7 rows in set (0.00 sec)
查看slave数据是否同步表中数据
root@(none) 20:45 mysql>select * from zjx.zwp520;
+------+------+
| id | name |
+------+------+
| 1 | zjx |
| 2 | zwp |
| 3 | xyx |
| 4 | jx |
| 5 | zrw |
| 6 | lml |
| 7 | lxj |
+------+------+
7 rows in set (0.04 sec)
# 数据相同，实验成功
822
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
