在Linux下搭建Mysql数据库，使两台服务器主机之间实现主从复制
Mysql数据库主从复制原理：
1.主从的概念：
原理： MySQL主从复制是一个异步的复制过程，主库发送更新事件到从库，从库读取更新记录，并执行更新记录，使得从库的内容与主库保持一致。
2.简单搭建原理：
1、两个主机安装MySQL数据库服务
2、修改主机，从机的MySQL配置文件，使得他们相关的配置文件信息能够在链接的时候可以进行对接；
3、确保主机ip和从机ip在同一网段，即在局域网内；
4、在从机上执行链接信息命令，让从机主动连接主机，由于配置文件的信息校验通过，主机允许从机访问主机MySQL服务从而实现主从结构；
5、在主机所执行操作，从机会对其进行复制，从而实现主从复制
Mysql数据库主从环境：
1.虚拟机环境：
两台虚拟机：ip需在同一网段
master：192.168.66.191
slave：192.168.66.192
2.节点基本环境：
两个节点都需对其进行基本操作：
1.设置主机名
2.关闭防火墙
3.关闭selinux
4.编写hosts文件
使其互通
3.Mysql数据库安装
在master，slave节点安装
安装MySQL：
wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-community-server -y
启动MySQL：
systemctl start mysqld
systemctl enable mysqld
对其初始化：
mysql_secure_installation
Enter current password for root (enter for none): #初次运行直接回车
Set root password? [Y/n] y #是否设置root密码
New password: #设置root用户的密码
Re-enter new password: #再次输入你设置的root密码
Remove anonymous users? [Y/n] y #是否移除匿名用户，生产环境建议删除
Disallow root login remotely? [Y/n] n #是否禁止root远程登录
Remove test database and access to it? [Y/n] n #是否删除test数据库
Reload privilege tables now? [Y/n] y #是否重新加载权限表
进入数据库验证初始化是否成功：
master:
[root@master ~]# mysql -uroot -p123456
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 20
Server version: 5.6.51 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye
[root@master ~]#
slave:
[root@slave ~]# mysql -uroot -p123456
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 11
Server version: 5.6.51 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> exit
Bye
[root@slave ~]#
Mysql数据库主从环境搭建：
1.修改MySQL配置文件：
主节点(master)：
[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id=1 #服务id，唯一标识
log-bin=master-bin #启用二进制日志
以上2条配置是主从复制必须添加的
以下配置可根据自己所需选择添加
#binlog-do-db=db01 #需要同步的库
#binlog-ignore-db=mysql #不需要同步的库，可写多个
#expire_logs_days=7 #自动清理7天前的log文件
#log_bin_index = master-bin.index #指定binlog文件的索引文件
[root@master ~]# systemctl restart mysqld
从节点(slave)：
[root@slave ~]# vim /etc/my.cnf
server-id=2 #服务id唯一标识
relay-log-index = slave-relay-bin.index #主服务器i/o日志读取，记录和存放
以上2条配置是主从复制必须配置
以下参数可避免更新不及时，slave重启导致主从复制出错
#read_only = 1
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
#relay-log = slave-relay-bin
[root@slave ~]# systemctl restart mysqld
2.Mysql数据库启动主从复制：
主节点（master）：
[root@master ~]# mysql -uroot -p123456
#查看master节点状态：
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
从节点（slave）:
[root@slave ~]# mysql -uroot -p123456
#执行接入master服务器MySQL服务语句：
mysql> change master to master_host='192.168.66.191',
-> master_port=3306,master_user='root',
-> master_password='123456',master_log_file='master-bin.000001',
-> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#以上语句的参数随自己主机参数进行更改
#开启slave节点
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
#查看slave状态：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.66.191
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: master-bin.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: 120
Relay_Log_Space: 457
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: ff046bd7-d7d8-11eb-ad07-000c29e75af0
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
ERROR:
No query specified
若 Slave_IO_Running: 和 Slave_SQL_Running: 都为 Yes 即为 success！！
3.验证mysql主从复制：
1.在master节点新建一个test库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
2.在从服务器中可看到master节点新建的test库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
3.在主服务器test库中建test表
mysql> use test
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql>
4.在从服务器中查看test表是否同步过来
mysql> use test
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql>
验证成功 ！ success ! !
表中数据可自行插入对其验证！
Mysql数据库主从复制环境搭建完成！
110
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
