1.MYSQL主从复制
1.1用途：
Mysql主从复制的目的是实现数据冗余备份，从而保障服务器压力不断增加时一旦出现主库宕机可以快速将web应用数据库配置切换到slave数据库，以确保web应用较高的可用率。
1.2工作原理：
- Slave上执行slave start，Slave IO线程会通过在Master创建的授权用户连接上至Master，并请求master从指定的文件和位置之后发送bin-log日志内容；
- Master接收到来自slave IO线程的请求后，master IO线程根据slave发送的指定bin-log日志position点之后的内容，然后返回给slave的IO线程。
- 返回的信息中除了bin-log日志内容外，还有master最新的binlog文件名以及在binlog中的下一个指定更新position点；
- Slave IO线程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和position点记录到master.info文件中，以便在下一次读取的时候能告知master从响应的bin-log文件名及最后一个position点开始发起请求；
- Slave Sql线程检测到relay-log中内容有更新，会立刻解析relay-log的内容成在Master真实执行时候的那些可执行的SQL语句，将解析的SQL语句并在Slave里执行，执行成功后，Master库与Slave库保持数据一致。
1.3实施注意事项:
1.需要准备两台以上服务器，可以配置一主多从，多主多从；
2.必须确保slave库的position点与master库的同步开始点位一致，文件名一致
1.4主从结构：
1.5实施过程:
1.准备两台服务器(centos7.4)，都安装好MySQL数据库
主库：
IP：192.168.1.112 版本：MySQL8.0.31
从库：
IP: 192.168.1.123 版本：MySQL8.0.31
wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm ###下载镜像包
rpm -ivh mysql80-community-release-el7-1.noarch.rpm ###获取安装包
sed -i 's#gpgcheck=1#gpgcheck=0#g' /etc/yum.repos.d/mysql-community.repo ###避免版本问题密钥报错
yum install mysql-community-server -y ###yum安装数据库
systemctl enable mysqld ###设置开机自启
systemctl start mysqld ###启动MySQL
grep 'password' /var/log/mysqld.log ###获取初始密码
2022-12-05T09:59:08.779856Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ++V5oq:aurX>
[root@zzp112 ~]# mysql -uroot -p'++V5oq:aurX>' ####登录MySQL
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 8
Server version: 8.0.31
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> alter user 'root'@'localhost' identified by 'P@ssw0rd'; ###设置新密码
Query OK, 0 rows affected (0.01 sec)
mysql> set global validate_password.policy=0; ###设置密码强度
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password.length=1; ###设置密码长度
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456'; ###设置密码
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges; ###刷新2.配置主库 /etc/my.cnf配置文件[mysqld]部分
[root@zzp112 ~]# vim /etc/my.cnf
配置内容：添加以下
[mysqld]
server-id = 1
log-bin = mysql-bin3.Master数据库服务器命令行中 创建tongbu用户及密码并设置权限，执行如下命令，查看bin-log文件及position点
[root@zzp112 ~]# mysql -uroot -p123456 ###登录
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 9
Server version: 8.0.31 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> create user 'tongbu'@'%' identified with mysql_native_password by 'P@ssw0rd'; ####创建同步用户
Query OK, 0 rows affected (0.04 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'%'; ####设置tongbu用户权限
Query OK, 0 rows affected (0.01 sec)
mysql> show master status; ####查看起始点位
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1305 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> 4.slave数据库，配置/etc/my.cnf，则需修改server-id，MASTER与Slave端server-id不能一样，Slave端也无需开启bin-log功能
添加内容：
[mysqld]
server-id = 25.Slave指定Master IP、用户名、密码、bin-log文件名（ mysql-bin.000002）及position（1305）
[root@zzp123 ~]# mysql -uroot -p123456
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 9
Server version: 8.0.31 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> change master to
master_host='192.168.10.110',master_user='tongbu',master_password='P@ssw0rd',master_log_file='mysql--bin.000001',master_log_pos=664;
Query OK, 0 rows affected, 8 warnings (0.04 sec) ###指定Master IP、用户名、密码、bin-log文件名（ mysql-bin.000002）及position（1305）6.在slave启动slave start，并执行show slave status\G查看Mysql主从状态：
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
> show slave status\G ###查看主从状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.112
Master_User: tongbu
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1305
Relay_Log_File: zzp123-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes ####I/O线程已启动
Slave_SQL_Running: Yes ####SQL线程已启动
Replicate_Do_DB:
Replicate_Ignore_DB: 7.在主库创建数据库查看从库是否同步：
2.MySQL主主复制
1.在原来主从基础上修改slave库 /etc/my.cnf文件
修改内容：
[mysqld]
server-id = 2
log-bin = mysql-bin12.Master123数据库服务器命令行中 创建tongbu用户及密码并设置权限，执行如下命令，查看bin-log文件及position点
[root@zzp123 ~]# mysql -uroot -p123456
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 16
Server version: 8.0.31 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> create user 'tongbu2'@'%' identified with mysql_native_password by 'P@ssw0rd';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'tongbu'@'%';
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> GRANT REPLICATION SLAVE ON *.* TO 'tongbu2'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 | 1512 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)3.Slave112指定Master IP、用户名、密码、bin-log文件名（ binlog.000001 ）及position（1512）
mysql> change master to
-> master_host='192.168.1.123',master_user='tongbu2',master_password='P@ssw0rd',master_log_file='binloog.000001
Query OK, 0 rows affected, 8 warnings (0.03 sec)
mysql> slave start;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'slave start' at line 1
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.123
Master_User: tongbu2
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 1512
Relay_Log_File: zzp112-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: 4.测试：123库创建数据库，112库可以查询到
112库创建表插入数据，123库也可以查询到
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
