How to Configure MySQL 8.0 Replication on Ubuntu 22.04 LTS Server – 2023

This video is to help you Configure MySQL 8.0 Community Server Replication on Ubuntu 22.04.

PLEASE SUBSCRIBE 🙂
PLEASE HIT LIKE IF IT HELPED 🙂

MySQL is an open-source relational database management system, that has been adopted widely. Some Infrastructure setups, require that you have a read-only database server,for reading operations like SELECT statements.

The MySQL replication process allows you to maintain multiple copies of MySQL data. All data in the source server will be synced to the replica server automatically. If your source server fails, you can promote a Replica to a Source for commit operations.

Another usage of replication is to spread read and write workloads across multiple servers for easy scalability.

You will learn how to set up a two-node MySQL 8 replication on Ubuntu 22.04.
You can implement this setup in the production environment to perform live copies of your databases.

GIVE SUPPORT – https://www.patreon.com/lazysysad
BUY ME A COFFEE – https://www.buymeacoffee.com/lazysysad
PAYPAL – https://www.paypal.com/donate/?hosted_button_id=K4RQ3LAWHGVS6

Below are the steps used:
SOURCE
1. sudo ufw allow from 192.168.0.202 to any port 3306
2. sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
innodb_flush_log_at_trx_commit=1
sync_binlog=1
3. sudo systemctl restart mysql.service
4. mysql -u root -p
5. CREATE USER replication_user@192.168.0.202 IDENTIFIED WITH mysql_native_password BY ‘StrongPassw0rd!’;
6. GRANT REPLICATION SLAVE ON *.* to replication_user@192.168.0.202;
7. FLUSH PRIVILEGES;
8. SHOW GRANTS FOR replication_user@192.168.0.202;

REPLICA
1. sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address = 0.0.0.0
server-id = 2
log-bin = /var/log/mysql/mysql-bin.log
report_host = mysql-replica
read_only=1
2. sudo systemctl restart mysql.service

SOURCE:
1. SHOW MASTER STATUS G

REPLICA:
1. mysql -u root -p
CHANGE MASTER TO MASTER_HOST=’192.168.0.50′,
MASTER_USER=’replication_user’,
MASTER_PASSWORD=’StrongPassw0rd!’,
MASTER_LOG_FILE=’mysql-bin.000001′,
MASTER_LOG_POS=875;
2. START SLAVE;
3. SHOW SLAVE STATUS G

SOURCE:
1. SHOW REPLICAS;
2. CREATE DATABASE test_replica_db;
3. SHOW DATABASES;

REPLICA:
1. SHOW DATABASES;

LINKS:
How to Install Ubuntu 22.04 LTS Server on VirtualBox in Windows 10 – https://youtu.be/tEfewxvysGk
How to Install MySQL 8.0 Database Server on Ubuntu 22.04 LTS Server – https://youtu.be/M0o0rJJlLJg

Chapters:
00:00 Intro
01:46 Update Server UFW Rules
02:04 Configure the Source Server
03:39 Create Replication User on Source Server
04:40 Configure the Replica Server
06:01 Initialize Replication
07:49 Verify Replication
08:33 Outro

Please enjoy the video and if you have any questions, leave a comment down below.

Remember to Like, Share and Subscribe if you enjoyed the video!

#ubuntu
#mysql
#database
#linux
#thelazysysadmin

source

ubuntu