virtualserversvps.com<\/a>\u2014a 2 GB RAM instance is sufficient for learning and light workloads.<\/p>\n\nStep 1: Install and Configure MySQL<\/h2>\n\n
If MySQL is not already running, install it and secure the installation:<\/p>\n\n
sudo apt update\nsudo apt install mysql-server -y\nsudo mysql_secure_installation<\/code><\/pre>\n\nFollow the prompts to set a root password and remove anonymous users. Then edit the MySQL configuration file:<\/p>\n\n
sudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code><\/pre>\n\nFind the [mysqld]<\/code> section and add or modify these lines:<\/p>\n\nserver-id = 1\nlog_bin = \/var\/log\/mysql\/mysql-bin.log\nbinlog_do_db = your_database_name\nbind-address = 127.0.0.1<\/code><\/pre>\n\nReplace your_database_name<\/code> with the actual database you want to replicate. The bind-address<\/code> stays local because both master and slave run on the same host. Restart MySQL:<\/p>\n\nsudo systemctl restart mysql<\/code><\/pre>\n\nStep 2: Create a Replication User<\/h2>\n\n
Log into MySQL as root:<\/p>\n\n
sudo mysql -u root -p<\/code><\/pre>\n\nCreate a user specifically for replication:<\/p>\n\n
CREATE USER 'replicator'@'127.0.0.1' IDENTIFIED BY 'strong_password';\nGRANT REPLICATION SLAVE ON *.* TO 'replicator'@'127.0.0.1';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\nNote the master status\u2014you will need these coordinates for the slave:<\/p>\n\n
SHOW MASTER STATUS;<\/code><\/pre>\n\nWrite down the File and Position values from the output.<\/p>\n\n
Step 3: Configure the Slave Instance<\/h2>\n\n
On a single VPS, the slave runs as a separate MySQL instance on a different port. The cleanest approach is to use MySQL’s multi-source or run a second MySQL process with a separate data directory and config file. For simplicity, we will use Docker to run the slave instance on port 3307 while the master runs natively on 3306.<\/p>\n\n
Install Docker if not already present:<\/p>\n\n
sudo apt install docker.io -y\nsudo systemctl start docker<\/code><\/pre>\n\nRun the slave container:<\/p>\n\n
sudo docker run -d \\\n --name mysql-slave \\\n -e MYSQL_ROOT_PASSWORD=slave_root_pass \\\n -p 127.0.0.1:3307:3306 \\\n mysql:8.0<\/code><\/pre>\n\nNow configure the slave to connect to the master. Connect to the slave container:<\/p>\n\n
sudo docker exec -it mysql-slave mysql -u root -p<\/code><\/pre>\n\nRun the following, replacing the MASTER_LOG_FILE and MASTER_LOG_POS with the values from Step 2:<\/p>\n\n
CHANGE MASTER TO\n MASTER_HOST='127.0.0.1',\n MASTER_PORT=3306,\n MASTER_USER='replicator',\n MASTER_PASSWORD='strong_password',\n MASTER_LOG_FILE='mysql-bin.000001',\n MASTER_LOG_POS=1234;\n\nSTART SLAVE;<\/code><\/pre>\n\nCheck the slave status:<\/p>\n\n
SHOW SLAVE STATUS\\G<\/code><\/pre>\n\nLook for Slave_IO_Running: Yes<\/code> and Slave_SQL_Running: Yes<\/code>. If either says No<\/code>, check the Last_IO_Error<\/code> or Last_SQL_Error<\/code> field for details.<\/p>\n\nStep 4: Test Replication<\/h2>\n\n
On the master (port 3306), create a test database and a table:<\/p>\n\n
CREATE DATABASE test_repl;\nUSE test_repl;\nCREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));\nINSERT INTO users VALUES (1, 'Alice');<\/code><\/pre>\n\nOn the slave (port 3307), verify the data arrived:<\/p>\n\n
USE test_repl;\nSELECT * FROM users;<\/code><\/pre>\n\nYou should see the row with Alice. If you do, replication is working. You can now use the slave for read-only queries, backups, or testing without affecting the master.<\/p>\n\n