高品质网站建设公司,邢台信息港首页,网络市场营销,网站用access做数据库吗目录
一、本次所用结构如图---一主多从级联#xff1a;
二、IP。
三、配置M1#xff1a;
四、从库M1S1#xff1a; 五、从库M2配置#xff1a;
六、 从库M2S1#xff1a; 一、本次所用结构如图--- 一主多从级联#xff1a; 二、IP。这里M1S1和M1S2一样的#xff0…目录
一、本次所用结构如图---一主多从级联
二、IP。
三、配置M1
四、从库M1S1 五、从库M2配置
六、 从库M2S1 一、本次所用结构如图--- 一主多从级联 二、IP。这里M1S1和M1S2一样的所以就只展示M1S1了。
主机IPM1192.168.127.160M1S1192.168.127.161M2192.168.127.163M2S1192.168.127.162
三、配置M1
#配置文件
[root# masterI ~]# vim /etc/my.cnf
[mysqld]
character-set-serverutf8mb4
log_bin mysql-bin
server_id 160
#创建同步用户
M1 [(none)]grant replication slave on *.* to sync192.168.127.% identified by Sync12345.;M1 [(none)]show grants for sync192.168.127.% ;
----------------------------------------------------------
| Grants for sync192.168.127.% |
----------------------------------------------------------
| GRANT REPLICATION SLAVE ON *.* TO sync192.168.127.% |
----------------------------------------------------------
1 row in set (0.00 sec)#备份数据库用于slave同步
M1 [(none)]FLUSH TABLES WITH READ LOCK; #先锁表
[rootmasterI ~]# mysqldump -u root -pRoot12345. -B haxi school /backup/all_date %F.sql#将数据传给从库
[root# masterI ~]# scp /backup/all_2023-07-25.sql 192.168.127.161:/backup/
root192.168.127.161s password:
all_2023-07-25.sql 100% 7435 3.1MB/s 00:00
[root# masterI ~]# scp /backup/all_2023-07-25.sql 192.168.127.162:/backup/
root192.168.127.162s password:
all_2023-07-25.sql 100% 7435 6.2MB/s 00:00
[root# masterI ~]# scp /backup/all_2023-07-25.sql 192.168.127.163:/backup/
root192.168.127.163s password:
all_2023-07-25.sql 100% 7435 3.5MB/s 00:00#当完成备份后解锁
M1 [(none)]UNLOCK TABLES;
四、从库M1S1
#配置文件
[rootNode01 ~]# vim /etc/my.cnf
[mysqld]
character-set-serverutf8mb4
server_id 161#将M1已原有的库数据同步到从库 ---其他两个均同
[rootNode01 backup]# mysql -uroot -pRoot12345. /backup/all_2023-07-25.sql
[rootNode01 backup]# mysql -uroot -pRoot12345. -e show databases;
--------------------
| Database |
--------------------
| information_schema |
| haxi |
| mysql |
| performance_schema |
| school |
| sys |
--------------------#查看主库的二进制文件和position
M1 [(none)]show master status;
-------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------
| mysql_bin.000005 | 449 | | | |
-------------------------------------------------------------------------------
1 row in set (0.00 sec)#指定主库具体信息
M1S1 [(none)]stop slave; #---先停止从库同步
Query OK, 0 rows affected (0.01 sec)M1S1 [(none)]change master to- master_host192.168.127.160, - master_port3306,- master_usersync,- master_passwordSync12345.,- master_log_filemysql_bin.000005,- master_log_pos449;
Query OK, 0 rows affected, 2 warnings (0.01 sec)M1S1 [(none)]start slave; #---开启从库同步
Query OK, 0 rows affected (0.01 sec)
检测slave状况mysql show slave status \G 在主库创建一个名为name1的数据库到从库查看同步情况。 五、从库M2配置
#基本与M1相同但是M2S2需要从M2同步数据所以M2需要开启二进制日志文件。
[rootNode03 ~]# vim /etc/my.cnf
[mysqld]
character-set-serverutf8mb4
log_bin mysql-bin
server_id 163log_slave_updateson #---表示从服务器将M2将来自主服务器的二进制日志事件写入自己的二进制日志中用于自己的从服务器M2S1进行同步
#需要一个用户用于同步
M2S1 [(none)]grant replication slave on *.* to sync192.168.127.% identified by Sync12345.; 在主库创建一个名为name1的数据库到从库查看同步情况 六、 从库M2S1
[rootNode02 ~]# vim /etc/my.cnf
[mysqld]
character-set-serverutf8mb4
server_id 162M2 [(none)]show master status;
-------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------
| mysql_bin.000001 | 449 | | | |
-------------------------------------------------------------------------------
1 row in set (0.00 sec)M2S1 [(none)]stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)M2S1 [(none)]change master to- master_host192.168.127.163, - master_port3306,- master_usersync,- master_passwordSync12345.,- master_log_filemysql_bin.000001,- master_log_pos449;
Query OK, 0 rows affected, 2 warnings (0.00 sec)M2S1 [(none)]start slave;
Query OK, 0 rows affected (0.01 sec)检查slave状况 四个主机数据库不完全一致是因为我在配置过程中是分开配置检查但是通过最后的主库创建的数据库name3可以看到所有从库都能够实现同步
