理解MySQL主从复制原理
主服务器开启binlog日志，从库生成log dump线程，将binlog日志传给从库I/O线程，从库生成俩个线程，一个是I/O线程，一个是SQL线程，I/O线程去请主库的binlog日志，并将binlog日志中的文件写入relay log中，sql线程会读取relay log 中的内容，并解析成具体的操作，来实现主从一致，达到最终数据一致的目的。
2.完成MySQL主从复制（一主两从）
环境准备：
主机名IP地址端口号node01192.168.11.1103306node02192.168.11.1113306node03192.168.11.1123306
数据库准备：
create database company;
use company
CREATE TABLE `emp` (
`empno` int(4) NOT NULL,
`ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`mgr` int(4) NULL DEFAULT NULL,
`hiredate` date NOT NULL,
`sai` int(255) NOT NULL,
`comm` int(255) NULL DEFAULT NULL,
`deptno` int(2) NOT NULL,
PRIMARY KEY (`empno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `emp` VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
INSERT INTO `emp` VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
INSERT INTO `emp` VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
INSERT INTO `emp` VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
INSERT INTO `emp` VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
INSERT INTO `emp` VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
INSERT INTO `emp` VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
INSERT INTO `emp` VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
INSERT INTO `emp` VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
INSERT INTO `emp` VALUES (1011, '周泰', '文员', 1006, '2007-05-23', 11000, NULL, 20);
INSERT INTO `emp` VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
INSERT INTO `emp` VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
INSERT INTO `emp` VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
INSERT INTO `emp` VALUES (1015, '张三', '保洁员', 1001, '2013-05-01', 80000, 50000, 50);
方式一：基于三台服务器实现主从复制
主库配置：
1、在mysqld标签下添加server_id并开启bin_log日志
[root@node01 ~]# cat /etc/my.cnf[mysqld]
log_bin=mysql_bin
server_id=1
2、重启数据库服务
[root@node01 ~]# systemctl restart mysqld.service
3、授权同步账号和密码
mysql> grant replication slave on *.* to 'rep'@'192.168.11.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
4、查看授权信息
mysql> show grants for 'rep'@'192.168.11.%';
+--------------------------------------------------------+
| Grants for rep@192.168.11.% |
+--------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'192.168.11.%' |
+--------------------------------------------------------+
1 row in set (0.00 sec)
5、对表操作
# 锁表设置为只读# 为后边备份准备，注意生产环境要提前申请停机时间，停服mysql> flush tables with read lock;# 超过时间不操作会自动解锁，查看超时时间mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------+----------+
13 rows in set (0.01 sec)# 查看主库状态mysql> show master status ;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 | 11824 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
6、备份数据库数据
# 创建备份目录[root@node01 ~]# mkdir /server/backup -p[root@node01 ~]# mysqldump -uroot -p -A -B | gzip > /server/backup/mysql_bak.$(date +%F).sql.gzEnter password:
7、解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
8、主库备份数据传送到从库
# 在从库上常见备份目录[root@node02 ~]# mkdir /server/backup -p# scp传送[root@node01 ~]# scp /server/backup/mysql_bak.2023-03-25.sql.gz 192.168.11.111:/server/backup/[root@node01 ~]# scp /server/backup/mysql_bak.2023-03-25.sql.gz 192.168.11.112:/server/backup/
从库配置：
1、关闭bin_log参数，设置server-id
[root@node02 ~]# cat /etc/my.cnf[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=2
2、重启数据库服务
[root@node02 ~]# systemctl restart mysqld.service
3、还原从主库传输过来的数据文件
[root@node02 ~]# cd /server/backup/[root@node02 backup]# gzip -d mysql_bak.2023-03-25.sql.gz[root@node02 backup]# mysql -uroot -p < mysql_bak.2023-03-25.sqlEnter password:
4、检查数据完整性
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use company;
mysql> select * from company;# 数据完整，恢复完成
5、配置主从同步
# 查看主库的binlog和pos位置点mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001 | 11824 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)# 从库上配置mysql> change master to
-> master_host='192.168.11.110',
-> master_user='rep',
-> master_password='123456',
-> master_log_file='mysql_bin.000001',
-> master_log_pos=11824;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
6、启动从库同步并检查状态
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.11.110
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 11824
Relay_Log_File: node02-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes# 看目前最后俩行是否为YES，俩个线程都为YES才OK
测试：
1、主库创建一个数据库
mysql> create database test_master;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| mysql |
| performance_schema |
| sys |
| test_master |
+--------------------+
6 rows in set (0.00 sec)
2、从库检查
[root@node02 backup]# mysql -uroot -p -e 'show databases;'Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| mysql |
| performance_schema |
| sys |
| test_master |
+--------------------+
第二台从库一样的配置，除了server_id不同
