第一章 ，主从复制环境准备
1，准备主机并修改主机名如下：
主机名 | ip | 角色 |
db-master | 192.168.1.107 | 主库 |
db-slave | 192.168.1.109 | 从库 |
注意：本次使用系统为：centos7.9
2，下载社区版mysql二进制安装包
1）打开mysql官网下载地址：（本次使用的版本为：mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz）
https://dev.mysql.com/downloads/
2）选择：MySQL Community Server
3）选择：Archives
4）选择要下载的版本，点击download下载
5）上传mysql安装包到两台主机db-master和db-slave的/opt目录下
[root@db-master opt]# ls
mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz
[root@db-slave opt]# ls
mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz第二章，db-master主机安装数据库
1，创建mysql用户
[root@db-master ~]# useradd -s /sbin/nologin -M mysql #创建没有家目录并行不能登录的用户
[root@db-master ~]# tail -1 /etc/passwd #查看用户
mysql:x:1000:1000::/home/mysql:/sbin/nologin2，解压mysql安装包和更改属主和属组，并创建软连接
#解压
[root@db-master ~]# cd /opt
[root@db-master opt]# xz -d mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz
[root@db-master opt]# ls -l
total 1143408
-rw-r--r-- 1 root root 1171045888 Apr 27 17:00 mysql-8.0.31-linux-glibc2.12-x86_64.tar
[root@db-master opt]# tar xf mysql-8.0.31-linux-glibc2.12-x86_64.tar
[root@db-master opt]# ls -l
total 1143408
drwxr-xr-x 9 root root 129 Apr 27 17:05 mysql-8.0.31-linux-glibc2.12-x86_64
-rw-r--r-- 1 root root 1171045888 Apr 27 17:00 mysql-8.0.31-linux-glibc2.12-x86_64.tar
更改属主和属组
[root@db-master opt]# chown -R mysql:mysql mysql-8.0.31-linux-glibc2.12-x86_64
[root@db-master opt]# ls -ld mysql-8.0.31-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql 129 Apr 27 17:05 mysql-8.0.31-linux-glibc2.12-x86_64
#在/usr/local目录创建软连接mysql：
[root@db-master opt]# cd /usr/local/
[root@db-master local]# ln -s /opt/mysql-8.0.31-linux-glibc2.12-x86_64 mysql
[root@db-master local]# ls -l mysql
lrwxrwxrwx 1 root root 40 Apr 27 17:08 mysql -> /opt/mysql-8.0.31-linux-glibc2.12-x86_643，创建mysql数据目录并更改属主和属组
[root@db-master opt]# mkdir -p /data/mysql/
[root@db-master opt]# chown -R mysql:mysql /data/mysql/
[root@db-master opt]# ll -d /data/mysql/
drwxr-xr-x 2 mysql mysql 6 Apr 27 17:32 /data/mysql/4，添加环境变量，编辑/etc/profile,在最后加入以下内容：
[root@db-master opt]# vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin #加入该内容
[root@db-master opt]# source /etc/profile #让环境变量生效5，查看mysql版本
[root@db-master opt]# mysql --version
mysql Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)6，编辑mysql配置文件my.cnf，内容如下：
vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.log
log_bin=/data/mysql/mysql-bin
binlog_format=row
skip-name-resolve
server-id=52
gtid-mode=on
enforce-gtid-cnotallow=true
log_replica_updates=1
relay_log_purge=0
max_cnotallow=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_redo_log_capacity=1073741824
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
binlog_expire_logs_secnotallow=604800
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock
prompt=3306 [\\d]>7，初始化
[root@db-master mysql]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql8，编辑mysql启动文件mysqld.service，可以让systemctl管理
vim /etc/systemd/system/mysqld.service
[Unit]
Descriptinotallow=MySQL Server
Documentatinotallow=mam:mysqld(8)
Documentatinotallow=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=50009，启动和设置开启自启动
[root@db-master opt]# systemctl daemon-reload #重载systemctl配置
[root@db-master opt]# systemctl enable mysqld #设置开启自启动
[root@db-master mysql]# systemctl start mysqld.service #启动mysql10，查看mysql状态
[root@db-master mysql]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Thu 2023-04-27 20:38:43 CST; 2min 9s ago
Docs: http://dev.mysql.com/doc/refman/en/using-systemd.html
Main PID: 9252 (mysqld)
CGroup: /system.slice/mysqld.service
└─9252 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Apr 27 20:38:43 db-master systemd[1]: Started MySQL Server.
#可以看到 Active: active (running) 说明mysql在运行了！！11，查看mysql端口是否起来
[root@db-master mysql]# netstat -tunlp| grep 3306
tcp6 0 0 :::3306 :::* LISTEN 9252/mysqld12，登录mysql
[root@db-master ~]# mysql #此时mysql还没有密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
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.
3306 [(none)]>13，修改root密码
3306 [(none)]>alter user root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.06 sec)
3306 [(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)14，用密码重新登录
[root@db-master ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
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.
3306 [(none)]>15，创建主从同步账号并授权
3306 [(none)]>create user backup@'192.168.1.%' identified with mysql_native_password by '123456'; #创建用户，切记：生产环境要用强密码
Query OK, 0 rows affected (0.01 sec)
3306 [(none)]>grant all on *.* to backup@'192.168.1.%'; #授权给用户backup
Query OK, 0 rows affected (0.01 sec)
3306 [(none)]>flush privileges;
Query OK, 0 rows affected (0.00 sec)第三章 db-slave主机安装数据库
1，安装步骤和db-master安装步骤1-14部相同，不需要做15步。其6步需要修改配置文件
db-slave安装mysql的my.cnf配置文件只有一项要更改，即server-id值不能相同，比如：
vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/
socket=/tmp/mysql.sock
log-error=/data/mysql/mysql.log
log_bin=/data/mysql/mysql-bin
binlog_format=row
skip-name-resolve
server-id=53 #该值两台机不能相同
gtid-mode=on
enforce-gtid-cnotallow=true
log_replica_updates=1
relay_log_purge=0
max_cnotallow=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_redo_log_capacity=1073741824
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
binlog_expire_logs_secnotallow=604800
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M
[client]
socket=/tmp/mysql.sock
prompt=3306 [\\d]>2，安装完db-slave数据库并登录，设置从库
1）查看主库binlog名字和位置，在db-master主机登录数据库并查看
3306 [(none)]>show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1503
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 08ada0b2-e4f8-11ed-8392-000c297db6f4:1-6
1 row in set (0.00 sec)3，登录从库，并执行以下命令
CHANGE MASTER TO MASTER_HOST='192.168.1.107',MASTER_USER='backup',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1503,MASTER_CONNECT_RETRY=10;以上命令说明：
MASTER_HOST='192.168.107', #为主库主机的ip地址 MASTER_USER='backup', #为创建的同步账户 MASTER_PASSWORD='123456', #同步账户的密码 MASTER_PORT=3306, #主库端口 MASTER_LOG_FILE='mysql-bin.000002', #主库master当前状态binlog文件名 MASTER_LOG_POS=154, #binlog的位置 MASTER_CONNECT_RETRY=10; #如果断开，尝试从新同步数次
4，启动从库功能slave
3306 [(none)]>start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)5，查看从库状态
3306 [(none)]>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.1.107
Master_User: backup
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1503
Relay_Log_File: db-slave-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000002
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: 1503
Relay_Log_Space: 539
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: 52
Master_UUID: 08ada0b2-e4f8-11ed-8392-000c297db6f4
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
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: 70e5532e-e500-11ed-87cc-000c29a29623:1-2
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)以上的状态信息中两条都为yes，其它没有error，说明同步状态正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes第四章 测试主从同步功能是否正常
1，在主库创建数据库
3306 [(none)]>create database test;
Query OK, 1 row affected (0.07 sec)
3306 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)2，在从库查看是否有test数据库
3306 [(none)]>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)以上测试说明同步成功！
