Let's look at how to set up replication in MySQL manually.
Complete the following steps on the primary:
sudo ufw allow from any to any port 3306
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# bind-address = primary_ip (comment out!)
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = chat
/etc/init.d/mysql restart
mysql -u root
mysql> CREATE USER 'replication'@'replica_server_ip_addr' IDENTIFIED WITH mysql_native_password BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'replica_server_ip';
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
mysql> exit
After setting up the primary, you can run the following steps on your replica. Make sure to replace the placeholder values shown in these steps with the actual values based on your environment!
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = chat
relay-log = /var/log/mysql/relay.log
/etc/init.d/mysql restart
mysql -u root
mysql> CHANGE REPLICATION SOURCE TO SOURCE_USER='replication', SOURCE_PASSWORD='password', SOURCE_HOST='source_ip_address', SOURCE_LOG_FILE='binlog_file_name', SOURCE_LOG_POS=log_position;
mysql> START REPLICA;
Now that things are configured, you can run the following commands in the primary and check the replica to see if the values have been adequately replicated.
mysql> CREATE DATABASE chat;
mysql> USE chat;
mysql> CREATE TABLE message (
id INT AUTO_INCREMENT NOT NULL,
message varchar(256),
PRIMARY KEY(id));
mysql> INSERT INTO message (message) values ('hello there!');
If you use a database on the Scaler Pro plan in PlanetScale, you get replication set up automatically! Each Scaler Pro database is configured with one primary and two replicas by default. PlanetScale can even automatically detect issues with a primary and do automatic failover to one of the replica servers.
