环境准备
CentOS7
cat /etc/os-release
NAME="CentOS Linux"
VERSION="7 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="7"
PRETTY_NAME="CentOS Linux 7 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:7"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"
CENTOS_MANTISBT_PROJECT="CentOS-7"
CENTOS_MANTISBT_PROJECT_VERSION="7"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="7"
MySQL5.7.28
配置Ip,hostname
IP:192.168.123.70/24
IP:192.168.123.71/24
IP:192.168.123.72/24
[user@localhost ~]$ ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eno16777984: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP qlen 1000
link/ether 00:50:56:91:56:46 brd ff:ff:ff:ff:ff:ff
inet 192.168.123.70/24 brd 192.168.123.255 scope global eno16777984
valid_lft forever preferred_lft forever
inet6 fe80::250:56ff:fe91:5646/64 scope link
valid_lft forever preferred_lft forever
hostname:db01
hostname:db02
hostname:db03
[root@localhost user]# uname -a
Linux db01 3.10.0-229.el7.x86_64 #1 SMP Fri Mar 6 11:36:42 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux
修改主机名(root用户才有写的权限):
[root@localhost user]# vi /etc/hostname
清理历史环境
[root@db01 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.41-2.el7_0.x86_64
[root@db01 ~]# yum remove mariadb-libs -y
创建用户和组
[root@db01 ~]# useradd mysql -s /sbin/nologin
[root@db01 ~]# id mysql
uid=1001(mysql) gid=1001(mysql) 组=1001(mysql)
创建相关目录
-
创建软件目录
-
创建数据目录
-
创建日志目录
mkdir -p /app/database/ mkdir -p /data/3306/ mkdir -p /binlog/3306/
设置权限
chown -R mysql.mysql /app/ /data/ /binlog
上传并解压MySQL软件
tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.30-linux-glibc2.12-x86_64 mysql
设置环境变量
[root@db01 database]# vi /etc/profile
#添加一行
export PATH=/app/database/mysql/bin:$PATH
[root@db01 database]# source /etc/profile
[root@db01 database]# mysql -V
mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) u sing EditLine wrapper
初始化系统库表
[root@db01 database]# mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/
2020-05-19T06:58:05.679573Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-19T06:58:06.351750Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-05-19T06:58:06.424756Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-05-19T06:58:06.484401Z 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: 1768bc0d-999e-11ea-83d2-005056915646.
2020-05-19T06:58:06.485660Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-05-19T06:58:08.245682Z 0 [Warning] CA certificate ca.pem is self signed.
2020-05-19T06:58:08.411215Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
可能的报错
mysqld:error while loading shared libraries: libaio.so.1:
connot open shared object file:No such file or directory
#解决
yum install -y libaio-devel
#重新初始化系统库表
mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/
#重新初始化可能遇到的错误
[ERROR] --initialize specified but the data directory has files in it.
#解决
data directory has files in it 删了data/3306下的文件
配置文件设置
主节点配置
[root@db01 database]# cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/app/database/mysql
datadir=/data/3306
server_id=6
port=3306
socket=/tmp/mysql.sock
log-bin=mysql-bin-6
binlog-format=row
[mysql]
socket=/tmp/mysql.sock
从节点配置
[mysqld]
user=mysql
basedir=/app/database/mysql
datadir=/data/3306
server_id=7
port=3306
socket=/tmp/mysql.sock
relay-log=mysql-relay-7
[mysql]
socket=/tmp/mysql.sock
[mysqld]
user=mysql
basedir=/app/database/mysql
datadir=/data/3306
server_id=8
port=3306
socket=/tmp/mysql.sock
relay-log=mysql-relay-8
[mysql]
socket=/tmp/mysql.sock
重启MySQL服务生效配置文件
sudo service mysql restart
准备MySQL启动脚本
cd /app/database/mysql/support-files/
#拷贝mysql的启动脚本至系统软件管理目录中
cp mysql.server /etc/init.d/mysqld
service mysqld start
chkconfig --add mysqld
service mysqld stop
sytemctl start mysqld
主从复制
关闭防火墙
[root@db01 etc]# firewall-cmd --state
running
[root@db01 etc]# systemctl stop firewalld.service
启动实例
sytemctl start mysqld
检查信息
server_id
[root@db01 etc]# mysql -S /tmp/mysql.sock -e "select @@server_id";
+-------------+
| @@server_id |
+-------------+
| 6 |
+-------------+
[root@db02 ~]# mysql -S /tmp/mysql.sock -e "select @@server_id";
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
[root@db03 ~]# mysql -S /tmp/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 8 |
+-------------+
或者在mysql终端执行(以下mysql -S /tmp/mysql.sock -e ""同理)
select @@server_id
主库binlog
[root@db01 ~]# mysql -S /tmp/mysql.sock -e "select @@log_bin";
+-----------+
| @@log_bin |
+-----------+
| 1 |
+-----------+
主库建立复制用户
[root@db01 ~]# mysql -S /tmp/mysql.sock -e "grant replication slave,replication client on *.*to'repl'@'%' identified by '123456'";
[root@db01 ~]# mysql -S /tmp/mysql.sock -e "select user,host from mysql.user"; +---------------+-----------+
| user | host |
+---------------+-----------+
| repl | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
查看主库状态
重点查看二进制文件(File: mysql-bin-6.000004)和起点位置(Position: 154)
[root@db01 ~]# mysql -S /tmp/mysql.sock -e "show master status \G"
*************************** 1. row ***************************
File: mysql-bin-6.000005
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
告知从节点复制信息
[root@db02 ~]# mysql -S /tmp/mysql.sock
mysql> change master to
> master_host='192.168.123.70',
> master_port=3306,
> master_user='repl',
> master_password='123456',
> master_log_file='mysql-bin-6.000005',
> master_log_pos=154;
>start slave;
[root@db03 ~]# mysql -S /tmp/mysql.sock
mysql> change master to
> master_host='192.168.123.70',
> master_port=3306,
> master_user='repl',
> master_password='123456',
> master_log_file='mysql-bin-6.000005',
> master_log_pos=154;
>start slave;
查看主从状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
若不为Yes看下面的错误信息提示。
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.123.70
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: mysql-bin-6.000005
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-7.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin-6.000005
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: 154
Relay_Log_Space: 527
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: 6
Master_UUID: 1768bc0d-999e-11ea-83d2-005056915646
Master_Info_File: /data/3306/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave 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:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version: