1、主机信息

2、master安装mysql

[root@master ~]# systemctl stop firewalld
#关闭防火墙
[root@master ~]# systemctl disable firewalld
#设置防火墙开机不自启
[root@master ~]# systemctl status firewalld
#查看防火墙状态
● firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
Active: inactive (dead)
Docs: man:firewalld(1)
[root@master ~]# ll
total 650716
-rw-------. 1 root root
1297 Oct 15 15:21 anaconda-ks.cfg
-rw-r--r--. 1 root root 666328842 Oct 15 16:19 mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
[root@master ~]# tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
#解压
mysql-5.7.35-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-5.7.35-linux-glibc2.12-x86_64/bin/myisamchk
mysql-5.7.35-linux-glibc2.12-x86_64/bin/myisamlog
mysql-5.7.35-linux-glibc2.12-x86_64/bin/myisampack
mysql-5.7.35-linux-glibc2.12-x86_64/bin/mysql
...(省略)
[root@master ~]# ll
total 650716
-rw-------. 1 root root
1297 Oct 15 15:21 anaconda-ks.cfg
drwxr-xr-x. 9 root root
129 Oct 15 16:19 mysql-5.7.35-linux-glibc2.12-x86_64
-rw-r--r--. 1 root root 666328842 Oct 15 16:19 mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
[root@master ~]# mv mysql-5.7.35-linux-glibc2.12-x86_64 /usr/local/mysql
#move到/user/local/mysql
[root@master ~]# cd /usr/local
[root@master local]# ll
total 0
drwxr-xr-x. 2 root root
6 Apr 11
2018 bin
drwxr-xr-x. 2 root root
6 Apr 11
2018 etc
drwxr-xr-x. 2 root root
6 Apr 11
2018 games
drwxr-xr-x. 2 root root
6 Apr 11
2018 include
drwxr-xr-x. 2 root root
6 Apr 11
2018 lib
drwxr-xr-x. 2 root root
6 Apr 11
2018 lib64
drwxr-xr-x. 2 root root
6 Apr 11
2018 libexec
drwxr-xr-x. 9 root root 129 Oct 15 16:19 mysql
drwxr-xr-x. 2 root root
6 Apr 11
2018 sbin
drwxr-xr-x. 5 root root
49 Oct 15 15:14 share
drwxr-xr-x. 2 root root
6 Apr 11
2018 src
[root@master local]# useradd mysql
#创建mysql用户
[root@master ~]# mkdir -p /data/mysql
#创建数据目录
[root@master ~]# chown -R mysql:mysql /data/mysql
#更改目录权限给mysql
[root@master ~]# ll /data
total 0
drwxr-xr-x. 2 mysql mysql 6 Oct 15 16:24 mysql
[root@master ~]# vi /etc/my.cnf
#配置my.cnf，配置信息如下
[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql/data/mysql.sock
[mysqld]
datadir = /data/mysql/data
basedir = /usr/local/mysql
tmpdir = /tmp
socket = /data/mysql/data/mysql.sock
pid-file = /data/mysql/data/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 833306
default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0
#### log ####
log_timestamps=system
log_bin = /data/mysql/log/mysql-bin
log_bin_index = /data/mysql/log/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql/log/mysql-relay-bin
relay_log_index=/data/mysql/log/mysql-relay-bin.index
log_error = /data/mysql/log/mysql-error.log
#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%
#后面创建新的schema之后可以添加至此，重启数据库生效
#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
[root@master ~]# cd /usr/local/mysql/bin

[root@master bin]# ./mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/data/mysql/data --user=mysql --initialize
#初始化mysql
[root@master bin]# cat /data/mysql/log/mysql-error.log
#查看密码
2021-10-15T16:44:20.872078+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-10-15T16:44:21.221129+08:00 0 [Warning] InnoDB: New log files created, LSN=45790
2021-10-15T16:44:21.291641+08:00 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-10-15T16:44:21.311688+08:00 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 176daea4-2d94-11ec-bb2a-5254007baee3.
2021-10-15T16:44:21.313335+08:00 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-10-15T16:44:22.887598+08:00 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2021-10-15T16:44:22.887611+08:00 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2021-10-15T16:44:22.888337+08:00 0 [Warning] CA certificate ca.pem is self signed.
2021-10-15T16:44:23.137972+08:00 1 [Note] A temporary password is generated for root@localhost: CWKx..a&2pgM
[root@master bin]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
#将mysql服务放在/etc/init.d/mysql
[root@master bin]# service mysql start
#开启mysql服务
Starting MySQL.. SUCCESS!
[root@master bin]# ps -ef|grep mysql
#查看mysql进程
root
1904
1
0 16:46 pts/0
00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/data/mysql.pid
mysql
2311
1904
6 16:46 pts/0
00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/log/mysql-error.log --pid-file=/data/mysql/data/mysql.pid --socket=/data/mysql/data/mysql.sock --port=3306
root
2344
1525
0 16:47 pts/0
00:00:00 grep --color=auto mysql
[root@master bin]# ./mysql -u root -p
#登录mysql
Enter password:
Welcome to the MySQL monitor.
Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.35-log
Copyright (c) 2000, 2021, 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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (5.01 sec)
mysql> alter user 'root'@'localhost' password expire never;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
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> update user set host ='%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

3、standby 安装mysql（同master安装mysql）

standby my.cnf

[client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql/data/mysql.sock
[mysqld]
datadir = /data/mysql/data
basedir = /usr/local/mysql
tmpdir = /tmp
socket = /data/mysql/data/mysql.sock
pid-file = /data/mysql/data/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 723306
#server id与主库不同
read_only=1
#设置standby为只读
default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0
#### log ####
log_timestamps=system
log_bin = /data/mysql/log/mysql-bin
log_bin_index = /data/mysql/log/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql/log/mysql-relay-bin
relay_log_index=/data/mysql/log/mysql-relay-bin.index
log_error = /data/mysql/log/mysql-error.log
#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%
#### semi sync replication settings #####
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

4、master上创建同步用户

mysql> CREATE USER 'mysync'@'%' IDENTIFIED BY 'mysync';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysync'@'%' IDENTIFIED BY 'mysync';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |
2276 |
|
|
|
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5、备库上设置

mysql> change master to
-> master_host='192.168.4.205',
-> master_user='mysync',
-> master_password='mysync',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=2276;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.205
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2276
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...(省略)

最后

以上就是孝顺鞋子为你收集整理的MySQL5.7主备环境搭建1、主机信息2、master安装mysql3、standby 安装mysql（同master安装mysql）4、master上创建同步用户5、备库上设置的全部内容，希望文章能够帮你解决MySQL5.7主备环境搭建1、主机信息2、master安装mysql3、standby 安装mysql（同master安装mysql）4、master上创建同步用户5、备库上设置所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。