二进制安装mysql8.0

二进制安装+主从复制搭建–mysql8.0

1.安装包下载

https://downloads.mysql.com/archives/community/

[root@db01 ~]# cd /opt/
[root@db01 opt]# ls
mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz

###2.卸载自带mariadb

[root@db01 ~]#  rpm -qa | grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
[root@db01 ~]# yum remove mariadb-libs-5.5.56-2.el7.x86_64

3.解压缩并移动到/usr/local/mysqlls

cd /opt
tar -xf mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz 
mv mysql-8.0.24-linux-glibc2.12-x86_64 /usr/local/mysql

4.创建mysql用户和组

groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql

-r 建立系统账号
- g 指定用户所属的群组
-s 指定用户登入后所使用的shell

5.创建数据目录，日志目录，配置文件目录 ，变更权限

mkdir /usr/local/mysql/data -p
mkdir /usr/local/mysql/etc -p
mkdir /usr/local/mysql/log -p
chown -R mysql:mysql /usr/local/mysql/

6.编辑mysql配置文件

vim /usr/local/mysql/etc/my.cnf
[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
[mysqld]
port = 3306
mysqlx_port = 33060
mysqlx_socket = /usr/local/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysqld.pid
log-error = /usr/local/mysql/log/error.log
#这个就是用之前的身份认证插件
default-authentication-plugin = mysql_native_password
#保证日志的时间正确
log_timestamps = SYSTEM

7.初始化数据库，并查看日志

cd /usr/local/mysql
bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
tailf /usr/local/mysql/log/error.log

记住这个临时密码。后边会用到

[root@db01 ~]# tailf /usr/local/mysql/log/error.log
2021-05-20T10:59:23.734141+08:00 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.24) initializing of server in progress as process 2153
2021-05-20T10:59:23.742895+08:00 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-05-20T10:59:24.308241+08:00 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-05-20T10:59:25.585488+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Sy(77lSEg<-q

8.设置启动文件，设置环境变量

cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
/etc/init.d/mysqld start

ps -ef | grep mysql

配置环境变量

vim /etc/profile
PATH=$PATH:$HOME/bin:/usr/local/mysql/bin
source /etc/profile
[root@db01 mysql]# which mysql
/usr/local/mysql/bin/mysql

9.重置root密码

mysql -uroot -p
#重置临时密码
mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

同样的方法配置：db02，server-id不能一样。

10.授权root远程登录

show databases;
use mysql;
select host, user, authentication_string, plugin from user;
create user 'root'@'%' identified by '123456';
grant all privileges on *.* to 'root'@'%' with grant option;
select host, user, authentication_string, plugin from user;

搭建一主一从

修改配置文件

在主masterd数据库db01配置文件my.cnf新增如下：
server-id = 100
log-bin = mysql-bin
innodb-file-per-table =ON
skip_name_resolve=ON

vim /usr/local/mysql/etc/my.cnf

在从slave-db02配置文件新增如下：
relay-log=relay-log1
relay-log-index=relay-log.index
server-id = 105
innodb_file_per_table=ON
skip_name_resolve=ON


重启数据库
/etc/init.d/mysqld restart

主库创建复制账户
create user 'repl'@'192.168.31.%' identified with mysql_native_password by '123456';
grant replication slave on *.* to repl@'192.168.31.%';
flush privileges;

删除用户
delete from mysql.user where user='repl' and host='192.168.31.%';



获取主节点当前binary log文件名和位置(position)
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      848 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


在slave--db02节点设置主节点参数
CHANGE MASTER TO
MASTER_HOST='192.168.31.203',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=848;

change master to master_host='94.191.88.237', master_user='wwze', master_password='wish', \
master_log_file='mysql-bin.000002', master_log_pos=885, master_connect_retry=30;

开启主从同步
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

正常如下：
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
----------------------------------------------------------------------------------------------
查看主从同步状态
show slave status\G

提示如下错误
    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
 
查看得知已经到000005了
mysql>  show binary logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 |      2687 | No        |
| mysql-bin.000002 |       179 | No        |
| mysql-bin.000003 |       179 | No        |
| mysql-bin.000004 |       179 | No        |
| mysql-bin.000005 |       156 | No        |
+------------------+-----------+-----------+
5 rows in set (0.00 sec)

mysql> show variables like "log_bin";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

master配置文件

[mysql]
port=3306
socket=/usr/local/mysql/data/mysql.sock
[mysqld]
port=3306
mysqlx_port=33060
mysqlx_socket=/usr/local/mysql/data/mysqlx.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
pid-file=/usr/local/mysql/data/mysqld.pid
log-error=/usr/local/mysql/log/error.log
default-authentication-plugin=mysql_native_password
log_timestamps=SYSTEM
server-id=100
log-bin=mysql-bin
innodb-file-per-table=ON
skip_name_resolve=ON

slave配置文件

[mysql]
port=3306
socket=/usr/local/mysql/data/mysql.sock
[mysqld]
port=3306
mysqlx_port=33060
mysqlx_socket=/usr/local/mysql/data/mysqlx.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
pid-file=/usr/local/mysql/data/mysqld.pid
log-error=/usr/local/mysql/log/error.log
default-authentication-plugin=mysql_native_password
log_timestamps=SYSTEM
relay-log=relay-log1
relay-log-index=relay-log.index
server-id=105
innodb_file_per_table=ON
skip_name_resolve=ON

/local/mysql/data/mysqld.pid
log-error=/usr/local/mysql/log/error.log
default-authentication-plugin=mysql_native_password
log_timestamps=SYSTEM
relay-log=relay-log1
relay-log-index=relay-log.index
server-id=105
innodb_file_per_table=ON
skip_name_resolve=ON