题目
- 将 MYSQL 主从复制的用途、工作原理、实施注意事项写出来。并且划出主从结构图和把实施的步骤写在文档里面。
- 构建 MYSQL 主主复制，实现在主库操作增删改，从库能够同步，在从库操作增删改，主库也能同步数据。
主从复制
用途
可实现数据备份，读写分离，亦可解决单点故障，实现冗余互备。
工作原理
- 主库 I/O 线程将新增，删除，更改操作记录在 binlog 文件中，并对每个操作添加相应点位。
- 从库 I/O 线程根据配置向主库发送读取相应 binlog 文件及点位数据的请求。
- 主库根据收到的请求返回相应数据及当前 binlog 文件及点位数据。
- 从库 I/O 线程接收到主库返回的数据后，将 binglog 数据写入到自己的中间文件中；同时，记录供下次读取使用的 binlog 文件名和相应点位。
- 从库 SQL 线程将自己中继文件中的数据读取出来，解析成 SQL 语句，并在从库执行相应的操作。
注意事项
- 主从库的 server-id 必须是全局唯一的，不可重复。
- 配置从库时，binlog 文件和点位一定要和主库一致。
- 搭建完主从结构后一定要确认从库 I/O 线程和 SQL 线程是否正常工作。
- 要尽量降低主从之间的延迟。
- 出现主从异常后，要根据实际业务情况选择合适的解决方案。
- 恢复异常或新增从库时，如果需要锁表，需多方沟通，确认可行再操作。解锁时要确认主从正常工作，方可解锁。
主从结构图
实施步骤
-
安装 mysql 8：
$ yum -y install mysql-community-server -
编辑配置文件：
/etc/my.cnf-
Master 编辑内容：
[mysqld] # 数据库 GID 不可重复 server-id = 110 # 日志文件前缀 log-bin = mysql-bin -
Slave 编辑内容：
[mysqld] # 数据库 GID 不可重复 server-id = 111
-
-
启动数据库：
$ systemctl start mysqld -
登录主库创建从库连接账号：
> create user 'tongbu'@'%' identified with mysql_native_password by 'WahahaAyaya123!'; -
给账号授权：
> grant replication slave on *.* to 'tongbu'@'%'; -
查看主库 Master 状态：
+------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 1573 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) -
登录从库设置 Master 信息：
> change master to master_host='192.168.0.110', master_user='tongbu', master_password='WahahaAyaya123!', master_log_file='mysql-bin.000001', master_log_pos=1573; -
从库开启同步：
> start slave; -
查看 slave 状态信息：
> show slave status\GSlave_IO_Running: Yes Slave_SQL_Running: Yes -
测试：
-
在 Master 库创建数据库：
mysql> create database test_03; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test_01 | | test_03 | +--------------------+ 7 rows in set (0.00 sec) -
查看 Slave 库：
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test_03 | +--------------------+ 6 rows in set (0.01 sec)
-
互为主从
基础环境：两台虚拟机分别为 110, 111，均已装好 MySQL 8。
搭建步骤：
-
修改两个数据库的配置文件：
# 110 [mysqld] server-id = 110 log-bin = 110-bin # 111 [mysqld] server-id = 111 log-bin = 111-bin -
重启数据库：
$ systemctl restart mysqld -
创建账号：
-- 110 mysql> create user 'tb110'@'%' identified with mysql_native_password by 'WahahaAyaya123!'; -- 111 mysql> create user 'tb111'@'%' identified with mysql_native_password by 'WahahaAyaya123!'; -
授权：
-- 110 mysql> grant replication slave on *.* to 'tb110'@'%'; -- 111 mysql> grant replication slave on *.* to 'tb111'@'%'; -
查看 Master 状态：
-- 110 mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | 110-bin.000001 | 662 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) -- 111 mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | 111-bin.000001 | 662 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) -
配置 Slave:
-- 110 mysql> change master to -> master_host='192.168.0.111', -> master_user='tb111', -> master_password='WahahaAyaya123!', -> master_log_file='111-bin.000001', -> master_log_pos=662; -- 111 mysql> change master to -> master_host='192.168.0.110', -> master_user='tb110', -> master_password='WahahaAyaya123!', -> master_log_file='110-bin.000001', -> master_log_pos=662; -
开启 Slave：
> start slave; -
查看状态：
-- 110 Slave_IO_State: Waiting for source to send event Master_Host: 192.168.0.111 Master_User: tb111 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: 111-bin.000001 Read_Master_Log_Pos: 662 Relay_Log_File: centos7-110-relay-bin.000002 Relay_Log_Pos: 324 Relay_Master_Log_File: 111-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes -- 111 Slave_IO_State: Waiting for source to send event Master_Host: 192.168.0.110 Master_User: tb110 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: 110-bin.000001 Read_Master_Log_Pos: 662 Relay_Log_File: centos7-111-relay-bin.000009 Relay_Log_Pos: 324 Relay_Master_Log_File: 110-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes -
测试建库：
-- 110 mysql> create database test110; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test110 | | test_01 | | test_03 | +--------------------+ 7 rows in set (0.00 sec) -- 111 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test110 | | test_03 | +--------------------+ 6 rows in set (0.01 sec) -
测试建表：
-- 111 mysql> use test110; Database changed mysql> create table t111 (id int,name varchar(32),stunt varchar(64)); Query OK, 0 rows affected (0.03 sec) mysql> desc t111; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | stunt | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) -- 110 mysql> use test110; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> desc t111; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | stunt | varchar(64) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) -
测试插入数据：
-- 110 mysql> insert into t111 value (1,'李莫愁','冰魄银针'), (2,'杨过','黯然销魂掌'), (2,'小龙女','玉女素心剑'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t111; +------+-----------+-----------------+ | id | name | stunt | +------+-----------+-----------------+ | 1 | 李莫愁 | 冰魄银针 | | 2 | 杨过 | 黯然销魂掌 | | 2 | 小龙女 | 玉女素心剑 | +------+-----------+-----------------+ 3 rows in set (0.00 sec) -- 111 mysql> select * from t111; +------+-----------+-----------------+ | id | name | stunt | +------+-----------+-----------------+ | 1 | 李莫愁 | 冰魄银针 | | 2 | 杨过 | 黯然销魂掌 | | 2 | 小龙女 | 玉女素心剑 | +------+-----------+-----------------+ 3 rows in set (0.00 sec) -
测试修改数据：
-- 111 mysql> update t111 set id = 3 where name = '小龙女'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t111; +------+-----------+-----------------+ | id | name | stunt | +------+-----------+-----------------+ | 1 | 李莫愁 | 冰魄银针 | | 2 | 杨过 | 黯然销魂掌 | | 3 | 小龙女 | 玉女素心剑 | +------+-----------+-----------------+ 3 rows in set (0.00 sec) -- 110 mysql> select * from t111; +------+-----------+-----------------+ | id | name | stunt | +------+-----------+-----------------+ | 1 | 李莫愁 | 冰魄银针 | | 2 | 杨过 | 黯然销魂掌 | | 3 | 小龙女 | 玉女素心剑 | +------+-----------+-----------------+ 3 rows in set (0.00 sec) -
测试删除数据：
-- 110 mysql> delete from t111 where id = 1; Query OK, 1 row affected (0.01 sec) mysql> select * from t111; +------+-----------+-----------------+ | id | name | stunt | +------+-----------+-----------------+ | 2 | 杨过 | 黯然销魂掌 | | 3 | 小龙女 | 玉女素心剑 | +------+-----------+-----------------+ 2 rows in set (0.00 sec) -- 111 mysql> select * from t111; +------+-----------+-----------------+ | id | name | stunt | +------+-----------+-----------------+ | 2 | 杨过 | 黯然销魂掌 | | 3 | 小龙女 | 玉女素心剑 | +------+-----------+-----------------+ 2 rows in set (0.00 sec)
198
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
