mysql 8.0 主从复制_mysql 8.0 主从复制配置

背景：

主库：

192.168.211.128

从库：

192.168.211.129

一、关闭防火墙

[root@node01 ~]# systemctl disable firewalld

[root@node01 ~]# systemctl stop firewalld

[root@node01 ~]# 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)

二、关闭selinux

[root@node01 ~]# vi /etc/selinux/config

SELINUX=disabled

[root@node01 ~]# getenforce

Disabled

临时关闭selinux

[root@node01 ~]# setenforce 0

setenforce: SELinux is disabled

三、卸载centos自带数据库

[root@node01 ~]# rpm -qa | grep mariadb | xargs -i yum remove -y {}

Loaded plugins: fastestmirror

Resolving Dependencies

--> Running transaction check

---> Package mariadb-libs.x86_64 1:5.5.56-2.el7 will be erased

--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64

--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-6.el7.x86_64

--> Running transaction check

---> Package postfix.x86_64 2:2.10.1-6.el7 will be erased

--> Finished Dependency Resolution

Dependencies Resolved

=======================================================================================

Package Arch Version Repository Size

=======================================================================================

Removing:

mariadb-libs x86_64 1:5.5.56-2.el7 @anaconda 4.4 M

Removing for dependencies:

postfix x86_64 2:2.10.1-6.el7 @anaconda 12 M

Transaction Summary

=======================================================================================

Remove 1 Package (+1 Dependent package)

Installed size: 17 M

Downloading packages:

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Erasing : 2:postfix-2.10.1-6.el7.x86_64 1/2

Erasing : 1:mariadb-libs-5.5.56-2.el7.x86_64 2/2

Verifying : 1:mariadb-libs-5.5.56-2.el7.x86_64 1/2

Verifying : 2:postfix-2.10.1-6.el7.x86_64 2/2

Removed:

mariadb-libs.x86_64 1:5.5.56-2.el7

Dependency Removed:

postfix.x86_64 2:2.10.1-6.el7

Complete!

[root@node01 ~]# rpm -qa | grep mariadb

[root@node01 ~]#

四、添加用户组及用户

[root@node01 ~]# groupadd mysql

[root@node01 ~]# useradd -g mysql mysql

[root@node01 ~]# id mysql

uid=1000(mysql) gid=1000(mysql) groups=1000(mysql)

[root@node01 ~]# passwd mysql

Changing password for user mysql.

New password:

BAD PASSWORD: The password is shorter than 8 characters

Retype new password:

passwd: all authentication tokens updated successfully.

[root@node01 ~]#

五、创建mysql相关目录

[root@node01 ~]# mkdir -p /data/mysql3306/data

[root@node01 ~]# mkdir -p /data/mysql3306/binlog

[root@node01 ~]# chown -R mysql.mysql /data*

六、下载mysql8.0安装包，上传并解压

下载地址：

https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.18-linux-glibc2.12-x86_64.tar

解压mysql安装包:

[mysql@node01 ~]$ tar -xvf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz

七、创建mysql安装包软连接

[root@node01 data]# ln -s /data/mysql-8.0.18-linux-glibc2.12-x86_64 /usr/local/mysql

[root@node01 data]# chown -R mysql.mysql /usr/local/mysql*

八、创建my.cnf参数文件

[root@node01 data]# vi /etc/my.cnf

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld]

port=3306

user=mysql

server_id=1

socket=/tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mysql3306/data

log-error=/data/mysql3306/data/error.log

log_bin=/data/mysql3306/binlog/mysql-bin

九、配置用户环境变量

[mysql@node01 ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

export MYSQL_HOME=/usr/local/mysql

export PATH=$PATH:$MYSQL_HOME/bin

[root@node02 ~]# vi /etc/profile

export MYSQL_HOME=/usr/local/mysql

export PATH=$PATH:$MYSQL_HOME/bin

十、初始化mysql数据库

[mysql@node01 ~]$ mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3306/data

十一、添加mysql服务

[root@node01 data]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

十二、启动mysql数据库、登录数据库

[mysql@node01 ~]$ /etc/init.d/mysqld start

Starting MySQL.. SUCCESS!

[mysql@node01 ~]$ mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

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> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql>

十三、配置mysql数据库自动启动

[root@node01 data]# chkconfig --add mysqld

[root@node01 data]# chkconfig --list

Note: This output shows SysV services only and does not include native

systemd services. SysV configuration data might be overridden by native

systemd configuration.

If you want to list systemd services use 'systemctl list-unit-files'.

To see services enabled on particular target use

'systemctl list-dependencies [target]'.

mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off

netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off

network 0:off 1:off 2:on 3:on 4:on 5:on 6:off

十四、从库配置跟主库一致，只是参数文件不一样

[root@node02 ~]# cat /etc/my.cnf

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld]

port=3306

user=mysql

server_id=2

socket=/tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mysql3306/data

log-error=/data/mysql3306/data/error.log

log_bin=/data/mysql3306/binlog/mysql-bin

十五、主库创建用户repl@%并授权

mysql> create user 'repl'@'192.168.211.%' identified by 'mysql';

Query OK, 0 rows affected (0.31 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.211.%';

Query OK, 0 rows affected (0.05 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

十六、关闭主从数据库

[root@node01 data]# /etc/init.d/mysqld stop

Shutting down MySQL... SUCCESS!

[root@node02 ~]# /etc/init.d/mysqld stop

Shutting down MySQL. SUCCESS!

十七、修改主从库参数文件

主库：

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld]

port=3306

user=mysql

server_id=1

socket=/tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mysql3306/data

log-error=/data/mysql3306/data/error.log

log_bin=/data/mysql3306/binlog/mysql-bin

gtid_mode=ON

enforce_gtid_consistency=ON

log_slave_updates=1

从库：

[client]

port=3306

socket=/tmp/mysql.sock

[mysqld]

port=3306

user=mysql

server_id=2

socket=/tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mysql3306/data

log-error=/data/mysql3306/data/error.log

log_bin=/data/mysql3306/binlog/mysql-bin

gtid_mode=ON

enforce_gtid_consistency=ON

log_slave_updates=1

十八、开启数据库

[root@node01 data]# /etc/init.d/mysqld start

Starting MySQL... SUCCESS!

[root@node02 ~]# /etc/init.d/mysqld start

Starting MySQL.. SUCCESS!

十九、从库开启主从复制

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.211.128',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='mysql',

-> MASTER_PORT=3306,

-> MASTER_AUTO_POSITION=1;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.211.128

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 155

Relay_Log_File: node02-relay-bin.000002

Relay_Log_Pos: 369

Relay_Master_Log_File: mysql-bin.000001

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: 155

Relay_Log_Space: 578

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: 1

Master_UUID: d282ef12-ff38-11ea-bcf5-000c291cc010

Master_Info_File: mysql.slave_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: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

Master_public_key_path:

Get_master_public_key: 0

Network_Namespace:

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>