mysql主备环境同机搭建

社区版下载地址：

downloads.mysql.com/archives/community/

原包

##解压
[root@prdb19 u01]# tar -xvf mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz 
mysql-8.4.0-linux-glibc2.28-x86_64/bin/
mysql-8.4.0-linux-glibc2.28-x86_64/bin/myisam_ftdump
mysql-8.4.0-linux-glibc2.28-x86_64/bin/myisamchk
mysql-8.4.0-linux-glibc2.28-x86_64/bin/myisamlog

...

[root@prdb19 mysql-8.4.0-linux-glibc2.28-x86_64]# ll
total 292
drwxr-xr-x.  2 7161 31415   4096 Apr 10 16:28 bin
drwxr-xr-x.  2 7161 31415     38 Apr 10 16:28 docs
drwxr-xr-x.  3 7161 31415   4096 Apr 10 16:28 include
drwxr-xr-x.  6 7161 31415    200 Apr 10 16:28 lib
-rw-r--r--.  1 7161 31415 282183 Apr 10 14:26 LICENSE
drwxr-xr-x.  4 7161 31415     30 Apr 10 16:28 man
-rw-r--r--.  1 7161 31415    666 Apr 10 14:26 README
drwxr-xr-x. 28 7161 31415   4096 Apr 10 16:28 share
drwxr-xr-x.  2 7161 31415     77 Apr 10 16:28 support-files
[root@prdb19 mysql-8.4.0-linux-glibc2.28-x86_64]# 

##重命名目录
[root@prdb19 u01]# mv mysql-8.4.0-linux-glibc2.28-x86_64 mysql
[root@prdb19 u01]# ll
total 469884
drwxrwxr-x. 6 oracle oinstall        65 Aug 24 12:02 app
drwxr-xr-x. 9 root   root           129 Sep  5 16:18 mysql
-rw-r--r--. 1 root   root     481157440 Sep  5 16:17 mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz
[root@prdb19 u01]# 

##添加用户
groupadd mysql
useradd -r -g mysql mysql

##拷贝实例目录
cp -rp mysql mysqla
cp -rp mysql mysqlb

mkdir -p /u01/mysql1/data
mkdir -p /u01/mysql2/data
chown -R mysql mysql2
chgrp -R mysql mysql2
chown -R mysql mysql1
chgrp -R mysql mysql1

创建两个实例区
bin/mysqld --initialize --user=mysql --basedir=/u01/mysqla --datadir=/u01/mysql1/data
bin/mysqld --initialize --user=mysql --basedir=/u01/mysqlb --datadir=/u01/mysql2/data



##详细如下：
[root@prdb19 mysqla]# bin/mysqld --initialize --user=mysql --basedir=/u01/mysqla --datadir=/u01/mysql1/data
2024-09-05T09:13:25.199866Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-09-05T09:13:25.399705Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-09-05T09:13:25.431720Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-09-05T09:13:25.490057Z 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: 1b422e80-6b67-11ef-a527-08002739e9dc.
2024-09-05T09:13:25.492700Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-09-05T09:13:27.169740Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-09-05T09:13:27.169752Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-09-05T09:13:27.171928Z 0 [Warning] CA certificate ca.pem is self signed.
2024-09-05T09:13:28.180157Z 1 [Note] A temporary password is generated for root@localhost: kbsn7VkqU:Vs


[root@prdb19 mysqlb]# bin/mysqld --initialize --user=mysql --basedir=/u01/mysqla --datadir=/u01/mysql2/data
2024-09-05T09:27:40.542702Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-09-05T09:27:40.842853Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-09-05T09:27:40.881982Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-09-05T09:27:40.956218Z 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: 1927f53f-6b69-11ef-97fd-08002739e9dc.
2024-09-05T09:27:40.959594Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-09-05T09:27:42.467829Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-09-05T09:27:42.467842Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-09-05T09:27:42.470754Z 0 [Warning] CA certificate ca.pem is self signed.
2024-09-05T09:27:42.873013Z 1 [Note] A temporary password is generated for root@localhost: 5msHOfOrlq:&
[root@prdb19 mysqlb]# 


--启动连个mysql实例
-bash-4.2$ ps -ef|grep mysql
mysql     3590     1  0 17:26 pts/0    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql     3819  3590  0 17:26 pts/0    00:00:00 /u01/mysqla/bin/mysqld --basedir=/u01/mysqla --datadir=/u01/mysql1/data --plugin-dir=/u01/mysqla/lib/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/tmp/mysql1.sock --port=3306
root      5265  4621  0 17:28 pts/0    00:00:00 su - mysql
mysql     5272  5265  0 17:28 pts/0    00:00:00 -bash
mysql     6787  5272  0 17:29 pts/0    00:00:00 vim my.cnf
mysql    11352  5272  0 17:33 pts/0    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
mysql    11574 11352  5 17:33 pts/0    00:00:00 /u01/mysqlb/bin/mysqld --basedir=/u01/mysqlb --datadir=/u01/mysql2/data --plugin-dir=/u01/mysqlb/lib/plugin --log-error=/var/log/mariadb/mariadb2.log --pid-file=/var/run/mariadb/mariadb2.pid --socket=/tmp/mysql2.sock --port=3307
mysql    11671  5272  0 17:33 pts/0    00:00:00 ps -ef
mysql    11672  5272  0 17:33 pts/0    00:00:00 grep --color=auto mysql

--修改密码
set password for root@localhost = password('123456');

--修改/etc/my.cnf 文件
cp /etc/my.cnf /u01/mysqla/
cp /etc/my.cnf /u01/mysqlb/
mv /etc/my.cnf /etc/my.cnfbk



[root@prdb19 ~]# more /u01/mysqlb/my.cnf |grep -v "#"
[mysqld_safe]
log-error=/var/log/mariadb/mariadb2.log
pid-file=/var/run/mariadb/mariadb2.pid

[mysqld]
!includedir /etc/my.cnf.d
basedir = /u01/mysqlb
datadir = /u01/mysql2/data
port = 3307
socket = /tmp/mysql2.sock
log-error=/var/log/mariadb/mariadb2.log
pid-file=/var/run/mariadb/mariadb2.pid
server-id = 2
log-bin = /u01/mysql2/data/mysql-bin
binlog_format = row
sync_binlog = 1
performance_schema = off
[root@prdb19 ~]# 


[root@prdb19 ~]# more /u01/mysqla/my.cnf |grep -v "#"
[mysqld_safe]
log-error=/var/log/mariadb/mariadb1.log
pid-file=/var/run/mariadb/mariadb1.pid

[mysqld]
!includedir /etc/my.cnf.d
basedir = /u01/mysqla
datadir = /u01/mysql1/data
port = 3306
socket = /tmp/mysql1.sock
server-id= 1 
log_bin = mysql-bin
binlog_format = row
sync_binlog = 1
performance_schema = off
expire_logs_days=100
[root@prdb19 ~]# 

 常用维护命令；

## stop mysql
ps -ef|grep mysqld|grep -v grep|awk '{print $2}'|xargs kill -9

##start mysql
su - mysql -c 'cd /u01/mysqla;/u01/mysqla/bin/mysqld_safe --user=mysql &'
su - mysql -c 'cd /u01/mysqlb;/u01/mysqlb/bin/mysqld_safe --user=mysql &'

##
su - mysql -c 'cd /u01/mysqla;bin/mysql --socket=/tmp/mysql1.sock -u root -p123456'
su - mysql -c 'cd /u01/mysqlb;bin/mysql --socket=/tmp/mysql2.sock -u root -p123456'