MySQL 8 replication安裝 (ubuntu 20.4)

https://j7.lb168.tw/2020/12/ubuntu-install-mysql-8-0-mysql-community-server/

https://dev.mysql.com/downloads/repo/apt/

wget https://repo.mysql.com//mysql-apt-config_0.8.19-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.19-1_all.deb
sudo apt update
sudo apt install mysql-community-client mysql-community-server

mysql -u root -p [ENTER]
=> 再輸入root密碼
show databases;

mysql -u root -p
USE `mysql`

-- 1.建資料庫 - portaldb
CREATE DATABASE `portaldb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- 2.權限
CREATE USER 'portalap'@'%' IDENTIFIED BY '!pwd.168!';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON `portaldb`.* to 'portalap'@'%';
--DROP USER 'k8sap'@'%'
--Mysql 8已不支援此指令
--GRANT ALL PRIVILEGES ON `portaldb`.* TO 'k8sap'@'%' IDENTIFIED BY '!pwd.168!' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

mysql -u root -p
USE `mysql`

-- 1.建資料庫 - datadb
CREATE DATABASE `datadb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

-- 2.權限
CREATE USER 'dataap'@'%' IDENTIFIED BY '!pwd.168!';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON `datadb`.* to 'dataap'@'%';
--DROP USER 'dataap'@'%'
--Mysql 8已不支援此指令
--GRANT ALL PRIVILEGES ON `datadb`.* TO 'dataap'@'%' IDENTIFIED BY '!pwd.168!' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

https://blog.toright.com/posts/5062/mysql-replication-主從式架構設定教學.html

https://www.itread01.com/content/1547349681.html

3-1.建repl user

#master
USE mysql;
CREATE USER 'replication'@'%' IDENTIFIED WITH 'mysql_native_password' BY '!pwd.168!';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

select user,host,plugin,authentication_string from user;

3-2. 設定master/slave my.cnf

? /etc/mysql/my.cnf
#master
sudo vi /etc/my.cnf

新增
[mysqld]
log-bin=mysql-bin
server-id=1

sudo systemctl restart mysql

#slave
sudo vi /etc/my.cnf

新增
[mysqld]
server-id=2

sudo systemctl restart mysql

3-3.設定slave連master參數

#master
SHOW MASTER STATUS;

#slave
CHANGE MASTER TO
MASTER_HOST='172.24.42.71',
MASTER_USER='replication',
MASTER_PASSWORD='!pwd.168!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=3062;

SHOW SLAVE STATUS;

3-4.啟動replication

#slave
START SLAVE;
SHOW SLAVE STATUS;

註: 測試機裝node2，不建cluster (root password: 8888)