一、主从复制的概念
什么是主从复制：
主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库
主从复制的作用：
做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失
架构的扩展，业务量越来越大，I/O访问频率过高，单机无法满足，多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能
读写分离，使数据库能支撑更大的并发
主从复制的原理：
数据库有个bin-log二进制文件，记录了所有sql语句
我们的目标就是把主数据库的bin-log文件的sql语句复制到从库
让其在从数据的relay-log(中继日志)重做日志文件中再执行一次这些sql语句即可
总结：
从库slave生成两个线程，i/o线程和sql线程，i/o将变更记录写到二进制日志文件中，再写到中继日志中，sql线程读取中继日志，解析操作，最终数据统一
注意：
I/O进程：负责通信
SQL进程：负责写数据，根据log日志写数据
主从复制的部署
至少2台服务器（一台是master一台谁 slave）
注意：
所有节点关闭防火墙和selinux
保证yum仓库可用
保证网络畅通
如果是克隆的服务器需要修改每台数据库的server-uuid
在所有的服务器上面安装数据库
主服务器上面部署（master）
[root@master ~]# vi /etc/my.cnf
log-bin = my1log
server-id = 1[root@master ~]# mysql -u root -pPassion@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> grant all on *.* to 'slave'@'%' identified by 'Passion@123';
mysql> flush privileges;
mysql> exit
Bye重启服务
[root@master ~]# systemctl restart mysqld注意：
replication slave：
拥有此权限可以查看从服务器，从主服务器读取二进制日志
super权限：
允许用户使用修改全局变量的SET语句以及CHANGE MASTER语句
reload权限：
必须拥有reload权限，才可以执行flush [tables | logs | privileges]
Slave部署
[root@slave ~]# vi /etc/my.cnf
log-bin = my2log
server-id = 2重启服务：
[root@slave ~]# systemctl restart mysqld获取主服务器信息：（主服务器操作）
[root@master ~]# mysql -u root -pPassion@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
| my1log.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.39-log, Binlog ver: 4 |
| my1log.000001 | 123 | Previous_gtids | 1 | 154 | |
+---------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)指定主服务器信息：（从服务器操作）
[root@slave ~]# mysql -u root -pPassion@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> edit
-> ;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
注意：edit中添加的内容
CHANGE MASTER TO
MASTER_HOST='master',
MASTER_USER='slave',
MASTER_PASSWORD='QianFeng@123',
MASTER_PORT=3306,
MASTER_LOG_FILE='my1log.000001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
参数解释：
CHANGE MASTER TO
MASTER_HOST='mysql-master-1.blackmed.cn/ip',
MASTER_USER='slave', //主服务器用户（授权的用户）
MASTER_PASSWORD='big', //授权用户的密码
MASTER_PORT=3306, //主库的端口
MASTER_LOG_FILE='master2-bin.001', //日志文件（show binlog events;）查看的
MASTER_LOG_POS=4, //日志位置
MASTER_CONNECT_RETRY=10; //默认尝试次数
获取参数：
mysql> help change master to启动slave：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)注意：
stop slave；停止slave
reset master；删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件；用于第一次进行搭建主从库时，进行主库binlog初始化工作
reset slave；用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件
查看主从状态：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: slave
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: my1log.000001
Read_Master_Log_Pos: 154
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 361
Relay_Master_Log_File: my1log.000001
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: 154
Relay_Log_Space: 568
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: 85c6acc4-3db0-11ed-b302-000c29311164
Master_Info_File: /var/lib/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)注意：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
验证：
主服务器创建数据：
mysql> create database t1;
Query OK, 1 row affected (0.00 sec)从服务器查看数据：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| t1 |
+--------------------+
5 rows in set (0.00 sec)至此主从复制完成（bin-log的方式）
扫一扫
169
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
