MySQL is a widely-used RDBMS known for its scalability, cross-platform compatibility, and robust features. As a key component of the LAMP stack, MySQL facilitates the efficient storage and retrieval of data through structured tables, supporting SQL for data manipulation.
MySQL is a popular choice for web development anging from small projects to large-scale enterprise applications and used in this project because: 1. It has a strong focus on security. 2. Ease of use. 3. Because of its thriving community.
First things first, let’s get MySQL installed on your server: in this project two servers in used.
Example:
ubuntu@229-web-01:~$ mysql --version
mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64) using EditLine wrapper
ubuntu@229-web-01:~$
In order for us to verify that your servers are properly configured, we need you to create a user and password for both MySQL databases
Example
ubuntu@229-web-01:~$ mysql -uholberton_user -p -e "SHOW GRANTS FOR 'holberton_user'@'localhost'"
Enter password:
+-----------------------------------------------------------------+
| Grants for holberton_user@localhost |
+-----------------------------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO 'holberton_user'@'localhost' |
+-----------------------------------------------------------------+
ubuntu@229-web-01:~$
In order for you to set up replication, you’ll need to have a database with at least one table and one row in your primary MySQL server (web-01) to replicate from.
Example
ubuntu@229-web-01:~$ mysql -uholberton_user -p -e "use tyrell_corp; select * from nexus6"
Enter password:
+----+-------+
| id | name |
+----+-------+
| 1 | Leon |
+----+-------+
ubuntu@229-web-01:~$
Before you get started with your primary-replica synchronization, you need one more thing in place. On your primary MySQL server (web-01), create a new user for the replica server.
Example
ubuntu@229-web-01:~$ mysql -uholberton_user -p -e 'SELECT user, Repl_slave_priv FROM mysql.user'
+------------------+-----------------+
| user | Repl_slave_priv |
+------------------+-----------------+
| root | Y |
| mysql.session | N |
| mysql.sys | N |
| debian-sys-maint | Y |
| holberton_user | N |
| replica_user | Y |
+------------------+-----------------+
ubuntu@229-web-01:~$
Having a replica member on for your MySQL database has 2 advantages: - Redundancy: If you lose one of the database servers, you will still have another working one and a copy of your data - Load distribution: You can split the read operations between the 2 servers, reducing the load on the primary member and improving query response speed
- Host primarily MySQL on your first server - do not use the bind-address, just comment out this parameter (Must)
- Host MySQL replica on your second server.
- Setup replication for the MySQL database named tyrell_corp
Tip
- Once MySQL replication is setup, add a new record in your table via MySQL on server 1 and check if the record has been replicated in MySQL server 2. If you see it, it means your replication is working!
- Make sure that UFW is allowing connections on port 3306 (default MySQL port) otherwise replication will not work.
**SERVER ONE**
ubuntu@web-01:~$ mysql -uholberton_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1467
Server version: 5.5.49-0ubuntu0.14.04.1-log (Ubuntu)
Copyright (c) 2000, 2016, 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 master status;
+------------------+----------+--------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------------+------------------+
| mysql-bin.000009 | 107 | tyrell_corp | |
+------------------+----------+--------------------+------------------+
1 row in set (0.00 sec)
mysql>
**SERVER TWO**
root@web-02:/home/ubuntu# mysql -uholberton_user -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.5.49-0ubuntu0.14.04.1-log (Ubuntu)
Copyright (c) 2000, 2016, 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 slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 158.69.68.78
Master_User: replica_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000022
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000009
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: 107
Relay_Log_Space: 452
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
1 row in set (0.00 sec)
mysql>
END